SQL:Enable, Disable, Drop and Recreate FKs based on Primary Key table

-- Enable, Disable, Drop and Recreate FKs based on Primary Key table  
-- Works for SQL Server 2005 and above  

SET NOCOUNT ON  

DECLARE @operation VARCHAR(10)  
DECLARE @tableName sysname  
DECLARE @schemaName sysname  

SET @operation = 'DROP' --ENABLE, DISABLE, DROP  
SET @tableName = 'TableName'  
SET @schemaName = 'dbo'  

DECLARE @cmd NVARCHAR(1000) 

DECLARE   
   @FK_NAME sysname,  
   @FK_OBJECTID INT,  
   @FK_DISABLED INT,  
   @FK_NOT_FOR_REPLICATION INT,  
   @DELETE_RULE    smallint,     
   @UPDATE_RULE    smallint,     
   @FKTABLE_NAME sysname,  
   @FKTABLE_OWNER sysname,  
   @PKTABLE_NAME sysname,  
   @PKTABLE_OWNER sysname,  
   @FKCOLUMN_NAME sysname,  
   @PKCOLUMN_NAME sysname,  
   @CONSTRAINT_COLID INT  


DECLARE cursor_fkeys CURSOR FOR   
   SELECT  Fk.name,  
           Fk.OBJECT_ID,   
           Fk.is_disabled,   
           Fk.is_not_for_replication,   
           Fk.delete_referential_action,   
           Fk.update_referential_action,   
           OBJECT_NAME(Fk.parent_object_id) AS Fk_table_name,   
           schema_name(Fk.schema_id) AS Fk_table_schema,   
           TbR.name AS Pk_table_name,   
           schema_name(TbR.schema_id) Pk_table_schema  
   FROM    sys.foreign_keys Fk LEFT OUTER JOIN   
           sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id --inner join   
   WHERE   TbR.name = @tableName  
           AND schema_name(TbR.schema_id) = @schemaName  

OPEN cursor_fkeys  

FETCH NEXT FROM   cursor_fkeys   
   INTO @FK_NAME,@FK_OBJECTID,  
       @FK_DISABLED,  
       @FK_NOT_FOR_REPLICATION,  
       @DELETE_RULE,     
       @UPDATE_RULE,     
       @FKTABLE_NAME,  
       @FKTABLE_OWNER,  
       @PKTABLE_NAME,  
       @PKTABLE_OWNER  

