MSDN Documentation

Understanding ADO.NET DataSets

The DataSet object is a core component of ADO.NET, representing an in-memory cache of data. It can hold multiple DataTable objects, each representing a distinct table of data, along with relations, constraints, and other information necessary to manage a complete relational data structure.

What is a DataSet?

A DataSet is a collection of DataTable objects that are independently persisted. It can also contain DataRelation objects that link tables together, and Constraint objects that enforce business rules, such as uniqueness and referential integrity.

Key characteristics of a DataSet:

• In-Memory Cache: It holds data locally, allowing for disconnected data manipulation without a constant connection to the database.
• Relational Structure: Supports multiple tables, relationships between them, and enforcement of data integrity rules.
• XML Support: Can be serialized to and deserialized from XML, making it easy to transfer data between applications.
• Disconnected Operation: Ideal for client-server applications where the client can fetch data, modify it, and then send changes back to the server.

Key Components of a DataSet

A DataSet is comprised of several key collections:

• Tables: A collection of DataTable objects that constitute the data within the DataSet.
• Relations: A collection of DataRelation objects that define how tables are linked.
• EnforceConstraints: A boolean property that determines whether constraints are enforced when adding or modifying rows.

Creating and Populating a DataSet

You can create a DataSet and populate it using a DataAdapter:

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

// Assume connectionString and SQL query are defined
string connectionString = "Your_Connection_String";
string query = "SELECT CustomerID, CompanyName FROM Customers";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
    DataSet customerDataSet = new DataSet("Customers"); // Optional: Name the DataSet

    connection.Open();
    adapter.Fill(customerDataSet, "CustomerData"); // Populate and name the DataTable within the DataSet

    // Now customerDataSet.Tables["CustomerData"] contains the data
    foreach (DataRow row in customerDataSet.Tables["CustomerData"].Rows)
    {
        Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["CompanyName"]}");
    }
}
            

Modifying Data in a DataSet

You can directly modify the data within a DataTable contained in a DataSet:

// Assuming customerDataSet and its DataTable "CustomerData" are populated
DataRow newRow = customerDataSet.Tables["CustomerData"].NewRow();
newRow["CustomerID"] = 101;
newRow["CompanyName"] = "New Tech Solutions";
customerDataSet.Tables["CustomerData"].Rows.Add(newRow);

// To update an existing row:
DataRow existingRow = customerDataSet.Tables["CustomerData"].Rows[0]; // Get the first row
existingRow["CompanyName"] = "Acme Corporation (Updated)";
existingRow.AcceptChanges(); // Mark changes as accepted
            

Working with Relations

DataRelation objects allow you to define parent-child relationships between tables in a DataSet, enabling easier navigation and data integrity checks.

Serialization

DataSet objects can be easily serialized to and deserialized from XML:

// Serialize to XML
string xmlData = customerDataSet.GetXml();

// Deserialize from XML
DataSet loadedDataSet = new DataSet();
loadedDataSet.ReadXml(new System.IO.StringReader(xmlData));
            

Use Cases

• Building rich client applications that require local data caching and manipulation.
• Exchanging data between different tiers of an application, especially over network boundaries.
• Working with XML data sources.
• Implementing complex business logic that involves multiple related data tables.