Observer Design Pattern With Delegates N Event

It’s a very nice post by Code Project, how to implement

Observer Design Pattern using Delegates N Events.

Delete duplicate records from table (SQL)

March 14, 2013Arvind Kumar INTERVIEW QUESTIONS, Sql Query, SQL SERVER Leave a comment

How to delete duplicate records from sql table

1) Scenario 1 : If there is no any identity column available in table then use Common table expression

CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRcordTable
SELECT 1, 1
UNION ALL
SELECT 1, 1
UNION ALL
SELECT 1, 1
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4

— See the result what it gives
WITH CTE (COL1, COL2, DUPLICATECOUNT)
AS
(
SELECT COL1, COL2, ROW_NUMBER() OVER( PARTITION BY COL1, COL2 ORDER BY COL1) AS DUPLICATECOUNT1
FROM DUPLICATERCORDTABLE
)
SELECT * FROM CTE WHERE DUPLICATECOUNT > 1

— Delete the record

WITH CTE (COL1, COL2, DUPLICATEID )
AS
(
SELECT COL1, COL2, ROW_NUMBER() OVER(PARTITION BY COL1, COL2 ORDER BY COL1) AS DUPLICATEID
FROM DUPLICATERCORDTABLE
)
DELETE FROM CTE WHERE DUPLICATEID >1

2) Scenarion 2 : if table contains the identity column

CREATE TABLE TestTable (ID INT, NameCol VARCHAR(100))
GO
INSERT INTO TestTable (ID, NameCol)
SELECT 1, ‘First’
UNION ALL
SELECT 2, ‘Second’
UNION ALL
SELECT 3, ‘Second’
UNION ALL
SELECT 4, ‘Second’
UNION ALL
SELECT 5, ‘Second’
UNION ALL
SELECT 6, ‘Third’
GO

— Detecting Duplicate
SELECT NameCol, COUNT(*) TotalCount
FROM TestTable
GROUP BY NameCol
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC
GO
— Deleting Duplicate
DELETE
FROM TestTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM TestTable
GROUP BY NameCol
)
GO
Enjoy programming 🙂

Dynamic Query in SQL

March 12, 2013Arvind Kumar INTERVIEW QUESTIONS, Sql Query, SQL SERVER Leave a comment

Hi All,
We can use dynamic query with two ways :
Note : We must have to declare variable which will hold the dynamic query as NVarchchar/Ntext/NChar

1) First :
Declare @SQL nVarChar(1000) — N.B. string must be unicode for sp_executesql
SELECT @SQL = ‘SELECT * FROM tAction WHERE actionid = @actionId and actionname=@actionName’

Exec sp_executesql @SQL, N’@actionId nVarChar(50), @actionName nvarchar(10)’, @actionId = 2, @actionName=’update’

2) Second:
Declare @SQLtext NVarChar(1000) — N.B. string must be unicode for sp_executesql
declare @actionId int
set @actionId = 2
set @SQLtext = ‘SELECT * FROM tAction WHERE actionid = ‘ + cast( @actionId as varchar(10))
exec sp_executesql @SQLtext

Keep Programming 🙂

Differences between Stored Procedures and Functions

March 12, 2013Arvind Kumar Asp.net Programming Leave a comment

Procedure can return zero or n values whereas function can return one value which is mandatory.
Procedures can have input/output parameters for it whereas functions can have only input parameters.
Procedure allows select as well as DML statement in it whereas function allows only select statement in it.
Functions can be called from procedure whereas procedures cannot be called from function.
Exception can be handled by try-catch block in a procedure whereas try-catch block cannot be used in a function.
We can go for transaction management in procedure whereas we can’t go in function.
Procedures can not be utilized in a select statement whereas function can be embedded in a select statement.

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.
UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.
Inline UDF’s can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

In depth

Stored Procedure

A Stored Procedure is a program (or procedure) which is physically stored within a database. They are usually written in a proprietary database language like PL/SQL for Oracle database or PL/PgSQL for PostgreSQL. The advantage of a stored procedure is that when it is run, in response to a user request, it is run directly by the database engine, which usually runs on a separate database server. As such, it has direct access to the data it needs to manipulate and only needs to send its results back to the user, doing away with the overhead of communicating large amounts of data back and forth.

User-defined Function

A user-defined function is a routine that encapsulates useful logic for use in other queries. While views are limited to a single SELECT statement, user-defined functions can have multiple SELECT statements and provide more powerful logic than is possible with views.

User defined functions have three main categories:

