ADO.NET DataSet Objects

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 is designed to be a complete, self-contained source of data that can be manipulated independently of a data source.

Key Features of DataSet

• In-Memory Cache: Stores data retrieved from a data source.
• Disconnected Architecture: Can be used without an active connection to the database.
• Multiple DataTables: Can hold multiple, related or unrelated tables.
• Relations: Supports defining relationships between tables within the DataSet.
• Constraints: Allows enforcement of uniqueness and foreign key constraints.
• Row State Tracking: Keeps track of the state of each row (Added, Modified, Deleted, Unchanged).
• Extended Properties: Can store custom properties associated with tables and columns.

Working with DataSets

Creating a DataSet

You can create a new DataSet instance like this:

using System.Data;

// Create a new DataSet
DataSet myDataSet = new DataSet("MySampleDataSet");
            

Adding Tables to a DataSet

A DataSet contains a collection of DataTable objects. You add DataTable objects to the DataSet.Tables collection.

DataTable customersTable = new DataTable("Customers");

// Define columns for the Customers table
customersTable.Columns.Add("CustomerID", typeof(int));
customersTable.Columns.Add("CustomerName", typeof(string));

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

// Add the DataTable to the DataSet
myDataSet.Tables.Add(customersTable);
            

Populating a DataSet

Typically, a DataSet is populated using a DataAdapter. The DataAdapter acts as a bridge between the DataSet and the data source.

// Assuming you have a SqlConnection and SqlCommand
SqlDataAdapter adapter = new SqlDataAdapter("SELECT CustomerID, CustomerName FROM Customers", connection);

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

Accessing Data in a DataSet

You can access the DataTable objects within a DataSet by name or index. Then, you can iterate through the rows of the DataTable.

// Access the Customers table
DataTable customers = myDataSet.Tables["Customers"];

// Iterate through rows
foreach (DataRow row in customers.Rows)
{
    Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["CustomerName"]}");
}
            

Relations and Constraints

The DataSet allows you to define relationships between tables, similar to foreign key relationships in a database. This enables easy navigation between related data.

Creating a Relation

DataTable ordersTable = new DataTable("Orders");
ordersTable.Columns.Add("OrderID", typeof(int));
ordersTable.Columns.Add("CustomerID", typeof(int));
ordersTable.Columns.Add("OrderDate", typeof(DateTime));
myDataSet.Tables.Add(ordersTable);

// Define the relationship
DataColumn parentColumn = myDataSet.Tables["Customers"].Columns["CustomerID"];
DataColumn childColumn = myDataSet.Tables["Orders"].Columns["CustomerID"];
myDataSet.Relations.Add("FK_Customer_Orders", parentColumn, childColumn);
            

DataRow customerRow = myDataSet.Tables["Customers"].Rows[0];
DataRow[] orderRows = customerRow.GetChildRows("FK_Customer_Orders");

foreach (DataRow order in orderRows)
{
    Console.WriteLine($"Order ID: {order["OrderID"]}, Date: {order["OrderDate"]}");
}
                

Row States and Versions

Each DataRow maintains information about its state (Added, Modified, Deleted, Unchanged) and versions (Current, Original). This is crucial for tracking changes when updating a data source.

DataRow rowToModify = myDataSet.Tables["Customers"].Rows[0];
rowToModify["CustomerName"] = "Updated Customer Name";
// The row state is automatically set to Modified
Console.WriteLine($"Row State: {rowToModify.RowState}");
// You can access original and current values:
// Console.WriteLine($"Original Name: {rowToModify["CustomerName", DataRowVersion.Original]}");
// Console.WriteLine($"Current Name: {rowToModify["CustomerName", DataRowVersion.Current]}");