ADO.NET

DataTable Overview

The DataTable object is a core component of ADO.NET. It represents an in-memory table of data. It can be used to store data retrieved from a data source, to hold data that has been manipulated, or to provide data for user interfaces. A DataTable consists of a collection of columns (DataColumn objects) and a collection of rows (DataRow objects).

Key Concepts

• DataTable: Represents a single table of data in memory.
• DataColumn: Defines the schema of a column, including its name, data type, and constraints.
• DataRow: Represents a single record or row of data within a DataTable.
• DataRelation: Defines a relationship between two DataTable objects, similar to foreign key relationships in a database.
• Constraint: Enforces data integrity rules, such as unique keys and foreign keys.

Creating and Populating a DataTable

You can create a DataTable programmatically or by using the XSD designer in Visual Studio. Here's a basic example of creating a DataTable in C#:

using System;
using System.Data;

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

        // Define columns
        DataColumn idColumn = new DataColumn("CustomerID", typeof(int));
        idColumn.AutoIncrement = true;
        idColumn.AutoIncrementSeed = 1;
        idColumn.AutoIncrementStep = 1;
        idColumn.AllowDBNull = false;

        DataColumn nameColumn = new DataColumn("CustomerName", typeof(string));
        nameColumn.AllowDBNull = false;

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

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

        // Add primary key constraint
        dataTable.Constraints.Add(new UniqueConstraint(idColumn));

        // Add rows
        DataRow row1 = dataTable.NewRow();
        row1["CustomerName"] = "Alfreds Futterkiste";
        row1["City"] = "Berlin";
        dataTable.Rows.Add(row1);

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

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

Working with DataRows

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

Accessing Data:

• By column name: row["ColumnName"]
• By column index: row[columnIndex]

Modifying Data:

You can directly assign new values to the columns of a DataRow.

// Assuming 'row' is a DataRow
row["City"] = "New York";
dataTable.AcceptChanges(); // Saves the changes
                    

Deleting Rows:

Use the Delete() method of a DataRow. The row is marked for deletion and removed when AcceptChanges() is called.

DataRow rowToDelete = dataTable.Rows[0]; // Example: get the first row
rowToDelete.Delete();
dataTable.AcceptChanges(); // Permanently removes the row
                    

DataTable and DataAdapter

The DataAdapter class (e.g., SqlDataAdapter, OleDbDataAdapter) is crucial for bridging DataTables with data sources. It handles retrieving data into a DataTable (using Fill()) and persisting changes from a DataTable back to the data source (using Update()).

Key Properties and Methods

Further Reading

• DataColumn Class
• DataRow Class
• DataSet Overview