Dot Net - Assembly

January 24, 2013

SQL - TRUNCATE/DELETE/DROP tables referenced by a foreign key constraint

Download Sample

This article explains how to delete/truncate/drop without affecting schema properties (disabling constraints), even though tables have relations.

Why we need this

We can also use the below in-built method for deleting all tables.

EXEC sp_MSforeachtable @command1 = 'DELETE ?'

[But problem is: In TRUNCATE or DROP operations, SQL throws error if a table is referred in some other tables.]

If you want to drop or truncate a table that is referenced somewhere, you should get the following error.

“Could not drop object 'TableName' because it is referenced by a FOREIGN KEY constraint.”

Code

CREATE PROCEDURE udpFlushAllTablesByDBName

    @DBName            VARCHAR(200),

    @FlushType        VARCHAR(20)

AS

BEGIN

    SET NOCOUNT ON;

    IF EXISTS (SELECT TOP 1 1 FROM SYS.TABLES WHERE OBJECT_ID = OBJECT_ID('tmpTable'))

    BEGIN

        DROP TABLE tmpTable

END

    DECLARE @Query    NVARCHAR(MAX);

    SET @Query =

    'WITH EliminateUnwanted (Name,[Object_ID],parent_object_id ,referenced_object_id)  AS

        SELECT

            [T].Name,

            [T].[Object_ID],

            [F].parent_object_id,

            [F].referenced_object_id

        FROM ' + @DBName + '.SYS.TABLES [T]

        LEFT JOIN ' + @DBName + '.SYS.FOREIGN_KEYS [F] ON [T].object_id = [F].parent_object_id

        WHERE [T].Name NOT IN (''SysDiagrams'', ''tmpTable'') AND [T].TYPE = ''U''

),

    SetPriority (Name,[Object_ID],parent_object_id ,referenced_object_id, parentObjectName, Priorities)  AS

        SELECT

