Sunday, May 11, 2008

How to use Connection object efficiently

In this article, I am going to show how to use Connection object efficiently and what are the standard practices we should follow while working with connection objects.
 
 Irrespective of what .NET data provider we are using we should consider following points.

1. Open the database connection as late as possible, Just open it before you are going to use it.
2. Use the connection object for as short period as possible. Don't let it open even if you are not using.
3. Close the connection as soon as possible. You can do that by calling Close() or Dispose() method of the connection object. This also ensures that you connection is closed even if it is into broken stage.

To guarantee that the connection has been closed before method returns, consider using finally block or use using statement in C#.

Using Finally block to ensure that connection has been closed


SqlConnection conn = new SqlConnection(connStr1);
try
{
conn.Open();
// Do your work
}
catch (Exception ee)
{
lblMessage.Text = ee.Message;
}
finally
{
conn.Close();
conn.Dispose();

}


using statement to ensures that connection has been closed

private void UsingStatement()
{
// using guarantees that Dispose() method of the object has been called before
// In this case as soon as you will finish doing your work using will automatically call conn.Dispose() and closes the connection
using (SqlConnection conn = new SqlConnection(connStr))
{
// do your work
}


// Connection object is not available here
}

You can apply using statement to all methods that implement IDisposable interface (Objects that has Dispose() method) like SqlDataReader, SqlDataAdapter etc.

Conclusion

The conclusion here is that database Connection is very precious resources for any application and you should use it very efficiently to make your application scalable and error free.

No comments: