Microsoft Docs

Introduction

ADO.NET introduces two core objects that form the backbone of data access and manipulation: the DataSet and the DataAdapter. Together, they provide a powerful mechanism for retrieving, storing, and updating data from various data sources, particularly relational databases, in an application-independent manner.

The DataSet represents a collection of DataTable objects, enabling you to work with data in memory, while the DataAdapter acts as a bridge, facilitating the transfer of data between the DataSet and the data source.

The DataSet Object

A DataSet is an in-memory representation of data. It can hold multiple tables, and more importantly, it can maintain relationships between these tables, just like a relational database. This makes it ideal for working with disconnected data, where you retrieve data, manipulate it on the client, and then send the changes back to the server.

DataTables

Within a DataSet, you find one or more DataTable objects. Each DataTable represents a single table of data. A DataTable contains a collection of columns (DataColumn objects) that define the schema, and a collection of rows (DataRow objects) that contain the actual data.

Relations and Constraints

DataSet supports the definition of relationships between its contained DataTable objects. These relationships are crucial for navigating between related data. You can define:

• Unique Constraints: Ensures that values in a specified column or set of columns are unique within a DataTable.
• Foreign Key Constraints: Enforces referential integrity between two DataTable objects. This is analogous to foreign key constraints in a relational database.

Foreign Key Relations

Foreign key relations allow you to define how one table's column(s) reference another table's column(s). This is essential for performing joins and ensuring consistency when updating related data.

For example, if you have a Customers table and an Orders table, you can define a foreign key relation between Customers.CustomerID and Orders.CustomerID.

The DataAdapter

The DataAdapter is the component responsible for moving data between a data source and a DataSet. It uses four distinct commands to manage this process:

• SelectCommand: Retrieves data from the data source.
• InsertCommand: Inserts new records into the data source.
• UpdateCommand: Updates existing records in the data source.
• DeleteCommand: Deletes records from the data source.

Fill Method

The most common operation of a DataAdapter is to populate a DataSet using its Fill method. This method executes the SelectCommand and loads the results into a specified DataTable within the DataSet.

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

// Assuming 'connectionString' is properly defined
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", connection);
    DataSet dataSet = new DataSet();
    adapter.Fill(dataSet, "Customers"); // Fills the DataSet with a DataTable named "Customers"

    // Now you can access data in dataSet.Tables["Customers"]
}
                    

Update Method

The DataAdapter's Update method is used to persist changes made to the DataSet back to the data source. It examines the RowState property of each row in the specified DataTable and executes the appropriate command (InsertCommand, UpdateCommand, or DeleteCommand) for each modified row.

// Assuming 'dataSet' is populated and modified, and 'adapter' is configured
// For example, a new row was added to dataSet.Tables["Customers"]
// Or an existing row's CompanyName was changed.

// You would typically need to set InsertCommand, UpdateCommand, DeleteCommand first
// For brevity, assuming they are configured.

// Example for UpdateCommand (simplified)
// adapter.UpdateCommand = new SqlCommand("UPDATE Customers SET CompanyName = @CompanyName WHERE CustomerID = @CustomerID", connection);
// adapter.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 50, "CompanyName");
// adapter.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.Int, 0, "CustomerID");

// adapter.Update(dataSet, "Customers"); // Sends changes back to the database
                    

SelectCommand, InsertCommand, UpdateCommand, DeleteCommand

These four properties of a DataAdapter are IDbCommand objects that define the SQL statements or stored procedures used to interact with the data source. They are crucial for both retrieving and persisting data.

When using the Update method, each command typically uses parameters to safely pass values from the DataSet to the data source, preventing SQL injection attacks.

Common Workflow

A typical workflow involving DataAdapters and DataSets looks like this:

1. Create a SqlConnection (or other database connection object) and open it.
2. Create a SqlDataAdapter (or appropriate adapter for your data source).
3. Configure the SelectCommand of the DataAdapter to retrieve the desired data.
4. Create a DataSet.
5. Use the DataAdapter.Fill(dataSet) method to populate the DataSet.
6. Close the database connection.
7. Manipulate the data within the DataSet (add, modify, delete rows).
8. When ready to save changes:
1. Re-open the database connection.
2. Configure the InsertCommand, UpdateCommand, and DeleteCommand of the DataAdapter.
3. Use the DataAdapter.Update(dataSet) method to send changes back to the data source.
4. Close the database connection.

Advantages

• Disconnected Architecture: Allows applications to operate on data without maintaining an open connection to the data source, improving scalability and responsiveness.
• Data Independence: DataSet objects are independent of the underlying data source, meaning you can load data from a SQL Server, process it, and then persist it to an XML file or another source without rewriting your data access logic.
• Rich In-Memory Model: Provides a complete relational model in memory, supporting tables, columns, rows, relations, and constraints.
• Ease of Use: Simplifies complex data retrieval and update operations.

Conclusion

DataAdapters and DataSets are fundamental components of ADO.NET that enable efficient data management. By abstracting the data source and providing a robust in-memory data model, they empower developers to build flexible and powerful data-driven applications.