Scalar-valued function – returns a scalar value such as an integer or a timestamp. Can be used as column name in queries.
Inline function – can contain a single SELECT statement.
Table-valued function – can contain any number of statements that populate the table variable to be returned. They become handy when you need to return a set of rows, but you can’t enclose the logic for getting this rowset in a single SELECT statement.

reference : http://www.codeproject.com/Tips/286539/Difference-between-stored-procedure-and-function

How to Change Style from Code behind in ASP.Net

January 17, 2011Arvind Kumar Asp.net Programming, C# Programming Leave a comment

Suppose you have a div in Asp.net page
like the given code
<div runat=”server” id=”divError” style=”color: Red; font-weight: bold;” />
and you have to change div font color from red to green from code behind

Code

divError.Style[“color”] = “Green”;

divError.Attributes.Add(“Style”, “color:Green; font-weight: bold;”);
Continue reading →

Set default version for visual studio

December 2, 2010Arvind Kumar .Net Framework, Asp.net Programming, C# Programming, Computers and Internet Leave a comment

Set default version for visual studio, when you have more than one version installed on you system and you want to set default one when you run from run command

Here is the solution HKEY_LOCAL_MACHINE\Software\Microsft\Windows\CurrentVersion\App Paths\devenv.exe Change the default path here Set the path like this

C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\devenv.exe – VS-2008
Or
C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\devenv.exe – VS-2010

Arvind Kumar
[9910310129]

Convert number to Roman Number

November 17, 2010Arvind Kumar .Net Framework, C# Programming Leave a comment

/// <summary>
///generate the roman numbers from integer
/// </summary>
/// <param name=”number”>interger to convert</param>
/// <returns>roman character equvalent to number</returns>
public static stringInt32ToRoman(int i)
{
    if((i <= 0) || (i > 3999))
    {
        throw newArgumentException(“Value must be in the range 1 – 3999.”);
    }
    int[] values = new int[]
    {
        1000, 900, 500, 400, 100, 90, 50, 40, 10, 9, 5, 4, 1
    };
    string[] numerals = new string[]
    {
        “M”, “CM”, “D”, “CD”, “C”, “XC”, “L”, “XL”, “X”, “IX”, “V”, “IV”, “I”
   };

    StringBuilder result = newStringBuilder();
    for(int idx = 0; idx < values.Length; idx++)
    {
        while(i >= values[idx])
        {
            i -= values[idx];
            result.Append(numerals[idx]);
        }
    }
    returnresult.ToString();
}

Output

4 = IV5 = V
10 = X40 = XL50 = L

Arvind Kumar
[9910310129]

Create alphabet series like excel

May 26, 2010Arvind Kumar C# Programming Leave a comment


using System;
namespace GenerateAlphaSeries
{
    class Program
    {
        static void Main(string[] args)
        {
            for (int i = 1; i <= 500; i++)
            {
                Console.Write(AlphaCharSeriesEncode(i) + ", ");
            }
            Console.ReadKey();
        }

        /// <summary>        
        /// Holds the alphabet character series        
        /// </summary> 
        private static char[] alphaChars
            = "aabcdefghijklmnopqrstuvwxyz".ToCharArray();

        /// <summary>         
        /// Converts integer to alphabet character series like excel row-col         
        /// </summary>         
        /// <param name="value">Int32</param>         
        /// <returns>string:value equvalent to integer. 
        /// Eg : 27=aa, 28=ab, 100=cv so on</returns>         
        private static string AlphaCharSeriesEncode(Int32 value)
        {
            string returnValue = null;
            do
            {
                returnValue = alphaChars[value % 26] + returnValue;
                value /= 26;
            }
            while (value != 0);
            return returnValue;
        }
    }
}
Output
a, b, c, d, e .............................,
aa, ab, ac, ad, ae, ----------------,
ba, bb, bc, bd, be, ---------------,
and so on.....

Arvind Kumar
[9910310129]

Talking about Abstract Factory

April 28, 2010Arvind Kumar DESIGN PATTERN Leave a comment

Abstract Factory

What is abstract factory?

The Abstract Factory Pattern provides an interface for creating families of related or dependent objects without specifying their concrete classes
A class whose sole job is to easily create and return instances of other classes.

Scenario – 1 [ ADO.NET 2.0 ]

We have a product and we sell to different clients.
Clients have different type of databases (Oracle, SQL server, Sybase, MYSql etc..) and we don’t know, client will use which database in application.

Solution

Implementation

//You have to use these namespaces
using System.Configuration;
using System.Data.Common;
 
AppSettingsReader apprdr = new AppSettingsReader();
string provider = apprdr.GetValue("provider", typeof(string)).ToString();
string connString = apprdr.GetValue("conStr", typeof(string)).ToString();
 
