Connection Usage Patterns

Irrespective of the .NET data provider you use, you must always:

Open a database connection as late as possible.
Use the connection for as short a period as possible.
Close the connection as soon as possible. The connection is not returned to the pool until it is closed through either the Close or Dispose method. You should also close a connection even if you detect that it has entered the broken state. This ensures that it is returned to the pool and marked as invalid. The object pooler periodically scans the pool, looking for objects that have been marked as invalid.

To guarantee that the connection is closed before a method returns, consider one of the approaches illustrated in the two code samples that follow. The first uses a finally block. The second uses a C# using statement, which ensures that an object's Dispose method is called.
The following code ensures that a finally block closes the connection. Note that this approach works for both Visual Basic .NET and C# because Visual Basic .NET supports structured exception handling.

public void DoSomeWork()

  SqlConnection conn = new SqlConnection(connectionString);

  SqlCommand cmd = new SqlCommand("CommandProc", conn );

  cmd.CommandType = CommandType.StoredProcedure;

try

    conn.Open();

    cmd.ExecuteNonQuery();

  catch (Exception e)

    // Handle and log error

  finally

    conn.Close();

The following code shows an alternate approach that uses a C# using statement. Note that Visual Basic .NET does not provide a using statement or any equivalent functionality.

public void DoSomeWork()

  // using guarantees that Dispose is called on conn, which will

  // close the connection.

  using (SqlConnection conn = new SqlConnection(connectionString))

    SqlCommand cmd = new SqlCommand("CommandProc", conn);

    fcmd.CommandType = CommandType.StoredProcedure;

    conn.Open();

    cmd.ExecuteQuery();

You can also apply this approach to other objects—for example, SqlDataReader or OleDbDataReader—which must be closed before anything else can be done with the current connection.

Error Handling

ADO.NET errors are generated and handled through the underlying structured exception handling support that is native to the .NET Framework. As a result, you handle errors within your data access code in the same way that you handle errors elsewhere in your application. Exceptions can be detected and handled through standard .NET exception handling syntax and techniques.
This section shows you how to develop robust data access code and explains how to handle data access errors. It also provides specific exception handling guidance relating to the SQL Server .NET Data Provider.

.NET Exceptions

The .NET data providers translate database-specific error conditions into standard exception types, which you should handle in your data access code. The database-specific error details are made available to you through properties of the relevant exception object.
All .NET exception types ultimately are derived from the base Exception class in the System namespace. The .NET data providers throw provider-specific exception types. For example, the SQL Server .NET Data Provider throws SqlException objects whenever SQL Server returns an error condition. Similarly, the OLE DB .NET Data Provider throws exceptions of type OleDbException, which contain details exposed by the underlying OLE DB provider.
Figure 1 shows the .NET data provider exception hierarchy. Notice that the OleDbException class is derived from ExternalException, the base class for all COM Interop exceptions. The ErrorCode property of this object stores the COM HRESULT generated by OLE DB.

Shashi Ray

Connection Usage Patterns

Reactions