.NET Datasets

Introduction

A DataSet is one of the core components of ADO.NET, providing a powerful in-memory representation of data. It's designed to work independently of any data source, making it ideal for disconnected scenarios where you need to work with data without maintaining a constant connection to the database.

Think of a DataSet as a mini in-memory database that can contain multiple tables (DataTables), relationships between those tables (DataRelations), and constraints. This makes it particularly useful when you need to:

• Work with data offline
• Manipulate data before sending it back to the database
• Transfer data between different applications
• Cache database data for improved performance

In this tutorial, we'll explore how DataSets work in .NET, how to create and manipulate them, and see some practical examples of their use in real-world applications.

DataSet Basics

Structure of a DataSet

A DataSet consists of the following main components:

1. DataTables: Collection of data tables, each representing a single table or result set
2. DataRelations: Defines relationships between tables (similar to foreign key relationships in a database)
3. Constraints: Rules that ensure data integrity (like unique constraints or check constraints)

Let's look at a simple example of creating a DataSet:

csharp

// Create a new DataSet
DataSet customersDataSet = new DataSet("CustomerOrders");

// Now we have an empty DataSet named "CustomerOrders"
Console.WriteLine($"DataSet Name: {customersDataSet.DataSetName}");

Output:

DataSet Name: CustomerOrders

Creating and Populating DataSets

Let's see how to create and populate a DataSet with data:

Method 1: Using a DataAdapter

A DataAdapter is the bridge between your DataSet and the database. It contains the SQL commands needed to connect to the database, fetch data, and update the database with changes.

csharp

using System;
using System.Data;
using System.Data.SqlClient;

public class DataSetExample
{
    public static void Main()
    {
        // Connection string for SQL Server
        string connectionString = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True";
        
        // Create the connection
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Create a DataAdapter to fetch Customer data
            SqlDataAdapter customerAdapter = new SqlDataAdapter(
                "SELECT CustomerID, CompanyName, ContactName, Country FROM Customers", 
                connection);
                
            // Create and fill the DataSet
            DataSet customersDataSet = new DataSet("CustomerData");
            customerAdapter.Fill(customersDataSet, "Customers");
            
            // Display the number of rows fetched
            Console.WriteLine($"Fetched {customersDataSet.Tables["Customers"].Rows.Count} customer records");
        }
    }
}

Output (approximate):

Fetched 91 customer records

Method 2: Building DataSets Manually

You can also create DataSets from scratch without connecting to a database:

csharp

// Create a new DataSet
DataSet schoolDataSet = new DataSet("School");

// Create a DataTable for students
DataTable studentsTable = new DataTable("Students");

// Define columns
studentsTable.Columns.Add("StudentID", typeof(int));
studentsTable.Columns.Add("Name", typeof(string));
studentsTable.Columns.Add("Grade", typeof(string));

// Add the table to the DataSet
schoolDataSet.Tables.Add(studentsTable);

// Add rows to the table
studentsTable.Rows.Add(1, "John Smith", "A");
studentsTable.Rows.Add(2, "Jane Doe", "B");
studentsTable.Rows.Add(3, "Bob Johnson", "A-");

// Display the data
foreach (DataRow row in studentsTable.Rows)
{
    Console.WriteLine($"ID: {row["StudentID"]}, Name: {row["Name"]}, Grade: {row["Grade"]}");
}

Output:

ID: 1, Name: John Smith, Grade: A
ID: 2, Name: Jane Doe, Grade: B
ID: 3, Name: Bob Johnson, Grade: A-

Working with DataTables in a DataSet

A DataSet can contain multiple DataTables. Let's explore how to work with multiple tables and create relationships between them:

csharp

// Create a DataSet for a bookstore
DataSet bookstoreDataSet = new DataSet("Bookstore");

// Create Authors table
DataTable authorsTable = new DataTable("Authors");
authorsTable.Columns.Add("AuthorID", typeof(int));
authorsTable.Columns.Add("Name", typeof(string));
authorsTable.Columns.Add("Country", typeof(string));

// Set Primary Key
authorsTable.PrimaryKey = new DataColumn[] { authorsTable.Columns["AuthorID"] };

