ADO.Net connection usage patterns help in improving the code for ASP.Net applications as well as managed code to handle the connection pooling. When you use any Data Provider to make your web application user interactive for providing dynamic content and wide range of features then you must fine tune your skills to play with database queries and connections.
Whichever .Net Data Provider you are using, you must always:
You must trace and debug your code during the development period of application to find out whether there is any possibility of connection errors or exceptions. You must be sure about your code that it is properly handling the open and closed state of database connection. Coz only closed or disposed connections are collected back in connection pool otherwise it results in connection pool leakage or connection exceptions.
In the following code example finally block is used in try catch block that guarantees it will close the connection whether the code block throws any error or not. You can use this code in both VB.Net and C#.Net
public void runProc() {
SqlConnection conn = new SqlConnection(connectionString); SqlCommand cmd = new SqlCommand("updateProc", conn ); cmd.CommandType = CommandType.StoredProcedure;
try {
conn.Open(); cmd.ExecuteNonQuery();
} catch (Exception e) {
// Handle error message
} finally {
conn.Close();
}
Another alternate approach that can be used in C# code is the use of using statement that provides the similar functionality:
using (SqlConnection conn = new SqlConnection(connectionString)) {
SqlCommand cmd = new SqlCommand("updateProc", conn); fcmd.CommandType = CommandType.StoredProcedure; conn.Open(); cmd.ExecuteQuery();
But you must also use try catch block to handle the sqlExceptions.
Currently rated 5.0 by 1 people
Tags: asp.net, ado.net, database connection, connection pooling, c# code
7/24/2008 5:25:41 PM
