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

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.

Requirements

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]
   GO
   
   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]
   GO
   					

2. In SQL Server, create a stored procedure with the following code:

   CREATE PROC sp_UpdateXML @empdata nText
   AS 
    DECLARE @hDoc int   
    exec sp_xml_preparedocument @hDoc OUTPUT,@empdata   
   
   --This code updates old data.
    UPDATE Employee 
    SET 
      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
   GO
   					

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>
           [STAThread]
           static void Main(string[] args)
           {
               try
               {
                   BulkInsertUpdate();
                       System.Console.WriteLine("Successfully inserted and updated data");
                       System.Console.Read();
               }
               catch (System.Data.SqlClient.SqlException e)
               {
                   System.Diagnostics.Debug.WriteLine (e.Message); 
                   System.Console.WriteLine(e.Message);
               }
           }
           static void BulkInsertUpdate()
           {
               //Steps:
               //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.
   			
               //Declaration
               System.Data.DataSet         objDS;
               SqlConnection               objCon;
               SqlCommand                  objCom1;
               SqlDataAdapter              objAdpt1;
               String                      sConn;
               sConn = "user id=myUser;password=YourPassword;" + 
                       "Database=YourDatabase;Server=YourServer";
               objDS = new DataSet();
               objCon = new SqlConnection(sConn);
               objCon.Open();
               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)
               {
                   i++;
                   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", 
                      System.Data.SqlDbType.NText));
               objCom.Parameters[0].Value = sb.ToString();;
               objCom.ExecuteNonQuery();
           }
   
           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");
               objAdpt1.Fill(objDS);				
           }
       }
   }
   					

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: