ADO - ADO.NET DataTable LoadOption and Record Version Management
1. Introduction
In ADO.NET, the DataTable class is used to store and manage data in memory in the form of rows and columns. It allows applications to retrieve information from databases, modify records, and maintain changes before updating the original database.
When data is loaded into an existing DataTable, it is important to decide how the incoming values should affect the records that are already present. ADO.NET provides the LoadOption enumeration to control this process.
LoadOption determines how incoming database values are applied to the existing records in a DataTable. It helps developers decide whether to preserve the original values, replace the current values, or update both versions of a record.
This feature is particularly useful when applications need to synchronize data, refresh existing records, or manage changes made by users.
2. Understanding Record Versions in ADO.NET
Before learning about LoadOption, it is important to understand record versions in ADO.NET.
A DataRow can maintain different versions of its values depending on its state and modification history.
The three important record versions are:
Original Version
The Original version represents the values that were present when the record was last accepted into the DataTable. These values are useful when comparing the current record with its previous state.
For example, suppose an employee's original salary is ₹30,000. After the record is loaded and accepted, the Original version stores ₹30,000.
Current Version
The Current version represents the latest values available in the DataRow. If a user modifies an employee's salary from ₹30,000 to ₹35,000, the Current version stores ₹35,000 while the Original version continues to store ₹30,000.
Proposed Version
The Proposed version contains temporary values being edited before they are committed to the DataRow. It is commonly available when BeginEdit() is used.
For example, if an employee's salary is changed from ₹35,000 to ₹40,000 inside a BeginEdit() operation, ₹40,000 is stored temporarily as the Proposed version. After EndEdit() is called, the proposed values become the Current values.
3. What Is the LoadOption Enumeration?
LoadOption is an enumeration available in ADO.NET that controls how incoming records are merged into an existing DataTable.
It is commonly used with the DataTable.Load() method.
The LoadOption enumeration provides three main options:
OverwriteChanges
OverwriteChanges replaces both the Original and Current values with the incoming values.
This option is useful when the application needs to refresh records completely using the latest database information.
For example, suppose an employee's salary is originally ₹30,000 and a user changes it to ₹35,000. If the database contains a salary of ₹32,000 and the DataTable is loaded using OverwriteChanges, both the Original and Current versions become ₹32,000.
Any previous local modification is overwritten.
PreserveChanges
PreserveChanges updates the Original version with the incoming database values while keeping the existing Current values unchanged.
This option is useful when an application wants to retrieve the latest database information without losing modifications made locally by users.
For example:
-
Original salary: ₹30,000
-
Current salary: ₹35,000
-
Incoming database salary: ₹32,000
After loading with PreserveChanges:
-
Original version: ₹32,000
-
Current version: ₹35,000
This allows the application to retain the user's modification while keeping the latest database value available for comparison.
Upsert
Upsert updates the Current version with incoming values while preserving the existing Original version.
This option is useful when an application needs to apply incoming data without replacing the original values used for change tracking.
For example:
-
Original salary: ₹30,000
-
Current salary: ₹35,000
-
Incoming database salary: ₹32,000
After loading with Upsert:
-
Original version: ₹30,000
-
Current version: ₹32,000
This option is helpful when incoming records should become the current data while the previous original values remain available.
4. Comparison of LoadOption Values
The following table explains how each LoadOption affects record versions.
|
LoadOption |
Original Version |
Current Version |
|---|---|---|
|
OverwriteChanges |
Updated |
Updated |
|
PreserveChanges |
Updated |
Preserved |
|
Upsert |
Preserved |
Updated |
Understanding these differences helps developers select the correct option depending on the application's data synchronization requirements.
5. Using LoadOption with the DataTable.Load() Method
The DataTable.Load() method is used to load data from a DataReader into a DataTable.
It accepts a LoadOption parameter that determines how incoming records should be applied.
Example in C#:
using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
static void Main()
{
string connectionString =
"Data Source=.;Initial Catalog=CompanyDB;Integrated Security=True;TrustServerCertificate=True";
DataTable employeeTable = new DataTable();
using (SqlConnection connection =
new SqlConnection(connectionString))
{
connection.Open();
string query = "SELECT * FROM Employees";
using (SqlCommand command =
new SqlCommand(query, connection))
{
using (SqlDataReader reader = command.ExecuteReader())
{
employeeTable.Load(
reader,
LoadOption.PreserveChanges
);
}
}
}
Console.WriteLine("Employee records loaded successfully.");
}
}
Explanation of the program:
-
A database connection is established using SqlConnection.
-
A DataTable object named employeeTable is created to store employee information.
-
A SQL query retrieves records from the Employees table.
-
SqlDataReader executes the query and reads the returned records.
-
The DataTable.Load() method loads the retrieved records into the DataTable.
-
LoadOption.PreserveChanges ensures that existing Current values are preserved while incoming values update the Original versions of matching records.
The example uses PreserveChanges to demonstrate how locally modified records can be protected during data loading.
6. Importance of Record Version Management
Record version management plays an important role in applications that frequently retrieve and modify database information.
Its major benefits include:
Maintaining Data Integrity: Record versions help applications identify differences between previously stored values and newly modified values.
Supporting Data Synchronization: LoadOption makes it easier to synchronize incoming database records with existing in-memory data.
Preserving User Modifications: PreserveChanges helps prevent locally modified values from being overwritten when fresh database information is loaded.
Tracking Changes: Original and Current versions allow developers to identify which values have changed.
Improving Data Management: Developers can select suitable loading strategies depending on whether they want to preserve, replace, or update existing records.
7. Practical Applications of LoadOption
LoadOption and record version management are useful in several real-world applications.
-
Employee Management Systems: When employee salary, department, or contact details are updated, applications can compare previous and current values.
-
Banking Applications: Financial applications can maintain previous and current transaction-related information during data processing.
-
Inventory Management: Applications can synchronize stock information while tracking changes made by users.
-
Customer Relationship Management: Customer details can be refreshed without unnecessarily losing locally modified information.
-
Business Reporting Systems: Applications can compare database values with previously loaded records to identify changes.
8. Conclusion
ADO.NET DataTable LoadOption and Record Version Management provide an effective way to control how incoming database records interact with existing in-memory data. The three LoadOption values, OverwriteChanges, PreserveChanges, and Upsert, offer different approaches for managing Original and Current record versions.
By understanding these options, developers can prevent unwanted data replacement, preserve user modifications, and maintain accurate records during database synchronization. These features are especially useful when developing data-driven applications that frequently retrieve, modify, and update information.