Microsoft Docs

Understanding ADO.NET Datasets

This document provides a comprehensive overview of ADO.NET Datasets, a fundamental component for working with data in .NET applications. Datasets represent an in-memory cache of data retrieved from a data source.

What is a Dataset?

A DataSet object is an in-memory representation of data. It is a collection of DataTable objects, which in turn contain rows and columns of data. DataSet objects are useful for:

• Holding data retrieved from a database.
• Working with data offline or disconnected from the data source.
• Performing complex data operations such as merging, relating, and manipulating data without direct interaction with the database.
• Implementing caching mechanisms.

Key Components of a Dataset

• DataTable: Represents a single table of data, similar to a database table. It contains columns (defining the schema) and rows (holding the data).
• DataColumn: Defines a column within a DataTable, including its name, data type, and constraints.
• DataRow: Represents a single row of data within a DataTable.
• DataRelation: Defines a relationship between two DataTable objects, similar to foreign key constraints in a database.
• Constraint: Enforces data integrity within a DataTable, such as unique keys and foreign keys.

Creating and Populating a Dataset

You can create a DataSet programmatically or have it populated by a DataAdapter.

Programmatic Creation:

using System.Data;

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

// Create a DataTable
DataTable customersTable = new DataTable("Customers");

// Add columns to the DataTable
customersTable.Columns.Add("CustomerID", typeof(int));
customersTable.Columns.Add("CompanyName", typeof(string));
customersTable.Columns.Add("ContactName", typeof(string));

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

// Add rows to the DataTable
customersTable.Rows.Add(1, "Acme Corp", "John Doe");
customersTable.Rows.Add(2, "Beta Inc", "Jane Smith");

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

Populating with a DataAdapter:

This is the more common and efficient way to populate a DataSet. A DataAdapter bridges the gap between a DataSet and a data source.

using System.Data;
using System.Data.SqlClient; // Example for SQL Server

// Assuming you have a SqlConnection and SqlCommand
SqlConnection connection = new SqlConnection("YourConnectionString");
SqlCommand command = new SqlCommand("SELECT CustomerID, CompanyName, ContactName FROM Customers", connection);

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

// Create a SqlDataAdapter
SqlDataAdapter adapter = new SqlDataAdapter(command);

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

Working with Data in a Dataset

Once populated, you can access and manipulate the data within the DataSet.

Accessing Tables:

DataTable customers = myDataSet.Tables["Customers"];
// or
DataTable customers = myDataSet.Tables[0];
            

Iterating Through Rows:

foreach (DataRow row in customers.Rows)
{
    Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["CompanyName"]}");
}
            

Filtering Data:

You can use LINQ to query DataTables or the Select method.

// Using Select method
DataRow[] foundRows = customers.Select("CustomerID = 1");
if (foundRows.Length > 0)
{
    Console.WriteLine($"Found: {foundRows[0]["CompanyName"]}");
}

// Using LINQ (requires System.Data.DataSetExtensions)
var companyNames = from DataRow row in customers.Rows
                   where (int)row["CustomerID"] > 1
                   select row["CompanyName"].ToString();

foreach (var name in companyNames)
{
    Console.WriteLine(name);
}
            

Dataset Best Practices and Considerations

Conclusion

ADO.NET Datasets are a powerful tool for managing data in memory. They provide flexibility for disconnected scenarios and complex data manipulation. Understanding their components and how to use them effectively is crucial for building robust .NET data-driven applications.