C# DataSet.Merge方法代码示例

private void DemonstrateMerge() 
{
    // Create a DataSet with one table, two columns, and three rows.
    DataSet dataSet = new DataSet("dataSet");
    DataTable table = new DataTable("Items");
    DataColumn idColumn = new DataColumn("id", 
        Type.GetType("System.Int32"));
    idColumn.AutoIncrement=true;
    DataColumn itemColumn = new DataColumn("Item", 
        Type.GetType("System.Int32"));

    // DataColumn array to set primary key.
    DataColumn[] keyColumn= new DataColumn[1];
    DataRow row;

    // Create variable for temporary DataSet. 
    DataSet changeDataSet;

    // Add columns to table, and table to DataSet.   
    table.Columns.Add(idColumn);
    table.Columns.Add(itemColumn);
    dataSet.Tables.Add(table);

    // Set primary key column.
    keyColumn[0]= idColumn;
    table.PrimaryKey=keyColumn;

    // Add ten rows.
    for(int i = 0; i <10;i++)
    {
        row=table.NewRow();
        row["Item"]= i;
        table.Rows.Add(row);
    }

    // Accept changes.
    dataSet.AcceptChanges();
    PrintValues(dataSet, "Original values");

    // Change two row values.
    table.Rows[0]["Item"]= 50;
    table.Rows[1]["Item"]= 111;

    // Add one row.
    row=table.NewRow();
    row["Item"]=74;
    table.Rows.Add(row);

    // Insert code for error checking. Set one row in error.
    table.Rows[1].RowError= "over 100";
    PrintValues(dataSet, "Modified and New Values");
    // If the table has changes or errors, create a subset DataSet.
    if(dataSet.HasChanges(DataRowState.Modified | 
        DataRowState.Added)& dataSet.HasErrors)
    {
        // Use GetChanges to extract subset.
        changeDataSet = dataSet.GetChanges(
            DataRowState.Modified|DataRowState.Added);
        PrintValues(changeDataSet, "Subset values");
        // Insert code to reconcile errors. In this case reject changes.
        foreach(DataTable changeTable in changeDataSet.Tables)
        {
            if (changeTable.HasErrors)
            {
                foreach(DataRow changeRow in changeTable.Rows)
                {
                    //Console.WriteLine(changeRow["Item"]);
                    if((int)changeRow["Item",
                        DataRowVersion.Current ]> 100)
                    {
                        changeRow.RejectChanges();
                        changeRow.ClearErrors();
                    }
                }
            }
        }
        PrintValues(changeDataSet, "Reconciled subset values");
        // Merge changes back to first DataSet.
        dataSet.Merge(changeDataSet);
        PrintValues(dataSet, "Merged Values");
    }
}

private void PrintValues(DataSet dataSet, string label)
{
    Console.WriteLine("\n" + label);
    foreach(DataTable table in dataSet.Tables)
    {
        Console.WriteLine("TableName: " + table.TableName);
        foreach(DataRow row in table.Rows)
        {
            foreach(DataColumn column in table.Columns)
            {
                Console.Write("\table " + row[column] );
            }
            Console.WriteLine();
        }
    }
}

private void DemonstrateMergeTable()
   {
       // Create a DataSet with one table, two columns, and ten rows.
       DataSet dataSet = new DataSet("dataSet");
       DataTable table = new DataTable("Items");

       // Add table to the DataSet
       dataSet.Tables.Add(table);

       // Add columns
       DataColumn c1 = new DataColumn("id", 
           Type.GetType("System.Int32"),"");
       DataColumn c2 = new DataColumn("Item", 
           Type.GetType("System.Int32"),"");
       table.Columns.Add(c1);
       table.Columns.Add(c2);

       // DataColumn array to set primary key.
       DataColumn[] keyCol= new DataColumn[1];

       // Set primary key column.
       keyCol[0]= c1;
       table.PrimaryKey=keyCol;

       // Add a RowChanged event handler for the table.
       table.RowChanged += new 
           DataRowChangeEventHandler(Row_Changed);

       // Add ten rows.
       for(int i = 0; i <10;i++)
       {
           DataRow row=table.NewRow();
           row["id"] = i;
           row["Item"]= i;
           table.Rows.Add(row);
       }
       // Accept changes.
       dataSet.AcceptChanges();

       PrintValues(dataSet, "Original values");

       // Create a second DataTable identical to the first.
       DataTable t2 = table.Clone();

       // Add three rows. Note that the id column can'te be the
       // same as existing rows in the DataSet table.
       DataRow newRow;
       newRow = t2.NewRow();
       newRow["id"] = 14;
       newRow["item"] = 774;

       //Note the alternative method for adding rows.
       t2.Rows.Add(new Object[] { 12, 555 });
       t2.Rows.Add(new Object[] { 13, 665 });

       // Merge the table into the DataSet
       Console.WriteLine("Merging");
       dataSet.Merge(t2);
       PrintValues(dataSet, "Merged With table.");
   }

   private void Row_Changed(object sender, 
       DataRowChangeEventArgs e)
   {
       Console.WriteLine("Row Changed " + e.Action.ToString() 
           + "\table" + e.Row.ItemArray[0]);
   }

   private void PrintValues(DataSet dataSet, string label)
   {
       Console.WriteLine("\n" + label);
       foreach(DataTable table in dataSet.Tables)
       {
           Console.WriteLine("TableName: " + table.TableName);
           foreach(DataRow row in table.Rows)
           {
               foreach(DataColumn column in table.Columns)
               {
                   Console.Write("\table " + row[column] );
               }
               Console.WriteLine();
           }
       }
   }

