Wednesday, October 3, 2007

C# Using Using for Using Connections

A wise man said, "The using statement declares that you are using a disposable object for a short period of time.  As soon as the using block ends, the CLR releases the corresponding object immediately by calling its Dispose() method ".

And I just thought "Using" was syntactical sugar for VBers.  *

What this implies is you should do your standard vanilla SQL connection like this:

 string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"]
System.Data.SqlClient.SqlConnection conn = new System.Data.SqlClient.SqlConnection(connectionString);

using (conn)
{
     conn.Open();
     System.Data.SqlClient.SqlParameter param = new System.Data.SqlClient.SqlParameter();
.... etc etc....


}


* Not to be confused with With.
And I'm not sure if Using was available in .NET 1.1

Also:

SQL Server and Oracle connection pooling use a full-text match algorithm. That means any minor change in the connection string will thwart connection pooling, even if the change is simply to reverse the order of parameters or add an extra blank space at the end. For this reason, it's imperative that you don't hard-code the connection string in different web pages. Instead, you should store the connection string in one place—preferably in the <connectionStrings> section of the web.config file.

No comments: