DataTable Class

The DataTable class represents an in-memory collection of data that is organized into tables. It is part of the ADO.NET set of classes used for data access in .NET applications.

Key Features:

  • Represents a relational table of data.
  • Can be strongly typed or loosely typed.
  • Supports querying and manipulation of data.
  • Can be used independently or as part of a DataSet.

Introduction

The DataTable class is fundamental to ADO.NET. It provides a rich set of functionalities for managing data in memory. Each DataTable contains a collection of DataColumn objects (defining the schema of the table) and a collection of DataRow objects (representing the data itself).

Creating a DataTable

You can create a DataTable in several ways:

  • Instantiating the class directly.
  • Using a DataSet to add a new table.
  • Populating it from a data source (e.g., a SQL query).

Programmatic Creation

Here's a C# example of creating a simple DataTable with columns:

C# Example 

using System.Data;

// Create a new DataTable
DataTable productsTable = new DataTable("Products");

// Define columns
DataColumn idColumn = new DataColumn("ProductID", typeof(int));
idColumn.AutoIncrement = true;
idColumn.AutoIncrementSeed = 1;
idColumn.AutoIncrementStep = 1;
productsTable.Columns.Add(idColumn);

DataColumn nameColumn = new DataColumn("ProductName", typeof(string));
productsTable.Columns.Add(nameColumn);

DataColumn priceColumn = new DataColumn("Price", typeof(decimal));
productsTable.Columns.Add(priceColumn);

// Set primary key
productsTable.PrimaryKey = new DataColumn[] { idColumn };

// Add some rows
productsTable.Rows.Add(null, "Laptop", 1200.50m);
productsTable.Rows.Add(null, "Keyboard", 75.00m);
productsTable.Rows.Add(null, "Mouse", 25.99m);

// You can now access data
foreach (DataRow row in productsTable.Rows)
{
    Console.WriteLine($"ID: {row["ProductID"]}, Name: {row["ProductName"]}, Price: {row["Price"]}");
}

DataTable Structure

A DataTable consists of the following key components:

  • Columns: Define the schema, data types, and constraints. Represented by DataColumn objects.
  • Rows: Contain the actual data for each record. Represented by DataRow objects.
  • Constraints: Rules enforced on the data, such as primary keys and foreign keys.
  • Relations: Define relationships between tables, typically when a DataTable is part of a DataSet.

Working with Data Rows

You can add, update, delete, and retrieve rows from a DataTable.

Adding Rows

Use the ImportRow method to add a copy of a row from another table, or use NewRow to create a new row and then populate it.

C# Example (Adding Rows) 

// Assuming 'productsTable' is already defined as above

// Method 1: Using NewRow
DataRow newRow = productsTable.NewRow();
newRow["ProductName"] = "Monitor";
newRow["Price"] = 350.75m;
productsTable.Rows.Add(newRow);

// Method 2: Directly creating and adding (less common for auto-increment keys)
// productsTable.Rows.Add(5, "Webcam", 50.00m); // If ProductID was manually managed

Updating Rows

You can directly access and modify values in a DataRow.

C# Example (Updating Rows) 

// Find a row (e.g., by primary key)
DataRow rowToUpdate = productsTable.Rows.Find(2); // Find row with ProductID = 2

if (rowToUpdate != null)
{
    rowToUpdate["Price"] = 80.00m; // Update the price
    rowToUpdate.AcceptChanges(); // Mark changes as accepted
}

Deleting Rows

Use the Delete method on a DataRow and then AcceptChanges.

C# Example (Deleting Rows) 

// Find a row to delete
DataRow rowToDelete = productsTable.Rows.Find(3); // Find row with ProductID = 3

if (rowToDelete != null)
{
    rowToDelete.Delete(); // Mark row for deletion
    // To actually remove deleted rows, you might need to iterate and call AcceptChanges carefully
    // or use DataView filtering. For immediate removal, consider recreating the table or filtering.
    productsTable.AcceptChanges(); // This commits the deletion
}

Querying a DataTable

The Select method allows you to filter rows using a simple query syntax.

C# Example (Querying) 

// Find all products with a price greater than $100
DataRow[] expensiveProducts = productsTable.Select("Price > 100");

foreach (DataRow row in expensiveProducts)
{
    Console.WriteLine($"Expensive Product: {row["ProductName"]}, Price: {row["Price"]}");
}

// Find a specific product by name
DataRow[] specificProduct = productsTable.Select("ProductName = 'Laptop'");
if (specificProduct.Length > 0)
{
    Console.WriteLine($"Found Laptop with ID: {specificProduct[0]["ProductID"]}");
}

The Select method uses a syntax similar to SQL's WHERE clause but is a subset of its capabilities.

DataTable in a DataSet

DataTable objects are commonly used within a DataSet to represent multiple related tables.

Conclusion

The DataTable class is a powerful tool for data manipulation within .NET applications. Its ability to store, query, and manage data in memory makes it a cornerstone of ADO.NET.