Microsoft Learn

DataTable and DataRow in ADO.NET

The DataTable and DataRow objects are fundamental building blocks in ADO.NET for representing tabular data in memory. They provide a rich set of functionalities for manipulating, querying, and managing datasets locally, independent of their original data source.

Understanding DataTable

A DataTable object represents a single table of data in a DataSet. It can be used independently or as part of a larger DataSet. Each DataTable contains:

• Columns: Define the structure of the table, including column names, data types, and constraints.
• Rows: Contain the actual data, with each row being an instance of a DataRow.
• Constraints: Rules like unique constraints and foreign key constraints to maintain data integrity.
• Relations: Define relationships between this table and other DataTable objects within the same DataSet.

You can create a DataTable in several ways:

• Populating it from a data source (e.g., SQL Server, XML).
• Creating it programmatically in your code.

Creating a DataTable Programmatically

Here's an example of how to create a simple DataTable with a few columns:

public DataTable CreateSampleDataTable()
{
    DataTable dataTable = new DataTable("Products");

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

    dataTable.Columns.Add("ProductName", typeof(string));
    dataTable.Columns.Add("Price", typeof(decimal));
    dataTable.Columns.Add("StockQuantity", typeof(int));

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

    return dataTable;
}

Understanding DataRow

A DataRow object represents a single record or row of data within a DataTable. Each DataRow contains values for each column defined in its parent DataTable.

Accessing and Modifying DataRow Values

You can access individual cell values using either the column name or the column ordinal index. You can also modify these values directly.

DataTable productsTable = CreateSampleDataTable();

    // Add a new row
    DataRow newRow = productsTable.NewRow();
    newRow["ProductName"] = "Laptop";
    newRow["Price"] = 1200.50m;
    newRow["StockQuantity"] = 50;
    productsTable.Rows.Add(newRow);

    // Accessing a value
    string productName = (string)productsTable.Rows[0]["ProductName"];
    decimal price = (decimal)productsTable.Rows[0]["Price"];

    // Modifying a value
    if (productsTable.Rows.Count > 0)
    {
        productsTable.Rows[0]["Price"] = 1150.00m;
    }

    // Iterating through rows
    foreach (DataRow row in productsTable.Rows)
    {
        Console.WriteLine($"Product: {row["ProductName"]}, Price: {row["Price"]}");
    }

Common DataTable Operations

• Adding Rows: Use DataTable.NewRow() to create a new row object, populate its values, and then add it to the Rows collection.
• Deleting Rows: Call the Delete() method on a DataRow. The row is marked for deletion and removed when AcceptChanges() is called on the DataTable.
• Finding Rows: Use the Select() method with a filter expression or the Find() method with primary key values.
• Updating Rows: Modify the values of a DataRow directly.
• Handling Changes: The DataTable tracks changes made to its rows. You can access changed rows using DataTable.GetChanges().

DataTable and DataAdapter

While DataTable and DataRow are excellent for in-memory data manipulation, they are typically populated and updated using a DataAdapter (like SqlDataAdapter or OleDbDataAdapter). The DataAdapter bridges the gap between your DataTable and a data source, handling the retrieval and saving of data.

Summary

DataTable and DataRow are powerful components of ADO.NET that enable you to work with data in a structured, in-memory format. Understanding their properties and methods is crucial for building robust data-driven applications.