Information
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

DataTable.Load Method (IDataReader, LoadOption)

Note: This method is new in the .NET Framework version 2.0.

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 according to the value of the loadOption parameter.

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

public void Load (
	IDataReader reader,
	LoadOption loadOption
)
public void Load (
	IDataReader reader, 
	LoadOption loadOption
)
public function Load (
	reader : IDataReader, 
	loadOption : LoadOption
)

Parameters

reader

A IDataReader that provides one or more result sets.

loadOption

A value from the LoadOption enumeration that indicates how rows already in the DataTable are combined with incoming rows that share the same primary key.

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

Condition

Behavior

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 don't exist in the DataTable.

The Load method adds the extra column(s) 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 null value for the missing column. If no default value or null can be used, then the Load method throws an exception. If no specific default value has been supplied, the Load method uses the null 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. See Row States and Row Versions for more information.

In this method call, the specified LoadOption parameter influences the processing of the incoming data. How should the Load method handle loading rows that have the same primary key as existing rows? Should it modify current values, original values, or both? These issues, and more, are controlled by the loadOption parameter.

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 varies, depending 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 when called with each of the LoadOption values, and also shows how the values interact with the row state for the row being loaded. 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.

Existing DataRowState

Upsert

OverwriteChanges

PreserveChanges (Default behavior)

Added

Current = <Incoming>

Original = -<Not available>

State = <Added>

RowAction = Change

Current = <Incoming>

Original = <Incoming>

State = <Unchanged>

RowAction = ChangeCurrentAndOriginal

Current = <Existing>

Original = <Incoming>

State = <Modified>

RowAction = ChangeOriginal

Modified

Current = <Incoming>

Original = <Existing>

State = <Modified>

RowAction = Change

Current = <Incoming>

Original = <Incoming>

State = <Unchanged>

RowAction = ChangeCurrentAndOriginal

Current = <Existing>

Original = <Incoming>

State = <Modified>

RowAction =ChangeOriginal

Deleted

(Load does not affect deleted rows)

Current = ---

Original = <Existing>

State = <Deleted>

(New row is added with the following characteristics)

Current = <Incoming>

Original = <Not available>

State = <Added>

RowAction = Add

Undo delete and

Current = <Incoming>

Original = <Incoming>

State = <Unchanged>

RowAction = ChangeCurrentAndOriginal

Current = <Not available>

Original = <Incoming>

State = <Deleted>

RowAction = ChangeOriginal

Unchanged

Current = <Incoming>

Original = <Existing>

If new value is the same as the existing value then

State = <Unchanged>

RowAction = Nothing

Else

State = <Modified>

RowAction = Change

Current = <Incoming>

Original = <Incoming>

State = <Unchanged>

RowAction = ChangeCurrentAndOriginal

Current = <Incoming>

Original = <Incoming>

State = <Unchanged>

RowAction = ChangeCurrentAndOriginal

(Not present)

Current = <Incoming>

Original = <Not available>

State = <Added>

RowAction = Add

Current = <Incoming>

Original = <Incoming>

State = <Unchanged>

RowAction = ChangeCurrentAndOriginal

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.

If you specify the OverwriteChanges or PreserveChanges options when calling the Load method, then the assumption is made that the incoming data is coming from the DataTable's primary data source, and the DataTable tracks changes and can propagate the changes back to the data source. If you select the Upsert option, it is assumed that the data is coming from one of a secondary data source, such as data provided by a middle-tier component, perhaps altered by a user. In this case, the assumption is that the intent is to aggregate data from one or more data sources in the DataTable, and then perhaps propagate the data back to the primary data source. The LoadOption parameter is used for determining the specific version of the row that is to be used for primary key comparison. The table below provides the details.

Load option

DataRow version used for primary key comparison

OverwriteChanges

Original version, if it exists, otherwise Current version

PreserveChanges

Original version, if it exists, otherwise Current version

Upsert

Current version, if it exists, otherwise Original version

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 focuses on data issues, including handling the various loading options.

