ADO.NET Data Connection and Commands

This document explores the fundamental components of ADO.NET for interacting with data sources: the Connection and Command objects.

The Connection Object

The Connection object represents an open connection to a data source. It is the first step in any data access operation. ADO.NET provides different connection classes for various data providers, such as SqlConnection for SQL Server, OleDbConnection for OLE DB data sources, and OracleConnection for Oracle.

Key Properties and Methods:

• ConnectionString: A string that contains parameters required to establish a connection (e.g., server name, database name, authentication details).
• ConnectionTimeout: The time in seconds to wait for a connection to establish before timing out.
• State: Indicates the current state of the connection (e.g., Closed, Open).
• Open(): Opens the connection to the data source.
• Close(): Closes the connection to the data source.
• Dispose(): Releases the resources used by the connection.

Example: Opening a Connection (Conceptual)

using System.Data;
using System.Data.SqlClient; // For SQL Server

// ...

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

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

The Command Object

The Command object is used to execute commands against a data source. These commands can be SQL statements, stored procedures, or other data manipulation language (DML) statements.

Key Properties and Methods:

• CommandText: The SQL statement or stored procedure name to execute.
• CommandType: Specifies how the CommandText property is to be interpreted (e.g., Text for SQL statements, StoredProcedure for stored procedures).
• Connection: The Connection object associated with this command.
• Parameters: A collection of parameters for the command.
• ExecuteNonQuery(): Executes a command that returns no rows (e.g., INSERT, UPDATE, DELETE). Returns the number of rows affected.
• ExecuteReader(): Executes a command and returns a DataReader object to iterate through the results.
• ExecuteScalar(): Executes a command and returns the first column of the first row in the result set.
• ExecuteXmlReader(): Executes a command and returns an XmlReader.

Executing Commands:

Commands are typically executed within an open connection context.

Executing a Non-Query (INSERT, UPDATE, DELETE)

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

// ... Assume 'connection' is an already open SqlConnection ...

string sqlInsert = "INSERT INTO Products (ProductName, UnitPrice) VALUES (@Name, @Price)";
using (SqlCommand command = new SqlCommand(sqlInsert, connection))
{
    command.Parameters.AddWithValue("@Name", "New Gadget");
    command.Parameters.AddWithValue("@Price", 19.99);

    try
    {
        int rowsAffected = command.ExecuteNonQuery();
        Console.WriteLine($"Rows affected: {rowsAffected}");
    }
    catch (SqlException ex)
    {
        Console.WriteLine("Error executing command: " + ex.Message);
    }
}
            

Executing a Query with DataReader

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

// ... Assume 'connection' is an already open SqlConnection ...

string sqlSelect = "SELECT ProductID, ProductName, UnitPrice FROM Products WHERE UnitPrice > @MinPrice";
using (SqlCommand command = new SqlCommand(sqlSelect, connection))
{
    command.Parameters.AddWithValue("@MinPrice", 50.00);

    try
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"ID: {reader["ProductID"]}, Name: {reader["ProductName"]}, Price: {reader["UnitPrice"]}");
            }
        }
    }
    catch (SqlException ex)
    {
        Console.WriteLine("Error executing query: " + ex.Message);
    }
}
            

Command and Connection Lifecycle Management

Effective management of Connection and Command objects is vital for performance and resource utilization. The using statement in C# is the preferred way to ensure that these disposable objects are properly disposed of, even if exceptions occur.

Best Practice: Using Statement

string connectionString = "Server=myServerAddress;Database=myDataBase;Integrated Security=SSPI;"; // Example with Windows Auth

try
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        Console.WriteLine("Connection opened using 'using'.");

        string sql = "SELECT COUNT(*) FROM Customers";
        using (SqlCommand command = new SqlCommand(sql, connection))
        {
            object result = command.ExecuteScalar();
            Console.WriteLine($"Total customers: {result}");
        } // Command disposed here
    } // Connection closed and disposed here
    Console.WriteLine("Connection closed and disposed.");
}
catch (SqlException ex)
{
    Console.WriteLine("An error occurred: " + ex.Message);
}
            

This approach ensures that the connection is always closed and released, and any associated resources for the command are cleaned up.