Commands and DataReaders

ADO.NET provides objects to interact with data sources. At the core of data manipulation are Command objects, used to execute SQL statements or stored procedures, and DataReader objects, used for efficient, forward-only, read-only access to data.

DbCommand Objects

The DbCommand class (and its provider-specific subclasses like SqlCommand for SQL Server or OracleCommand for Oracle) represents an SQL statement or stored procedure to be executed against a data source.

Key Properties:

• CommandText: The SQL statement or stored procedure name.
• CommandType: Specifies whether the CommandText is a stored procedure or a text command.
• Connection: The DbConnection object associated with the command.
• Parameters: A collection of DbParameter objects for parameterized queries.

Executing Commands:

Commands can be executed in various ways:

• ExecuteReader(): Returns a DataReader object for retrieving rows.
• ExecuteNonQuery(): Executes a SQL statement that returns no rows (e.g., INSERT, UPDATE, DELETE) and returns the number of rows affected.
• ExecuteScalar(): Executes a query and returns the value of the first column of the first row in the result set.
• ExecuteXmlReader(): Executes the command and returns an XmlReader.

Example: Executing a SELECT query

using (SqlConnection connection = new SqlConnection(connectionString))
{
    string query = "SELECT CustomerID, CompanyName FROM Customers WHERE City = @City";
    SqlCommand command = new SqlCommand(query, connection);
    command.Parameters.AddWithValue("@City", "London");

    connection.Open();
    SqlDataReader reader = command.ExecuteReader();

    while (reader.Read())
    {
        Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}");
    }
    reader.Close();
}
            

DbDataReader Objects

DbDataReader (and its provider-specific subclasses like SqlDataReader) provides a way to retrieve a forward-only stream of rows from a data source. It is the most efficient way to retrieve data because it reads data as it is returned from the database, without buffering the entire result set in memory.

Key Methods:

• Read(): Advances the DataReader to the next record. Returns true if there are more rows; otherwise, false.
• GetOrdinal(string fieldName): Gets the zero-based column ordinal of the specified column name.
• GetValue(int i): Gets the value of the specified column.
• IsDBNull(int i): Returns a boolean indicating whether the specified column contains a null value.

Accessing Data:

Data can be accessed by column ordinal or column name. It's generally recommended to use the column ordinal for performance, obtained using GetOrdinal.

int idOrdinal = reader.GetOrdinal("CustomerID");
int nameOrdinal = reader.GetOrdinal("CompanyName");

while (reader.Read())
{
    if (!reader.IsDBNull(idOrdinal))
    {
        int customerId = reader.GetInt32(idOrdinal);
        string companyName = reader.GetString(nameOrdinal);
        Console.WriteLine($"ID: {customerId}, Name: {companyName}");
    }
}