C# DataSet and DataTable
When working with databases in C#, ADO.NET provides powerful components to manage data efficiently. Among these components, DataSet and DataTable stand out as essential tools for in-memory data manipulation.
Introduction to DataSet and DataTable
A DataSet is an in-memory representation of relational data that consists of a collection of DataTable objects and the relationships between them. Think of it as an in-memory database that allows you to work with data disconnected from its source.
A DataTable, on the other hand, represents a single table of in-memory data. It contains collections of DataRow and DataColumn objects that store and define the data and its structure.
Creating and Working with DataTable
Creating a Simple DataTable
Let's start by creating a basic DataTable:
using System;
using System.Data;
class Program
{
static void Main()
{
// Create a new DataTable
DataTable customers = new DataTable("Customers");
// Add columns
customers.Columns.Add("ID", typeof(int));
customers.Columns.Add("Name", typeof(string));
customers.Columns.Add("Email", typeof(string));
customers.Columns.Add("JoinDate", typeof(DateTime));
// Display column information
Console.WriteLine("Columns in the Customers table:");
foreach (DataColumn column in customers.Columns)
{
Console.WriteLine($"- {column.ColumnName} ({column.DataType})");
}
}
}
Output:
Columns in the Customers table:
- ID (System.Int32)
- Name (System.String)
- Email (System.String)
- JoinDate (System.DateTime)
Adding Data to a DataTable
Now, let's add some rows to our DataTable:
// Add rows
customers.Rows.Add(1, "John Doe", "[email protected]", DateTime.Parse("2022-01-15"));
customers.Rows.Add(2, "Jane Smith", "[email protected]", DateTime.Parse("2022-02-20"));
customers.Rows.Add(3, "Bob Johnson", "[email protected]", DateTime.Parse("2022-03-10"));
// Display the data
Console.WriteLine("\nCustomer Data:");
foreach (DataRow row in customers.Rows)
{
Console.WriteLine($"ID: {row["ID"]}, Name: {row["Name"]}, " +
$"Email: {row["Email"]}, Joined: {((DateTime)row["JoinDate"]).ToShortDateString()}");
}
Output:
Customer Data:
ID: 1, Name: John Doe, Email: [email protected], Joined: 1/15/2022
ID: 2, Name: Jane Smith, Email: [email protected], Joined: 2/20/2022
ID: 3, Name: Bob Johnson, Email: [email protected], Joined: 3/10/2022
Setting Constraints on DataTable
DataTable objects can include constraints to maintain data integrity:
// Create a DataTable with constraints
DataTable products = new DataTable("Products");
// Add columns
products.Columns.Add("ProductID", typeof(int));
products.Columns.Add("ProductName", typeof(string));
products.Columns.Add("Price", typeof(decimal));
products.Columns.Add("Category", typeof(string));
// Set primary key
products.PrimaryKey = new DataColumn[] { products.Columns["ProductID"] };
// Add a unique constraint
UniqueConstraint uniqueNameConstraint = new UniqueConstraint(
"UniqueProductName", products.Columns["ProductName"]);
products.Constraints.Add(uniqueNameConstraint);
// Add a check constraint for positive prices
products.Columns["Price"].Expression = "Price >= 0";
Console.WriteLine("Products table created with constraints");
Creating and Working with DataSet
Creating a Simple DataSet
A DataSet can contain multiple DataTable objects:
using System;
using System.Data;
// Create a new DataSet
DataSet companyData = new DataSet("CompanyDatabase");
// Add the existing tables
companyData.Tables.Add(customers);
companyData.Tables.Add(products);
Console.WriteLine($"\nDataSet '{companyData.DataSetName}' contains {companyData.Tables.Count} tables:");
foreach (DataTable table in companyData.Tables)
{
Console.WriteLine($"- {table.TableName}");
}
Output:
DataSet 'CompanyDatabase' contains 2 tables:
- Customers
- Products
Creating Relationships Between Tables
One of the main advantages of a DataSet is the ability to define relationships between tables:
// Create a new table for orders
DataTable orders = new DataTable("Orders");
orders.Columns.Add("OrderID", typeof(int));
orders.Columns.Add("CustomerID", typeof(int));
orders.Columns.Add("OrderDate", typeof(DateTime));
orders.Columns.Add("TotalAmount", typeof(decimal));
// Set primary key
orders.PrimaryKey = new DataColumn[] { orders.Columns["OrderID"] };
// Add to dataset
companyData.Tables.Add(orders);
// Create a relationship between Customers and Orders
DataRelation customerOrders = new DataRelation(
"CustomerOrders",
customers.Columns["ID"],
orders.Columns["CustomerID"]
);
companyData.Relations.Add(customerOrders);
Console.WriteLine("\nAdded relationship between Customers and Orders tables");
Navigating Related Data
With relationships defined, we can navigate between related records:
// Add some order data
orders.Rows.Add(101, 1, DateTime.Now.AddDays(-5), 150.75m);
orders.Rows.Add(102, 1, DateTime.Now.AddDays(-2), 89.50m);
orders.Rows.Add(103, 2, DateTime.Now.AddDays(-7), 245.33m);
// Access related data
Console.WriteLine("\nCustomers and their orders:");
foreach (DataRow customerRow in customers.Rows)
{
Console.WriteLine($"\nCustomer: {customerRow["Name"]}");
Console.WriteLine("Orders:");
DataRow[] customerOrders = customerRow.GetChildRows("CustomerOrders");
if (customerOrders.Length > 0)
{
foreach (DataRow orderRow in customerOrders)
{
Console.WriteLine($" Order #{orderRow["OrderID"]} - " +
$"Date: {((DateTime)orderRow["OrderDate"]).ToShortDateString()}, " +
$"Amount: ${orderRow["TotalAmount"]}");
}
}
else
{
Console.WriteLine(" No orders");
}
}
Output:
Customers and their orders:
Customer: John Doe
Orders:
Order #101 - Date: [5 days ago], Amount: $150.75
Order #102 - Date: [2 days ago], Amount: $89.50
Customer: Jane Smith
Orders:
Order #103 - Date: [7 days ago], Amount: $245.33
Customer: Bob Johnson
Orders:
No orders
Filtering and Querying DataTables
Using Select Method
DataTable provides methods for querying data:
// Filter customers who joined in 2022
DataRow[] results = customers.Select("JoinDate >= #1/1/2022# AND JoinDate <= #12/31/2022#");
Console.WriteLine("\nCustomers who joined in 2022:");
foreach (DataRow row in results)
{
Console.WriteLine($"- {row["Name"]} ({((DateTime)row["JoinDate"]).ToShortDateString()})");
}
// Filter orders with amount greater than $100
DataRow[] largeOrders = orders.Select("TotalAmount > 100");
Console.WriteLine("\nOrders with amount greater than $100:");
foreach (DataRow row in largeOrders)
{
Console.WriteLine($"- Order #{row["OrderID"]} - ${row["TotalAmount"]}");
}
Computed Columns
You can add computed columns to a DataTable:
// Add a computed column to the orders table
orders.Columns.Add("Tax", typeof(decimal), "TotalAmount * 0.08");
orders.Columns.Add("GrandTotal", typeof(decimal), "TotalAmount + Tax");
Console.WriteLine("\nOrders with tax calculations:");
foreach (DataRow row in orders.Rows)
{
Console.WriteLine($"Order #{row["OrderID"]} - Subtotal: ${row["TotalAmount"]}, " +
$"Tax: ${row["Tax"]}, Total: ${row["GrandTotal"]}");
}
Output:
Orders with tax calculations:
Order #101 - Subtotal: $150.75, Tax: $12.06, Total: $162.81
Order #102 - Subtotal: $89.50, Tax: $7.16, Total: $96.66
Order #103 - Subtotal: $245.33, Tax: $19.63, Total: $264.96
Practical Example: Loading and Saving Data
Working with a Database
Let's see a practical example of loading data from a database into a DataSet and making changes:
using System;
using System.Data;
using System.Data.SqlClient;
public void LoadAndModifyData()
{
// Connection string
string connectionString = "Data Source=(local);Initial Catalog=SampleDB;Integrated Security=True";
// Create the dataset and adapter
DataSet productData = new DataSet();
// Create and configure the adapter
SqlDataAdapter adapter = new SqlDataAdapter(
"SELECT ProductID, ProductName, UnitPrice, CategoryID FROM Products",
connectionString);
// Configure commands for the adapter
SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
try
{
// Fill the dataset
adapter.Fill(productData, "Products");
Console.WriteLine($"Loaded {productData.Tables["Products"].Rows.Count} products");
// Make some changes
DataTable productsTable = productData.Tables["Products"];
// Update a product price
DataRow[] productToUpdate = productsTable.Select("ProductName = 'Chai'");
if (productToUpdate.Length > 0)
{
decimal oldPrice = (decimal)productToUpdate[0]["UnitPrice"];
productToUpdate[0]["UnitPrice"] = oldPrice * 1.1m; // 10% increase
Console.WriteLine($"Updated Chai price from {oldPrice:C} to {(decimal)productToUpdate[0]["UnitPrice"]:C}");
}
// Add a new product
DataRow newProduct = productsTable.NewRow();
newProduct["ProductName"] = "New Organic Tea";
newProduct["UnitPrice"] = 25.99m;
newProduct["CategoryID"] = 1;
productsTable.Rows.Add(newProduct);
Console.WriteLine("Added new product: New Organic Tea");
// Save changes back to the database
adapter.Update(productData, "Products");
Console.WriteLine("Changes saved to database");
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
}
}
Saving DataSet/DataTable to XML
DataSet and DataTable can be serialized to XML, which is useful for persisting data:
// Save dataset to XML
companyData.WriteXml("CompanyData.xml");
companyData.WriteXmlSchema("CompanyData.xsd");
Console.WriteLine("\nDataSet saved to XML file");
// Load dataset from XML
DataSet loadedData = new DataSet();
loadedData.ReadXml("CompanyData.xml");
Console.WriteLine($"\nLoaded {loadedData.Tables.Count} tables from XML file");
Best Practices for Using DataSet and DataTable
- Use DataSets for multiple related tables, but prefer DataTable for single-table operations.
- Set primary keys to enable efficient lookups and relationships.
- Define proper column data types to prevent data conversion issues.
- Use constraints to enforce data integrity.
- Consider memory impact for large datasets - use filtering and pagination.
- Properly dispose of connections when working with databases.
- Use DataView for sorting, filtering, and searching operations.
Common Performance Tips
-
Set proper initial capacity if you know the approximate number of rows:
csharpDataTable table = new DataTable("LargeTable");
table.MinimumCapacity = 10000; -
Batch processing can improve performance when adding many rows:
csharp// Turn off notifications during bulk operations
table.BeginLoadData();
for (int i = 0; i < 10000; i++)
{
table.Rows.Add(i, $"Item {i}");
}
table.EndLoadData(); -
Use a DataView for frequent filtering and sorting operations:
csharpDataView view = new DataView(customers);
view.Sort = "Name ASC";
view.RowFilter = "JoinDate > #1/1/2022#";
Summary
DataSet and DataTable are powerful components in the ADO.NET framework that provide an in-memory representation of relational data. They allow you to:
- Create and manipulate data in a disconnected manner
- Define relationships between tables
- Enforce data integrity through constraints
- Query and filter data using expressions
- Serialize data to and from XML
- Make batch updates to a database
These components are especially useful when you need to work with multiple tables of data, perform complex data manipulation operations, or work in a disconnected environment.
Additional Resources and Exercises
Additional Resources
Exercises
-
Create a simple inventory system with two tables: Products and Categories. Establish a relationship between them and add methods to add, update, and delete products.
-
Build a DataTable-based contact manager that allows users to add contacts, search by name or email, and categorize contacts.
-
Create a data processing utility that loads data from a CSV file into a DataTable, performs some analysis (like grouping and aggregating), and outputs the results.
-
Implement a simple invoice system using DataSet with tables for Customers, Products, Orders, and OrderDetails. Include proper relationships and constraints.
-
Create a library management system using DataTables for Books, Members, and Loans, with methods to check books in and out.
By practicing with these exercises, you'll gain a solid understanding of how to use DataSet and DataTable effectively in your C# database applications.
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)