ADO - ADO.NET DataTable Load and LoadDataRow Methods for Importing Database Records

 
 

1. Introduction

ADO.NET provides several methods for working with data stored in databases. One important component of ADO.NET is the DataTable, which is used to store and manage data in a tabular format consisting of rows and columns. A DataTable can hold data retrieved from a database, an XML file, or another data source.

The Load() and LoadDataRow() methods are useful features of the DataTable class. They help developers import records into a DataTable and manage existing records efficiently. These methods reduce the amount of manual coding required when transferring data from one source to another.

Although both methods are used to populate a DataTable, they perform different tasks. The Load() method is mainly used to load records from a data reader, whereas the LoadDataRow() method is used to insert a new row or update an existing row based on its primary key.

2. Understanding the DataTable Load() Method

The Load() method is used to populate a DataTable with records obtained from a data source. It commonly works with an object that implements the IDataReader interface, such as SqlDataReader.

When an application retrieves information from a SQL Server database using a data reader, the Load() method can transfer those records directly into a DataTable.

This method is particularly useful when developers want to convert forward-only database results into an in-memory table that supports additional operations such as sorting, filtering, editing, and searching.

Syntax:

DataTable table = new DataTable();
table.Load(dataReader);

Here:

  • DataTable represents the in-memory table used to store records.

  • Load() is the method responsible for importing the records.

  • dataReader represents the data source containing the retrieved database records.

Example of the Load() Method

Consider a student management system that stores student information in a SQL Server database.

The database contains a table named Students with the following columns:

StudentID

StudentName

Course

101

Arun

Computer Science

102

Priya

Information Technology

103

Rahul

Electronics

The following C# program retrieves the student records and loads them into a DataTable.

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

class Program
{
    static void Main()
    {
        string connectionString =
            "Data Source=SERVER;Initial Catalog=CollegeDB;Integrated Security=True;";

        using (SqlConnection con =
            new SqlConnection(connectionString))
        {
            con.Open();

            string query = "SELECT * FROM Students";

            using (SqlCommand cmd =
                new SqlCommand(query, con))
            using (SqlDataReader reader = cmd.ExecuteReader())
            {
                DataTable dt = new DataTable();

                dt.Load(reader);

                foreach (DataRow row in dt.Rows)
                {
                    Console.WriteLine(
                        row["StudentID"] + " " +
                        row["StudentName"] + " " +
                        row["Course"]);
                }
            }
        }
    }
}

Explanation:

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

  2. The SqlCommand object executes a SELECT query to retrieve student records.

  3. The ExecuteReader() method returns a SqlDataReader containing the query results.

  4. A new DataTable object named dt is created.

  5. The Load() method transfers the records from the SqlDataReader into the DataTable.

  6. The foreach loop accesses each row and displays the student information.

After loading, the DataTable contains all the retrieved student records in memory. The reader is consumed during this operation and is closed when its using block ends.

3. Understanding the LoadDataRow() Method

The LoadDataRow() method is another useful method of the DataTable class. It is used to add a new record or update an existing record in a DataTable.

Unlike the Load() method, which imports records from a data reader, LoadDataRow() works with an array of values representing the columns of a row.

One important feature of this method is that it can identify an existing record using the table's primary key. If a matching record is found, the method updates that record. Otherwise, it creates a new row.

Syntax:

DataRow row = table.LoadDataRow(values, true);

Here:

  • table represents the DataTable object.

  • values represents an array containing values for the columns.

  • true indicates that the method should accept the changes made to the row after loading.

The second parameter is called fLoad. It controls how the changes are applied and accepted.

Example of the LoadDataRow() Method

Consider a student management system containing the following records:

StudentID

StudentName

Course

101

Arun

Computer Science

102

Priya

Information Technology

Suppose the application receives a new record for student ID 103. It also receives updated information for student ID 102.

The following example demonstrates how LoadDataRow() handles both situations.

using System;
using System.Data;

class Program
{
    static void Main()
    {
        DataTable dt = new DataTable();

        dt.Columns.Add("StudentID", typeof(int));
        dt.Columns.Add("StudentName", typeof(string));
        dt.Columns.Add("Course", typeof(string));

        dt.PrimaryKey = new DataColumn[]
        {
            dt.Columns["StudentID"]
        };

        dt.Rows.Add(101, "Arun", "Computer Science");
        dt.Rows.Add(102, "Priya", "Information Technology");

        dt.LoadDataRow(
            new object[] { 103, "Rahul", "Electronics" },
            true
        );

        dt.LoadDataRow(
            new object[] { 102, "Priya", "Computer Science" },
            true
        );

        foreach (DataRow row in dt.Rows)
        {
            Console.WriteLine(
                row["StudentID"] + " " +
                row["StudentName"] + " " +
                row["Course"]);
        }
    }
}

