Connection Pooling

By default, connection pooling is automatically turned on for .NET Data Providers. When you request a new connection, the .NET data provider checks the connection string for database, user name, and so on, and searches the pool for an open connection matching these credentials. If it can find a matching connection, it retrieves the connection from the pool and returns it to you. Otherwise, it creates a new connection for you.

Before a connection can be pooled, it must be explictly closed. Simply letting the connection variable go out of scope, or setting the variable to null does not close the connection! You can call a connection's Close or Dispose method to close the connection (Dispose automatically calls Close).

In C#:

conn.Close();

And in VB .NET:

conn.Close()

The C# using Statement

Rather than calling the Close method of the connection object manually, C# has a using statement (not to be confused with the using <namspace> statement) that you can use to open a connection at the start of a using block, and the runtime automatically calls the Close method for you at the end of the using block.

For example, the following code implements a using statement that creates a SQL Connection object. The connection is used to retrieve a list of data tables in the specified database. At the end of the using statement (the closing curly brace), behind the scenes the Dispose method of the connection object is automatically called, which in turn calls the connection object's Close method.

using (SqlConnection conn = new SqlConnection(connString))
{
	SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM INFORMATION_SCHEMA.TABLES " +
					"WHERE TABLE_TYPE = 'BASE TABLE' " +
					"AND TABLE_NAME <> 'dtproperties' " +
					"ORDER BY TABLE_TYPE",
					conn);
 
	DataTable dt = new DataTable();
	da.Fill(dt);
}