Microsoft Developer Network

Documentation

Understanding Datasets and DataViews in ADO.NET

The DataSet object is a core component of ADO.NET, providing an in-memory representation of data from a relational database. It's a collection of DataTable objects, each representing a table of data, along with relationships and constraints between them. The DataView object, on the other hand, offers a dynamic view of a DataTable, allowing you to sort, filter, and navigate through the data without modifying the underlying table.

The DataSet Object

A DataSet can hold multiple tables, allowing you to work with related data in a disconnected manner. This is particularly useful in multi-tiered applications where you might fetch data from a server, manipulate it on the client, and then send it back for updates.

Key Features of DataSet:

  • In-memory cache: Stores data locally.
  • Multiple tables: Can contain several DataTable objects.
  • Relationships: Supports defining foreign key relationships between tables.
  • Constraints: Allows enforcement of unique keys, foreign keys, and check constraints.
  • Row States: Tracks the state of each row (Added, Modified, Deleted, Unchanged).

Here's a simple C# example of creating and populating a DataSet:

using System.Data;

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

// Create a DataTable for Customers
DataTable customersTable = new DataTable("Customers");
customersTable.Columns.Add("CustomerID", typeof(int));
customersTable.Columns.Add("CustomerName", typeof(string));
customersTable.PrimaryKey = new DataColumn[] { customersTable.Columns["CustomerID"] };

// Add some rows
customersTable.Rows.Add(1, "Alice Smith");
customersTable.Rows.Add(2, "Bob Johnson");

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

// Create another DataTable for Orders
DataTable ordersTable = new DataTable("Orders");
ordersTable.Columns.Add("OrderID", typeof(int));
ordersTable.Columns.Add("CustomerID", typeof(int));
ordersTable.Columns.Add("OrderDate", typeof(DateTime));
ordersTable.PrimaryKey = new DataColumn[] { ordersTable.Columns["OrderID"] };

// Add some rows
ordersTable.Rows.Add(101, 1, new DateTime(2023, 10, 26));
ordersTable.Rows.Add(102, 1, new DateTime(2023, 10, 27));
ordersTable.Rows.Add(103, 2, new DateTime(2023, 10, 28));

// Add the DataTable to the DataSet
ds.Tables.Add(ordersTable);

// Define a relationship
ds.Relations.Add("CustomerOrders", ds.Tables["Customers"].Columns["CustomerID"], ds.Tables["Orders"].Columns["CustomerID"]);

The DataView Object

A DataView provides a customizable view of a single DataTable. It acts as a binder between the DataTable and data-bound controls, enabling efficient sorting, filtering, and searching of data. Unlike creating a new DataTable, a DataView doesn't duplicate the data; it references the existing data in the DataTable.

Key Features of DataView:

  • Sorting: Allows defining sort orders for columns.
  • Filtering: Enables applying filters to select specific rows based on criteria.
  • Dynamic Updates: Reflects changes made to the underlying DataTable in real-time.
  • Row Navigation: Provides methods to move through the data.

Consider the customersTable from the previous example. We can create a DataView to sort it by name:

// Assuming 'ds' is the DataSet from the previous example
DataTable customersTable = ds.Tables["Customers"];

// Create a DataView
DataView dvCustomers = new DataView(customersTable);

// Sort the DataView by CustomerName
dvCustomers.Sort = "CustomerName ASC";

// You can now iterate through dvCustomers to see the sorted data
foreach (DataRowView drv in dvCustomers) {
Console.WriteLine($"ID: {drv["CustomerID"]}, Name: {drv["CustomerName"]}");
}

You can also filter the data. For instance, to see only customers whose ID is greater than 1:

// Assuming 'dvCustomers' is the DataView created above
dvCustomers.RowFilter = "CustomerID > 1";

// Iterate to see filtered results
foreach (DataRowView drv in dvCustomers) {
Console.WriteLine($"ID: {drv["CustomerID"]}, Name: {drv["CustomerName"]}");
}

Note on Data Binding

DataView objects are commonly used with data-bound controls in UI frameworks like Windows Forms and ASP.NET. They provide an efficient way to present and interact with data without complex data manipulation in the UI layer.

When to Use Which

  • Use DataSet when you need to work with multiple related tables in memory, often in a disconnected scenario, and require the ability to manage relationships and constraints.
  • Use DataView when you need to present a specific, sortable, and filterable view of data from a single DataTable, especially for data binding purposes.

Important Considerations

DataSet can consume significant memory if it holds large amounts of data. For scenarios where you only need to read data sequentially without the need for sorting or filtering on the client, consider using DataReader objects, which are more memory-efficient.