DataTable.Load Method (IDataReader)

Fills a DataTable with values from a data source using the supplied IDataReader. If the DataTable already contains rows, the incoming data from the data source is merged with the existing rows.

Namespace:  System.Data
Assembly:  System.Data (in System.Data.dll)

member Load : 
        reader:IDataReader -> unit


Type: System.Data.IDataReader

An IDataReader that provides a result set.

The Load method consumes the first result set from the loaded IDataReader, and after successful completion, sets the reader's position to the next result set, if any. When converting data, the Load method uses the same conversion rules as the DbDataAdapter.Fill method.

The Load method must take into account three specific issues when loading the data from an IDataReader instance: schema, data, and event operations. When working with the schema, the Load method may encounter conditions as described in the following table. The schema operations take place for all imported result sets, even those containing no data.



The DataTable has no schema.

The Load method infers the schema based on the result set from the imported IDataReader.

The DataTable has a schema, but it is incompatible with the loaded schema.

The Load method throws an exception corresponding to the particular error that occurs when attempting to load data into the incompatible schema.

The schemas are compatible, but the loaded result set schema contains columns that do not exist in the DataTable.

The Load method adds the extra columns to DataTable's schema. The method throws an exception if corresponding columns in the DataTable and the loaded result set are not value compatible. The method also retrieves constraint information from the result set for all added columns. Except for the case of Primary Key constraint, this constraint information is used only if the current DataTable does not contain any columns at the start of the load operation.

The schemas are compatible, but the loaded result set schema contains fewer columns than does the DataTable.

If a missing column has a default value defined or the column's data type is nullable, the Load method allows the rows to be added, substituting the default or a null reference (Nothing in Visual Basic) value for the missing column. If no default value or a null reference (Nothing in Visual Basic) can be used, then the Load method throws an exception. If no specific default value has been supplied, the Load method uses the a null reference (Nothing in Visual Basic) value as the implied default value.

Before considering the behavior of the Load method in terms of data operations, consider that each row within a DataTable maintains both the current value and the original value for each column. These values may be equivalent, or may be different if the data in the row has been changed since filling the DataTable. For more information, see Row States and Row Versions.

This version of the Load method attempts to preserve the current values in each row, leaving the original value intact. (If you want finer control over the behavior of incoming data, see DataTable.Load.) If the existing row and the incoming row contain corresponding primary key values, the row is processed using its current row state value, otherwise it's treated as a new row.

In terms of event operations, the RowChanging event occurs before each row is changed, and the RowChanged event occurs after each row has been changed. In each case, the Action property of the DataRowChangeEventArgs instance passed to the event handler contains information about the particular action associated with the event. This action value depends on the state of the row before the load operation. In each case, both events occur, and the action is the same for each. The action may be applied to either the current or original version of each row, or both, depending on the current row state.

The following table displays behavior for the Load method. The final row (labeled "(Not present)") describes the behavior for incoming rows that don't match any existing row. Each cell in this table describes the current and original value for a field within a row, along with the DataRowState for the value after the Load method has completed. In this case, the method doesn't allow you to indicate the load option, and uses the default, PreserveChanges.

Existing DataRowState

Values after Load method, and event action


Current = <Existing>

Original = <Incoming>

State = <Modified>

RowAction = ChangeOriginal


Current = <Existing>

Original = <Incoming>

State = <Modified>

RowAction = ChangeOriginal


Current = <Not available>

Original = <Incoming>

State = <Deleted>

RowAction = ChangeOriginal


Current = <Incoming>

Original = <Incoming>

State = <Unchanged>

RowAction = ChangeCurrentAndOriginal

(Not present)

Current = <Incoming>

Original = <Incoming>

State = <Unchanged>

RowAction = ChangeCurrentAndOriginal

Values in a DataColumn can be constrained through use of properties such as ReadOnly and AutoIncrement. The Load method handles such columns in a manner that is consistent with the behavior defined by the column's properties. The read only constraint on a DataColumn is applicable only for changes that occur in memory. The Load method's overwrites the read-only column values, if needed.

To determine which version of the primary key field to use for comparing the current row with an incoming row, the Load method uses the original version of the primary key value within a row, if it exists. Otherwise, the Load method uses the current version of the primary key field.

The following example demonstrates several of the issues involved with calling the Load method. First, the example focuses on schema issues, including inferring a schema from the loaded IDataReader, and then handling incompatible schemas, and schemas with missing or additional columns. The example then calls the Load method, displaying the data both before and after the load operation.

No code example is currently available or this language may not be supported.

.NET Framework

Supported in: 4.6, 4.5, 4, 3.5, 3.0, 2.0

.NET Framework Client Profile

Supported in: 4, 3.5 SP1

XNA Framework

Supported in: 3.0, 2.0, 1.0
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
© 2015 Microsoft