MSDN Documentation

Datasets in ADO.NET

A DataSet is an in-memory representation of data, typically from a relational data source. It is a collection of DataTable objects, which in turn contain DataRow objects representing records and DataColumn objects defining the schema. DataSet objects are particularly useful for working with disconnected data scenarios.

Key Characteristics of a DataSet

• In-Memory Cache: Holds data locally, allowing for manipulation without constant database interaction.
• Disconnected Operation: Can be populated from a data source and then updated independently. Changes can be merged back to the source later.
• Schema and Data: Stores both the structure (tables, columns, relations) and the actual data.
• XML Support: Can be serialized to and deserialized from XML.
• Constraints and Relations: Supports defining primary keys, foreign keys, and other constraints for data integrity.

Creating and Populating a DataSet

You can create a DataSet programmatically or populate it using a DataAdapter.

Programmatic Creation:

Manually define tables, columns, and add rows.

using System.Data;

DataSet myDataSet = new DataSet("MySampleData");

DataTable employeesTable = new DataTable("Employees");
employeesTable.Columns.Add("EmployeeID", typeof(int));
employeesTable.Columns.Add("FirstName", typeof(string));
employeesTable.Columns.Add("LastName", typeof(string));
employeesTable.PrimaryKey = new DataColumn[] { employeesTable.Columns["EmployeeID"] };

employeesTable.Rows.Add(1, "Nancy", "Davolio");
employeesTable.Rows.Add(2, "Andrew", "Fuller");

myDataSet.Tables.Add(employeesTable);

// You can add more tables and define relationships
            

Populating with a DataAdapter:

This is the most common method, where a DataAdapter (like SqlDataAdapter or OleDbDataAdapter) fills the DataSet from a database.

using System.Data;
using System.Data.SqlClient; // For SQL Server

// Assuming connectionString is properly defined
SqlConnection connection = new SqlConnection(connectionString);
SqlDataAdapter adapter = new SqlDataAdapter("SELECT EmployeeID, FirstName, LastName FROM Employees", connection);
DataSet employeeDataSet = new DataSet();

adapter.Fill(employeeDataSet, "Employees");
            

Working with DataTables within a DataSet

Once populated, you can access individual DataTable objects by name or index.

DataTable employees = myDataSet.Tables["Employees"]; // Or myDataSet.Tables[0];

foreach (DataRow row in employees.Rows)
{
    Console.WriteLine($"ID: {row["EmployeeID"]}, Name: {row["FirstName"]} {row["LastName"]}");
}
            

Managing Changes and Updates

DataSet keeps track of the original and current versions of rows, enabling effective tracking of modifications.

• Row State: Rows can be in states like Added, Modified, Deleted, or Unchanged.
• AcceptChanges: Commits all changes made to the DataSet or a specific table, resetting row states to Unchanged.
• RejectChanges: Reverts all changes made since the last AcceptChanges call.
• GetChanges: Returns a DataSet containing only the rows that have been changed. This is crucial for updating the data source.

DataSet vs. DataTable

A DataTable represents a single table of data, whereas a DataSet can contain multiple, related DataTable objects.

Summary

The DataSet is a cornerstone of ADO.NET for managing relational data in memory. Its ability to handle disconnected data, support constraints, and integrate with XML makes it a versatile tool for many data-driven applications.