private void DemonstrateMerge()
   {
       // Create a DataSet with one table, two columns, 
       // and three rows.
       DataSet dataSet = new DataSet("dataSet");
       DataTable table = new DataTable("Items");
       DataColumn idColumn = new DataColumn("id", 
           Type.GetType("System.Int32"),"");
       idColumn.AutoIncrement=true;
       DataColumn itemColumn = new DataColumn("Item", 
           Type.GetType("System.Int32"),"");

       // DataColumn array to set primary key.
       DataColumn[] keyColumn= new DataColumn[1];
       DataRow row;

       // Create variable for temporary DataSet. 
       DataSet changesDataSet;

       // Add RowChanged event handler for the table.
       table.RowChanged+=new DataRowChangeEventHandler(
           Row_Changed);
       dataSet.Tables.Add(table);
       table.Columns.Add(idColumn);
       table.Columns.Add(itemColumn);

       // Set primary key column.
       keyColumn[0]= idColumn;
       table.PrimaryKey=keyColumn;
       // Add ten rows.
       for(int i = 0; i <10;i++)
       {
           row=table.NewRow();
           row["Item"]= i;
           table.Rows.Add(row);
       }
       // Accept changes.
       dataSet.AcceptChanges();
       PrintValues(dataSet, "Original values");

       // Change row values.
       table.Rows[0]["Item"]= 50;
       table.Rows[1]["Item"]= 111;

       // Add one row.
       row=table.NewRow();
       row["Item"]=74;
       table.Rows.Add(row);

       // Insert code for error checking. Set one row in error.
       table.Rows[1].RowError= "over 100";
       PrintValues(dataSet, "Modified and New Values");

       // If the table has changes or errors, 
       // create a subset DataSet.
       if(dataSet.HasChanges(DataRowState.Modified | 
           DataRowState.Added)&& dataSet.HasErrors)
       {
           // Use GetChanges to extract subset.
           changesDataSet = dataSet.GetChanges(
               DataRowState.Modified|DataRowState.Added);
           PrintValues(changesDataSet, "Subset values");

           // Insert code to reconcile errors. In this case, reject changes.
           foreach(DataTable changesTable in changesDataSet.Tables)
           {
               if (changesTable.HasErrors)
               {
                   foreach(DataRow changesRow in changesTable.Rows)
                   {
                       //Console.WriteLine(changesRow["Item"]);
                       if((int)changesRow["Item",DataRowVersion.Current ]> 100)
                       {
                           changesRow.RejectChanges();
                           changesRow.ClearErrors();
                       }
                   }
               }
           }
           // Add a column to the changesDataSet.
           changesDataSet.Tables["Items"].Columns.Add(
               new DataColumn("newColumn"));
           PrintValues(changesDataSet, "Reconciled subset values");
           // Merge changes back to first DataSet.
           dataSet.Merge(changesDataSet, false, 
               System.Data.MissingSchemaAction.Add);
       }
       PrintValues(dataSet, "Merged Values");
   }

   private void Row_Changed(object sender, DataRowChangeEventArgs e)
   {
       Console.WriteLine("Row Changed " + e.Action.ToString() 
           + "\table" + e.Row.ItemArray[0]);
   }

   private void PrintValues(DataSet dataSet, string label)
   {
       Console.WriteLine(label + "\n");
       foreach(DataTable table in dataSet.Tables)
       {
           Console.WriteLine("TableName: " + table.TableName);
           foreach(DataRow row in table.Rows)
           {
               foreach(DataColumn column in table.Columns)
               {
                   Console.Write("\table " + row[column] );
               }
               Console.WriteLine();
           }
       }
   }