Explanation:

  1. A DataTable named dt is created.

  2. Three columns are added: StudentID, StudentName, and Course.

  3. StudentID is configured as the primary key.

  4. Two existing student records are inserted into the DataTable.

  5. The first LoadDataRow() call inserts student ID 103 because that primary key does not already exist.

  6. The second LoadDataRow() call finds student ID 102 and updates its Course value.

  7. The foreach loop displays all the records stored in the DataTable.

Expected Output:

101 Arun Computer Science
102 Priya Computer Science
103 Rahul Electronics

This demonstrates how LoadDataRow() can insert new records and update existing records without requiring separate insertion and update operations.

4. Difference Between Load() and LoadDataRow()

Although both methods are used to populate a DataTable, their purposes and working mechanisms are different.

Feature

Load() Method

LoadDataRow() Method

Main purpose

Imports multiple records from a data reader

Inserts or updates an individual row

Data source

IDataReader

Array of column values

Record handling

Loads a set of records

Handles one row at a time

Primary key

Uses the reader's data and load behavior

Can use the DataTable primary key to locate a matching row

Common usage

Retrieving database query results

Synchronizing individual records

Input parameter

Data reader

Object array and Boolean value

Result

Populates the DataTable

Returns the affected DataRow

The Load() method is more suitable when retrieving a collection of records from a database. The LoadDataRow() method is useful when applications need to insert new records or update existing records in an in-memory table.

5. Advantages of Using Load() and LoadDataRow()

These methods provide several advantages in database application development.

1. Simplified Data Importing

The Load() method makes it easier to transfer database query results into a DataTable. Developers do not need to manually create each row and assign every column value.

2. Efficient Record Management

The LoadDataRow() method simplifies the process of inserting and updating records. It can locate existing rows using primary keys and update their values.

3. Better Data Organization

Both methods help organize database information into rows and columns. This makes it easier for applications to process and display records.

4. Reduced Programming Effort

Using these built-in methods reduces repetitive coding. Developers can perform data-loading operations with fewer instructions.

5. Improved Application Flexibility

Once records are loaded into a DataTable, developers can perform operations such as filtering, sorting, searching, and modifying data without continuously accessing the database.

6. Practical Applications

The Load() and LoadDataRow() methods are useful in several real-world applications.

Student Management Systems: Educational applications can use the Load() method to retrieve student details from a database and display them in a DataGridView. The LoadDataRow() method can insert or update individual student records.

Employee Management Systems: Human resource applications can load employee information into a DataTable for reporting and analysis. Individual employee details can be updated using LoadDataRow().

Inventory Management Systems: Inventory applications can retrieve product information from a database and maintain an in-memory table. New product details can be inserted, while existing product information can be updated.

Banking Applications: Banking systems can use DataTables to manage account-related information retrieved from databases. The LoadDataRow() method can help update records within an in-memory table.

Business Reporting Applications: Reporting applications can load large collections of database records into DataTables and perform additional processing before generating reports.

7. Important Considerations

While using these methods, developers should understand a few important points.

  • The Load() method requires a compatible data reader, and the reader should be positioned appropriately before loading.

  • The LoadDataRow() method works best when a primary key is defined for identifying existing records.

  • The number and order of values supplied to LoadDataRow() should match the DataTable's columns.

  • Data types must be compatible with the column definitions.

  • The fLoad parameter determines whether changes are accepted after loading. When it is true, the changes are accepted; when it is false, they remain pending.

  • A DataTable stores information in application memory. Changes made to it do not automatically update the original database. Developers must use appropriate database commands or a DataAdapter to persist those changes.

8. Conclusion

The Load() and LoadDataRow() methods are important features of ADO.NET that simplify data importing and record management. The Load() method allows developers to transfer multiple records from a data reader into a DataTable, while the LoadDataRow() method provides a convenient way to insert new records or update existing ones based on primary keys. These methods improve code simplicity, data organization, and application flexibility. By understanding how they work and when to use them, developers can build efficient database-driven applications using ADO.NET.