*,

            OBJECT_NAME(referenced_object_id) AS parentObjectName,

            CASE

                WHEN referenced_object_id IS NULL AND [Object_ID] NOT IN ( SELECT ISNULL(referenced_object_id,'''') FROM EliminateUnwanted )  THEN 1

                WHEN [Object_ID] NOT IN ( SELECT ISNULL(referenced_object_id,'''') FROM EliminateUnwanted )  THEN 2

                WHEN ([Object_ID] IN ( SELECT ISNULL(referenced_object_id,'''') FROM EliminateUnwanted ) AND parent_object_id IS NULL)  THEN 4

                ELSE 3

            END ''PRIORITY''

        FROM EliminateUnwanted

),

    DuplicateRemoval (Occurence, Name,[Object_ID],parent_object_id ,referenced_object_id, parentObjectName, Priorities)  AS

        SELECT

            ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY NAME) AS Occurence

,*

        FROM SetPriority

    SELECT

        ROW_NUMBER() OVER(ORDER BY Priorities) RowNo,

        Name,

        Object_ID,

        parent_object_id,

        referenced_object_id,

        parentObjectName,

        Priorities

    INTO tmpTable

    FROM DuplicateRemoval

    WHERE Occurence = 1'

    --SELECT @Query

    EXECUTE sp_executesql @Query

    DECLARE

        @TableName        VARCHAR(100),

        @Count            BIGINT,

        @FlushMethod    VARCHAR(30);

    SELECT @FlushMethod =  CASE

                                WHEN @FlushType = 'TRUNCATE' THEN 'TRUNCATE TABLE '

                                WHEN @FlushType = 'DROP' THEN 'DROP TABLE '

                                ELSE 'DELETE '

                            END;

    SET @Count = 1

    WHILE EXISTS(SELECT TOP 1 1 FROM tmpTable WHERE RowNo = @Count )

    BEGIN

        SELECT @TableName = NAME FROM tmpTable WHERE RowNo = @Count

        SET @Query = @FlushMethod + @DBName + '.dbo.' + @TableName

        EXECUTE sp_executesql @Query

        SET @Count = @Count + 1;

END

    DROP TABLE tmpTable

    SET NOCOUNT OFF;

END

How it functions

Get all tables from given database
Ordered by priorities

Independent tables have high priority.
Least tables from relations (child tables) have medium priority
Referenced tables (parent) have low priority

Truncate/Delete/Drop tables by priorities

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. ]

November 28, 2012

C# - Accessing Remote File By Using Impersonation

Sometime we may face “Could not create the file” or “Access denied on 'Some file Name' ” or "Unable to copy file. Access to the path is denied" error while try to access or modify a file on a remote machine. After reviewing the code you find out, that because the current user does not have access on a remote machine.

Now we want to force your application to do some restricted activity by a user that who not having privileges to do. we call this procedure Impersonation.

In General Impersonation is, A person act like another.

Unprivileged user can access remote machine like privileged one by using privileged users credentials.

We can achieve Impersonation in may ways, I`m taking "WNetCancelConnection2" function from GAC dll ("mpr.dll").

Note:
mpr.dll is a module containing functions used to handle communication between the Windows operating system and the installed network providers.

Download Here or copy and use below Remote Access Helper class

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Runtime.InteropServices;

using System.ComponentModel;

using System.Net;

namespace SomeNamespace.Utility

    public class RemoteAccessHelper

        public class NetworkConnection : IDisposable

            string _networkName;

            public NetworkConnection(string networkName, NetworkCredential credentials)

                _networkName = networkName;

                var netResource = new NetResource()

                    Scope = ResourceScope.GlobalNetwork,

                    ResourceType = ResourceType.Disk,

                    DisplayType = ResourceDisplaytype.Share,

                    RemoteName = networkName

};

                var result = WNetAddConnection2(

                    netResource,

                    credentials.Password,

                    credentials.UserName,

0);

                if (result != 0)

                    throw new Win32Exception(result, "Error connecting to remote share");

            ~NetworkConnection()

                Dispose(false);

            public void Dispose()

                Dispose(true);

                GC.SuppressFinalize(this);

            protected virtual void Dispose(bool disposing)

                WNetCancelConnection2(_networkName, 0, true);

            [DllImport("mpr.dll")]

            private static extern int WNetAddConnection2(NetResource netResource,

                string password, string username, int flags);

            [DllImport("mpr.dll")]

            private static extern int WNetCancelConnection2(string name, int flags,

                bool force);

        [StructLayout(LayoutKind.Sequential)]

        public class NetResource

            public ResourceScope Scope;

            public ResourceType ResourceType;

            public ResourceDisplaytype DisplayType;

            public int Usage;

            public string LocalName;

            public string RemoteName;

            public string Comment;

            public string Provider;

        public enum ResourceScope : int

            Connected = 1,

            GlobalNetwork,

            Remembered,

            Recent,

            Context

};

        public enum ResourceType : int

            Any = 0,

            Disk = 1,

            Print = 2,

            Reserved = 8,

        public enum ResourceDisplaytype : int

            Generic = 0x0,

            Domain = 0x01,

            Server = 0x02,

            Share = 0x03,

            File = 0x04,

            Group = 0x05,

            Network = 0x06,

            Root = 0x07,

            Shareadmin = 0x08,

            Directory = 0x09,

            Tree = 0x0a,

            Ndscontainer = 0x0b

Below i`m describing some piece of code to show, how to use RemoteAccessHelper.cs ?

var oNetworkCredential =

    new System.Net.NetworkCredential()

        Domain = "domainName",

        UserName = "domainName" + "\\" + "admin login name",

        Password = "admin password"

};

using (new RemoteAccessHelper.NetworkConnection(@"\\" + "domainName", oNetworkCredential))

    String[] sFolderNames = Directory.GetDirectories( "domainName" + "\\FolderName");

    foreach (String sFolderName in sFolderNames)

        string[] sarrZipFiles = Directory.GetFiles(sFolderName, "*.txt");

        foreach (String sFile in sarrZipFiles)

            // Some unprivileged operations

Source: Stackoverflow

November 26, 2012

Fixed - AJAX AsyncFileUpload is Not Working in IE9

Today i got a mail from client, says that "File Upload functionality not seems working."
I went to site and find out.
"AJAX AsyncFileUpload control not access-able, It was enabled false"
i knew, Problem not made by me, it from somewhere. After plugging my hair for an hour, got this solution.
Problem not comes from CSS, Script or Code Behind,

Solution:
Problem was, i`m using AJAX control tool kit version 3.5.40412.2, This one not up-to-date, It having errors with IE9.

Download Latest version of Ajax Control Toolkit Or use the below one.
http://ajaxcontroltoolkit.codeplex.com/releases/view/63654
The new release solved my AsyncFileUpload problem with IE 9.