static void Main()
{
    // This example examines a number of scenarios involving the 
    // DataTable.Load method.
    Console.WriteLine("Load a DataTable and infer its schema:");

    // The table has no schema. The Load method will infer the 
    // schema from the IDataReader:
    DataTable table = new DataTable();

    // Retrieve a data reader, based on the Customers data. In
    // an application, this data might be coming from a middle-tier
    // business object:
    DataTableReader reader = new DataTableReader(GetCustomers());

    table.Load(reader);
    PrintColumns(table);

    Console.WriteLine(" ============================= ");
    Console.WriteLine(
        "Load a DataTable from an incompatible IDataReader:");

    // Create a table with a single integer column. Attempt
    // to load data from a reader with a schema that is 
    // incompatible. Note the exception, determined
    // by the particular incompatibility:
    table = GetIntegerTable();
    reader = new DataTableReader(GetStringTable());
    try
    {
        table.Load(reader);
    
    catch (Exception ex)
    {
        Console.WriteLine(ex.GetType().Name + ":" + ex.Message);
    

    Console.WriteLine(" ============================= ");
    Console.WriteLine(
        "Load a DataTable with an IDataReader that has extra columns:");

    // Note that loading a reader with extra columns adds
    // the columns to the existing table, if possible:
    table = GetIntegerTable();
    reader = new DataTableReader(GetCustomers());
    table.Load(reader);
    PrintColumns(table);

    Console.WriteLine(" ============================= ");
    Console.WriteLine(
        "Load a DataTable with an IDataReader that has missing columns:");

    // Note that loading a reader with missing columns causes 
    // the columns to be filled with null data, if possible:
    table = GetCustomers();
    reader = new DataTableReader(GetIntegerTable());
    table.Load(reader);
    PrintColumns(table);

    // Demonstrate the various possibilites when loading data into
    // a DataTable that already contains data.
    Console.WriteLine(" ============================= ");
    Console.WriteLine("Demonstrate data considerations:");
    Console.WriteLine("Current value, Original value, (RowState)");
    Console.WriteLine(" ============================= ");
    Console.WriteLine("Original table:");

    table = SetupModifiedRows();
    DisplayRowState(table);

    Console.WriteLine(" ============================= ");
    Console.WriteLine("Data in IDataReader to be loaded:");
    DisplayRowState(GetChangedCustomers());

    PerformDemo(LoadOption.OverwriteChanges);
    PerformDemo(LoadOption.PreserveChanges);
    PerformDemo(LoadOption.Upsert);

    Console.WriteLine("Press any key to continue.");
    Console.ReadKey();


private static void DisplayRowState(DataTable table)
{
    for (int i = 0; i <= table.Rows.Count - 1; i++)
    {
        object current = "--";
        object original = "--";
        DataRowState rowState = table.Rows[i].RowState;

        // Attempt to retrieve the current value, which doesn't exist
        // for deleted rows:
        if (rowState != DataRowState.Deleted)
        {
            current = table.Rows[i]["Name", DataRowVersion.Current];
        

        // Attempt to retrieve the original value, which doesn't exist
        // for added rows:
        if (rowState != DataRowState.Added)
        {
            original = table.Rows[i]["Name", DataRowVersion.Original];
        
        Console.WriteLine("{0: {1, {2 ({3)", i, 
            current, original, rowState);
    


private static DataTable GetChangedCustomers()
{
    // Create sample Customers table.
    DataTable table = new DataTable();

    // Create two columns, ID and Name.
    DataColumn idColumn = table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Name", typeof(string));

    // Set the ID column as the primary key column.
    table.PrimaryKey = new DataColumn[] { idColumn ;

    table.Rows.Add(new object[] { 0, "XXX" );
    table.Rows.Add(new object[] { 1, "XXX" );
    table.Rows.Add(new object[] { 2, "XXX" );
    table.Rows.Add(new object[] { 3, "XXX" );
    table.Rows.Add(new object[] { 4, "XXX" );
    table.AcceptChanges();
    return table;


private static DataTable GetCustomers()
{
    // Create sample Customers table, in order
    // to demonstrate the behavior of the DataTableReader.
    DataTable table = new DataTable();

    // Create two columns, ID and Name.
    DataColumn idColumn = table.Columns.Add("ID", typeof(int));
    table.Columns.Add("Name", typeof(string));

    // Set the ID column as the primary key column.
    table.PrimaryKey = new DataColumn[] { idColumn ;

    table.Rows.Add(new object[] { 0, "Mary" );
    table.Rows.Add(new object[] { 1, "Andy" );
    table.Rows.Add(new object[] { 2, "Peter" );
    table.AcceptChanges();
    return table;


private static DataTable GetIntegerTable()
{
    // Create sample Customers table, in order
    // to demonstrate the behavior of the DataTableReader.
    DataTable table = new DataTable();

    // Create two columns, ID and Name.
    DataColumn idColumn = table.Columns.Add("ID", typeof(int));

    // Set the ID column as the primary key column.
    table.PrimaryKey = new DataColumn[] { idColumn ;

    table.Rows.Add(new object[] { 4 );
    table.Rows.Add(new object[] { 5 );
    table.AcceptChanges();
    return table;


private static DataTable GetStringTable()
{
    // Create sample Customers table, in order
    // to demonstrate the behavior of the DataTableReader.
    DataTable table = new DataTable();

    // Create two columns, ID and Name.
    DataColumn idColumn = table.Columns.Add("ID", typeof(string));

    // Set the ID column as the primary key column.
    table.PrimaryKey = new DataColumn[] { idColumn ;

    table.Rows.Add(new object[] { "Mary" );
    table.Rows.Add(new object[] { "Andy" );
    table.Rows.Add(new object[] { "Peter" );
    table.AcceptChanges();
    return table;


private static void PerformDemo(LoadOption optionForLoad)
{

    // Load data into a DataTable, retrieve a DataTableReader containing
    // different data, and call the Load method. Depending on the
    // LoadOption value passed as a parameter, this procedure displays
    // different results in the DataTable.
    Console.WriteLine(" ============================= ");
    Console.WriteLine("table.Load(reader, {0)", optionForLoad);
    Console.WriteLine(" ============================= ");

    DataTable table = SetupModifiedRows();
    DataTableReader reader = new DataTableReader(GetChangedCustomers());
    table.RowChanging +=new DataRowChangeEventHandler(HandleRowChanging);

    table.Load(reader, optionForLoad);
    Console.WriteLine();
    DisplayRowState(table);


private static void PrintColumns(DataTable table)
{
    // Loop through all the rows in the DataTableReader
    foreach (DataRow row in table.Rows)
    {
        for (int i = 0; i < table.Columns.Count; i++)
        {
            Console.Write(row[i] + " ");
        
        Console.WriteLine();
    


private static DataTable SetupModifiedRows()
{
    // Fill a DataTable with customer info, and 
    // then modify, delete, and add rows.

    DataTable table = GetCustomers();
    // Row 0 is unmodified.
    // Row 1 is modified.
    // Row 2 is deleted.
    // Row 3 is added.
    table.Rows[1]["Name"] = "Sydney";
    table.Rows[2].Delete();
    DataRow row = table.NewRow();
    row["ID"] = 3;
    row["Name"] = "Melony";
    table.Rows.Add(row);

    // Note that the code doesn't call
    // table.AcceptChanges()
    return table;


static void HandleRowChanging(object sender, DataRowChangeEventArgs e)
{
    Console.WriteLine(
        "RowChanging event: ID = {0, action = {1", e.Row["ID"], e.Action);


Windows 98, Windows 2000 SP4, Windows CE, Windows Millennium Edition, Windows Mobile for Pocket PC, Windows Mobile for Smartphone, Windows Server 2003, Windows XP Media Center Edition, Windows XP Professional x64 Edition, Windows XP SP2, Windows XP Starter Edition

The .NET Framework does not support all versions of every platform. For a list of the supported versions, see System Requirements.

.NET Framework

Supported in: 2.0

.NET Compact Framework

Supported in: 2.0
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

Show:
© 2014 Microsoft