Dot Net - Assembly: December 2012

December 31, 2012

C# - SQL Helper Class

Below I have mentioned my SQL Helper class that is used to communicate SQL in better way.

using System;

using System.Data;

using System.Data.SqlClient;

namespace Utility

    public static class SqlHelper

        #region "FILL DATA TABLE"

        public static void Fill(DataTable dataTable, String procedureName)

            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());

            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);

            oCommand.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;

            oConnection.Open();

            using (SqlTransaction oTransaction = oConnection.BeginTransaction())

try

                    oAdapter.SelectCommand.Transaction = oTransaction;

                    oAdapter.Fill(dataTable);

                    oTransaction.Commit();

                catch

                    oTransaction.Rollback();

                    throw;

                finally

                    if (oConnection.State == ConnectionState.Open)

                        oConnection.Close();

                    oConnection.Dispose();

                    oAdapter.Dispose();

        public static void Fill(DataTable dataTable, String procedureName, SqlParameter[] parameters)

            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());

            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);

            oCommand.CommandType = CommandType.StoredProcedure;

            if (parameters != null)

                oCommand.Parameters.AddRange(parameters);

            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;

            oConnection.Open();

            using (SqlTransaction oTransaction = oConnection.BeginTransaction())

try

                    oAdapter.SelectCommand.Transaction = oTransaction;

                    oAdapter.Fill(dataTable);

                    oTransaction.Commit();

                catch

                    oTransaction.Rollback();

                    throw;

                finally

                    if (oConnection.State == ConnectionState.Open)

                        oConnection.Close();

                    oConnection.Dispose();

                    oAdapter.Dispose();

        #endregion

        #region "FILL DATASET"

        public static void Fill(DataSet dataSet, String procedureName)

            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());

            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);

            oCommand.CommandType = CommandType.StoredProcedure;

            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;

            oConnection.Open();

            using (SqlTransaction oTransaction = oConnection.BeginTransaction())

try

                    oAdapter.SelectCommand.Transaction = oTransaction;

                    oAdapter.Fill(dataSet);

                    oTransaction.Commit();

                catch

                    oTransaction.Rollback();

                    throw;

                finally

                    if (oConnection.State == ConnectionState.Open)

                        oConnection.Close();

                    oConnection.Dispose();

                    oAdapter.Dispose();

        public static void Fill(DataSet dataSet, String procedureName, SqlParameter[] parameters)

            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());

            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);

            oCommand.CommandType = CommandType.StoredProcedure;

            if (parameters != null)

                oCommand.Parameters.AddRange(parameters);

            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oAdapter.SelectCommand = oCommand;

            oConnection.Open();

            using (SqlTransaction oTransaction = oConnection.BeginTransaction())

try

                    oAdapter.SelectCommand.Transaction = oTransaction;

                    oAdapter.Fill(dataSet);

                    oTransaction.Commit();

                catch

                    oTransaction.Rollback();

                    throw;

                finally

                    if (oConnection.State == ConnectionState.Open)

                        oConnection.Close();

                    oConnection.Dispose();

                    oAdapter.Dispose();

        #endregion

        #region "EXECUTE SCALAR"

        public static object ExecuteScalar(String procedureName)

            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());

            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);

            oCommand.CommandType = CommandType.StoredProcedure;

            object oReturnValue;

            oConnection.Open();

            using (SqlTransaction oTransaction = oConnection.BeginTransaction())

try

                    oCommand.Transaction = oTransaction;

                    oReturnValue = oCommand.ExecuteScalar();

                    oTransaction.Commit();

                catch

                    oTransaction.Rollback();

                    throw;

                finally

                    if (oConnection.State == ConnectionState.Open)

                        oConnection.Close();

                    oConnection.Dispose();

                    oCommand.Dispose();

            return oReturnValue;

        public static object ExecuteScalar(String procedureName, SqlParameter[] parameters)

            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());

            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);

            oCommand.CommandType = CommandType.StoredProcedure;

            object oReturnValue;

            oConnection.Open();

            using (SqlTransaction oTransaction = oConnection.BeginTransaction())

