Microsoft Developer Network

Understanding ADO.NET DataSets

The DataSet object is a fundamental component of ADO.NET, providing an in-memory, relational data store. It allows you to cache data, manipulate it, and then update the data source. A DataSet can contain multiple, independently accessible tables, along with constraints and relationships between them.

Key Characteristics of a DataSet:

• In-Memory Cache: Holds data retrieved from a data source.
• Relational: Can contain multiple DataTable objects, representing tables.
• Disconnected: Operates independently of the original data source once populated.
• Supports Schema: Includes metadata about the data, such as column names, data types, and constraints.
• Relationship Management: Allows defining relationships between tables using DataRelation objects.

Creating and Populating DataSets

You can create a DataSet programmatically and then populate it using a DataAdapter.

Example: Creating and Populating with a SqlDataAdapter

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

public class DataSetExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Your_Connection_String_Here";
        string query = "SELECT CustomerID, CompanyName, ContactName FROM Customers";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
            DataSet dataSet = new DataSet("CustomerData");

            // Fill the DataSet
            adapter.Fill(dataSet, "Customers");

            // Accessing data
            DataTable customersTable = dataSet.Tables["Customers"];
            foreach (DataRow row in customersTable.Rows)
            {
                Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["CompanyName"]}, Contact: {row["ContactName"]}");
            }
        }
    }
}

DataTables and Relations

A DataSet is composed of one or more DataTable objects. You can define relationships between these tables to represent foreign key constraints or simply to navigate between related data.

Defining a Relationship

// Assume we have two DataTables: Orders and Customers
            // and we want to link Orders.CustomerID to Customers.CustomerID

            DataTable ordersTable = dataSet.Tables["Orders"];
            DataTable customersTable = dataSet.Tables["Customers"];

            DataColumn parentColumn = customersTable.Columns["CustomerID"];
            DataColumn childColumn = ordersTable.Columns["CustomerID"];

            DataRelation relation = new DataRelation("FK_Customer_Orders", parentColumn, childColumn);
            dataSet.Relations.Add(relation);

            // Now you can navigate:
            // Get the customer for a specific order row
            DataRow orderRow = ordersTable.Rows[0];
            DataRow customerRow = orderRow.GetParentRow("FK_Customer_Orders");
            Console.WriteLine($"Customer for order: {customerRow["CompanyName"]}");
            

Using DataView

DataView provides a way to sort, filter, and search the data in a DataTable without modifying the underlying data itself. It's particularly useful for data-bound controls.

Filtering with DataView

// Filter the Customers table for companies starting with 'A'
            DataView view = new DataView(dataSet.Tables["Customers"]);
            view.RowFilter = "CompanyName LIKE 'A%'";

            foreach (DataRowView rowView in view)
            {
                Console.WriteLine($"Matching Company: {rowView["CompanyName"]}");
            }
            

XML Serialization

DataSet objects can be easily serialized to and deserialized from XML format, which is beneficial for data exchange and persistence.

Writing DataSet to XML

dataSet.WriteXml("customers.xml");
            dataSet.WriteXmlSchema("customers.xsd");
            

Advanced Usage and Best Practices

• Schema Definition: Use DataTable.Columns.Add() with constraints to define your schema.
• Constraints: Implement UniqueConstraint and ForeignKeyConstraint for data integrity.
• AcceptChanges/RejectChanges: Manage changes made to the DataSet.
• DataRowState: Examine the state of rows (Added, Modified, Deleted, Unchanged).
• Performance: For large datasets, consider fetching data in batches or using alternative methods if disconnected operations are not strictly required.