MSDN Documentation

Datasets and DataTables in ADO.NET

ADO.NET provides a rich set of classes for working with data. Among the most fundamental are DataSet and DataTable, which allow you to represent data in memory in a structured and relational manner. These classes are crucial for scenarios where you need to load data from a source, manipulate it offline, and then potentially update the source.

The DataSet Class

A DataSet object represents a complete collection of data, including related tables, constraints, and relationships. Think of it as an in-memory representation of a relational database. A DataSet can contain one or more DataTable objects, and these tables can be linked via DataRelation objects.

Key features of a DataSet:

• Multiple Tables: Can hold a collection of DataTable objects.
• Schema Definition: Defines the structure of the data it contains.
• Constraints: Supports primary keys, foreign keys, and unique constraints.
• Relations: Allows you to define relationships between tables, enabling easy navigation.
• Row States: Tracks the state of each row (e.g., Added, Modified, Deleted, Unchanged), which is vital for updates.
• Change Tracking: Records changes made to the data.

The DataTable Class

A DataTable object represents a single table of data in memory. It consists of a collection of DataRow objects and a DataColumn collection that defines the schema (columns and their data types).

Key features of a DataTable:

• Columns: Defined by DataColumn objects, specifying name, data type, and constraints.
• Rows: Represented by DataRow objects, holding the actual data values for each record.
• Primary Key: Can have a primary key defined to uniquely identify rows.
• Constraints: Supports unique constraints on columns.
• Filtering and Sorting: Allows you to filter and sort rows.
• Accept/Reject Changes: Methods to manage pending changes.

Working with DataSet and DataTable

You typically populate a DataSet or DataTable using a DataAdapter. The DataAdapter acts as a bridge between the DataSet/DataTable and a data source.

Example: Creating and Populating a DataTable

Here's a basic example of how to create a DataTable and add some data to it programmatically:

using System;
using System.Data;

public class DataTableExample
{
    public static void Main(string[] args)
    {
        // Create a new DataTable
        DataTable customersTable = new DataTable("Customers");

        // Define columns
        DataColumn idColumn = new DataColumn("CustomerID", typeof(int));
        idColumn.AutoIncrement = true;
        idColumn.AutoIncrementSeed = 1;
        idColumn.AutoIncrementStep = 1;
        idColumn.ReadOnly = true;
        customersTable.Columns.Add(idColumn);

        DataColumn nameColumn = new DataColumn("Name", typeof(string));
        customersTable.Columns.Add(nameColumn);

        DataColumn cityColumn = new DataColumn("City", typeof(string));
        customersTable.Columns.Add(cityColumn);

        // Set primary key
        customersTable.PrimaryKey = new DataColumn[] { customersTable.Columns["CustomerID"] };

        // Add rows
        DataRow row1 = customersTable.NewRow();
        row1["Name"] = "Alice Smith";
        row1["City"] = "New York";
        customersTable.Rows.Add(row1);

        DataRow row2 = customersTable.NewRow();
        row2["Name"] = "Bob Johnson";
        row2["City"] = "Los Angeles";
        customersTable.Rows.Add(row2);

        // Add another row directly
        customersTable.Rows.Add(new object[] { null, "Charlie Brown", "Chicago" });

        // Display data
        Console.WriteLine("--- Customer Data ---");
        foreach (DataRow row in customersTable.Rows)
        {
            Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["Name"]}, City: {row["City"]}");
        }

        // Find a row
        DataRow foundRow = customersTable.Rows.Find(2);
        if (foundRow != null)
        {
            Console.WriteLine($"\nFound row with ID 2: {foundRow["Name"]}, {foundRow["City"]}");
        }
    }
}
            

Example: Using a DataSet with Multiple Tables

A DataSet is ideal for representing related data from multiple tables. You can load them and define relations.

using System;
using System.Data;

public class DataSetExample
{
    public static void Main(string[] args)
    {
        // Create a DataSet
        DataSet companyData = new DataSet("Company");

        // Create Products table
        DataTable productsTable = new DataTable("Products");
        productsTable.Columns.Add("ProductID", typeof(int)).AutoIncrement = true;
        productsTable.Columns.Add("ProductName", typeof(string));
        productsTable.PrimaryKey = new DataColumn[] { productsTable.Columns["ProductID"] };

        // Create Orders table
        DataTable ordersTable = new DataTable("Orders");
        ordersTable.Columns.Add("OrderID", typeof(int)).AutoIncrement = true;
        ordersTable.Columns.Add("ProductID", typeof(int)); // Foreign key
        ordersTable.Columns.Add("OrderDate", typeof(DateTime));
        ordersTable.PrimaryKey = new DataColumn[] { ordersTable.Columns["OrderID"] };

        // Add tables to the DataSet
        companyData.Tables.Add(productsTable);
        companyData.Tables.Add(ordersTable);

        // Populate Products table
        productsTable.Rows.Add(null, "Laptop");
        productsTable.Rows.Add(null, "Keyboard");
        productsTable.Rows.Add(null, "Mouse");

        // Populate Orders table
        // Assuming ProductID 1 is Laptop, 2 is Keyboard, 3 is Mouse
        ordersTable.Rows.Add(null, 1, DateTime.Now.AddDays(-5)); // Laptop order
        ordersTable.Rows.Add(null, 2, DateTime.Now.AddDays(-3)); // Keyboard order
        ordersTable.Rows.Add(null, 1, DateTime.Now.AddDays(-1)); // Another Laptop order

        // Define a relation between Products and Orders
        DataColumn parentColumn = companyData.Tables["Products"].Columns["ProductID"];
        DataColumn childColumn = companyData.Tables["Orders"].Columns["ProductID"];
        companyData.Relations.Add("ProductOrders", parentColumn, childColumn);

        // Accessing related data
        Console.WriteLine("--- Orders for Laptops ---");
        DataRow laptopRow = companyData.Tables["Products"].Rows.Find(1); // Find the Laptop row

        if (laptopRow != null)
        {
            // Get related child rows (Orders)
            DataRow[] laptopOrders = laptopRow.GetChildRows("ProductOrders");
            foreach (DataRow orderRow in laptopOrders)
            {
                Console.WriteLine($"Order ID: {orderRow["OrderID"]}, Date: {orderRow["OrderDate"]}");
            }
        }
    }
}
            

DataRowState and Change Tracking

DataRow objects maintain a state indicating whether they are new, modified, deleted, or unchanged. This is crucial for synchronizing changes back to the data source using a DataAdapter.

• DataRowState.Added: The row was added to the DataTable.
• DataRowState.Modified: The row's data was changed since it was loaded or its changes were last accepted.
• DataRowState.Deleted: The row was deleted from the DataTable.
• DataRowState.Unchanged: The row has not been modified since it was loaded or its changes were last accepted.

The AcceptChanges() method commits all pending changes (added, modified, deleted rows) and sets their state to Unchanged. The RejectChanges() method discards all pending changes.