try

                    if (parameters != null)

                        oCommand.Parameters.AddRange(parameters);

                    oCommand.Transaction = oTransaction;

                    oReturnValue = oCommand.ExecuteScalar();

                    oTransaction.Commit();

                catch

                    oTransaction.Rollback();

                    throw;

                finally

                    if (oConnection.State == ConnectionState.Open)

                        oConnection.Close();

                    oConnection.Dispose();

                    oCommand.Dispose();

            return oReturnValue;

        #endregion

        #region "EXECUTE NON QUERY"

        public static int ExecuteNonQuery(string procedureName)

            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());

            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);

            oCommand.CommandType = CommandType.StoredProcedure;

            int iReturnValue;

            oConnection.Open();

            using (SqlTransaction oTransaction = oConnection.BeginTransaction())

try

                    oCommand.Transaction = oTransaction;

                    iReturnValue = oCommand.ExecuteNonQuery();

                    oTransaction.Commit();

                catch

                    oTransaction.Rollback();

                    throw;

                finally

                    if (oConnection.State == ConnectionState.Open)

                        oConnection.Close();

                    oConnection.Dispose();

                    oCommand.Dispose();

            return iReturnValue;

        public static int ExecuteNonQuery(string procedureName, SqlParameter[] parameters)

            SqlConnection oConnection = new SqlConnection(AccessConfig.GetConnectionString());

            SqlCommand oCommand = new SqlCommand(procedureName, oConnection);

            oCommand.CommandType = CommandType.StoredProcedure;

            int iReturnValue;

            oConnection.Open();

            using (SqlTransaction oTransaction = oConnection.BeginTransaction())

try

                    if (parameters != null)

                        oCommand.Parameters.AddRange(parameters);

                    oCommand.Transaction = oTransaction;

                    iReturnValue = oCommand.ExecuteNonQuery();

                    oTransaction.Commit();

                catch

                    oTransaction.Rollback();

                    throw;

                finally

                    if (oConnection.State == ConnectionState.Open)

                        oConnection.Close();

                    oConnection.Dispose();

                    oCommand.Dispose();

            return iReturnValue;

        #endregion

How to Use:

public static int YourFunction1(int param1, int param2)

    SqlParameter[] objParameter = new SqlParameter[2];

    objParameter[0] = new SqlParameter("@param1", param1);

    objParameter[1] = new SqlParameter("@param2", param2);

    return Convert.ToInt32(Data.ExecuteScalar("YourProcedureName", objParameter));

public static int YourFunction2()

    return Data.ExecuteNonQuery("YourProcedureName");

public static void YourFunction3(int param1, int param2, int param3, ref DataTable dt)

    SqlParameter[] objParameter = new SqlParameter[3];

    objParameter[0] = new SqlParameter("@param1", param1);

    objParameter[1] = new SqlParameter("@param2", param2);

    objParameter[2] = new SqlParameter("@param3", param3);

    dt.Fill("YourProcedureName", objParameter);

Feedback are accepted.

SQL Server - Generate data script for a table, conditionally

While Data migration or Project Release, we use Generate data script frequently. For taking whole data means we can use “Tasks -> Generate Scripts” Option, provided by SQL. But if we want to generate data script for a specific data rows than, there is no option in SQL to do this.

After a little bit of googling i find out a link, that has a procedure to facilitate generate data script, But problem is we can’t generate data script conditionally (Again the same problem).

But this time we have a source, the procedure formed by dynamic query. I have added a little bit of stuff to conditional table data script Generation.

CREATE PROC [dbo].[InsertGenerator]

    @tableName            VARCHAR(100),

    @whereCondition        VARCHAR(MAX) = NULL

AS

BEGIN

    --    Declare a cursor to get col name and

    --    its data type for the specified table

    DECLARE cursCol CURSOR

    FAST_FORWARD

FOR

        SELECT

            column_name, data_type

        FROM information_schema.columns

        WHERE table_name = @tableName

    -- OPEN CURSOR

    OPEN cursCol

    DECLARE

        @string NVARCHAR(3000),        --for storing the first half of INSERT statement

        @stringData NVARCHAR(3000), --for storing the data (VALUES) related statement

        @dataType NVARCHAR(1000);    --data types returned for respective columns

    SET @string='INSERT '+@tableName+'('

    SET @stringData=''

    DECLARE @colName NVARCHAR(50)

    FETCH NEXT FROM cursCol INTO @colName,@dataType

    IF @@fetch_status <> 0

        BEGIN

            PRINT 'Table '+@tableName+' not found, processing skipped.'

            CLOSE curscol

            DEALLOCATE curscol

            RETURN

END

    WHILE @@FETCH_STATUS=0

        BEGIN

            IF @dataType in ('varchar','char','nchar','nvarchar')

                BEGIN

                    SET @stringData

                            = @stringData+''''+'''+isnull('''''+'''''+'

                              + @colName + '+'''''+''''',''NULL'')+'',''+'

