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

  1. The program establishes a connection to the SQL Server database using SqlConnection.

  2. A SQL query retrieves all records from the Employees table.

  3. The SqlCommand object executes the query.

  4. The SqlDataReader retrieves the database records.

  5. A new DataTable object is created to store the retrieved information.

  6. The Load() method transfers the records from the DataReader into the DataTable.

  7. The OverwriteChanges option specifies how incoming values should be applied to existing records.

  8. 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 IDataReader interface.

  • 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.