Data Connections and Commands

A foundational aspect of ADO.NET is the ability to establish connections to data sources and execute commands against them. This involves using provider-specific classes to interact with databases.

Connections

A connection represents a unique session with a data source. ADO.NET provides a set of classes for managing connections, typically found within the System.Data namespace and provider-specific namespaces like System.Data.SqlClient, System.Data.OleDb, and System.Data.Odbc.

The `DbConnection` Class

The abstract base class DbConnection provides a common interface for connection objects. Concrete implementations handle the specifics of connecting to a particular data source.

Connection String: A string that contains information required to connect to a data source, such as server name, database name, and authentication credentials.
Opening and Closing Connections: Connections should be explicitly opened before use and closed when finished to release resources. Using a using statement (in C#) or a Using block (in VB.NET) is the recommended practice for ensuring connections are properly closed and disposed of.

Key Properties and Methods:

ConnectionString: Gets or sets the connection string used by the derived class to connect to the data source.
ConnectionTimeout: Gets the time-out period in seconds to wait for a connection to be established.
State: Gets the current state of the connection (e.g., Closed, Open).
Open(): Opens the database connection with the properties and behavior specified by the connection string.
Close(): Closes the connection to the data source.
Dispose(): Releases the resources used by the connection.

Commands

A command represents a query or statement to be executed against a data source. ADO.NET offers a robust set of classes for this purpose, allowing you to retrieve data, modify data, or call stored procedures.

The `DbCommand` Class

The abstract base class DbCommand serves as the foundation for command objects. Similar to connections, concrete implementations are provided by specific data providers.

Command Text: The SQL statement, stored procedure name, or other command to be executed.
Command Type: Specifies how the CommandText property is to be interpreted (e.g., Text for a SQL statement, StoredProcedure for a stored procedure).
Parameters: Commands can accept parameters, which is crucial for security (preventing SQL injection) and for passing dynamic values into queries.

Key Properties and Methods:

CommandText: Gets or sets the SQL statement or stored procedure to execute.
CommandType: Gets or sets a value indicating how to interpret the CommandText property.
Connection: Gets or sets the DbConnection object to which the command belongs.
Parameters: Gets a collection of parameters associated with the command.
ExecuteNonQuery(): Executes a Transact-SQL statement against the connection and returns the number of rows affected. Useful for INSERT, UPDATE, DELETE statements.
ExecuteReader(): Executes the CommandText against the Connection and returns a DbDataReader object. Ideal for retrieving result sets.
ExecuteScalar(): Executes the query, and returns the first column of the first row in the result set returned by the query. Useful for aggregate functions like COUNT, SUM.
ExecuteXmlReader(): Executes the CommandText at the Connection and returns an XmlReader object.

Example: Executing a Simple Query

Here's a C# example demonstrating how to establish a connection and execute a simple SELECT query using SqlConnection and SqlCommand:


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

public class DataAccess
{
    public static void GetCustomerData(string connectionString)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string sql = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = @Country";
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                // Add a parameter to prevent SQL injection
                command.Parameters.AddWithValue("@Country", "USA");

                try
                {
                    connection.Open();
                    Console.WriteLine("Connection opened successfully.");

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}");
                            }
                        }
                        else
                        {
                            Console.WriteLine("No customers found for the specified country.");
                        }
                    }
                }
                catch (SqlException ex)
                {
                    Console.WriteLine($"An error occurred: {ex.Message}");
                }
            }
        }
    }
}

This example illustrates the core principles of ADO.NET data access: creating a connection, defining a command, associating the command with the connection, opening the connection, executing the command (in this case, using ExecuteReader to get data), and handling potential exceptions.

MSDN Documentation