// Create Books table
DataTable booksTable = new DataTable("Books");
booksTable.Columns.Add("BookID", typeof(int));
booksTable.Columns.Add("Title", typeof(string));
booksTable.Columns.Add("AuthorID", typeof(int));
booksTable.Columns.Add("Price", typeof(decimal));

// Set Primary Key
booksTable.PrimaryKey = new DataColumn[] { booksTable.Columns["BookID"] };

// Add tables to the DataSet
bookstoreDataSet.Tables.Add(authorsTable);
bookstoreDataSet.Tables.Add(booksTable);

// Add sample data
authorsTable.Rows.Add(1, "Jane Austen", "England");
authorsTable.Rows.Add(2, "Ernest Hemingway", "USA");

booksTable.Rows.Add(101, "Pride and Prejudice", 1, 12.99m);
booksTable.Rows.Add(102, "Emma", 1, 14.99m);
booksTable.Rows.Add(103, "The Old Man and the Sea", 2, 11.99m);

Creating Relationships Between DataTables

One of the powerful features of DataSets is the ability to define relationships between tables, similar to foreign key relationships in a database:

csharp

// Continuing from the previous example
// Create a relationship between Authors and Books
DataRelation relation = new DataRelation(
    "AuthorBooks",
    authorsTable.Columns["AuthorID"],
    booksTable.Columns["AuthorID"]
);

// Add the relationship to the DataSet
bookstoreDataSet.Relations.Add(relation);

// Now we can navigate from an author to their books
DataRow authorRow = authorsTable.Rows.Find(1); // Find Jane Austen
if (authorRow != null)
{
    Console.WriteLine($"Author: {authorRow["Name"]}");
    Console.WriteLine("Books:");
    
    // Get all books by this author using the relationship
    foreach(DataRow bookRow in authorRow.GetChildRows("AuthorBooks"))
    {
        Console.WriteLine($"  - {bookRow["Title"]} (${bookRow["Price"]})");
    }
}

Output:

Author: Jane Austen
Books:
  - Pride and Prejudice ($12.99)
  - Emma ($14.99)

Filtering and Searching Data in DataSets

You can perform various operations to search and filter data in a DataSet:

Using DataView

csharp

// Create a DataView for the Books table
DataView booksView = new DataView(booksTable);

// Filter books by price less than $13
booksView.RowFilter = "Price < 13";

Console.WriteLine("Books under $13:");
foreach (DataRowView rowView in booksView)
{
    Console.WriteLine($"{rowView["Title"]} - ${rowView["Price"]}");
}

// Sort books by title
booksView.Sort = "Title ASC";
booksView.RowFilter = ""; // Clear previous filter

Console.WriteLine("\nBooks sorted by title:");
foreach (DataRowView rowView in booksView)
{
    Console.WriteLine(rowView["Title"].ToString());
}

Output:

Books under $13:
Pride and Prejudice - $12.99
The Old Man and the Sea - $11.99

Books sorted by title:
Emma
Pride and Prejudice
The Old Man and the Sea

Using Select Method

csharp

// Find all books by Jane Austen (AuthorID = 1)
DataRow[] janeAustenBooks = booksTable.Select("AuthorID = 1");

Console.WriteLine("Jane Austen's books:");
foreach (DataRow row in janeAustenBooks)
{
    Console.WriteLine(row["Title"].ToString());
}

Output:

Jane Austen's books:
Pride and Prejudice
Emma

Modifying DataSets and Persisting Changes

DataSets can be modified in memory, and those changes can be sent back to the database:

csharp

using System;
using System.Data;
using System.Data.SqlClient;

