How to use Connection object efficiently

SheoNarayan
Posted by in ADO.NET category on for Beginner level | Views : 6687 red flag
Rating: 4 out of 5  
 2 vote(s)

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.
Page copy protected against web site content infringement by Copyscape

About the Author

SheoNarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com
Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Posted by: Akiii on: 2/20/2011 | Points: 25
Thank you Sir for a very nice explanation of "How to use Connection object efficiently". I am new to DOT NET domain and it will help me immensely.

Akiii

Login to post response

Comment using Facebook(Author doesn't get notification)