DataSets and DataTables

A DataSet object is an in-memory representation of data, including tables, relationships, and constraints. It is a collection of DataTable objects, which represent tables of data.

Understanding DataSets

A DataSet can hold multiple tables, allowing you to work with related data without needing to maintain separate connections to the database. Each DataTable within a DataSet is an independent in-memory table that can be populated with data from various sources, not just relational databases.

Understanding DataTables

A DataTable represents a single table of data in memory. It consists of a collection of DataRow objects, each representing a row in the table, and a DataColumn collection, which defines the schema of the table (column names, data types, etc.).

Key Features and Use Cases

Offline Data Access: Load data into a DataSet or DataTable and then work with it when disconnected from the data source.
Data Manipulation: Perform operations like filtering, sorting, searching, and updating data in memory.
Data Caching: Store frequently accessed data in memory to reduce database load.
XML Data Integration: Easily read and write DataSet objects to and from XML format.
Working with Multiple Tables: A DataSet is ideal for scenarios where you need to manage related data from several tables simultaneously.

Creating and Populating a DataTable

You can create a DataTable programmatically or by filling it from a data source using a DataAdapter.


// Programmatic creation of a 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
customersTable.Rows.Add(1, "Alice Smith", "New York");
customersTable.Rows.Add(2, "Bob Johnson", "London");
customersTable.Rows.Add(3, "Charlie Brown", "Paris");

// You can then add this DataTable to a DataSet
// DataSet myDataSet = new DataSet();
// myDataSet.Tables.Add(customersTable);

Creating and Populating a DataSet

A DataSet can contain multiple DataTable objects. These tables can be related to each other through defined relationships.


// Create a DataSet
DataSet companyData = new DataSet("Company");

// Create and add the Customers table
DataTable customersTable = new DataTable("Customers");
customersTable.Columns.Add("CustomerID", typeof(int));
customersTable.Columns.Add("CustomerName", typeof(string));
customersTable.Rows.Add(1, "Alice Smith");
customersTable.Rows.Add(2, "Bob Johnson");

companyData.Tables.Add(customersTable);

// Create and add the Orders table
DataTable ordersTable = new DataTable("Orders");
ordersTable.Columns.Add("OrderID", typeof(int));
ordersTable.Columns.Add("CustomerID", typeof(int));
ordersTable.Columns.Add("OrderDate", typeof(DateTime));
ordersTable.Rows.Add(101, 1, new DateTime(2023, 10, 26));
ordersTable.Rows.Add(102, 2, new DateTime(2023, 10, 25));
ordersTable.Rows.Add(103, 1, new DateTime(2023, 10, 24));

companyData.Tables.Add(ordersTable);

// Optional: Define a relation between the tables
DataColumn parentColumn = companyData.Tables["Customers"].Columns["CustomerID"];
DataColumn childColumn = companyData.Tables["Orders"].Columns["CustomerID"];
DataRelation relation = new DataRelation("CustomerOrders", parentColumn, childColumn);
companyData.Relations.Add(relation);

Note

When using a DataAdapter (like SqlDataAdapter), you can automatically populate a DataSet or DataTable by calling the Fill() method. This is the most common way to get data from a database into a DataSet.

Accessing Data

Once populated, you can access data in DataTables using various methods, including iterating through rows or using the Select() method to filter rows.


// Accessing data in a DataTable
foreach (DataRow row in customersTable.Rows)
{
    Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["CustomerName"]}");
}

// Filtering data using Select()
DataRow[] londonCustomers = customersTable.Select("City = 'London'");
foreach (DataRow row in londonCustomers)
{
    Console.WriteLine($"Found in London: {row["CustomerName"]}");
}

Tip

For complex data shaping and querying within a DataSet, consider using LINQ to DataSet, which provides a powerful and expressive way to query in-memory data.

Modifying Data

You can add, update, and delete rows in a DataTable. Changes are tracked, allowing you to later accept or reject them.


// Adding a new row
DataRow newRow = customersTable.NewRow();
newRow["CustomerID"] = 4;
newRow["CustomerName"] = "Diana Prince";
newRow["City"] = "Themyscira";
customersTable.Rows.Add(newRow);

// Updating a row
DataRow[] rowsToUpdate = customersTable.Select("CustomerID = 2");
if (rowsToUpdate.Length > 0)
{
    rowsToUpdate[0]["City"] = "Manchester";
}

// Deleting a row
DataRow[] rowsToDelete = customersTable.Select("CustomerID = 3");
if (rowsToDelete.Length > 0)
{
    rowsToDelete[0].Delete();
}

// Accepting changes
// customersTable.AcceptChanges();

// Rejecting changes
// customersTable.RejectChanges();

Understanding and effectively using DataSet and DataTable objects is fundamental to leveraging the full power of ADO.NET for data management in your applications.