MSDN Documentation

ADO.NET: Connections and Commands

This document explores the fundamental components of ADO.NET for interacting with data sources: DbConnection and DbCommand objects. Understanding these objects is crucial for building data-driven applications.

DbConnection Objects

A DbConnection object represents a unique session with a data source. It is the first step in any ADO.NET operation, establishing a link to the database. Different data providers (e.g., SQL Server, Oracle, OLE DB) implement their own specific connection classes that inherit from the abstract DbConnection base class.

Key Properties and Methods:

• ConnectionString: A string containing parameters required to establish the connection (e.g., server name, database name, authentication details).
• 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., Open, Closed).
• Open(): Opens the database connection.
• Close(): Closes the database connection. It's important to always close connections when they are no longer needed to release resources.
• Dispose(): Releases all resources used by the connection.

using System.Data.SqlClient;

// ...

string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
SqlConnection connection = null;

try
{
    connection = new SqlConnection(connectionString);
    connection.Open();
    Console.WriteLine("Connection opened successfully!");
    Console.WriteLine("Connection State: " + connection.State);
}
catch (SqlException ex)
{
    Console.WriteLine("Error connecting to database: " + ex.Message);
}
finally
{
    if (connection != null && connection.State == System.Data.ConnectionState.Open)
    {
        connection.Close();
        Console.WriteLine("Connection closed.");
    }
}
            

DbCommand Objects

A DbCommand object represents a Transact-SQL statement or stored procedure to be executed against a data source. It is used to perform operations like querying data, inserting, updating, or deleting records.

Key Properties and Methods:

• Connection: Specifies the DbConnection object to execute the command against.
• CommandText: The SQL statement or stored procedure name.
• CommandType: Specifies how the CommandText property is to be interpreted (e.g., Text for SQL statements, StoredProcedure for stored procedures).
• Parameters: A collection of parameters associated with the command. Using parameters is highly recommended to prevent SQL injection vulnerabilities and improve performance.
• ExecuteReader(): Executes the command and returns a DbDataReader object.
• ExecuteNonQuery(): Executes the command and returns the number of rows affected. Useful for INSERT, UPDATE, DELETE statements.
• ExecuteScalar(): Executes the command and returns the value of the first column of the first row in the result set. Useful for aggregate functions like COUNT(*) or SUM().
• ExecuteResultSet(): (Less common, often handled by DataAdapters) Executes the command and returns data.

using System.Data.SqlClient;

// Assume 'connection' is an already opened SqlConnection object

string sql = "SELECT CustomerID, CompanyName FROM Customers WHERE City = @City";

try
{
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        // Add parameter to prevent SQL injection
        command.Parameters.AddWithValue("@City", "London");

        using (SqlDataReader reader = command.ExecuteReader())
        {
            Console.WriteLine("Customers in London:");
            while (reader.Read())
            {
                Console.WriteLine($"- {reader["CompanyName"]} (ID: {reader["CustomerID"]})");
            }
        }
    }
}
catch (SqlException ex)
{
    Console.WriteLine("Error executing command: " + ex.Message);
}
            

Best Practices

• Use using statements: This ensures that resources (like connections and commands) are properly disposed of, even if errors occur.
• Parameterize your queries: Always use DbParameter objects to pass values to your SQL statements. This is critical for security and performance.
• Keep connections open for the shortest duration necessary: Opening and closing connections can be resource-intensive. However, holding them open for too long can also lead to issues. A common pattern is to open a connection, perform all necessary operations, and then close it.
• Handle exceptions: Database operations can fail for many reasons. Implement robust error handling to gracefully manage these situations.
• Choose the right provider: Use the appropriate ADO.NET provider for your specific database.

Summary Table