While viewing some of the C# code written by a new programmer, I noticed that they lack of calling Dispose() method on SqlConnection and SqlCommand objects. And the dabase code was not placed in try/finally blocks. This coding style is typical newbie style of development that everyone, including myself, attempted in the very beginning.

SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(commandString, con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();

The problem in the above code is that SqlConnection and SqlCommand implement IDisposable, which means they could have unmanaged resources to cleanup and it is our job, to make sure Dispose() gets called on these classes after we are finished with them. And, because an exception could be raised if the database is unavailable, we need to make sure Dispose() gets called even in the case of an exception.

Its better to use the “using” keyword in C#. Internally, this generates a try / finally around the object being allocated and calls Dispose() for you. It saves you the hassle of manually creating the try / finally block and calling Dispose().

The new code would looking something like this:

using (SqlConnection con = new SqlConnection(connectionString))
{
    using (SqlCommand cmd = new SqlCommand(commandString, con))
   {
      con.Open();
      cmd.ExecuteNonQuery();
   }
}

This is essentially equivalent to the following:

SqlConnection con = null;
SqlCommand cmd = null;
try
{
     con = new SqlConnection(connectionString);
     cmd = new SqlCommand(commandString, cn);
     con.Open();
     cmd.ExecuteNonQuery();
}
finally
{
      if (null != cm);
         cmd.Dispose();
      if (null != cn)
         con.Dispose();
}

You may notice the lack of calling Close() on the SqlConnection class, con. Internally, Dispose() checks the status of the connection and closes it for you. Therefore, technically you don’t need to call Close() on the connection (con) as Dispose() will do it for you. In addition, Dispose() destroys the connection string of the SqlConnection class. Therefore, if you want to re-open the connection after calling Dispose() on con, you will have to re-establish the connection string. Not doing so will throw an exception.

Microsoft has introduced a new Optional Parameter feature in C# 4.0. In this if we will not specify the value of parameter then function will automatically take default value rather then giving the compile time error.

Lets look at the example:

public void SendEMail(string toAddress, string bodyText, bool ccAdmin = true, bool isBodyHtml = false)
{
   // Full implementation here
}

Least specified “overload” consuming code written will look like this:

SendEMail("jhon@foo.com", "Hello World");

The IL that the C# compiler will generate will actually be the equivalent of this:

SendEMail("jhon@foo.com", "Hello World", true, false);

The best part in this is that, unlike traditional method overloading, you have the ability to omit only the 3rd parameter in conjunction with the new Named Parameters language feature and write your code like this:

SendEMail("jhon@foo.com", "Hello World", isBodyHtml: true);

This will allow consuming code to only pass 3 arguments for succinctness but still invoke the appropriate overload since the IL generated in that instance will be equivalent to this:

SendEMail("jhon@foo.com", "Hello World", true, true);

Earlier the row selection in Gridview/Listview has been based on the row index on the page.The problem with kind of selection is that if you select the fourth item on page 1 and the move on to page 2 the the fourth item on this page is still selected.

To resolve this issue Microsoft has now made a functionality to selected rows on the basis of Data Key rather then Row index. Earlier this feature is only supported in Dynamic Data projects in the .NET Framework 3.5 SP1. When this persisted row selection is enabled , the current row selection is based on Data Key , this means when you select fourth row on page 1 and move on to page 2 then nothing is selected on page 2, when we again mave back to page 1 then fourth row is still selected.

To Enable this feature in Gridview and Listview we simply have to include EnablePersistedSelection=”true” as shown in the following figure.

<asp:GridView id="GridView2" runat="server" EnablePersistedSelection="true"> 
</asp:GridView>

Resource:
http://geekswithblogs.net/ranganh/archive/2009/09/08/whatrsquos-new-in-asp.net-4.0-ndash-part-iii-ndash-persisting.aspx

We can use SQL Server Publishing Wizard to deploy our local Database to remote hosting server/production server.This feature is available in SQL Server 2008/VWD 2008.

Below is the complete step by step guide of this process.

Step1. Open Server Explorer and add a Data connection and connect to a your database which you need to publish. For this example i am using my tsqlchallenge database. You should point to the database you want to publish.

Step 2: Right Click tsqlchallenge.dbo node in Server explorer context menu will appear. You will find “Publish to provider” option there.

Step 3. Click Publish to Provider to launch Database Publishing Wizard

Step 4. Select Database which you want to publish.

Step 5.  Select output mode and file location for were script (.SQL) will generate and save. You can also directly publish it your shared hosting also by selecting “Publish to shared hosting provider”

Step 6. In the  NEXT  step you will get the Publishing Options. Where you have the option to choose the script for target database (SQL Server 2000 or SQL Server 2005) and the types of data to publish (Schema, Data or Schema+Data).

Step 7. Click finish & Review your selection summary this will now generate the script file.

Next you will see your publishing progress.

Step 8. The file generated ( .SQL)  contains your database  script that you can run on any SQL server to re-create all the tables, sprocs, views, triggers, full-text catalogs, etc. for a database, as well as import and add all of the table row data that was in the database at the time the .SQL file was created

Step 9. Now that you have .SQL file with all the necessary script to recreate the database, You can use Query Analyzer or Web Based SQL Server Admin to run this Script against your production SQL Server