MSDN Documentation

Connection Objects in ADO.NET

Connection objects represent a unique session with a data source. The connection is the primary interface between an ADO.NET application and a data source. Connection objects are used to establish a connection to the data source, execute commands, and retrieve data.

ADO.NET provides different provider-specific connection classes, such as SqlConnection for SQL Server, OracleConnection for Oracle, OleDbConnection for OLE DB data sources, and OdbcConnection for ODBC data sources. All these classes inherit from the abstract base class DbConnection.

Key Properties and Methods

Connection objects expose several important properties and methods:

• ConnectionString: A string that contains the parameters required to establish a connection to the data source. This typically includes server name, database name, authentication credentials, etc.
• ConnectionTimeout: The time in seconds to wait for a connection to be established before terminating the attempt.
• State: Returns the current state of the connection (e.g., Closed, Open).
• Open(): Establishes an open connection to the data source.
• Close(): Closes the connection to the data source.
• Dispose(): Releases all resources used by the connection. It is crucial to call this method when you are finished with the connection.

Using Connection Objects

The typical lifecycle of a connection object involves creating it, opening it, using it to execute commands, and then closing and disposing of it. It's best practice to wrap connection usage in a using statement to ensure proper disposal.

Example: Connecting to SQL Server

The following C# code snippet demonstrates how to use a SqlConnection object:

using System;
using System.Data;
using System.Data.SqlClient;

public class ConnectionExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

        // Using statement ensures the connection is disposed of properly
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                Console.WriteLine("Opening connection...");
                connection.Open();
                Console.WriteLine("Connection state: " + connection.State);

                // Here you would typically create and execute a Command object

                Console.WriteLine("Connection opened successfully.");
            }
            catch (SqlException ex)
            {
                Console.WriteLine("Error connecting to database: " + ex.Message);
            }
            finally
            {
                // The 'using' statement handles closing and disposing,
                // but you can also explicitly call Close() if needed.
                if (connection.State == ConnectionState.Open)
                {
                    connection.Close();
                    Console.WriteLine("Connection closed.");
                }
            }
        }
    }
}
            

Connection Pooling

ADO.NET providers, especially for SQL Server, utilize connection pooling. Connection pooling is a technique where a set of connections is maintained in memory, ready to be used by applications. When an application requests a connection, the pooler provides an available connection. When the application closes the connection, it is returned to the pool rather than being physically closed. This significantly improves application performance by reducing the overhead of establishing new connections.

Configuring Connection Pooling

Connection pooling is enabled by default for most ADO.NET data providers. You can configure pooling behavior by adding specific keywords to your connection string, such as:

• Pooling=true/false: Enables or disables connection pooling.
• Max Pool Size=N: Specifies the maximum number of connections allowed in the pool.
• Min Pool Size=N: Specifies the minimum number of connections to keep in the pool.

Best Practices

• Always use the using statement or explicitly call Dispose() on connection objects to release resources.
• Keep connections open for the shortest time necessary to minimize resource contention.
• Configure connection strings carefully, especially regarding security credentials.
• Leverage connection pooling for performance.

Understanding and effectively using connection objects is fundamental to building efficient and robust data access applications with ADO.NET.