SQL Server Performance Tuning and Query Optimization Videos

If you are like me, who don't get much chance to get your hands dirty in fine tuning sql server queries, then you must watch this videos.

I am really thankful to this guy, who has posted such a useful videos.

http://www.youtube.com/playlist?list=PL2900t3sPCl1MZi88lYsRLUcSled8wAMU

Frankly speaking their is lot of materials out their on this topic and I always avoid learning because of that.  This videos helped me to quickly get started to attack problem I was facing.

If you landed here searching how to improve performance of your website then along with database sql indexing you should also look for this checklist.
http://dotnetguts.blogspot.com/2012/09/all-about-aspnet-website-performance.html

Generate C# Class from JSON File or URL

I have came across a useful site which will be helpful in generating C# Class from JSON File or URL.

This will be very useful when you are using JsonConvert.DeserializeObject Method.

http://json2csharp.com/

Generate JSON File from database data using c#

Following code will help you to generate JSON file from database table.

//Get records from database
var products = db.Products.ToList();

//Generate JSON from database data
using (StringWriter writer = new StringWriter())
{
    // Json.Write sends a Json-encoded string to the writer.
    System.Web.Helpers.Json.Write(products, writer);
   
    // When ready, you can send the writer
    // output to the browser, a file, etc.
    //Response.Write(writer); 
    /*Uncomment above line to view JSON
       output in browser*/

    using (StreamWriter outfile =
               new StreamWriter(@"c:\Temp\Products.json"))
    {
        outfile.Write(writer.ToString());
    }
}

Please note: In order to have this code work, you will need to have "System.web.Helpers" dll added to your solution.

Adding Column to SQL Server using Database Defensive Programming Technique

Recently I have learned good way to add column to sql server using database defensive programming technique from my co-worker.  All the credit for this blog post goes to him.  Thank you sir incase you are reading this blog post. (I have purposefully avoid mentioning name of co-worker due to privacy reason.)

Following example is very simple and self explanatory, Incase if you didn't get anything then pass your comment in comment section.

BEGIN TRANSACTION
IF EXISTS(SELECT 1 from information_schema.tables 
          where table_name = 'MyTableName')
  BEGIN
    Print('Table Exist');

    --Add Column MyColumn
    IF NOT EXISTS(SELECT 1 from information_schema.columns 
                  where table_name = 'MyTableName' 
                  and Column_Name='MyColumn')
     BEGIN
 ALTER TABLE MyTableName ADD MyColumn varchar(345) NULL;
 Print('MyColumn Column Added');
     END

    ELSE
     
     BEGIN
 Print('MyColumn Column Already Exist');
     END



  END

Else
    BEGIN
  Print('Table does not Exist');
    END


IF @@ERROR <> 0
    BEGIN
        PRINT('Problem in running script, Rolling back');
        ROLLBACK TRANSACTION;
    END
ELSE
 BEGIN
  PRINT('Script Run Successfully');
        COMMIT TRANSACTION;
 END