Microsoft Docs

Understanding DataSet and DataTable in ADO.NET

The DataSet and DataTable objects are fundamental building blocks of ADO.NET, providing an in-memory representation of data. They allow you to work with data independently of the data source, enabling rich manipulation and caching capabilities.

What is a DataTable?

A DataTable represents a single table of data in memory. It consists of a collection of DataRow objects and a DataColumn collection that defines the schema of the table. Think of it as a local copy of a database table.

Key Properties and Methods:

• Columns: A collection of DataColumn objects that define the table's structure.
• Rows: A collection of DataRow objects representing the data.
• Select(): Allows you to query the DataTable using a filter expression and sort order.
• AcceptChanges(): Commits all pending changes to the DataTable.
• GetChanges(): Returns a subset of the DataTable that contains rows that have been added, deleted, or modified.

Example: Creating and Populating a DataTable

using System.Data;

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

// Define columns
DataColumn idColumn = new DataColumn("CustomerID", typeof(int));
DataColumn nameColumn = new DataColumn("CustomerName", typeof(string));
DataColumn cityColumn = new DataColumn("City", typeof(string));

customersTable.Columns.Add(idColumn);
customersTable.Columns.Add(nameColumn);
customersTable.Columns.Add(cityColumn);

// Add rows
DataRow row1 = customersTable.NewRow();
row1["CustomerID"] = 1;
row1["CustomerName"] = "Alice Smith";
row1["City"] = "New York";
customersTable.Rows.Add(row1);

DataRow row2 = customersTable.NewRow();
row2["CustomerID"] = 2;
row2["CustomerName"] = "Bob Johnson";
row2["City"] = "London";
customersTable.Rows.Add(row2);

// You can access data like this:
// Console.WriteLine($"Customer Name: {customersTable.Rows[0]["CustomerName"]}");
                

What is a DataSet?

A DataSet represents a complete set of data, potentially comprising multiple, related DataTable objects. It's designed to hold relational data from multiple tables, along with constraints and relationships between them. A DataSet can be populated from various data sources, including SQL Server, XML files, or other DataAdapters.

Key Properties and Methods:

• Tables: A collection of DataTable objects contained within the DataSet.
• Relations: A collection of DataRelation objects defining relationships between tables.
• EnforceConstraints: A boolean property that determines whether constraints (like foreign keys) are enforced.
• Clone(): Creates a new DataSet with the same schema but no data.
• Copy(): Creates a new DataSet with the same schema and data.

Example: Creating a DataSet with Multiple DataTables

using System.Data;

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

// Create and populate the 'Products' DataTable
DataTable productsTable = new DataTable("Products");
productsTable.Columns.Add("ProductID", typeof(int));
productsTable.Columns.Add("ProductName", typeof(string));
productsTable.Columns.Add("Price", typeof(decimal));

productsTable.Rows.Add(101, "Laptop", 1200.50m);
productsTable.Rows.Add(102, "Mouse", 25.00m);

// Create and populate the 'Orders' DataTable
DataTable ordersTable = new DataTable("Orders");
ordersTable.Columns.Add("OrderID", typeof(int));
ordersTable.Columns.Add("ProductID", typeof(int));
ordersTable.Columns.Add("Quantity", typeof(int));

ordersTable.Rows.Add(1, 101, 2);
ordersTable.Rows.Add(2, 102, 5);
ordersTable.Rows.Add(3, 101, 1);

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

// Optionally, define a relationship
DataRelation relation = new DataRelation("ProductOrders",
    dataSet.Tables["Products"].Columns["ProductID"],
    dataSet.Tables["Orders"].Columns["ProductID"]);
dataSet.Relations.Add(relation);

// Accessing data from related tables:
// foreach (DataRow orderRow in dataSet.Tables["Orders"].Rows)
// {
//     DataRow productRow = orderRow.GetParentRow("ProductOrders");
//     Console.WriteLine($"Order {orderRow["OrderID"]} includes {orderRow["Quantity"]} of {productRow["ProductName"]}");
// }
                

When to Use DataSet and DataTable

• DataTable: Ideal for scenarios where you need to work with a single table of data, such as loading data into a grid control, performing client-side filtering, or manipulating a single dataset before updating a database.
• DataSet: Useful when you need to represent and manage multiple related tables of data. This is common when fetching data for complex forms or reports that involve joins across tables, or when working with XML data that has a hierarchical structure.

Key Concepts for Working with DataSets and DataTables

• Schema Definition: Carefully define your columns (name, data type, constraints) to ensure data integrity.
• Row States: Rows in a DataTable have states like Added, Modified, Deleted, or Unchanged, which are crucial for tracking changes.
• Data Relations: Define relationships between tables within a DataSet to enable navigation and constraint enforcement.
• DataView: A DataView provides a customizable view of a DataTable, allowing sorting, filtering, and searching without modifying the underlying data.

Mastering DataSet and DataTable is essential for effective data access and manipulation in .NET applications, providing a robust in-memory data model.