ADO.NET Dataset Class

The System.Data.DataSet class is an in-memory representation of data. It is a collection of DataTable objects that can represent multiple tables of data and the relationships between them. A DataSet is useful for scenarios where you need to work with data offline, such as in disconnected applications, or when you need to manage complex data structures.

Key Features of DataSet

• In-Memory Cache: Holds data in memory, allowing for rapid access and manipulation without constant database round trips.
• Collections of Tables: Contains one or more DataTable objects, each representing a single table of data.
• Relationships: Supports defining DataRelation objects to link tables, mimicking foreign key relationships.
• Constraints: Allows for the enforcement of data integrity through Constraint objects like UniqueConstraint and ForeignKeyConstraint.
• Extensive API: Provides a rich set of properties and methods for querying, filtering, sorting, and manipulating data.
• XML Support: Can be serialized to and deserialized from XML, making it easy to exchange data.

Creating and Populating a DataSet

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

Using DataAdapter

The most common way to populate a DataSet is by using a DataAdapter (e.g., SqlDataAdapter for SQL Server, OleDbDataAdapter for OLE DB) and a Command object.

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

// Assume connectionString and selectCommandText are defined
string connectionString = "Your_Connection_String_Here";
string selectCommandText = "SELECT CustomerID, CompanyName FROM Customers";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter(selectCommandText, connection);
    DataSet dataSet = new DataSet();

    // Fill the DataSet with data
    adapter.Fill(dataSet, "Customers"); // "Customers" is the name of the DataTable

    // Access the DataTable
    DataTable customersTable = dataSet.Tables["Customers"];

    // Now you can work with the customersTable...
    foreach (DataRow row in customersTable.Rows)
    {
        Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["CompanyName"]}");
    }
}
            

Programmatic Creation

You can also define the structure of a DataSet and add tables manually.

DataSet myDataSet = new DataSet("MyDatabase");

// Create a new DataTable
DataTable productsTable = new DataTable("Products");

// Define columns
DataColumn idColumn = new DataColumn("ProductID", typeof(int));
idColumn.AutoIncrement = true;
idColumn.AutoIncrementSeed = 1;
idColumn.AutoIncrementStep = 1;
idColumn.Unique = true;
idColumn.AllowDBNull = false;

DataColumn nameColumn = new DataColumn("ProductName", typeof(string));
nameColumn.MaxLength = 100;
nameColumn.AllowDBNull = false;

// Add columns to the DataTable
productsTable.Columns.Add(idColumn);
productsTable.Columns.Add(nameColumn);

// Set primary key
productsTable.PrimaryKey = new DataColumn[] { idColumn };

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

// Add some data rows
DataRow newRow = productsTable.NewRow();
newRow["ProductName"] = "Chai";
productsTable.Rows.Add(newRow);

newRow = productsTable.NewRow();
newRow["ProductName"] = "Aniseed Syrup";
productsTable.Rows.Add(newRow);
            

Working with Data in a DataSet

Once a DataSet is populated, you can interact with its contents:

• Accessing Tables: Use dataSet.Tables["TableName"] or dataSet.Tables[index].
• Accessing Rows: Iterate through dataTable.Rows. Each element is a DataRow.
• Accessing Columns: Access column values using dataRow["ColumnName"] or dataRow[index].
• Filtering and Sorting: Use dataTable.Select() to filter rows and manipulate the DefaultView of a DataTable for sorting.

Filtering Rows

// Assuming 'customersTable' is a populated DataTable
string filterExpression = "CompanyName LIKE 'A%'";
DataRow[] filteredRows = customersTable.Select(filterExpression);

Console.WriteLine($"Found {filteredRows.Length} companies starting with 'A':");
foreach (DataRow row in filteredRows)
{
    Console.WriteLine(row["CompanyName"]);
}
            

Sorting Rows

// Assuming 'customersTable' is a populated DataTable
customersTable.DefaultView.Sort = "CompanyName ASC";
DataTable sortedTable = customersTable.DefaultView.ToTable();

Console.WriteLine("Companies sorted alphabetically:");
foreach (DataRow row in sortedTable.Rows)
{
    Console.WriteLine(row["CompanyName"]);
}
            

Relationships between Tables

You can define relationships to connect DataTable objects within a DataSet, enabling you to navigate between related data.

// Assuming you have two DataTables: 'customersTable' and 'ordersTable'
// and they both have a common 'CustomerID' column.

DataColumn parentColumn = myDataSet.Tables["Customers"].Columns["CustomerID"];
DataColumn childColumn = myDataSet.Tables["Orders"].Columns["CustomerID"];

DataRelation relation = new DataRelation("CustomerOrders", parentColumn, childColumn);
myDataSet.Relations.Add(relation);

// Now you can find orders for a specific customer
DataRow customerRow = myDataSet.Tables["Customers"].Rows[0]; // Get the first customer
DataRow[] ordersForCustomer = customerRow.GetChildRows(relation);

Console.WriteLine($"Orders for {customerRow["CompanyName"]}:");
foreach (DataRow orderRow in ordersForCustomer)
{
    Console.WriteLine($"- Order ID: {orderRow["OrderID"]}");
}