Home / .NET / APIs / Data Access / ADO.NET / Datasets

ADO.NET Datasets

A Dataset represents an in-memory cache of data. It is a collection of DataTable objects, which in turn contain DataRow objects and DataColumn objects, allowing you to work with data in a disconnected or connected manner. Datasets are invaluable for scenarios where you need to manage complex data structures, perform client-side manipulations, and synchronize changes with a data source.

Core Concepts of ADO.NET Datasets

DataTable

A DataTable object represents a single table of data in memory. It contains a collection of DataColumn objects that define the schema of the table, and a collection of DataRow objects that hold the actual data.


using System.Data;

DataTable customersTable = new DataTable("Customers");
customersTable.Columns.Add("CustomerID", typeof(int));
customersTable.Columns.Add("CompanyName", typeof(string));
customersTable.Columns.Add("ContactName", typeof(string));

// Add rows
customersTable.Rows.Add(1, "Alfreds Futterkiste", "Maria Anders");
customersTable.Rows.Add(2, "Ana Trujillo Emparedados y helados", "Ana Trujillo");

DataRow

A DataRow object represents a single record or row within a DataTable. You can access and modify column values within a DataRow.


DataRow newRow = customersTable.NewRow();
newRow["CustomerID"] = 3;
newRow["CompanyName"] = "Chop-suey Chinese";
newRow["ContactName"] = "Renate Müller";
customersTable.Rows.Add(newRow);

DataColumn

A DataColumn object represents a column in a DataTable. It defines the name, data type, and other properties of the column.

Relations and Constraints

Datasets support the definition of relationships between DataTable objects (using DataRelation) and constraints (like unique constraints or foreign key constraints) to enforce data integrity within the in-memory data.

Key Classes and Members

Core Dataset Classes

System.Data.DataSet: The main container for related DataTable objects.
System.Data.DataTable: Represents a single table of data.
System.Data.DataRow: Represents a single row of data.
System.Data.DataColumn: Represents a column in a DataTable.
System.Data.DataRelation: Defines a relationship between two tables.
System.Data.Constraint: Base class for constraints (e.g., UniqueConstraint, ForeignKeyConstraint).

Common Operations

DataSet.Tables.Add(): Adds a DataTable to the DataSet.
DataTable.Rows.Add(): Adds a new DataRow to a DataTable.
DataTable.Select(): Retrieves rows that match a specified filter expression.
DataSet.Merge(): Merges another DataSet or DataTable into the current one.

Use Cases for Datasets

Disconnected Data Access: Retrieve data from a data source, close the connection, and then work with the data in memory.
Client-Side Data Manipulation: Allow users to edit, add, or delete data on the client and then persist the changes back to the server.
Complex Data Structures: Represent hierarchical data or data with relationships between tables.
Data Caching: Store frequently accessed data in memory for faster retrieval.
XML Integration: Easily read and write data from/to XML format.

Example: Loading and Displaying Data

This example demonstrates loading data from a database into a DataSet and then binding it to a UI element (conceptually).


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

// Assume connection string is defined elsewhere
// string connectionString = "Your_Connection_String_Here";

// 1. Create a DataSet
DataSet dataSet = new DataSet();

// 2. Create a Connection and Command
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    // Load Customers table
    SqlDataAdapter customersAdapter = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers", connection);
    customersAdapter.Fill(dataSet, "Customers");

    // Load Orders table
    SqlDataAdapter ordersAdapter = new SqlDataAdapter("SELECT OrderID, CustomerID, OrderDate FROM Orders", connection);
    ordersAdapter.Fill(dataSet, "Orders");

    // Define a relation between Customers and Orders
    DataRelation relation = new DataRelation("CustomerOrders",
        dataSet.Tables["Customers"].Columns["CustomerID"],
        dataSet.Tables["Orders"].Columns["CustomerID"]);
    dataSet.Relations.Add(relation);

    // Now dataSet.Tables["Customers"] and dataSet.Tables["Orders"] are populated
    // and linked by the "CustomerOrders" relation.
}

// 3. Work with the data (e.g., bind to a UI control)
// For demonstration, let's iterate and print
Console.WriteLine("Customers:");
foreach (DataRow row in dataSet.Tables["Customers"].Rows)
{
    Console.WriteLine($"  ID: {row["CustomerID"]}, Name: {row["CompanyName"]}");
}

Console.WriteLine("\nOrders:");
foreach (DataRow row in dataSet.Tables["Orders"].Rows)
{
    Console.WriteLine($"  OrderID: {row["OrderID"]}, CustomerID: {row["CustomerID"]}, Date: {row["OrderDate"]}");
}

// Accessing related data
Console.WriteLine("\nOrders for Alfreds Futterkiste:");
DataRow[] customerRows = dataSet.Tables["Customers"].Select("CompanyName = 'Alfreds Futterkiste'");
if (customerRows.Length > 0)
{
    DataRow alfredsRow = customerRows[0];
    DataRow[] ordersForAlfreds = alfredsRow.GetChildRows("CustomerOrders");
    foreach (DataRow orderRow in ordersForAlfreds)
    {
        Console.WriteLine($"  OrderID: {orderRow["OrderID"]}, Date: {orderRow["OrderDate"]}");
    }
}

Datasets provide a robust and flexible way to manage data within your .NET applications, offering powerful capabilities for disconnected data scenarios and complex data handling.

.NET Documentation