HOW TO: Perform Bulk Updates and Inserts Using OpenXML with .NET Providers in Visual C# .NET

For a Microsoft Visual C++ .NET version of this article, see 316245 .

This article refers to the following Microsoft .NET Framework Class Library namespaces:

  • System.Data
  • System.Data.SqlClient



This step-by-step article describes how to do bulk inserts and updates with different Microsoft .NET data providers by using the OpenXML method. Although the sample in this article uses the SqlClient managed provider, you can also use the OLEDB or the ODBC managed provider.


The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:
  • Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, or Microsoft Windows NT 4.0 Server
  • Microsoft Visual Studio .NET
  • Microsoft SQL Server 2000

Create the Project

  1. In SQL Server, create a table with the following code:
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employee]') 
    and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Employee]

    CREATE TABLE [dbo].[Employee] (
    [EmployeeId] [int] NOT NULL ,
    [FirstName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [LastName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
  2. In SQL Server, create a stored procedure with the following code:
    CREATE PROC sp_UpdateXML @empdata nText
    DECLARE @hDoc int
    exec sp_xml_preparedocument @hDoc OUTPUT,@empdata

    --This code updates old data.
    UPDATE Employee
    Employee.FirstName = XMLEmployee.FirstName,
    Employee.LastName = XMLEmployee.LastName
    FROM OPENXML(@hDoc, 'NewDataSet/Employee')
    WITH (EmployeeId Integer, FirstName varchar(100), LastName varchar(100)) XMLEmployee
    WHERE Employee.EmployeeId = XMLEmployee.EmployeeId

    --This code inserts new data.

    Insert Into Employee
    SELECT EmployeeId, FirstName, LastName
    FROM OPENXML (@hdoc, '/NewDataSet/Employee',1)
    WITH (EmployeeId Integer, FirstName varchar(100), LastName varchar(100)) XMLEmployee
    Where XMLEmployee.EmployeeId Not IN (Select EmployeeID from Employee)

    EXEC sp_xml_removedocument @hDoc
  3. Start Visual Studio .NET, and then create a new Console Application project in Visual C# .NET.
  4. Copy and paste the following code into Class1 of the console application:
    using System;
    using System.Data.SqlClient;
    using System.Data;

    namespace ConsoleApplication1
    /// <summary>
    /// Summary description for Class1
    /// </summary>
    class Class1
    /// <summary>
    /// Main entry point for the application
    /// </summary>
    static void Main(string[] args)
    System.Console.WriteLine("Successfully inserted and updated data");
    catch (System.Data.SqlClient.SqlException e)
    System.Diagnostics.Debug.WriteLine (e.Message);
    static void BulkInsertUpdate()
    //1. Create the dataset.
    //2. Update the dataset.
    //3. Insert some data.
    //4. Save the changed data as XML
    // and send XML to SQL Server through the stored procedure.

    System.Data.DataSet objDS;
    SqlConnection objCon;
    SqlCommand objCom1;
    SqlDataAdapter objAdpt1;
    String sConn;
    sConn = "user id=myUser;password=YourPassword;" +
    objDS = new DataSet();
    objCon = new SqlConnection(sConn);
    objCom1 = new SqlCommand();
    objCom1.Connection = objCon;
    objAdpt1 = new SqlDataAdapter();

    //Step 1: Create the dataset.
    CreateDataSetFromEmployee(objDS, objCom1,objAdpt1);

    //Step 2: Update the dataset.
    System.Data.DataTable tbl = objDS.Tables["Employee"];
    //DataRow aRow;
    int i = 0;
    foreach (DataRow aRow in tbl.Rows)
    aRow["FirstName"] = aRow["FirstName"].ToString() + i;
    aRow["LastName"] = aRow["LastName"].ToString() + i;

    //Step 3: Insert some data.
    for( int ii = 1; ii <= 5; ii++)
    DataRow newRow = tbl.NewRow();
    int j = ii+100;
    newRow["EmployeeId"] = j;
    newRow["FirstName"] = "Fname" + j;
    newRow["LastName"] = "LName" + j;
    tbl.Rows.Add( newRow);

    //Step 4: Save the changed data as XML,
    //and send the XML to SQL Server through the stored procedure.
    //In SQL Server, you wrote a stored procedure that
    //accepts this XML and updates the corresponding table.

    SaveThroughXML(objDS, objCon);

    static void SaveThroughXML(DataSet objDS, SqlConnection objCon)
    //Change the column mapping first.
    DataTable tbl = objDS.Tables["Employee"];
    System.Text.StringBuilder sb = new System.Text.StringBuilder( 1000);
    System.IO.StringWriter sw = new System.IO.StringWriter(sb);

    foreach( DataColumn col in tbl.Columns)
    col.ColumnMapping = System.Data.MappingType.Attribute;

    objDS.WriteXml(sw, System.Data.XmlWriteMode.WriteSchema);

    SqlCommand objCom = new SqlCommand();
    objCom.Connection = objCon;
    objCom.CommandType = CommandType.StoredProcedure;
    objCom.CommandText = "sp_UpdateXML";

    objCom.Parameters.Add( new SqlParameter( "@empdata",
    objCom.Parameters[0].Value = sb.ToString();;

    static void CreateDataSetFromEmployee( DataSet objDS,
    SqlCommand objCom1,SqlDataAdapter objAdpt1)

    //Create related objects.
    objCom1.CommandType = CommandType.Text;
    objCom1.CommandText = "Select EmployeeId, FirstName,LastName from Employee";

    //Fill the Orders table.
    objAdpt1.SelectCommand = objCom1;
    objAdpt1.TableMappings.Add("Table", "Employee");
  5. Modify the connection string as appropriate for your environment.
  6. Press the F5 key to build and to run the application.
  7. Press the ENTER key to close the Console window when the application stops running.
NOTE: This example does not include any error handling.


For additional information about using .NET Managed Providers, click the article number below to view the article in the Microsoft Knowledge Base:

313480 INFO: Roadmap for .NET Data Providers

ID articolo: 315968 - Ultima revisione: 23 mar 2009 - Revisione: 1