END

            ELSE IF @dataType in ('text','ntext') --if the datatype is text or something else

                BEGIN

                    SET @stringData

                            = @stringData+'''''''''+isnull(cast('

                              + @colName + ' as varchar(2000)),'''')+'''''',''+'

END

            ELSE IF @dataType = 'money' --because money doesn't get converted from varchar implicitly

                BEGIN

                    SET @stringData

                            = @stringData+'''convert(money,''''''+isnull(cast('

                              + @colName + ' as varchar(200)),''0.0000'')+''''''),''+'

END

            ELSE IF @dataType='datetime'

                BEGIN

                    SET @stringData

                            = @stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'

                              + @colName +',121)+'''''+''''',''NULL'')+'',121),''+'

END

            ELSE IF @dataType='image'

                BEGIN

                    SET @stringData

                            = @stringData+'''''''''+isnull(cast(convert(varbinary,'

                              + @colName+') as varchar(6)),''0'')+'''''',''+'

END

            ELSE --presuming the data type is int,bit,numeric,decimal

            BEGIN

                SET @stringData

                            = @stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'

                              + @colName + ')+'''''+''''',''NULL'')+'',''+'

END

            SET @string= @string + @colName+','

            FETCH NEXT FROM cursCol INTO @colName,@dataType

END

    DECLARE @Query nvarchar(4000)

    SET @query

            = 'SELECT ''' + SUBSTRING(@string,0,len(@string)) + ') VALUES(''+ '

                + SUBSTRING(@stringData,0,LEN(@stringData)-2)+'''+'')'' FROM '

                + @tableName + ISNULL(' WHERE ' + @whereCondition, '')

    exec sp_executesql @query

    --select @query

    CLOSE cursCol

    DEALLOCATE cursCol

END

How to use:

USE [NorthWind]

GO

EXEC    [InsertGenerator]

        @tableName = N'Orders',

        @whereCondition = N'ShipCountry = ''USA'' AND EmployeeID = 3'

GO

December 27, 2012

InterView - Quickest Way to Find Prime Numbers

This week one of my college conducting interview for fresher candidates,
One question i have heard,

"Write a program to Find Prime.?"

Question on my mind, If that question asked on me, Am i capable to solve this in best way..?

That made the following program.

class Program

    static void Main(string[] args)

        int limit = 1000;

        PrintPrimeNos(limit);

    static void PrintPrimeNos(long limit)

        // Any number must divide with 1 and itself

        // and Even number never be a prime number (Any even no at-least can able to divide by 2 )

        // So dividend start with 3 & incremented by 2

        // This will reduce no of iterations

        for (long dividend = 3; dividend < limit; dividend = dividend + 2)

            bool IsPrimeNo = true;

            // Same as above

            // dividend / 2: Less than half of dividend only able to divide the dividend fully

            for (long divisor = 3; divisor < dividend / 2; divisor = divisor + 2)

                if (dividend % divisor == 0)

                    IsPrimeNo = false;

                    break;

            if (IsPrimeNo)

                Console.WriteLine(dividend);

        Console.ReadLine();

December 6, 2012

Interview - Quickest Way to Get nth Maximum Value From a Table

Now-a-days "Query to find 2nd Maximum value or Nth Maximum value from a table" are a common questions in interviews.

Here I'm given the best one to find nth max value from a table.
Below i mentioned 3 queries. 2 takes inbuild method, 1 Without using inbuild method

USE NorthWind

GO

WITH result AS

( SELECT DENSE_RANK() OVER(ORDER BY UnitPrice DESC) AS 'SecondMaximum',* FROM Products )

SELECT * FROM result WHERE SecondMaximum = 2;

GO

WITH result AS

( SELECT ROW_NUMBER() OVER(ORDER BY UnitPrice DESC) AS 'SecondMaximum',* FROM Products )

SELECT * FROM result WHERE SecondMaximum = 2;

GO

SELECT TOP 1 * FROM

( SELECT TOP 2 * FROM Products ORDER BY UnitPrice DESC ) result

ORDER BY UnitPrice ASC

the result is, the first one (DENSE_RANK) takes less time then remaining.

SecondMaximum value or Top value in subquery you can get nth maximum value

[ Hint: Here we can use "RANK" method instead of "DENSE_RANK" method, If the "UnitPrice" values are different. ]