How to use Connection object efficiently

Posted Date: 01 Nov 2008	Resource Type: Articles	Category: ASP.NET/Web Applications
Author: shekhar	Member Level: Gold
Rating:	Points: 10

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.

Responses

No responses found. Be the first to respond and make money from revenue sharing program.

Feedbacks

Popular Tags

What are tags ?

Search Tags

(No tags found.)

Post Feedback

This is a strictly moderated forum. Only approved messages will appear in the site. Please use 'Spell Check' in Google toolbar before you submit.
You must Sign In to post a response.

	Next Resource: AJAX application for continuos Integration
	Previous Resource: Adding field to a document library in sharepoint.
	Return to Discussion Resource Index
	Post New Resource
	Category: ASP.NET/Web Applications

Post resources and earn money!

Related Resources