The DataTable class in ADO.NET represents a single in-memory table that holds rows and columns of data. It is part of the disconnected architecture of ADO.NET and can be used independently or within a DataSet.

Key Features of DataTable

1. Stores data in-memory for manipulation.
2. Supports sorting, filtering, and searching.
3. Can be populated using a SqlDataAdapter.
4. Allows adding, modifying, and deleting rows without needing an active database connection.
5. Supports constraints (Primary Key, Unique, Foreign Key, etc.).

 

Namespace : 

using System.Data;

1. Creating a DataTable and Adding Columns

A DataTable can be created manually by defining columns and adding rows.

using System;
using System.Data;

class Program
{
    static void Main()
    {
        // Creating a DataTable
        DataTable table = new DataTable("Users");

        // Defining Columns
        table.Columns.Add("Id", typeof(int));
        table.Columns.Add("Username", typeof(string));
        table.Columns.Add("Email", typeof(string));

        // Setting Primary Key
        table.PrimaryKey = new DataColumn[] { table.Columns["Id"] };

        // Adding Rows
        table.Rows.Add(1, "JohnDoe", "john@example.com");
        table.Rows.Add(2, "JaneDoe", "jane@example.com");

        // Displaying Data
        foreach (DataRow row in table.Rows)
        {
            Console.WriteLine($"ID: {row["Id"]}, Username: {row["Username"]}, Email: {row["Email"]}");
        }
    }
}

• Columns.Add("ColumnName", typeof(DataType)) defines columns.
• table.Rows.Add(...) inserts a new row.
• PrimaryKey ensures uniqueness for the Id column.

 

2. Populating a DataTable from a Database

Instead of manually adding data, we can populate a DataTable using SqlDataAdapter.

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = "Server=myServer;Database=myDB;User Id=myUser;Password=myPassword;";
        string query = "SELECT Id, Username, Email FROM Users";

        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            SqlDataAdapter adapter = new SqlDataAdapter(query, conn);
            DataTable usersTable = new DataTable();
            adapter.Fill(usersTable); // Fetches data from the database

            foreach (DataRow row in usersTable.Rows)
            {
                Console.WriteLine($"ID: {row["Id"]}, Username: {row["Username"]}, Email: {row["Email"]}");
            }
        }
    }
}

 

3. Searching and Filtering in a DataTable

We can use Select() to filter rows dynamically.

DataRow[] filteredRows = table.Select("Username = 'JohnDoe'");

foreach (DataRow row in filteredRows)
{
    Console.WriteLine($"Found: {row["Username"]}, Email: {row["Email"]}");
}

Select("condition") allows SQL-like filtering.

 

4. Sorting a DataTable

We can sort using a DataView.

DataView view = new DataView(table);
view.Sort = "Username ASC";

foreach (DataRowView row in view)
{
    Console.WriteLine($"Sorted: {row["Username"]}, Email: {row["Email"]}");
}

DataView.Sort sorts data without modifying the DataTable.

 

5. Updating a DataTable

Rows in a DataTable can be updated before persisting changes to the database.

 

DataRow rowToUpdate = table.Rows.Find(1); // Find row with Id = 1
if (rowToUpdate != null)
{
    rowToUpdate["Username"] = "UpdatedUser";
    rowToUpdate["Email"] = "updated@example.com";
}

Console.WriteLine($"Updated: {rowToUpdate["Username"]}, Email: {rowToUpdate["Email"]}");

Find() requires a primary key to locate rows.

 

6. Deleting a Row

A row can be deleted before updating the database.

 

DataRow rowToDelete = table.Rows.Find(2);
if (rowToDelete != null)
{
    rowToDelete.Delete();
}

Delete() marks the row for deletion, but it remains in memory until AcceptChanges() is called.

 

7. Committing Changes (AcceptChanges and RejectChanges)

• AcceptChanges(): Confirms all modifications (additions, updates, deletions).
• RejectChanges(): Cancels all pending changes.

 

table.AcceptChanges();  // Confirms all changes
// or
table.RejectChanges();  // Cancels all changes

Best used when working in a disconnected mode.

 

8. Using Constraints in a DataTable

We can enforce Primary Key, Unique, and Foreign Key constraints.

Example: Unique Constraint

table.Constraints.Add(new UniqueConstraint("UniqueUsername", table.Columns["Username"]));

 

9. DataTable vs DataReader vs DataSet

Feature	DataTable	SqlDataReader	DataSet
Connection	Disconnected	Requires an active connection	Disconnected
Usage	Single table data storage	Read-only, forward-only	Multiple tables
Performance	Medium	Fast	Slower than DataTable
Memory Usage	Moderate	Low	High

 

Conclusion

The DataTable class is a powerful in-memory data structure for handling structured data in ADO.NET. It is best for:

• Disconnected operations.
• Sorting, filtering, and searching data efficiently.
• Working with multiple rows/columns in memory before updating the database.