Microsoft KB Archive/315968

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

Article ID: 315968

Article Last Modified on 12/26/2003

-

APPLIES TO


 * Microsoft ADO.NET 1.1
 * Microsoft ADO.NET 1.0
 * Microsoft Visual C# .NET 2003 Standard Edition
 * Microsoft Visual C# .NET 2002 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 64-bit Edition

-



This article was previously published under Q315968



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

IN THIS TASK
SUMMARY
 * Requirements
 * Create the Project

REFERENCES



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

back to the top

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

back to the top

Create the Project
  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   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  Start Visual Studio .NET, and then create a new Console Application project in Visual C# .NET.  Copy and paste the following code into Class1 of the console application: using System; using System.Data.SqlClient; using System.Data;

namespace ConsoleApplication1 {   ///     /// Summary description for Class1 ///    class Class1 {       ///         /// Main entry point for the application ///        [STAThread] static void Main(string[] args) {           try {               BulkInsertUpdate; System.Console.WriteLine(&quot;Successfully inserted and updated data&quot;); 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 = &quot;user id=myUser;password=YourPassword;&quot; + &quot;Database=YourDatabase;Server=YourServer&quot;; 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[&quot;Employee&quot;]; //DataRow aRow; int i = 0; foreach (DataRow aRow in tbl.Rows) {               i++; aRow[&quot;FirstName&quot;] = aRow[&quot;FirstName&quot;].ToString + i;               aRow[&quot;LastName&quot;] = aRow[&quot;LastName&quot;].ToString + i;            } //Step 3: Insert some data. for( int ii = 1; ii <= 5; ii++) {               DataRow    newRow = tbl.NewRow; int j = ii+100; newRow[&quot;EmployeeId&quot;] = j;               newRow[&quot;FirstName&quot;] = &quot;Fname&quot; + j;                newRow[&quot;LastName&quot;] = &quot;LName&quot; + 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[&quot;Employee&quot;]; 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 = &quot;sp_UpdateXML&quot;; objCom.Parameters.Add( new SqlParameter( &quot;@empdata&quot;, 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 = &quot;Select EmployeeId, FirstName,LastName from Employee&quot;;

//Fill the Orders table. objAdpt1.SelectCommand = objCom1; objAdpt1.TableMappings.Add(&quot;Table&quot;, &quot;Employee&quot;); objAdpt1.Fill(objDS); }   } }                     Modify the connection string as appropriate for your environment. Press the F5 key to build and to run the application. Press the ENTER key to close the Console window when the application stops running.</li></ol>

NOTE: This example does not include any error handling.

back to the top

<div class="references_section">