private void DemonstrateMergeMissingSchema()
   {
       // Create a DataSet with one table, two columns, 
       // and three rows.
       DataSet dataSet = new DataSet("dataSet");
       DataTable table = new DataTable("Items");
       DataColumn idColumn = new DataColumn("id", 
           Type.GetType("System.Int32"));
       idColumn.AutoIncrement=true;
       DataColumn itemColumn = new DataColumn("Item", 
           Type.GetType("System.Int32"));
       // DataColumn array to set primary key.

       DataColumn[] keyColumn= new DataColumn[1];
       DataRow row;
       // Create variable for temporary DataSet. 
       DataSet changeDataSet;

       // Add RowChanged event handler for the table.
       table.RowChanged+= new DataRowChangeEventHandler(
           Row_Changed);
       dataSet.Tables.Add(table);
       table.Columns.Add(idColumn);
       table.Columns.Add(itemColumn);

       // Set primary key column.
       keyColumn[0]= idColumn;
       table.PrimaryKey=keyColumn;

       // Add ten rows.
       for(int i = 0; i <10;i++)
       {
           row=table.NewRow();
           row["Item"]= i;
           table.Rows.Add(row);
       }

       // Accept changes.
       dataSet.AcceptChanges();
       PrintValues(dataSet, "Original values");

       // Change row values.
       table.Rows[0]["Item"]= 50;
       table.Rows[1]["Item"]= 111;

       // Add one row.
       row=table.NewRow();
       row["Item"]=74;
       table.Rows.Add(row);

       // Insert code for error checking. Set one row in error.
       table.Rows[1].RowError= "over 100";
       PrintValues(dataSet, "Modified and New Values");
       // If the table has changes or errors, create a subset DataSet.
       if(dataSet.HasChanges(DataRowState.Modified | 
           DataRowState.Added)& dataSet.HasErrors)
       {
           // Use GetChanges to extract subset.
           changeDataSet = dataSet.GetChanges(
               DataRowState.Modified|DataRowState.Added);
           PrintValues(changeDataSet, "Subset values");

           // Insert code to reconcile errors. Reject the changes.
           foreach(DataTable changeTable in changeDataSet.Tables)
           {
               if (changeTable.HasErrors)
               {
                   foreach(DataRow changeRow in changeTable.Rows)
                   {
                       //Console.WriteLine(changeRow["Item"]);
                       if((int)changeRow["Item", 
                           DataRowVersion.Current ]> 100)
                       {
                           changeRow.RejectChanges();
                           changeRow.ClearErrors();
                       }
                   }
               }
           }
           // Add a column to the changeDataSet to change the schema.
           changeDataSet.Tables["Items"].Columns.Add(
               new DataColumn("newColumn"));
           PrintValues(changeDataSet, "Reconciled subset values");

           // Add values to the rows for each column.
           foreach(DataRow rowItem in changeDataSet.Tables["Items"].Rows)
           {
               rowItem["newColumn"] = "my new schema value";
           }
           // Merge changes back to first DataSet.
           dataSet.Merge(changeDataSet, false,
               System.Data.MissingSchemaAction.Add);
       }
       PrintValues(dataSet, "Merged Values");
   }

   private void Row_Changed(object sender, DataRowChangeEventArgs e)
   {
       Console.WriteLine("Row Changed " + e.Action.ToString() 
           + "\table" + e.Row.ItemArray[0]);
   }

   private void PrintValues(DataSet dataSet, string label)
   {
       Console.WriteLine("\n" + label);
       foreach(DataTable table in dataSet.Tables)
       {
           Console.WriteLine("TableName: " + table.TableName);
           foreach(DataRow row in table.Rows)
           {
               foreach(DataColumn column in table.Columns)
               {
                   Console.Write("\table " + row[column] );
               }
               Console.WriteLine();
           }
       }
   }

