DataTable Objects in ADO.NET

The DataTable object is a fundamental component of ADO.NET, representing an in-memory table of data. It provides a rich set of features for data manipulation, querying, and management within your .NET applications.

Introduction

A DataTable is an essential part of the ADO.NET object model, allowing you to work with data in a disconnected or connected manner. It's similar to a database table but exists entirely in memory. This makes it ideal for scenarios where you need to process, filter, or aggregate data without constant database interaction.

Key characteristics of a DataTable include:

• In-Memory Representation: Holds data entirely in RAM.
• Schema Definition: Can define its structure through columns and constraints.
• Data Manipulation: Supports adding, updating, deleting, and finding rows.
• Relationships: Can define relationships with other DataTable objects within a DataSet.
• Sorting and Filtering: Allows for dynamic sorting and filtering of its contents.

Creating a DataTable

You can create a DataTable object using its constructor.

using System.Data;

DataTable myTable = new DataTable("Products");

Imports System.Data

Dim myTable As New DataTable("Products")

The string argument passed to the constructor is the name of the DataTable.

Adding Columns

Before you can add data, you need to define the structure of your DataTable by adding columns. You can use the Columns.Add() method.

Columns can be created dynamically, or by specifying a DataColumn object.

myTable.Columns.Add("ProductID", typeof(int));
myTable.Columns.Add("ProductName", typeof(string));
myTable.Columns.Add("UnitPrice", typeof(decimal));
myTable.Columns.Add("IsActive", typeof(bool));

myTable.Columns.Add("ProductID", GetType(Integer))
myTable.Columns.Add("ProductName", GetType(String))
myTable.Columns.Add("UnitPrice", GetType(Decimal))
myTable.Columns.Add("IsActive", GetType(Boolean))

You can also set properties for columns, such as making them a primary key.

DataColumn idColumn = new DataColumn("CategoryID", typeof(int));
idColumn.AutoIncrement = true;
idColumn.AutoIncrementSeed = 1;
idColumn.AutoIncrementStep = 1;
idColumn.AllowDBNull = false;
myTable.Columns.Add(idColumn);

myTable.PrimaryKey = new DataColumn[] { myTable.Columns["CategoryID"] };

Adding Rows

To add data to a DataTable, you create a new DataRow object and then add it to the Rows collection of the DataTable.

DataRow newRow = myTable.NewRow();
newRow["ProductID"] = 1;
newRow["ProductName"] = "Chai";
newRow["UnitPrice"] = 18.00;
newRow["IsActive"] = true;
myTable.Rows.Add(newRow);

newRow = myTable.NewRow();
newRow["ProductID"] = 2;
newRow["ProductName"] = "Chang";
newRow["UnitPrice"] = 19.00;
newRow["IsActive"] = true;
myTable.Rows.Add(newRow);

Dim newRow As DataRow = myTable.NewRow()
newRow("ProductID") = 1
newRow("ProductName") = "Chai"
newRow("UnitPrice") = 18.00D
newRow("IsActive") = True
myTable.Rows.Add(newRow)

newRow = myTable.NewRow()
newRow("ProductID") = 2
newRow("ProductName") = "Chang"
newRow("UnitPrice") = 19.00D
newRow("IsActive") = True
myTable.Rows.Add(newRow)

It's good practice to use myTable.NewRow() to create a row that conforms to the table's schema, ensuring proper data type handling.

DataTable Relationships

Within a DataSet, DataTable objects can be linked using DataRelation objects. This is crucial for representing master-detail relationships, similar to foreign key constraints in a database.

For example, if you have a "Customers" table and an "Orders" table, you can create a relationship where orders are linked to their respective customers.

DataSet myDataSet = new DataSet("CompanyData");

DataTable customersTable = new DataTable("Customers");
customersTable.Columns.Add("CustomerID", typeof(int));
customersTable.PrimaryKey = new DataColumn[] { customersTable.Columns["CustomerID"] };
customersTable.Rows.Add(1, "Alice");
customersTable.Rows.Add(2, "Bob");
myDataSet.Tables.Add(customersTable);

DataTable ordersTable = new DataTable("Orders");
ordersTable.Columns.Add("OrderID", typeof(int));
ordersTable.Columns.Add("CustomerID", typeof(int));
ordersTable.Columns.Add("OrderDate", typeof(DateTime));
myDataSet.Tables.Add(ordersTable);

// Create the relationship
DataRelation relation = new DataRelation("CustomerOrders",
    customersTable.Columns["CustomerID"],
    ordersTable.Columns["CustomerID"]);
myDataSet.Relations.Add(relation);

Finding Data

ADO.NET provides powerful ways to find specific rows within a DataTable.

• Select() method: Uses a filter expression to return an array of DataRow objects.
• Find() method: Used when a primary key is defined on the DataTable to efficiently locate a single row.

DataRow[] foundRows = myTable.Select("UnitPrice > 20");
foreach (DataRow row in foundRows)
{
    Console.WriteLine($"Product: {row["ProductName"]}, Price: {row["UnitPrice"]}");
}

// Assuming ProductID is the primary key
DataRow foundRow = myTable.Rows.Find(2);
if (foundRow != null)
{
    Console.WriteLine($"Found Product: {foundRow["ProductName"]}");
}

The Select() method supports DataView query syntax, which is similar to SQL WHERE clauses.

Updating and Deleting Data

Modifying and removing rows is straightforward.

• Updating: Access a DataRow (e.g., via Find() or Select()) and change its column values.
• Deleting: Call the Delete() method on a DataRow. The row is marked for deletion and removed when AcceptChanges() is called or when the table's contents are committed.

// Update a row
DataRow rowToUpdate = myTable.Rows.Find(1);
if (rowToUpdate != null)
{
    rowToUpdate["UnitPrice"] = 20.00;
    // To mark changes as pending
    // rowToUpdate.SetModified();
}

// Delete a row
DataRow rowToDelete = myTable.Rows.Find(2);
if (rowToDelete != null)
{
    rowToDelete.Delete();
}

// Accept changes to finalize updates and deletions
myTable.AcceptChanges();

When you call AcceptChanges(), any rows marked for deletion are removed, and any modified rows are updated to their new state. If you need to revert changes before accepting them, you can call RejectChanges().

DataView

A DataView object provides a customizable view of the data in a DataTable. It allows you to sort, filter, and search the data without modifying the underlying DataTable. You can create multiple DataView objects from a single DataTable.

DataView dataView = new DataView(myTable);

// Set sorting
dataView.Sort = "ProductName ASC";

// Set filtering
dataView.RowFilter = "UnitPrice > 15";

// Iterate through the filtered and sorted data
foreach (DataRowView rowView in dataView)
{
    Console.WriteLine($"Filtered Product: {rowView["ProductName"]}, Price: {rowView["UnitPrice"]}");
}

DataView is extremely useful for binding data to UI elements like grids (e.g., DataGridView in WinForms or GridView in ASP.NET).

Conclusion

The DataTable object is a powerful and flexible tool in ADO.NET for managing in-memory data. Whether you're building disconnected data access layers, performing complex data transformations, or simply need a structured way to hold data, DataTable provides the necessary functionality. Understanding its schema definition, data manipulation methods, and integration with DataView will significantly enhance your ability to work with data in .NET applications.