DataSet Class

The DataSet class in ADO.NET represents an in-memory cache of data. It is a disconnected data store that can hold multiple tables, relationships between them, and constraints. A DataSet is a fundamental component for working with data in a disconnected manner, allowing you to retrieve, manipulate, and persist data without maintaining a constant connection to the data source.

Overview

The DataSet class is part of the System.Data namespace. It provides a rich set of functionalities for:

Key Components of a DataSet

Creating and Populating a DataSet

You can create a new DataSet instance and then populate it using a DataAdapter or by programmatically adding DataTable objects.

Using DataAdapter

The most common way to populate a DataSet is by using a DataAdapter (e.g., SqlDataAdapter, OleDbDataAdapter) which executes commands against a data source and fills the DataSet with the results.


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

// Assume connectionString and selectCommand are defined

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlDataAdapter adapter = new SqlDataAdapter(selectCommand, connection);
    DataSet dataSet = new DataSet("MySampleData");
    adapter.Fill(dataSet, "Customers"); // Fills the DataSet with a DataTable named "Customers"
}

Programmatically

You can also define tables and columns manually and add rows.


DataSet myDataSet = new DataSet("ProductCatalog");

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

// Add columns
productsTable.Columns.Add("ProductID", typeof(int));
productsTable.Columns.Add("ProductName", typeof(string));
productsTable.Columns.Add("Price", typeof(decimal));

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

// Add rows
productsTable.Rows.Add(1, "Laptop", 1200.50);
productsTable.Rows.Add(2, "Keyboard", 75.00);
productsTable.Rows.Add(3, "Mouse", 25.99);

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

// You can add more tables and relations here...

Working with Data in a DataSet

Accessing Tables

You can access a specific DataTable within a DataSet by its name or index:


DataTable customersTable = myDataSet.Tables["Customers"];
// or
DataTable productsTable = myDataSet.Tables[0]; // Assuming Products is the first table

Iterating Through Rows

You can loop through the rows of a DataTable:


foreach (DataRow row in productsTable.Rows)
{
    int productId = (int)row["ProductID"];
    string productName = (string)row["ProductName"];
    decimal price = (decimal)row["Price"];
    Console.WriteLine($"ID: {productId}, Name: {productName}, Price: {price:C}");
}

Filtering and Sorting with DataView

The DataView class allows you to create a customizable view of a DataTable, enabling sorting, filtering, and searching.


DataView productView = new DataView(productsTable);
productView.RowFilter = "Price > 100"; // Filter for products with price over 100
productView.Sort = "ProductName ASC";  // Sort by product name ascending

foreach (DataRowView rowView in productView)
{
    Console.WriteLine(rowView["ProductName"]);
}

Handling Changes

DataSet tracks changes made to rows. You can access the different states of rows:

You can also work with original and current versions of a row using row.GetChildRows() or row.GetParentRow().

Accepting and Rejecting Changes

After making changes, you can either commit them or discard them:


// Make some changes to rows...

myDataSet.AcceptChanges(); // Commits all changes in the DataSet
// or
myDataSet.RejectChanges(); // Rejects all changes and reverts to the last accepted state

Relationships and Constraints

DataRelation objects link tables based on common columns, mimicking foreign key relationships. Constraints ensure data integrity:

Tip: When defining relations, make sure the data types of the related columns match.

XML Support

DataSet has excellent support for XML, allowing you to easily read and write its contents to XML format, preserving schema and data.


// Write DataSet to XML
myDataSet.WriteXml("data.xml");
myDataSet.WriteXmlSchema("data.xsd");

// Read DataSet from XML
myDataSet.ReadXml("data.xml");
myDataSet.ReadXmlSchema("data.xsd");

When to Use DataSet

DataSet is particularly useful in scenarios where:

Note: For simpler scenarios involving a single table and straightforward operations, DataTable might be sufficient. For scenarios requiring ORM capabilities, consider Entity Framework.

See Also