private void DemonstrateMergeTableAddSchema()
   {
       // Create a DataSet with one table, two columns, and ten rows.
       DataSet dataSet = new DataSet("dataSet");
       DataTable table = new DataTable("Items");

       // Add table to the DataSet
       dataSet.Tables.Add(table);

       // Create and add two columns to the DataTable
       DataColumn idColumn = new DataColumn("id", 
           Type.GetType("System.Int32"),"");
       idColumn.AutoIncrement=true;
       DataColumn itemColumn = new DataColumn("Item", 
           Type.GetType("System.Int32"),"");
       table.Columns.Add(idColumn);
       table.Columns.Add(itemColumn);

       // Set the primary key to the first column.
       table.PrimaryKey = new DataColumn[1]{ idColumn };

       // Add RowChanged event handler for the table.
       table.RowChanged+= new DataRowChangeEventHandler(Row_Changed);

       // Add ten rows.
       for(int i = 0; i <10;i++)
       {
           DataRow row=table.NewRow();
           row["Item"]= i;
           table.Rows.Add(row);
       }

       // Accept changes.
       dataSet.AcceptChanges();
       PrintValues(dataSet, "Original values");

       // Create a second DataTable identical to the first, with
       // one extra column using the Clone method.
       DataTable cloneTable = table.Clone();
       cloneTable.Columns.Add("extra", typeof(string));

       // Add two rows. Note that the id column can'table be the 
       // same as existing rows in the DataSet table.
       DataRow newRow;
       newRow=cloneTable.NewRow();
       newRow["id"]= 12;
       newRow["Item"]=555;
       newRow["extra"]= "extra Column 1";
       cloneTable.Rows.Add(newRow);

       newRow=cloneTable.NewRow();
       newRow["id"]= 13;
       newRow["Item"]=665;
       newRow["extra"]= "extra Column 2";
       cloneTable.Rows.Add(newRow);

       // Merge the table into the DataSet.
       Console.WriteLine("Merging");
       dataSet.Merge(cloneTable,false,MissingSchemaAction.Add);
       PrintValues(dataSet, "Merged With Table, Schema Added");
   }

   private void Row_Changed(object sender, 
       DataRowChangeEventArgs e)
   {
       Console.WriteLine("Row Changed " + e.Action.ToString() 
           + "\table" + e.Row.ItemArray[0]);
   }

   private void PrintValues(DataSet dataSet, string label)
   {
       Console.WriteLine("\n" + label);
       foreach(DataTable table in dataSet.Tables)
       {
           Console.WriteLine("TableName: " + table.TableName);
           foreach(DataRow row in table.Rows)
           {
               foreach(DataColumn column in table.Columns)
               {
                   Console.Write("\table " + row[column] );
               }
               Console.WriteLine();
           }
       }
   }

//引入命名空间
using System;
using System.Data;
using System.Data.SqlClient;

class Merge
{
  public static void Main()
  {
    SqlConnection mySqlConnection =new SqlConnection("server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI;");

    SqlCommand mySqlCommand = mySqlConnection.CreateCommand();

    mySqlCommand.CommandText =
      "SELECT ID, FirstName, LastName, Address " +
      "FROM Customers " +
      "WHERE ID IN ('001', '002', '003')";
    SqlDataAdapter mySqlDataAdapter = new SqlDataAdapter();
    mySqlDataAdapter.SelectCommand = mySqlCommand;
    DataSet myDataSet = new DataSet();
    mySqlConnection.Open();
    mySqlDataAdapter.Fill(myDataSet, "Customers");

    mySqlCommand.CommandText =
      "SELECT ID, FirstName, LastName, Address " +
      "FROM Customers " +
      "WHERE CustomerID IN ('008', '009')";
    DataSet myDataSet2 = new DataSet();
    mySqlDataAdapter.Fill(myDataSet2, "Customers2");

    mySqlCommand.CommandText =
      "SELECT TOP 5 ProductID, ProductName, UnitPrice " +
      "FROM Products " +
      "ORDER BY ProductID";
    DataSet myDataSet3 = new DataSet();
    mySqlDataAdapter.Fill(myDataSet3, "Products");

    mySqlConnection.Close();

    myDataSet.Merge(myDataSet2);

    myDataSet.Merge(myDataSet3, true, MissingSchemaAction.Add);

    foreach (DataTable myDataTable in myDataSet.Tables) {
      Console.WriteLine("\nReading from the " + myDataTable + "DataTable");
      foreach (DataRow myDataRow in myDataTable.Rows) {
        foreach (DataColumn myDataColumn in myDataTable.Columns) {
          Console.WriteLine(myDataColumn + "= " + myDataRow[myDataColumn]);
        }
      }
    }
  }
}