WHILE @@FETCH_STATUS = 0   
BEGIN   

   -- create statement for enabling FK  
   IF @operation = 'ENABLE'   
   BEGIN  
       SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME   
           + ']  CHECK CONSTRAINT [' + @FK_NAME + ']'  

      PRINT @cmd  
   END  

   -- create statement for disabling FK  
   IF @operation = 'DISABLE'  
   BEGIN     
       SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME   
           + ']  NOCHECK CONSTRAINT [' + @FK_NAME + ']'  

      PRINT @cmd  
   END  

   -- create statement for dropping FK and also for recreating FK  
   IF @operation = 'DROP'  
   BEGIN  

       -- drop statement  
       SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME   
       + ']  DROP CONSTRAINT [' + @FK_NAME + ']'     

      PRINT @cmd  

       -- create process  
       DECLARE @FKCOLUMNS VARCHAR(1000), @PKCOLUMNS VARCHAR(1000), @COUNTER INT  

       -- create cursor to get FK columns  
       DECLARE cursor_fkeyCols CURSOR FOR   
       SELECT  COL_NAME(Fk.parent_object_id, Fk_Cl.parent_column_id) AS Fk_col_name,   
               COL_NAME(Fk.referenced_object_id, Fk_Cl.referenced_column_id) AS Pk_col_name  
       FROM    sys.foreign_keys Fk LEFT OUTER JOIN   
               sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id INNER JOIN   
               sys.foreign_key_columns Fk_Cl ON Fk_Cl.constraint_object_id = Fk.OBJECT_ID   
       WHERE   TbR.name = @tableName  
               AND schema_name(TbR.schema_id) = @schemaName  
               AND Fk_Cl.constraint_object_id = @FK_OBJECTID -- added 6/12/2008  
       ORDER BY Fk_Cl.constraint_column_id  

       OPEN cursor_fkeyCols  

       FETCH NEXT FROM    cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME  

       SET @COUNTER = 1  
       SET @FKCOLUMNS = ''  
       SET @PKCOLUMNS = ''  
         
       WHILE @@FETCH_STATUS = 0   
       BEGIN   

           IF @COUNTER > 1   
           BEGIN  
               SET @FKCOLUMNS = @FKCOLUMNS + ','  
               SET @PKCOLUMNS = @PKCOLUMNS + ','  
           END  

           SET @FKCOLUMNS = @FKCOLUMNS + '[' + @FKCOLUMN_NAME + ']'  
           SET @PKCOLUMNS = @PKCOLUMNS + '[' + @PKCOLUMN_NAME + ']'  

           SET @COUNTER = @COUNTER + 1  
             
           FETCH NEXT FROM    cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME  
       END  

       CLOSE cursor_fkeyCols   
       DEALLOCATE cursor_fkeyCols   

       -- generate create FK statement  
       SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + ']  WITH ' +   
           CASE @FK_DISABLED   
               WHEN 0 THEN ' CHECK '  
               WHEN 1 THEN ' NOCHECK '  
           END +  ' ADD CONSTRAINT [' + @FK_NAME   
           + '] FOREIGN KEY (' + @FKCOLUMNS   
           + ') REFERENCES [' + @PKTABLE_OWNER + '].[' + @PKTABLE_NAME + '] ('   
           + @PKCOLUMNS + ') ON UPDATE ' +   
           CASE @UPDATE_RULE   
               WHEN 0 THEN ' NO ACTION '  
               WHEN 1 THEN ' CASCADE '   
               WHEN 2 THEN ' SET_NULL '   
               END + ' ON DELETE ' +   
           CASE @DELETE_RULE  
               WHEN 0 THEN ' NO ACTION '   
               WHEN 1 THEN ' CASCADE '   
               WHEN 2 THEN ' SET_NULL '   
               END + '' +  
           CASE @FK_NOT_FOR_REPLICATION  
               WHEN 0 THEN ''  
               WHEN 1 THEN ' NOT FOR REPLICATION '  
           END  

      PRINT @cmd  

   END  

   FETCH NEXT FROM    cursor_fkeys   
      INTO @FK_NAME,@FK_OBJECTID,  
           @FK_DISABLED,  
           @FK_NOT_FOR_REPLICATION,  
           @DELETE_RULE,     
           @UPDATE_RULE,     
           @FKTABLE_NAME,  
           @FKTABLE_OWNER,  
           @PKTABLE_NAME,  
           @PKTABLE_OWNER  
END  

CLOSE cursor_fkeys   
DEALLOCATE cursor_fkeys 

C# Command line parsing with NDesk.options

While creating command line exe we need to provide detail of options to end user.
This options detail can be provided by using the simple utility class called "NDesk.Options ".

Click on NDesk.Options link to download the utility.

This utility is a simple class that can be added to our project directly or can be added as a reference.

Let's see how we can use the same in our command line project.

• Open VS
• Create new Console Application
• Add the reference of NDesk.Options dll to the project. 

using System;
using NDesk.Options;

namespace NDeskSample
{
    class Program
    {
//Variables declared
        private static int FirstNum=0;
        private static int SecondNum=0;

