Back to: ADO.NET Core Tutorial For Beginners and Professionals
ADO.NET Core DataTable
In this article, I will discuss ADO.NET Core DataTable with Examples. Please read our previous article discussing the ADO.NET Core SqlDataReader Class. ADO.NET provides a collection of classes that work with data from various sources. One of the most commonly used in-memory representations of relational data is the DataTable. In .NET Core, the System.Data.DataTable class stores tabular data in memory, mirroring the structure of a relational database table. Its capabilities include storing columns and rows, enforcing constraints, and working in a disconnected mode.
What is ADO.NET Core DataTable?
ADO.NET Core DataTable (a class in the System.Data namespace) is an in-memory representation of structured data (similar to database tables) that allows developers to store, manipulate, and retrieve data without directly interacting with a database.
It contains a collection of DataColumn objects representing the schema and a collection of DataRow objects holding the actual data. You can create, add, update, and delete rows and columns, perform filtering and sorting, and establish relationships between tables, all in memory. It can be used for:
- Holding data retrieved from a database (or any other source) in a disconnected manner.
- Manipulating and processing tabular data without an active database connection.
- Supporting filtering, sorting, and data relations in an in-memory scenario.
Although it can be used alongside databases, a DataTable can also be used completely in-memory without connecting to any data source.
Syntax to Create a DataTable
The basic syntax to create a DataTable is as follows. You can optionally provide a name for the DataTable. If not provided, it can remain unnamed.
DataTable dataTable = new DataTable(“MyTableName”);
What are the Data Rows and Data Columns of a Data Table?
A DataColumn defines the schema (column name, data type, constraints) for the data, while a DataRow contains the actual data for each column. Together, they form the rows and columns of a DataTable.
DataColumn
A DataColumn represents a single column in a DataTable. You can define its name, data type, and other properties, such as whether null values are allowed, uniqueness, read-only, and default values. The following is the syntax to create a Data Column:
DataRow
A DataRow represents a single record (i.e., a row of data) in a DataTable. You can create a new DataRow that adheres to the DataTable’s schema, set its values, and then add it to the table. The following is the syntax to create a Data Row:
Creating an In-Memory DataTable with Multiple Columns and Rows
Let us see how to Create an In-Memory DataTable using a .NET Core Console Application. The following example illustrates how to create a DataTable with multiple columns of different data types, configure various column properties, create DataRows, add them to the DataTable, and finally, display the contents of the DataTable with console output.
using System.Data;
namespace ADODOTNETCoreDemo
{
public class Program
{
static void Main(string[] args)
{
// Create a DataTable with multiple columns and rows
// Create a new DataTable named "Employees"
DataTable dtEmployees = new DataTable("Employees");
// Create a DataColumn for Employee ID (integer, auto-incremented, unique)
DataColumn dcId = new DataColumn("ID", typeof(int))
{
Unique = true, // Ensures each ID is unique
AutoIncrement = true, // Automatically increment the value.
AutoIncrementSeed = 1, // Starting value for auto-increment.
AutoIncrementStep = 1 // Increment step.
};
dtEmployees.Columns.Add(dcId); // Add column to the table
// Create a DataColumn for Employee Name (string, max length and no nulls allowed)
DataColumn dcName = new DataColumn("Name", typeof(string))
{
AllowDBNull = false, // Values cannot be null
MaxLength = 50 // Maximum character length of 50
};
dtEmployees.Columns.Add(dcName);
// Create a DataColumn for Hire Date (DateTime, no nulls allowed)
DataColumn dcHireDate = new DataColumn("HireDate", typeof(DateTime))
{
AllowDBNull = false // Must have a valid date value
};
dtEmployees.Columns.Add(dcHireDate);
// Create a DataColumn for Salary (decimal with a default value)
DataColumn dcSalary = new DataColumn("Salary", typeof(decimal))
{
DefaultValue = 0.0m // Sets a default salary if none is provided
};
dtEmployees.Columns.Add(dcSalary);
// Create a DataColumn for Hire Date (DateTime, no nulls allowed)
DataColumn dcIsActive = new DataColumn("IsActive", typeof(bool))
{
DefaultValue = true // Set default to true
};
dtEmployees.Columns.Add(dcIsActive);
// Define the primary key for the table
dtEmployees.PrimaryKey = [dcId];
// Create and add DataRow objects
// Create first DataRow for "John Doe"
DataRow row = dtEmployees.NewRow(); // Create a new row with schema
row["Name"] = "John Doe"; // Set Name column value
row["HireDate"] = DateTime.Now; // Use the current date and time
row["Salary"] = 50000.50m; // Set Salary
row["IsActive"] = true; // Set Is Active
dtEmployees.Rows.Add(row); // Add the new row to the table
// Create second DataRow for "Jane Smith"
row = dtEmployees.NewRow();
row["Name"] = "Jane Smith";
row["HireDate"] = new DateTime(2024, 8, 23);
row["Salary"] = 60000.75m;
row["IsActive"] = false;
dtEmployees.Rows.Add(row);
// Create third DataRow for "Sara Taylor"
row = dtEmployees.NewRow();
row["Name"] = "Sara Taylor";
row["HireDate"] = DateTime.Now.AddDays(-5); // Hire date set to 5 days ago
row["Salary"] = 66000.75m;
// row["IsActive"] = true; By default the value is true
dtEmployees.Rows.Add(row);
// Display the content of the DataTable
Console.WriteLine("Employees DataTable created with sample data:");
foreach (DataRow dr in dtEmployees.Rows)
{
Console.WriteLine($"ID: {dr["ID"]}, Name: {dr["Name"]}, HireDate: {((DateTime)dr["HireDate"]).ToShortDateString()}, Salary: {dr["Salary"]}, IsActive: {dr["IsActive"]}");
}
}
}
}
Code Explanation:
- DataTable & Columns Creation: The code creates a table named Employees with five columns: ID, Name, HireDate, Salary, and IsActive. Each column is configured with specific properties (e.g., auto-increment for ID, AllowDBNull for Name and HireDate, etc.).
- DataTable Column Properties:
- Unique = true ensures no duplicate values in that column.
- AllowDBNull to set whether the column accepts NULL values.
- DefaultValue sets a default when no value is provided.
- MaxLength sets the maximum characters allowed for a column.
- AutoIncrement = true ensures the column value increments automatically.
- Adding DataRows: Three rows are created using NewRow(), filled with respective employee data, and then added to the DataTable.
- Output: The program loops through each DataRow and outputs the data to the console.
So, when you run the application, you will get the following output:
What is DataTable Parent-Child Relationship?
A parent–child relationship allows us to create relationships between two DataTables in a Data Set (A Data Set is a collection of Data tables). These relationships are similar to foreign key relationships in a relational database. For example, an Order Details table (child) can relate to an Orders table (parent) by OrderId. Using the DataRelation object, we can define parent and child tables by linking one or more columns. This is especially useful when modeling hierarchical or related data sets entirely in memory. In this model:
- Parent Table: Contains primary key column(s) that uniquely identify each row.
- Child Table: Contains a foreign key column referencing the parent’s primary key.
- DataRelation Object: This defines the relationship between the two tables, allowing easy navigation from parent rows to their corresponding child rows and vice versa.
Example: Parent and Child Relations using DataTable
In the example below, we create two DataTables: one representing a set of orders (parent) and the other representing order details (child). A DataRelation is defined to link the OrderID in the Orders table (parent) with the OrderID in the OrderDetails table (child). The following example code is self-explained, so please read the comment lines for a better understanding:
using System.Data;
namespace DataTableRelationExample
{
public class Program
{
static void Main(string[] args)
{
// **********************************************
// Create Parent DataTable: "Orders"
// **********************************************
// Initialize the Orders DataTable
DataTable dtOrders = new DataTable("Orders");
// Create OrderID column (int, auto-increment, unique)
DataColumn dcOrderId = new DataColumn("OrderID", typeof(int))
{
Unique = true,
AutoIncrement = true,
AllowDBNull = false,
AutoIncrementSeed = 10001, // Starting value for auto-increment.
AutoIncrementStep = 1 // Increment step.
};
dtOrders.Columns.Add(dcOrderId);
// Create OrderDate column
DataColumn dcOrderDate = new DataColumn("OrderDate", typeof(DateTime));
dtOrders.Columns.Add(dcOrderDate);
// Creae CustomerName column.
DataColumn customerNameColumn = new DataColumn("CustomerName", typeof(string))
{
AllowDBNull = false,
MaxLength = 50,
};
dtOrders.Columns.Add(customerNameColumn);
// Set the primary key for the Orders table
dtOrders.PrimaryKey = [dcOrderId];
// Add sample order rows
DataRow orderRow = dtOrders.NewRow();
orderRow["OrderDate"] = DateTime.Now; // Today's order
orderRow["CustomerName"] = "Pranaya"; //Customer Name
dtOrders.Rows.Add(orderRow);
orderRow = dtOrders.NewRow();
orderRow["OrderDate"] = DateTime.Now.AddDays(-1); // Order from yesterday
orderRow["CustomerName"] = "Rakesh"; //Customer Name
dtOrders.Rows.Add(orderRow);
// **********************************************
// Create Child DataTable: "OrderDetails"
// **********************************************
// Initialize the OrderDetails DataTable
DataTable dtOrderDetails = new DataTable("OrderDetails");
// Create DetailID column (int, auto-increment, unique)
DataColumn dcDetailId = new DataColumn("OrderDetailID", typeof(int))
{
Unique = true,
AutoIncrement = true,
AutoIncrementSeed = 1, // Starting value for auto-increment.
AutoIncrementStep = 1 // Increment step.
};
dtOrderDetails.Columns.Add(dcDetailId);
// Add OrderID column which will act as a foreign key.
DataColumn dcDetailOrderID = new DataColumn("OrderID", typeof(int))
{
AllowDBNull = false,
};
dtOrderDetails.Columns.Add(dcDetailOrderID);
// Add a Product column.
DataColumn dcProduct = new DataColumn("Product", typeof(string));
dtOrderDetails.Columns.Add(dcProduct);
// Add a Quantity column.
DataColumn dcQuantity = new DataColumn("Quantity", typeof(int));
dtOrderDetails.Columns.Add(dcQuantity);
// Set the primary key for the OrderDetails table
dtOrderDetails.PrimaryKey = [dcDetailId];
// Add sample rows to OrderDetails table and link them with Orders table
DataRow detailRow = dtOrderDetails.NewRow();
detailRow["OrderID"] = dtOrders.Rows[0]["OrderID"]; // Link to first order
detailRow["Product"] = "Laptop";
detailRow["Quantity"] = 1;
dtOrderDetails.Rows.Add(detailRow);
detailRow = dtOrderDetails.NewRow();
detailRow["OrderID"] = dtOrders.Rows[0]["OrderID"]; // Link to first order
detailRow["Product"] = "Mouse";
detailRow["Quantity"] = 2;
dtOrderDetails.Rows.Add(detailRow);
detailRow = dtOrderDetails.NewRow();
detailRow["OrderID"] = dtOrders.Rows[1]["OrderID"]; // Link to second order
detailRow["Product"] = "Keyboard";
detailRow["Quantity"] = 1;
dtOrderDetails.Rows.Add(detailRow);
// ************************************************************
// Create a DataSet to hold both tables and define the relation
// ************************************************************
// Create a DataSet to hold our DataTables.
DataSet ds = new DataSet();
// Add parent table
// Add the Orders table to the DataSet.
ds.Tables.Add(dtOrders);
// Add child table
// Add the OrderDetails table to the DataSet.
ds.Tables.Add(dtOrderDetails);
// ---------------------
// Establish Parent-Child Relationship using DataRelation.
// ---------------------
// The relation links the OrderID in Orders (parent) with OrderID in OrderDetails (child)
DataRelation relation = new DataRelation("Order_OrderDetails",
dcOrderId, //Parent datatable, dtOrders, OrderID column
dcDetailOrderID); //Child datatable, dtOrderDetails, OrderID column
// Add the relation to the DataSet
ds.Relations.Add(relation);
// **********************************************
// Display the Parent–Child relation data
// **********************************************
Console.WriteLine("Displaying Orders and their associated Order Details:");
// Iterate through each row in the parent table (Orders).
foreach (DataRow order in dtOrders.Rows)
{
// Output parent order details
Console.WriteLine($"\nOrder ID: {order["OrderID"]}, Customer: {order["CustomerName"]}, Order Date: {((DateTime)order["OrderDate"]).ToShortDateString()}");
// Get related child rows using the DataRelation.
DataRow[] details = order.GetChildRows(relation);
// Output each order detail (child rows) associated with the order
foreach (DataRow detail in details)
{
Console.WriteLine($"\tDetail ID: {detail["OrderDetailID"]}, Product: {detail["Product"]}, Quantity: {detail["Quantity"]}");
}
}
// Pause the console to review output
Console.ReadLine();
}
}
}
Code Explanation:
- Parent Table (Orders): This table contains an auto-increment OrderID, Customer Name, and an OrderDate column. Rows representing individual orders are added.
- Child Table (OrderDetails): This table contains an auto-increment OrderDetailID, a foreign key OrderID, and additional columns for product information. Rows are added that link back to the correct order via the OrderID field.
- DataSet (ds): A DataSet is created to hold multiple DataTables.
- DataRelation: A DataRelation is created to establish the parent–child relationship between the Orders and OrderDetails tables. This relation allows us to navigate from an order to its related order details using the GetChildRows method.
- Output: The program iterates through each parent row and uses the GetChildRows method to retrieve and display the child rows associated with each order.
Now, run the application, and you should get the following output:
DataRow States in ADO.NET Core DataTable
When working with a DataSet, every DataRow goes through a lifecycle that its RowState reflects:
- Added: When a new DataRow is created and added to a DataTable, its state is Added until the changes are committed with AcceptChanges().
- Modified: When an existing DataRow is changed after it has been loaded or previously accepted, its state is Modified.
- Deleted: When a DataRow is removed (using Delete()), its state changes to Deleted. (The row is not physically removed from the DataTable until AcceptChanges() is called.)
- Unchanged: When a row’s changes have been committed to become the new baseline (either initially accepted or after a commit), its state is Unchanged.
The methods AcceptChanges and RejectChanges work as follows:
- AcceptChanges(): Commits all the changes since the last accept, marking Added or Modified rows as Unchanged and permanently removing rows in the Deleted state.
- RejectChanges(): Rolls back all changes since the last accept. Any Modified rows revert to their original values, Added rows are removed, and Deleted rows are undeleted.
Let us see how changes to a DataTable can be either committed or rejected using the AcceptChanges and RejectChanges methods. The following example demonstrates the Added, Modified, Deleted, and Unchanged states of a DataRow and shows how AcceptChanges and RejectChanges affect these states. The following example code is self-explained, so please read the comment lines for a better understanding.
using System.Data;
namespace DataTableRowStates
{
class Program
{
static void Main(string[] args)
{
// Create a DataTable "Orders"
DataTable dtOrders = new DataTable("Orders");
// Define the OrderID column with auto-increment properties
DataColumn dcOrderID = new DataColumn("OrderID", typeof(int))
{
AutoIncrement = true,
AutoIncrementSeed = 1,
AutoIncrementStep = 1,
};
// Add columns to the table
dtOrders.Columns.Add(dcOrderID);
dtOrders.Columns.Add("OrderAmount", typeof(decimal));
// Set the primary key for the DataTable
dtOrders.PrimaryKey = [dcOrderID];
//*************************************************************
// Demonstrate the "Added" State.
//*************************************************************
// Create a new DataRow and set its OrderAmount.
DataRow orderRow = dtOrders.NewRow();
orderRow["OrderAmount"] = 250.00m;
// Add the row to the DataTable; its state is now Added.
dtOrders.Rows.Add(orderRow);
Console.WriteLine("State after adding new row: " + orderRow.RowState); // Expected: Added
//*************************************************************
// Accept the changes so that the state becomes "Unchanged".
//*************************************************************
dtOrders.AcceptChanges();
Console.WriteLine("State after AcceptChanges: " + orderRow.RowState); // Expected: Unchanged
DisplayOrders(dtOrders, "After initial AcceptChanges:");
//*************************************************************
// Demonstrate the "Modified" State.
//*************************************************************
// Update the OrderAmount; this makes the row Modified.
orderRow["OrderAmount"] = 300.00m;
Console.WriteLine("\nState after modifying row: " + orderRow.RowState); // Expected: Modified
// Accept changes so that the row becomes Unchanged
dtOrders.AcceptChanges();
Console.WriteLine("State after AcceptChanges post-modification: " + orderRow.RowState); // Expected: Unchanged
DisplayOrders(dtOrders, "After modifying and Accepting changes:");
//*************************************************************
// Demonstrate the "Deleted" State.
//*************************************************************
// Delete the row; its state will change to Deleted.
orderRow.Delete();
Console.WriteLine("\nState after deleting row: " + orderRow.RowState); // Expected: Deleted
// Optionally, Reject the deletion to bring the row back
dtOrders.RejectChanges();
Console.WriteLine("State after RejectChanges (undo delete): " + orderRow.RowState); // Expected: Unchanged
DisplayOrders(dtOrders, "After Rejecting deletion:");
//*************************************************************
// Demonstrate Accepting a Deletion Permanently.
//*************************************************************
// Delete the row again.
orderRow.Delete();
Console.WriteLine("\nState after deleting row again: " + orderRow.RowState); // Expected: Deleted
// Accept the deletion to permanently remove the row.
dtOrders.AcceptChanges();
Console.WriteLine("Number of rows after accepting deletion: " + dtOrders.Rows.Count); // Expected: 0
//*************************************************************
// Demonstrate an Added row being automatically removed by RejectChanges.
//*************************************************************
// Create a new row (state: Added)
DataRow newRow = dtOrders.NewRow();
newRow["OrderAmount"] = 400.00m;
dtOrders.Rows.Add(newRow);
Console.WriteLine("\nState of new row after addition: " + newRow.RowState); // Expected: Added
// Reject changes will remove rows in the Added state.
dtOrders.RejectChanges();
Console.WriteLine("Number of rows after RejectChanges on an Added row: " + dtOrders.Rows.Count); // Expected: 0
Console.ReadKey();
}
// Helper method to display current orders in the DataTable
private static void DisplayOrders(DataTable table, string message)
{
Console.WriteLine($"\n{message}");
foreach (DataRow row in table.Rows)
{
// Note: Even if a row is marked Deleted, you may access its Original data using row["Column", DataRowVersion.Original]
if (row.RowState != DataRowState.Deleted)
{
Console.WriteLine($"OrderID: {row["OrderID"]}, OrderAmount: {row["OrderAmount"]}");
}
}
}
}
}
Code Explanation
- Data Table Creation: A new DataTable (“Orders”) is created. A primary key column (OrderID) is defined with auto-increment properties. Another column (“OrderAmount”) is added.
- Added State: A new row is created and added to the table immediately after calling Rows.Add(), the row is in the Added state. Calling AcceptChanges() commits this addition, and the row becomes Unchanged.
- Modified State: The row’s “OrderAmount” value is updated, and its state changes to Modified. After calling AcceptChanges(), the changes are committed, and the state returns to Unchanged.
- Deleted State: The row is deleted with the Delete() method, putting it in the deleted state. When RejectChanges() is called, the delete is undone, and the row reverts to Unchanged. Deleting the row again and calling AcceptChanges() permanently removes the row from the table.
- Additional Added/RejectChanges Scenario: A new row is added (state becomes Added). Calling RejectChanges() on a row in the Added state removes it from the DataTable.
Output:
What is Data View?
A DataView is a dynamic view or representation of the data contained in a DataTable. It is an in-memory view of a DataTable’s data that allows us to create customized, dynamic presentations of that data without modifying the original DataTable. It provides a flexible way to filter, sort, and search through rows and is particularly useful when we need to display or work with a subset or a different order of the underlying data.
Syntax to Create a Data View:
- DataView dv = dt.DefaultView;
- DataView dv = new DataView(dt);
Filtering, Sorting, Searching, and Computing Aggregates with a Data Table:
Let us first understand the meaning of Filtering, Sorting, Searching, and Computing Aggregates, and then we will see how to implement this with a data table.
- Filtering: Filtering refers to retrieving a subset of rows from the DataTable that meet certain criteria. This is commonly accomplished using the Select method with a filter expression (similar to a WHERE clause in SQL).
- Sorting: Sorting organizes the DataTable rows based on one or more columns. Typically, a DataView is used to sort the rows without altering the underlying DataTable’s order. With a DataView, we can assign a sorting expression (like “ColumnName ASC” or “ColumnName DESC”) to order the rows.
- Searching: Searching uses filtering techniques (usually via the Select method) to find specific rows that match a given condition, such as the exact match of a column value.
- Computing Aggregates: Computing aggregates means calculating summary values like the sum, average, maximum, or minimum on a column (or columns) using the DataTable.Compute() method. This allows us to summarize data held in the table quickly.
Example: DataTable Filtering, Sorting, Searching, and Computing Aggregates
The following example demonstrates creating an in‐memory DataTable, then performing filtering, sorting, searching, and computing aggregates on it. Please modify the Program class as follows. The example code is self-explained, so please read the comment lines for a better understanding:
using System.Data;
namespace DataTableOperationsExample
{
public class Program
{
static void Main(string[] args)
{
// **********************************************
// Create an In-Memory DataTable: "Orders"
// **********************************************
// Create a new DataTable named "Orders"
DataTable dtOrders = new DataTable("Orders");
// ----------------------------------------------
// Define columns with various properties
// ----------------------------------------------
// OrderID column: integer, auto-increment, unique, primary key.
DataColumn dcOrderId = new DataColumn("OrderID", typeof(int))
{
AutoIncrement = true, // Automatically increments the value.
Unique = true, // Ensures no duplicate values.
AutoIncrementSeed = 1, // Starting value.
AutoIncrementStep = 1 // Increment step.
};
dtOrders.Columns.Add(dcOrderId);
// CustomerName column: string, cannot be null.
DataColumn dcCustomerName = new DataColumn("CustomerName", typeof(string))
{
AllowDBNull = false, // Value is mandatory.
MaxLength = 50 // Limit the length to 50 characters.
};
dtOrders.Columns.Add(dcCustomerName);
// OrderDate column: DateTime, cannot be null.
DataColumn dcOrderDate = new DataColumn("OrderDate", typeof(DateTime))
{
AllowDBNull = false // Requires a valid date.
};
dtOrders.Columns.Add(dcOrderDate);
// Amount column: decimal, cannot be null.
DataColumn dcAmount = new DataColumn("Amount", typeof(decimal))
{
AllowDBNull = false // Must have an amount.
};
dtOrders.Columns.Add(dcAmount);
// Set the primary key for the DataTable to enable fast searches.
dtOrders.PrimaryKey = [dcOrderId];
// ----------------------------------------------
// Add sample rows to the DataTable
// ----------------------------------------------
// Create and add the first row (Order from Alice)
DataRow row = dtOrders.NewRow();
row["CustomerName"] = "Alice";
row["OrderDate"] = new DateTime(2023, 1, 10);
row["Amount"] = 150.75m;
dtOrders.Rows.Add(row); // Add the row to the table
// Create and add the second row (Order from Bob)
row = dtOrders.NewRow();
row["CustomerName"] = "Bob";
row["OrderDate"] = new DateTime(2023, 2, 15);
row["Amount"] = 200.00m;
dtOrders.Rows.Add(row);
// Create and add the third row (Order from Charlie)
row = dtOrders.NewRow();
row["CustomerName"] = "Charlie";
row["OrderDate"] = new DateTime(2023, 3, 5);
row["Amount"] = 99.99m;
dtOrders.Rows.Add(row);
// Create and add the fourth row (Order from Diana)
row = dtOrders.NewRow();
row["CustomerName"] = "Diana";
row["OrderDate"] = new DateTime(2023, 1, 20);
row["Amount"] = 350.50m;
dtOrders.Rows.Add(row);
// ----------------------------------------------
// Display all rows from the DataTable
// ----------------------------------------------
Console.WriteLine("All Orders:");
foreach (DataRow r in dtOrders.Rows)
{
Console.WriteLine($"OrderID: {r["OrderID"]}, Customer: {r["CustomerName"]}, Date: {((DateTime)r["OrderDate"]).ToShortDateString()}, Amount: {r["Amount"]}");
}
// **********************************************
// Filtering: Select rows where Amount is greater than 150
// **********************************************
// Use the DataTable.Select() method with a filter expression.
DataRow[] filteredRows = dtOrders.Select("Amount > 150");
Console.WriteLine("\nFiltered Orders (Amount > 150):");
foreach (DataRow r in filteredRows)
{
Console.WriteLine($"OrderID: {r["OrderID"]}, Customer: {r["CustomerName"]}, Date: {((DateTime)r["OrderDate"]).ToShortDateString()}, Amount: {r["Amount"]}");
}
// **********************************************
// Sorting: Sort the DataTable by OrderDate in Descending Order
// **********************************************
// Create a DataView for sorting purposes.
DataView dvSorted = dtOrders.DefaultView;
dvSorted.Sort = "OrderDate DESC"; // Sort by OrderDate descending
Console.WriteLine("\nSorted Orders by OrderDate (Descending):");
foreach (DataRowView drv in dvSorted)
{
Console.WriteLine($"OrderID: {drv["OrderID"]}, Customer: {drv["CustomerName"]}, Date: {((DateTime)drv["OrderDate"]).ToShortDateString()}, Amount: {drv["Amount"]}");
}
// **********************************************
// Searching: Find a specific order using the primary key
// **********************************************
// Using DataTable.Rows.Find() to search the row with OrderID = 2
int searchOrderId = 2;
DataRow? foundRow = dtOrders.Rows.Find(searchOrderId);
if (foundRow != null)
{
Console.WriteLine("\nSearched Order (using Find with primary key):");
Console.WriteLine($"OrderID: {foundRow["OrderID"]}, Customer: {foundRow["CustomerName"]}, Date: {((DateTime)foundRow["OrderDate"]).ToShortDateString()}, Amount: {foundRow["Amount"]}");
}
else
{
Console.WriteLine($"\nOrder with OrderID = {searchOrderId} not found.");
}
// **********************************************
// Computing Aggregates: Calculate total, average, and count of Amount
// **********************************************
// Using DataTable.Compute() method to perform aggregate functions.
// Compute the total amount across all orders.
object sumAmountObj = dtOrders.Compute("Sum(Amount)", "");
decimal sumAmount = sumAmountObj != null ? Convert.ToDecimal(sumAmountObj) : 0;
Console.WriteLine($"\nAggregate - Total Amount: {sumAmount}");
// Compute the average order amount.
object avgAmountObj = dtOrders.Compute("Avg(Amount)", "");
decimal avgAmount = avgAmountObj != null ? Convert.ToDecimal(avgAmountObj) : 0;
Console.WriteLine($"Aggregate - Average Amount: {avgAmount}");
// Compute the count of orders.
object countOrdersObj = dtOrders.Compute("Count(OrderID)", "");
int countOrders = countOrdersObj != null ? Convert.ToInt32(countOrdersObj) : 0;
Console.WriteLine($"Aggregate - Total Orders Count: {countOrders}");
Console.ReadLine();
}
}
}
Code Explanations:
- Filtering: The Select() method with the filter expression “Amount > 150” extracts only those rows where the Amount exceeds 150. The filtered rows are then printed.
- Sorting: A DataView is created from the DataTable’s default view. The sorting expression “OrderDate DESC” arranges the rows by date in descending order. The sorted rows are printed by iterating over the DataRowView collection.
- Searching: Using the primary key, DataTable.Rows.Find() is used to locate a row where OrderID equals 2. The found row is printed; if not found, a message is displayed.
- Computing Aggregates: The Compute() method is used three times:
- Sum(Amount): Calculates the total order amount.
- Avg(Amount): Calculates the average order amount.
- Count(OrderID): Counts the total number of orders.
Output:
When Should We Use DataTable in ADO.NET Core?
Using a DataTable in ADO.NET Core is beneficial when:
- Disconnected Data Scenarios: When you retrieve data from a database and want to work with it without a continuous connection. DataTables allow you to work in a disconnected manner and can later be synchronized if needed.
- In-Memory Data Manipulation: When you require in-memory representation and manipulation of tabular data, such as filtering, searching, updating, and binding to UI controls (e.g., in Windows Forms or WPF).
- Data Binding: DataTables are ideal for data binding in various application frameworks. They let you display and update data easily in grid views or other controls.
- Complex Data Relations: When modeling relational data with parent–child relations entirely in memory. DataTables combined with DataRelations allow you to simulate database-like referential integrity without using a database.
In short, DataTable is ideal for temporary, in-memory data representations that require complex manipulation before persisting to a data store or being displayed to the user. It provides a highly flexible and efficient way to work with structured data without relying on external databases, making it suitable for various application scenarios.
In the next article, I will discuss ADO.NET Core DataTable using SQL Server with Examples. In this article, I explain ADO.NET Core DataTable with Examples. I want your feedback. Please post your feedback, questions, or comments about this article.
About the Author: Pranaya Rout
Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.