DataTable Class

The System.Data.DataTable class represents an in-memory table of data. It is one of the core objects in ADO.NET for working with data. A DataTable can be used independently or as part of a DataSet.

Purpose and Functionality

A DataTable allows you to:

• Store data in rows and columns, similar to a database table.
• Define a schema including columns (DataColumn) and constraints.
• Add, remove, and modify rows (DataRow).
• Filter and sort data.
• Handle relationships between tables.
• Persist and load data in various formats (e.g., XML).

Key Components

The DataTable class has several important associated objects and properties:

• Columns: A collection of DataColumn objects that define the structure of the table (data types, names, constraints).
• Rows: A collection of DataRow objects, where each row holds the actual data.
• Constraints: Rules applied to the data, such as primary keys and unique constraints, enforced by ConstraintCollection.
• PrimaryKey: A set of one or more columns that uniquely identify each row.
• DefaultView: A DataView object that allows for dynamic sorting and filtering of the rows in the DataTable.

Creating and Populating a DataTable

You can create a DataTable programmatically or by using a DataAdapter to fill it from a data source.

Programmatic Creation

Here's an example of creating a DataTable with columns:

using System;
using System.Data;

public class Example
{
    public static void Main(string[] args)
    {
        DataTable productsTable = new DataTable("Products");

        // Define columns
        DataColumn idColumn = new DataColumn("ProductID", typeof(int));
        idColumn.AutoIncrement = true; // Auto-incrementing ID
        idColumn.AutoIncrementSeed = 1;
        idColumn.AutoIncrementStep = 1;
        idColumn.AllowDBNull = false; // Cannot be null
        productsTable.Columns.Add(idColumn);

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

        DataColumn priceColumn = new DataColumn("Price", typeof(decimal));
        priceColumn.DefaultValue = 0.00; // Default price
        productsTable.Columns.Add(priceColumn);

        // Set Primary Key
        productsTable.PrimaryKey = new DataColumn[] { idColumn };

        // Add rows
        DataRow row1 = productsTable.NewRow();
        row1["ProductName"] = "Laptop";
        row1["Price"] = 1200.50;
        productsTable.Rows.Add(row1);

        DataRow row2 = productsTable.NewRow();
        row2["ProductName"] = "Keyboard";
        row2["Price"] = 75.99;
        productsTable.Rows.Add(row2);

        // Display data
        Console.WriteLine("--- Products Table ---");
        foreach (DataRow row in productsTable.Rows)
        {
            Console.WriteLine($"ID: {row["ProductID"]}, Name: {row["ProductName"]}, Price: {row["Price"]}");
        }
    }
}
            

Using DataAdapter

When working with a DataSet, you typically use a DataAdapter (like SqlDataAdapter or OleDbDataAdapter) to automatically create and populate a DataTable from a query:

// Assuming connection and command objects are already set up
// SqlDataAdapter adapter = new SqlDataAdapter(command);
// DataSet dataSet = new DataSet();
// adapter.Fill(dataSet, "Products");
// DataTable productsTable = dataSet.Tables["Products"];
            

Working with Data Rows

DataRow objects are used to represent individual records within a DataTable. You can create new rows using DataTable.NewRow(), populate them, and add them to the table's Rows collection.

Filtering and Sorting

The DataView property of a DataTable provides powerful filtering and sorting capabilities without modifying the underlying table:

// Assuming 'productsTable' is populated
DataView view = productsTable.DefaultView;

// Filter rows where Price is greater than 100
view.RowFilter = "Price > 100";

// Sort by ProductName
view.Sort = "ProductName ASC";

Console.WriteLine("\n--- Filtered and Sorted Products ---");
foreach (DataRowView rowView in view)
{
    Console.WriteLine($"Name: {rowView["ProductName"]}, Price: {rowView["Price"]}");
}
            

Modifying Data

You can update, delete, or add new rows using the DataTable.Rows collection. Each row can be accessed by its index or primary key.

// Example: Update a row
DataRow[] foundRows = productsTable.Select("ProductName = 'Keyboard'");
if (foundRows.Length > 0)
{
    foundRows[0]["Price"] = 80.00;
    Console.WriteLine("\nUpdated Keyboard price.");
}

// Example: Delete a row
DataRow rowToDelete = productsTable.Rows.Find(1); // Find by Primary Key
if (rowToDelete != null)
{
    rowToDelete.Delete();
    Console.WriteLine("\nDeleted ProductID 1.");
}
            

Conclusion

The DataTable class is a fundamental building block in ADO.NET for managing disconnected data. Its flexibility in defining structure, storing data, and providing querying capabilities makes it invaluable for data-driven applications.