        static void Main(string[] args)
        {
            var show_help=false;

//Intializing utility class with options 
            OptionSet options = new OptionSet(){
                {"F|FirstNum=","Value for First Number to add",(int v)=>FirstNum=v},
                {"S|SecondNum=","Value for Second Number to add",(int v)=>SecondNum=v},
                {"h|help","Show Help",v=> show_help=v!=null},
            };

            try
            {
                options.Parse(args); //Parsing the input arguments
            }
            catch (OptionException e)
            {
                Console.WriteLine(e.Message);
                Console.WriteLine("Try --help for more information.");
                 ShowHelp(options);
                    return;                
            }

            if (FirstNum == 0 || SecondNum == 0)
            {
                ShowHelp(options);
                return;
            }
            else
            {
               Console.WriteLine("{0} is the sum of given numbers",sum(FirstNum, SecondNum));
            }

            if (show_help)
            {
                ShowHelp(options);
                return;
            }
        }

        static int sum(int firstNo, int secondNo)
        {
            return firstNo + secondNo;
        }
//Displays the help for the options
        static void ShowHelp(OptionSet p)
        {
            Console.WriteLine("NDesk Usage:");            
            Console.WriteLine("Options:");
            p.WriteOptionDescriptions(Console.Out);
        }
    }
}

Further lets see how we can use exe with parameters we had just created.
Open the cmd prompt and goto the directory path were exe is build, In my case it was inside debug folder of bin.

Now using this exe with options is shown below

Lastly  to show help use it as shown below

 
Hope it helps someone :)

C# Using anonymous objects with dynamic programming

Imagine that we need to assign property on the fly to an object and later access the same property in the method,well now we can do it using dynamic programming.

Let's see it in action.

void CreateObject()

{

    var person = new { Name = "Shankar", Age = 26 };

    UseObject(person);

}

void UseObject(object o)

{ 

    //This will NOT work!

    Console.Write("The name is . . . ");   

    Console.WriteLine(o.Name);

}  

As you can see we are interested in accessing the property of the person object 

but the above code will not compile at all because the object doesn't have the 

Name property. So how can we access that dynamic property which we had assigned?

The answer is yes we can access it using dynamic keyword.

void UseObject(dynamic o)

{    //This will work!

    Console.Write("The name is . . . ");   

    Console.WriteLine(o.Name);

}

Yes, now we can access the Name property of the person object.

Hope it helps someone.....

C# Using Enyim Caching

There are situations which when arises we need to store the cache objects separately other then web-Server and Sql server.It is during such situtations decision of storing the cache objects on Memcached server comes into our mind and in C# world Enyim caching is one of the most matured api available for storing and retrieving the objects from Memcached server.

Let's start with it for working with MemCached we will first need the MemCached Server.
Links to download the memcache server are :

1. MemCache for Win32
2. MemCache for Win32 and 64 bit both(1.4 version)
3. Download directly

Once you have downloaded the files,you can either run MemCached as console application or install it as a Windows service(This is the recommended option).In order to install it as a service,run the following from the command line

C:\Path\memcached -d install

we will need client api which can store and retrieve objects or the one who can interact with the server.

For C# there are two most known client api's available

• Enyim Caching
• BeIT 

Well I prefer Enyim caching as it is more mature then other.So for downloading Enyim caching api you can go to nugget manager and type Enyim caching and add it to your project.I am creating a sample console application for the same as shown below

Fig:1.0

Fig.1.1

Once client api is added to the reference we need to update our web.config or app.config file as shown

Config sections

After adding the above configuration to config-sections add the below configuration for server where Memcached server is running

Server Configuration

Since Memcached server is running on local instance so the address given as "127.0.0.1" and  port "11211" is default on which server is listening
Now using the library is simple enough as shown is below fig for storing and retrieving the string value.

Above code will simply store the string  and retrieve the same using the key("first").

But in real life there will be requirement to store the List type of objects in cache retrieve it,so below code will help in doing the same.

  

As you can see in the above code List of contact is first converted to bytearray object and then stored in the Memcached server with the key "ContactList",once the list is stored in the cache as bytearray the same can retrieved using the key and can be converted from bytearray to object.  
Hope it would help someone :)