DbProviderFactory factory = DbProviderFactories.GetFactory(provider);
DbConnection con = factory.CreateConnection();
con.ConnectionString = connString;
 
DbCommand cmd = factory.CreateCommand(); 
cmd.CommandText = "<write your command text here>";
cmd.Connection = con;
con.Open();
cmd.ExecuteNonQuery();
con.Close();

Configuration Entries

<configuration>
  <appsettings>
    <add key="provider"
         value="System.Data.SqlClient"/>
    <add key="conStr"
         value= "DataSource=serverName; initial catalog=DbName;
         User ID=userName; Password=password" />
  </appsettings>
</configuration>

Scenario – 2 [ Pizza Store ]

want to open a Pizza store.
My pizza store will serve many types of pizza ( Veggie Pizza, Cheese pizza, Chicken pizza ) as competition with many pizza store in the market.
Now my pizza store has done so well, now every one wants a pizza store in their neighborhood. As a franchiser, I want to ensure the quality of franchise operations so I want them to use our time tested code.
But what about regional difference? Each franchise want to offer different styles of pizzas(New York, Chicago) depending on where the franchise store is located and tastea of the local pizza.

Solution

public abstract class Pizza
{
    internal String Name;
    internal string Dough;
    internal string Souce;
    public void Prepare()
    {
        Console.WriteLine("Preparing " + Name);
        Console.WriteLine("Tossing dough " + Dough);
        Console.WriteLine("Adding souce " + Souce);
    }
    public virtual void Bake()
    {
        Console.WriteLine("Bake for 25 minute");
    }
    public virtual void Cut()
    {
        Console.WriteLine("Cutting the pizza into diagonal slices.");
    }
    public virtual void Box()
    {
        Console.WriteLine("Place pizza in official Pizzastore box.");
    }
    public string GetName
    {
        get { return Name; }
    }
}
public class ChicagoChessePizza : Pizza
{
    public ChicagoChessePizza()
    {
        Name = "chichago style cheese pizza";
        Dough = "Extra Thick Crust Dough";
        Souce = "plum tomato sauce";
    }
    public override void Cut()
    {
        Console.WriteLine("Cutting the pizza into Square cut.");
    }
}
 
 
public class ChicagoVeggiePizza : Pizza
{
    public ChicagoVeggiePizza()
    {
        Name = "chichago style Veggie pizza";
        Dough = "Extra Thick Crust Dough";
        Souce = "plum tomato sauce";
    }
    public override void Cut()
    {
        Console.WriteLine("Cutting the pizza into Square cut.");
    }
}
 
public class NYChessePizza : Pizza
{
    public NYChessePizza()
    {
        Name = "New York style cheese pizza";
        Dough = "Thin Crust Dough";
        Souce = "Marinara Sauce";
    }
}
 
public class NYVeggiePizza : Pizza
{
    public NYVeggiePizza()
    {
        Name = "New York style Veggie pizza";
        Dough = "Thin Crust Dough";
        Souce = "Marinara Sauce";
    }
}
 
public abstract class PizzaStore
{
    public Pizza OrderPizza(string type)
    {
        Pizza pizza = CreatePizza(type);
        pizza.Prepare();
        pizza.Bake();
        pizza.Cut();
        pizza.Box();
        return pizza;
    }
    public abstract Pizza CreatePizza(string type);
}
 
public class ChicagoPizzaStore : PizzaStore
{
    public override Pizza CreatePizza(string type)
    {
        if (type.Equals("cheese"))
        {
            return new ChicagoChessePizza();
        }
        else if (type.Equals("veggie"))
        {
            return new ChicagoVeggiePizza();
        }
        else
        {
            return null;
        }
    }
}

Arvind Kumar
[9910310129]

Flickr Tags: Abstract factory pattern,Factory pattern,oops,pizza factory,design pattern,gang of four

LiveJournal Tags: Abstract factory pattern,Factory pattern,oops,pizza factory,design pattern,gang of four

del.icio.us Tags: Abstract factory pattern,Factory pattern,oops,pizza factory,design pattern,gang of four

Technorati Tags: Abstract factory pattern,Factory pattern,oops,pizza factory,design pattern,gang of four

Techno World

Your's Technical space

Observer Design Pattern With Delegates N Event

Delete duplicate records from table (SQL)

Dynamic Query in SQL

Differences between Stored Procedures and Functions

In depth

Stored Procedure

User-defined Function

How to Change Style from Code behind in ASP.Net

Set default version for visual studio

Convert number to Roman Number

Create alphabet series like excel

Talking about Abstract Factory