ADO.NET DataSet Overview

You are here: Documentation > .NET > Concepts > Data Access > ADO.NET > DataSet Overview

The DataSet object in ADO.NET is an in-memory representation of data. It is a collection of DataTable objects, which in turn contain rows and columns of data. The DataSet can hold multiple related tables, as well as constraints and relationships between them, allowing you to work with disconnected data.

Key Features of DataSet

• In-Memory Cache: Acts as a local cache for data retrieved from a data source.
• Disconnected Architecture: Enables applications to operate on data without maintaining a constant connection to the database.
• Multiple Tables: Can contain multiple DataTable objects, representing different tables or query results.
• Relationships and Constraints: Supports defining relationships (like foreign keys) and constraints (like unique keys) between tables within the DataSet.
• Schema and Data: Can store both the schema (table structure, column definitions) and the actual data.
• Row States: Each row in a DataTable has a state (Added, Modified, Deleted, Unchanged), which is crucial for tracking changes.

When to Use a DataSet

DataSet is particularly useful in scenarios where:

• You need to work with data offline or in a disconnected manner.
• You need to aggregate data from multiple disparate sources into a single, unified structure.
• You need to perform complex data manipulation, filtering, or sorting operations on a local copy of data.
• You are building applications that require data caching for performance improvements.
• You need to represent hierarchical data structures.

Note: While DataSet is powerful, for simpler scenarios or when performance is critical and you only need to read data sequentially, a DataReader might be a more efficient choice due to its forward-only, read-only nature.

Core Components

The primary components of a DataSet are:

DataTable

A DataTable represents a single table of data. It contains a collection of DataColumn objects (defining the schema) and a collection of DataRow objects (containing the actual data).

using System.Data;

// Creating a DataTable
DataTable productsTable = new DataTable("Products");

// Adding columns
productsTable.Columns.Add("ProductID", typeof(int));
productsTable.Columns.Add("ProductName", typeof(string));
productsTable.Columns.Add("Price", typeof(decimal));

// Adding rows
productsTable.Rows.Add(1, "Laptop", 1200.00m);
productsTable.Rows.Add(2, "Mouse", 25.50m);
productsTable.Rows.Add(3, "Keyboard", 75.00m);
            

DataRelation

A DataRelation defines a relationship between two tables within a DataSet, typically mimicking a foreign key relationship in a relational database.

// Assuming you have an Orders table with CustomerID
// and a Customers table with CustomerID

DataSet customerOrderData = new DataSet("CustomerOrders");

// ... create and populate OrdersTable and CustomersTable ...

// Define the relationship
DataRelation relation = new DataRelation(
    "CustomerOrders",                                // Relation name
    customerOrderData.Tables["Customers"].Columns["CustomerID"], // Parent column
    customerOrderData.Tables["Orders"].Columns["CustomerID"]     // Child column
);

customerOrderData.Relations.Add(relation);
            

Constraints

Constraints enforce data integrity within a DataTable. Common types include:

• UniqueConstraint: Ensures that values in a column or set of columns are unique.
• ForeignKeyConstraint: Enforces referential integrity between two tables.
• UniqueConstraint: Ensures that values in a column or set of columns are unique.
• ConstraintException: Thrown when a constraint is violated.

// Example of adding a primary key constraint
productsTable.PrimaryKey = new DataColumn[] { productsTable.Columns["ProductID"] };
            

Working with Changes

The DataSet is designed to track changes made to its data. You can iterate through rows and check their RowState property. This is crucial when you need to update the data source with the modifications made locally.

foreach (DataRow row in productsTable.Rows)
{
    if (row.RowState == DataRowState.Modified)
    {
        Console.WriteLine($"Product {row["ProductID"]} was modified.");
        // Logic to update the database for this modified row
    }
    else if (row.RowState == DataRowState.Added)
    {
        Console.WriteLine($"Product {row["ProductID"]} was added.");
        // Logic to insert this new row into the database
    }
    else if (row.RowState == DataRowState.Deleted)
    {
        Console.WriteLine($"Product {row["ProductID"]} was deleted.");
        // Logic to delete this row from the database
    }
}
            

Accepting and Rejecting Changes

After processing changes, you can accept them (making the current state the original) or reject them (reverting to the last accepted state).

• DataSet.AcceptChanges(): Commits all pending changes.
• DataSet.RejectChanges(): Reverts all pending changes.

This overview provides a foundational understanding of the ADO.NET DataSet. For more detailed information on specific features like merging, XML support, and advanced scenarios, please refer to the respective sections in the ADO.NET documentation.