DataTable Class

Represents an in-memory collection of data that can be accessed and manipulated. It is a fundamental component of ADO.NET for working with disconnected data.

Overview

The DataTable class is used to represent a single table of data in memory. It contains a collection of DataColumn objects, which define the schema of the table (the columns and their data types), and a collection of DataRow objects, which represent the actual data within the table. DataTable is part of the System.Data namespace.

Key features of DataTable include:

Schema Definition: Defines columns with specific data types, constraints, and default values.
Data Manipulation: Allows for adding, deleting, and modifying rows.
In-Memory Operations: Supports sorting, filtering, searching, and grouping of data without needing to access the database.
Relationships: Can define relationships between multiple DataTable objects within a DataSet.
Events: Provides events for row and column changes, enabling dynamic responses to data modifications.

Basic Usage Example

The following example demonstrates how to create a simple DataTable, define its schema, and populate it with data:


using System;
using System.Data;

public class DataTableExample
{
    public static void Main(string[] args)
    {
        // Create a new DataTable
        DataTable customerTable = new DataTable("Customers");

        // Define columns
        DataColumn idColumn = new DataColumn("CustomerID", typeof(int));
        idColumn.AutoIncrement = true; // Enable auto-increment for primary key
        idColumn.AutoIncrementSeed = 1;
        idColumn.AutoIncrementStep = 1;
        idColumn.ReadOnly = true; // Ensure ID cannot be changed manually

        DataColumn nameColumn = new DataColumn("Name", typeof(string));
        nameColumn.AllowDBNull = false; // Name is required

        DataColumn cityColumn = new DataColumn("City", typeof(string));

        // Add columns to the DataTable
        customerTable.Columns.Add(idColumn);
        customerTable.Columns.Add(nameColumn);
        customerTable.Columns.Add(cityColumn);

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

        // Add rows to the DataTable
        DataRow row1 = customerTable.NewRow();
        row1["Name"] = "Alfreds Futterkiste";
        row1["City"] = "Berlin";
        customerTable.Rows.Add(row1);

        DataRow row2 = customerTable.NewRow();
        row2["Name"] = "Ana Trujillo Emparedados y helados";
        row2["City"] = "México D.F.";
        customerTable.Rows.Add(row2);

        DataRow row3 = customerTable.NewRow();
        row3["Name"] = "Around the Horn";
        row3["City"] = "London";
        customerTable.Rows.Add(row3);

        // Display the data
        Console.WriteLine("Customer Data:");
        foreach (DataRow row in customerTable.Rows)
        {
            Console.WriteLine($"ID: {row["CustomerID"]}, Name: {row["Name"]}, City: {row["City"]}");
        }

        // Find a row
        Console.WriteLine("\nFinding row with CustomerID = 2:");
        DataRow foundRow = customerTable.Rows.Find(2);
        if (foundRow != null)
        {
            Console.WriteLine($"Found: Name = {foundRow["Name"]}, City = {foundRow["City"]}");
        }
        else
        {
            Console.WriteLine("Row not found.");
        }
    }
}

Key Properties and Methods

Properties

Columns: Gets a DataColumnCollection that contains all the columns for this table.
Rows: Gets a DataRowCollection that contains all the rows for this table.
TableName: Gets or sets the name of the table.
PrimaryKey: Gets or sets an array of DataColumn objects that represent the primary key of the table.
DataSet: Gets the DataSet that contains this table.

Methods

NewRow(): Creates a new DataRow with the same schema as the table.
Rows.Add(DataRow row): Adds a specified DataRow to the table.
Rows.Find(object primaryKey): Finds a row using its primary key value.
Select(string filterExpression): Returns an array of rows that match the specified filter expression.
AcceptChanges(): Commits all changes made to the table since it was last loaded or since AcceptChanges() was last called.
RejectChanges(): Rejects all changes made to the table since it was last loaded or since AcceptChanges() was last called.
Clone(): Creates and returns a shallow copy of the DataTable.
Copy(): Creates and returns a deep copy of the DataTable.

Common Scenarios

Loading Data with DataAdapter

Typically, a DataTable is populated using a DataAdapter:


using System.Data;
using System.Data.SqlClient; // Example for SQL Server

// Assume conn is an established SqlConnection
// Assume sqlSelectCommand is an SqlCommand with a valid SELECT statement

DataTable dataFromDB = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter(sqlSelectCommand, conn))
{
    adapter.Fill(dataFromDB); // Fills the DataTable with data from the database
}

Filtering Data

Use the Select method with a filter expression:


DataRow[] results = customerTable.Select("City = 'London'");
foreach (DataRow row in results)
{
    Console.WriteLine($"Found in London: {row["Name"]}");
}

// Filtering with multiple conditions and sorting
DataRow[] filteredAndSorted = customerTable.Select("Name LIKE 'A%'", "City DESC");

Updating Data

Modify existing rows or add new ones. Changes are marked as modified or added until AcceptChanges() is called.


// Find a row and update it
DataRow rowToUpdate = customerTable.Rows.Find(1);
if (rowToUpdate != null)
{
    rowToUpdate["City"] = "New Berlin";
    // The row is now in a 'Modified' state
}

// Add a new row (as shown in the basic example)

Deleting Rows

Rows can be marked for deletion. The actual removal happens when AcceptChanges() is called in the context of cascading deletes or when Clear() is used.


DataRow rowToDelete = customerTable.Rows.Find(3);
if (rowToDelete != null)
{
    rowToDelete.Delete(); // Marks the row for deletion
    // The row is now in a 'Deleted' state
}

// To remove rows marked for deletion and accept other changes:
// customerTable.AcceptChanges();
// To remove all rows:
// customerTable.Clear();

Note: When working with DataRowState (e.g., Added, Modified, Deleted, Unchanged), you can conditionally process rows based on their current state.