ADO - ADO.NET DataTable.Load Method and LoadOption Strategies
1. Introduction
In ADO.NET, the DataTable.Load() method is used to populate a DataTable object with records retrieved from a data source. It provides a convenient way to transfer data from a database into memory using a DataReader.
A DataTable is an in-memory representation of a database table. It stores data in rows and columns and allows applications to access, modify, filter, and process records without continuously communicating with the database.
The DataTable.Load() method is particularly useful when an application needs to retrieve records using a DataReader and store them in a DataTable for further processing.
One important feature of this method is its ability to control how incoming records are merged with existing records. This behaviour is managed through the LoadOption enumeration.
2. What Is the DataTable.Load() Method?
The DataTable.Load() method reads data from an IDataReader and copies the retrieved records into a DataTable.
It can create the necessary columns automatically based on the structure of the incoming data. If the DataTable already contains records, the method can merge the new records with the existing ones according to the specified loading strategy.
The basic syntax is:
public void Load(
IDataReader reader,
LoadOption loadOption
);
Here:
-
reader: Represents the data reader containing records retrieved from the database. -
loadOption: Specifies how incoming records should be merged with existing records.
The LoadOption parameter is optional. If it is not specified, the default behaviour is LoadOption.PreserveChanges.
3. Understanding the LoadOption Enumeration
The LoadOption enumeration determines how incoming database values are applied to an existing DataTable.
It provides three important options:
A. PreserveChanges
PreserveChanges maintains the current values of modified rows while updating their original values with the incoming database values.
This option is useful when an application contains user modifications that should not be overwritten by newly retrieved database records.
For example, consider an employee table containing the following information:
|
Employee ID |
Employee Name |
Department |
|---|---|---|
|
101 |
Rahul |
Sales |
Suppose the employee's department is changed locally from Sales to Marketing, but the change has not yet been saved to the database.
When new database records are loaded using PreserveChanges, the locally modified department value remains unchanged.
This approach helps applications preserve pending modifications while refreshing the original database values.
B. OverwriteChanges
OverwriteChanges replaces the existing row values with the incoming values from the data source.
This option is useful when the application needs to refresh its local data completely and does not need to preserve previous modifications.
For example, suppose the DataTable contains:
|
Employee ID |
Employee Name |
Department |
|---|---|---|
|
101 |
Rahul |
Marketing |
However, the database contains:
|
Employee ID |
Employee Name |
Department |
|---|---|---|
|
101 |
Rahul |
Finance |
When the data is loaded using OverwriteChanges, the existing local value is replaced with the database value, Finance.
This option is appropriate when the database is considered the authoritative source of information.
C. Upsert
Upsert updates the current values of matching rows with incoming database values while preserving their original values.
If a matching row does not exist, the incoming record is inserted as a new row.
This option is useful when applications need to synchronize their local DataTable with updated database information while retaining the original versions of existing records.
For example, consider the following existing DataTable:
|
Employee ID |
Employee Name |
Department |
|---|---|---|
|
101 |
Rahul |
Marketing |
The database contains:
|
Employee ID |
Employee Name |
Department |
|---|---|---|
|
101 |
Rahul |
Finance |
|
102 |
Priya |
HR |
When Upsert is used:
-
Employee 101 receives the updated current value Finance.
-
Employee 102 is inserted as a new record.
-
The original version of the existing employee record is preserved.
4. Example of Using DataTable.Load()
The following C# example demonstrates how to retrieve employee records from a SQL Server database and load them into a DataTable.
using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString =
"Data Source=.;Initial Catalog=CompanyDB;Integrated Security=True";
using (SqlConnection con =
new SqlConnection(connectionString))
{
con.Open();
string query = "SELECT * FROM Employees";
using (SqlCommand cmd =
new SqlCommand(query, con))
{
using (SqlDataReader reader =
cmd.ExecuteReader())
{
DataTable dt = new DataTable();
dt.Load(reader, LoadOption.OverwriteChanges);
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(
row["EmployeeName"] + " - " +
row["Department"]
);
}
}
}
}
}
}
Explanation of the Example
-
The program establishes a connection to the SQL Server database using
SqlConnection. -
A SQL query retrieves all records from the Employees table.
-
The
SqlCommandobject executes the query. -
The
SqlDataReaderretrieves the database records. -
A new DataTable object is created to store the retrieved information.
-
The
Load()method transfers the records from the DataReader into the DataTable. -
The
OverwriteChangesoption specifies how incoming values should be applied to existing records. -
Finally, a loop displays the employee names and departments stored in the DataTable.
5. How DataTable.Load() Handles Existing Records
One of the important features of the Load() method is its ability to work with an already populated DataTable.
When incoming records match existing rows, the method uses the selected LoadOption to determine which values should be preserved or replaced.
For this matching process, the DataTable needs an appropriate primary key or other identifying information to match incoming records with existing rows.
If no suitable primary key exists, the method may not be able to identify matching records as expected.
This feature is especially useful in applications that regularly retrieve updated information from databases.
6. Advantages of DataTable.Load()
The DataTable.Load() method provides several advantages:
Simplifies Data Retrieval: It allows developers to transfer records directly from a DataReader into a DataTable without manually creating and populating every row.
Supports Data Merging: It can combine incoming database records with existing DataTable records.
Preserves Local Changes: The PreserveChanges option helps prevent pending local modifications from being overwritten.
Provides Flexible Loading Options: Developers can select different loading strategies depending on application requirements.
Reduces Coding Effort: It automatically handles column creation and record loading based on the reader's structure.
Supports Disconnected Data Processing: Once records are loaded into a DataTable, applications can work with the data without maintaining an active database connection.
7. Limitations of DataTable.Load()
Although the method is useful, it has certain limitations:
-
It requires an object that implements the
IDataReaderinterface. -
Large datasets may consume considerable memory because the entire result is stored in memory.
-
Correct record matching depends on suitable primary key information.
-
Incorrect use of LoadOption may overwrite important local modifications.
-
It is not designed for directly saving modified DataTable records back to the database. A separate update mechanism is required.
8. Practical Applications of DataTable.Load()
The DataTable.Load() method is useful in several real-world application scenarios.
Employee Management Systems: Applications can retrieve employee information and load it into a DataTable for displaying and processing employee records.
Inventory Management: Businesses can retrieve product details and update locally stored inventory information.
Student Management Systems: Educational applications can load student records from databases for searching, filtering, and reporting.
Financial Applications: Applications can retrieve account information and compare incoming database values with locally modified records.
Data Synchronization: Systems can refresh local datasets while controlling how existing changes are preserved or replaced.
9. Difference Between PreserveChanges, OverwriteChanges, and Upsert
|
Feature |
PreserveChanges |
OverwriteChanges |
Upsert |
|---|---|---|---|
|
Existing modified values |
Preserved |
Replaced |
Replaced in current version |
|
Original values |
Updated |
Replaced |
Preserved |
|
New records |
Inserted |
Inserted |
Inserted |
|
Main purpose |
Protect local modifications |
Refresh existing data |
Synchronize incoming data |
|
Suitable for |
Applications with pending edits |
Complete data refresh |
Data synchronization |
10. Conclusion
The ADO.NET DataTable.Load() method is an important feature for transferring database records into an in-memory DataTable using a DataReader. It simplifies data retrieval and provides flexible mechanisms for handling existing records.
The three LoadOption strategies, PreserveChanges, OverwriteChanges, and Upsert, allow developers to control how incoming database information interacts with locally stored data.
By understanding these loading strategies, developers can build more reliable database applications, manage local modifications effectively, and maintain consistency between in-memory data and database records.