ADO.Net Connection Usage Patterns

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:

• Open the database connection late
• Use the connection for a short time period.
• Close the database connection as soon as possible when data transfer is complete. Coz only closed connection is returned back to the managed connection pool.

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.

C# Code Sample for Database Connection

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:

public void runProc()
{
    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.

Continue to next tutorial: ASP.Net Error and Exception Handling for ADO.Net to learn how to handle the errors or exceptions which occur while executing the data access code.