public class DataSetUpdateExample
{
    public static void Main()
    {
        string connectionString = "Data Source=(local);Initial Catalog=Northwind;Integrated Security=True";
        
        // Create a connection and DataAdapter
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Define the SELECT command for the adapter
            SqlDataAdapter adapter = new SqlDataAdapter(
                "SELECT ProductID, ProductName, UnitPrice, UnitsInStock FROM Products", 
                connection);
            
            // Define the UPDATE command for the adapter
            SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
            
            // Create and fill the DataSet
            DataSet productsDataSet = new DataSet();
            adapter.Fill(productsDataSet, "Products");
            
            // Get the Products table from the DataSet
            DataTable productsTable = productsDataSet.Tables["Products"];
            
            // Make changes to the data
            DataRow row = productsTable.Rows[0]; // Get the first product
            decimal originalPrice = (decimal)row["UnitPrice"];
            Console.WriteLine($"Original price for {row["ProductName"]}: ${originalPrice}");
            
            // Increase the price by 10%
            row["UnitPrice"] = Math.Round(originalPrice * 1.1m, 2);
            Console.WriteLine($"New price: ${row["UnitPrice"]}");
            
            // Update the database with the changes
            adapter.Update(productsDataSet, "Products");
            Console.WriteLine("Database updated successfully");
        }
    }
}

DataSet Schema and XML Integration

DataSets have excellent XML support, allowing you to export and import data in XML format:

csharp

// Export DataSet to XML
bookstoreDataSet.WriteXml("bookstore.xml");
bookstoreDataSet.WriteXmlSchema("bookstore.xsd");

Console.WriteLine("DataSet exported to XML");

// Create a new empty DataSet
DataSet newDataSet = new DataSet();

// Import from XML
newDataSet.ReadXml("bookstore.xml");

Console.WriteLine($"Imported DataSet has {newDataSet.Tables.Count} tables and {newDataSet.Relations.Count} relations");

Real-World Example: Customer Order Management

Here's a practical example of using a DataSet to manage customer orders in a small business application:

csharp

using System;
using System.Data;
using System.Data.SqlClient;

public class OrderManagementSystem
{
    private DataSet orderDataSet;
    private SqlDataAdapter customersAdapter;
    private SqlDataAdapter ordersAdapter;
    private SqlDataAdapter orderDetailsAdapter;
    private string connectionString;

    public OrderManagementSystem(string connString)
    {
        connectionString = connString;
        orderDataSet = new DataSet("OrderManagement");
        
        // Initialize the DataSet with tables and relationships
        InitializeDataSet();
    }

    private void InitializeDataSet()
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Create adapters
            customersAdapter = new SqlDataAdapter(
                "SELECT CustomerID, CompanyName, ContactName, Phone FROM Customers", 
                connection);
                
            ordersAdapter = new SqlDataAdapter(
                "SELECT OrderID, CustomerID, OrderDate, RequiredDate, ShippedDate FROM Orders", 
                connection);
                
            orderDetailsAdapter = new SqlDataAdapter(
                "SELECT OrderID, ProductID, UnitPrice, Quantity, Discount FROM [Order Details]", 
                connection);
            
            // Fill the DataSet
            customersAdapter.Fill(orderDataSet, "Customers");
            ordersAdapter.Fill(orderDataSet, "Orders");
            orderDetailsAdapter.Fill(orderDataSet, "OrderDetails");
            
            // Create relationships between tables
            DataRelation customerOrders = new DataRelation(
                "CustomerOrders",
                orderDataSet.Tables["Customers"].Columns["CustomerID"],
                orderDataSet.Tables["Orders"].Columns["CustomerID"]
            );
            
            DataRelation orderItems = new DataRelation(
                "OrderItems",
                orderDataSet.Tables["Orders"].Columns["OrderID"],
                orderDataSet.Tables["OrderDetails"].Columns["OrderID"]
            );
            
