Working with Data in ADO.NET

This section covers the fundamental aspects of interacting with data sources using ADO.NET. We'll explore how to retrieve, manipulate, and persist data effectively.

Retrieving Data

The primary mechanism for retrieving data is through the use of DataReader objects. These provide a forward-only, read-only stream of data from your data source.

Using SqlDataReader (or equivalent for other providers)

The DataReader is highly efficient for scenarios where you need to iterate through a result set without loading the entire dataset into memory. This is particularly useful for large tables or when you only need to process records one by one.

using System;
using System.Data;
using Microsoft.Data.SqlClient; // Or System.Data.SqlClient for .NET Framework

public class DataRetrieval
{
    public static void ReadCustomers(string connectionString)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string sql = "SELECT CustomerID, CompanyName, ContactName FROM Customers";
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                try
                {
                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}, Contact: {reader["ContactName"]}");
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error reading data: {ex.Message}");
                }
            }
        }
    }
}
            

Executing Commands

ADO.NET allows you to execute various types of commands against your data source, including queries, stored procedures, and data modification statements (INSERT, UPDATE, DELETE).

ExecuteNonQuery()

Used for commands that do not return a result set, such as INSERT, UPDATE, or DELETE statements. It returns the number of rows affected by the command.

ExecuteReader()

As shown above, this method is used to return a DataReader object, enabling you to read rows from a query result.

ExecuteScalar()

Used to retrieve a single value (e.g., a count, a sum, or a specific field) from a query. It returns the value of the first column of the first row in the result set.

public static int GetCustomerCount(string connectionString)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string sql = "SELECT COUNT(*) FROM Customers";
        using (SqlCommand command = new SqlCommand(sql, connection))
        {
            try
            {
                connection.Open();
                object result = command.ExecuteScalar();
                return (result == DBNull.Value) ? 0 : Convert.ToInt32(result);
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error getting customer count: {ex.Message}");
                return -1; // Indicate error
            }
        }
    }
}
            

Manipulating Data with DataAdapters and DataSets

For more complex data manipulation scenarios, especially when working offline or with disconnected scenarios, ADO.NET provides DataSet and DataAdapter objects.

A DataSet is an in-memory representation of data, containing one or more DataTable objects. A DataAdapter acts as a bridge between a DataSet and a data source, managing the retrieval and saving of data.

The DataAdapter Workflow

1. Create a DataAdapter (e.g., SqlDataAdapter).
2. Define SelectCommand to retrieve data and populate a DataSet using its Fill() method.
3. Make changes to the DataTable within the DataSet.
4. Use the DataAdapter's Update() method to synchronize changes back to the data source. This involves defining InsertCommand, UpdateCommand, and DeleteCommand.

Parameterizing Queries

It is essential to use parameterized queries to prevent SQL injection vulnerabilities and to improve performance by allowing the database to cache query plans.

public static void GetCustomerById(string connectionString, string customerId)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string sql = "SELECT CustomerID, CompanyName, ContactName FROM Customers WHERE CustomerID = @CustomerID";
        using (SqlCommand command = new SqlCommand(sql, connection))
        {
            // Add parameter
            command.Parameters.AddWithValue("@CustomerID", customerId);

            try
            {
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        Console.WriteLine($"Found Customer - ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}");
                    }
                    else
                    {
                        Console.WriteLine($"Customer with ID {customerId} not found.");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error retrieving customer: {ex.Message}");
            }
        }
    }
}
            

This approach clearly separates the SQL command text from the data values, providing a secure and efficient way to interact with your database.