DataAdapters and DataSets

DataSets and DataAdapters are fundamental components of ADO.NET that facilitate disconnected data access. A DataSet represents a set of data in memory, independent of any data source, while a DataAdapter acts as a bridge, filling a DataSet with data from a data source and resolving changes to that data back to the data source.

The DataSet Object

The DataSet is an in-memory representation of data. It can contain multiple, related DataTable objects, each representing a table of data. These tables can be linked via DataRelation objects, mirroring the relationships in a relational database.

  • Tables: A collection of DataTable objects.
  • Relations: A collection of DataRelation objects defining how tables are related.
  • Constraints: Rules like primary keys and unique constraints applied to tables.
Note: The DataSet is designed for disconnected scenarios, allowing you to retrieve data, modify it, and then submit those changes back to the source.

The DataAdapter Object

The DataAdapter is the primary mechanism for moving data between a DataSet and a data source. Different providers (e.g., SQL Server, Oracle, OleDb) offer specific DataAdapter implementations (like SqlDataAdapter, OracleDataAdapter, OleDbDataAdapter).

A DataAdapter typically has four core operations:

  • Fill(): Populates a DataSet with data from the data source.
  • SelectCommand: The Command object used to retrieve data.
  • InsertCommand: The Command object used to insert new records.
  • UpdateCommand: The Command object used to update existing records.
  • DeleteCommand: The Command object used to delete records.

The DataAdapter manages the process of sending commands to the data source and processing the results. It uses Connection objects internally to communicate with the database.

Working with DataAdapters and DataSets

Here's a simplified example of how to use a SqlDataAdapter and a DataSet to retrieve data:


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

public class DataAccess
{
    public static void RetrieveData(string connectionString)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string sql = "SELECT CustomerID, CompanyName, ContactName FROM Customers";
            SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);

            DataSet dataSet = new DataSet();

            try
            {
                connection.Open();
                adapter.Fill(dataSet, "Customers"); // Fills the DataSet with data from the "Customers" table

                // Now you can work with the data in the DataSet
                DataTable customersTable = dataSet.Tables["Customers"];
                foreach (DataRow row in customersTable.Rows)
                {
                    Console.WriteLine($"ID: {row["CustomerID"]}, Company: {row["CompanyName"]}, Contact: {row["ContactName"]}");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine("Error: " + ex.Message);
            }
        }
    }
}

Updating Data

Updating data involves modifying the rows in the DataSet and then calling the DataAdapter.Update() method. The DataAdapter automatically determines which rows have been added, modified, or deleted and executes the appropriate INSERT, UPDATE, or DELETE commands.

DataAdapter.Update() Process:

  1. The DataAdapter examines the RowState of each row in the specified DataTable.
  2. For added rows (RowState.Added), it executes the InsertCommand.
  3. For modified rows (RowState.Modified), it executes the UpdateCommand.
  4. For deleted rows (RowState.Deleted), it executes the DeleteCommand.
  5. After a successful update, the RowState of the affected rows is reset to Unchanged.
Tip: For efficient updates, consider using the AcceptChangesDuringUpdate property of the DataAdapter.

Benefits of Disconnected Data Access

  • Scalability: Applications can retrieve data, close the connection, perform operations, and then re-open the connection to submit changes, reducing the time database connections are held open.
  • Performance: Reduces network traffic and database load by fetching data in batches.
  • Flexibility: Allows for complex data manipulation and business logic to be applied to the data in memory without constant database interaction.