            orderDataSet.Relations.Add(customerOrders);
            orderDataSet.Relations.Add(orderItems);
        }
    }

    public void DisplayCustomerOrders(string customerId)
    {
        DataRow customerRow = orderDataSet.Tables["Customers"].Rows.Find(customerId);
        if (customerRow == null)
        {
            Console.WriteLine("Customer not found");
            return;
        }
        
        Console.WriteLine($"Customer: {customerRow["CompanyName"]}");
        Console.WriteLine($"Contact: {customerRow["ContactName"]}");
        Console.WriteLine($"Phone: {customerRow["Phone"]}");
        Console.WriteLine("\nOrders:");
        
        foreach (DataRow orderRow in customerRow.GetChildRows("CustomerOrders"))
        {
            DateTime orderDate = (DateTime)orderRow["OrderDate"];
            DateTime? shippedDate = orderRow["ShippedDate"] as DateTime?;
            
            Console.WriteLine($"Order #{orderRow["OrderID"]} - Ordered: {orderDate.ToShortDateString()}, " +
                $"Shipped: {(shippedDate.HasValue ? shippedDate.Value.ToShortDateString() : "Not shipped")}");
            
            decimal orderTotal = 0;
            foreach (DataRow detailRow in orderRow.GetChildRows("OrderItems"))
            {
                decimal lineTotal = (decimal)detailRow["UnitPrice"] * (int)detailRow["Quantity"] * 
                    (1 - Convert.ToDecimal(detailRow["Discount"]));
                orderTotal += lineTotal;
            }
            
            Console.WriteLine($"Total Items: {orderRow.GetChildRows("OrderItems").Length}, Order Value: ${orderTotal:F2}");
            Console.WriteLine();
        }
    }
    
    public void SaveChanges()
    {
        SqlCommandBuilder customersBuilder = new SqlCommandBuilder(customersAdapter);
        SqlCommandBuilder ordersBuilder = new SqlCommandBuilder(ordersAdapter);
        SqlCommandBuilder detailsBuilder = new SqlCommandBuilder(orderDetailsAdapter);
        
        // Update all tables
        customersAdapter.Update(orderDataSet, "Customers");
        ordersAdapter.Update(orderDataSet, "Orders");
        orderDetailsAdapter.Update(orderDataSet, "OrderDetails");
        
        Console.WriteLine("All changes saved to the database");
    }
}

// Usage example:
// OrderManagementSystem system = new OrderManagementSystem("connection-string-here");
// system.DisplayCustomerOrders("ALFKI");
// system.SaveChanges();

Performance Considerations

When working with DataSets, keep in mind:

1. Memory Usage: DataSets hold all data in memory, which can be resource-intensive for large datasets
2. Network Traffic: When using DataSets in client-server applications, larger DataSets mean more network traffic
3. Consider Using DataReader for Forward-Only Access: If you only need to read data once sequentially, a DataReader might be more efficient

Strong Typing with Typed DataSets

The examples we've seen so far use standard DataSets where columns are accessed by string names. .NET also supports Typed DataSets which provide a more strongly-typed approach:

csharp

// With a regular DataSet:
DataSet ds = new DataSet();
// ...fill dataset...
string name = (string)ds.Tables["Customers"].Rows[0]["CustomerName"];

// With a typed DataSet (after generating it):
// Note: Typed DataSets are typically generated using tools like Visual Studio
NorthwindDataSet typedDS = new NorthwindDataSet();
// ...fill dataset...
string name = typedDS.Customers[0].CustomerName;

Typed DataSets provide:

• Better IntelliSense support
• Compile-time type checking
• More readable code

Summary

In this tutorial, you've learned:

• What DataSets are and their basic structure (tables, relations, constraints)
• How to create and fill DataSets from databases or manually
• Working with multiple tables and creating relationships between them
• Filtering and searching data within a DataSet
• Making changes to DataSets and persisting those changes back to the database
• XML integration capabilities
• A practical real-world example of DataSets in action

DataSets are particularly useful when:

• You need to work with disconnected data
• You're working with multiple related tables
• You need to cache database results
• You want to transfer complex data structures between application tiers

Additional Resources

1. Microsoft Documentation on DataSets
2. ADO.NET Architecture
3. DataSet vs DataReader Performance Comparison

Exercises

1. Create a DataSet with two tables: Employees and Departments. Add appropriate columns to each table and establish a relationship between them.

2. Modify the Real-World Example above to include a method that can add a new order for an existing customer.

3. Create a simple Windows Forms or Console application that uses a DataSet to load data from a database, allows the user to modify it, and then saves the changes back.

4. Extend the bookstore DataSet example to include a "Reviews" table that has a foreign key relationship with the Books table. Add methods to display the average review score for each book.