Dataset and DataTable

The DataSet and DataTable classes are fundamental components of ADO.NET, providing an in-memory representation of data. They enable applications to work with relational data independently of the data source.

What is a DataSet?

A DataSet is an in-memory, cacheable copy of data obtained from a data source. It represents a collection of DataTable objects, along with their relationships and constraints. A DataSet can hold multiple tables, each with its own schema and data. This makes it ideal for scenarios where you need to work with data offline, merge data from different sources, or perform complex data manipulation.

What is a DataTable?

A DataTable represents a single table of data in memory. It is analogous to a database table, consisting of rows and columns. Each column has a name, data type, and other properties. Each row represents a record in the table.

Key Features and Benefits:

Disconnected Data: Both DataSet and DataTable operate in a disconnected manner, meaning they don't require a constant connection to the database. This improves performance and scalability.
Rich Functionality: They offer powerful features for data manipulation, including sorting, filtering, searching, and data validation.
Schema Definition: You can define the schema of a DataTable, including column names, data types, and constraints, before populating it with data.
Relations and Constraints: DataSet supports defining relationships between DataTable objects, similar to foreign key relationships in a database. It also allows for enforcing constraints like unique keys.
Data Merging: Multiple DataSet objects can be merged into a single DataSet, facilitating data aggregation.
XML Support: DataSet can easily read from and write to XML, making it interoperable with other systems.

Common Scenarios:

Caching data from a database for faster retrieval.
Performing complex client-side data operations without hitting the database repeatedly.
Merging data from multiple disparate sources into a unified view.
Creating reports or data grids that display and manipulate data.

Working with DataSet and DataTable:

Creating a DataTable:

You can create a DataTable programmatically and define its columns and rows:


using System.Data;

// 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;
productsTable.Columns.Add(idColumn);

productsTable.Columns.Add("ProductName", typeof(string));
productsTable.Columns.Add("Price", typeof(decimal));
productsTable.Columns.Add("StockLevel", typeof(int));

// Add rows
DataRow newRow = productsTable.NewRow();
newRow["ProductName"] = "Laptop";
newRow["Price"] = 1200.50m;
newRow["StockLevel"] = 50;
productsTable.Rows.Add(newRow);

newRow = productsTable.NewRow();
newRow["ProductName"] = "Keyboard";
newRow["Price"] = 75.00m;
newRow["StockLevel"] = 150;
productsTable.Rows.Add(newRow);

Creating a DataSet and adding a DataTable:

A DataSet acts as a container for one or more DataTable objects.


// Create a new DataSet
DataSet dataSet = new DataSet("Inventory");

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

Accessing Data:

You can iterate through rows and columns of a DataTable:


foreach (DataRow row in dataSet.Tables["Products"].Rows)
{
    Console.WriteLine($"Product: {row["ProductName"]}, Price: {row["Price"]}");
}

Microsoft Developer Network