Retrieving and Modifying Data with ADO.NET

ADO.NET provides a robust set of classes for interacting with data sources, enabling you to retrieve, manipulate, and persist data effectively. This section delves into the core concepts and techniques for data retrieval and modification.

Retrieving Data

The primary mechanism for retrieving data in ADO.NET involves using the DataReader object or populating a DataSet.

Using DataReader

The DataReader provides a forward-only, read-only stream of data from your data source. It's highly efficient for scenarios where you need to process data record by record without needing to hold the entire dataset in memory.

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

public class DataReaderExample
{
    public static void ReadCustomers(string connectionString)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string query = "SELECT CustomerID, CompanyName, ContactName FROM Customers";
            SqlCommand command = new SqlCommand(query, connection);

            try
            {
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"{reader["CustomerID"]} - {reader["CompanyName"]} - {reader["ContactName"]}");
                        }
                    }
                    else
                    {
                        Console.WriteLine("No rows found.");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }
    }
}

Using DataSet

A DataSet represents an in-memory cache of data. It can contain multiple tables, relationships, and constraints, making it suitable for disconnected data access scenarios or when you need to work with data offline.

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

public class DataSetExample
{
    public static DataSet GetCustomersDataSet(string connectionString)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string query = "SELECT CustomerID, CompanyName, ContactName FROM Customers";
            SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
            DataSet dataSet = new DataSet();

            try
            {
                adapter.Fill(dataSet, "Customers");
                return dataSet;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
                return null;
            }
        }
    }
}

Modifying Data

Modifying data typically involves executing SQL commands such as INSERT, UPDATE, and DELETE. ADO.NET provides the Command object for this purpose.

Executing Non-Query Commands

The ExecuteNonQuery method is used to execute SQL statements that do not return a result set, such as INSERT, UPDATE, or DELETE statements. It returns the number of rows affected by the command.

using System;
using Microsoft.Data.SqlClient;

public class ModifyDataExample
{
    public static int InsertCustomer(string connectionString, string customerId, string companyName, string contactName)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string query = "INSERT INTO Customers (CustomerID, CompanyName, ContactName) VALUES (@CustomerID, @CompanyName, @ContactName)";
            SqlCommand command = new SqlCommand(query, connection);

            command.Parameters.AddWithValue("@CustomerID", customerId);
            command.Parameters.AddWithValue("@CompanyName", companyName);
            command.Parameters.AddWithValue("@ContactName", contactName);

            try
            {
                connection.Open();
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) inserted.");
                return rowsAffected;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error inserting data: {ex.Message}");
                return -1;
            }
        }
    }
}

Updating and Deleting Data

The process for updating and deleting data is very similar to inserting. You construct the appropriate SQL statement and use ExecuteNonQuery.

using System;
using Microsoft.Data.SqlClient;

public class ModifyDataExample
{
    public static int UpdateCustomerContact(string connectionString, string customerId, string newContactName)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string query = "UPDATE Customers SET ContactName = @ContactName WHERE CustomerID = @CustomerID";
            SqlCommand command = new SqlCommand(query, connection);

            command.Parameters.AddWithValue("@CustomerID", customerId);
            command.Parameters.AddWithValue("@ContactName", newContactName);

            try
            {
                connection.Open();
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) updated.");
                return rowsAffected;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error updating data: {ex.Message}");
                return -1;
            }
        }
    }
}

Using DataAdapter for Batch Updates

For disconnected scenarios where you've modified a DataSet or DataTable, the DataAdapter's Update method is essential. It automatically generates and executes the necessary INSERT, UPDATE, and DELETE commands based on the changes tracked within the dataset.

Transactions

For critical operations that involve multiple data modifications, using transactions ensures data integrity. If any part of the transaction fails, the entire operation can be rolled back, leaving the database in its original state.

Refer to the Transactions section for detailed guidance on implementing transaction management.