Microsoft KB Archive/316244

= How to perform bulk updates and inserts by using the OpenXML method with .NET providers in Visual Basic .NET =

Article ID: 316244

Article Last Modified on 3/12/2004

-

APPLIES TO


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

-



This article was previously published under Q316244



For a Microsoft Visual C# .NET version of this article, see 315968.

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

 * INTRODUCTION
 * Requirements
 * Create the project
 * REFERENCES



INTRODUCTION
This step-by-step article describes how to perform bulk inserts and updates with different Microsoft .NET data providers by using the OpenXML method in Microsoft Visual Basic .NET. The sample project in this article uses the Microsoft SQL Server .NET Managed Provider (SqlClient). However, you can also use the Microsoft OLE DB .NET Managed Provider or the Microsoft ODBC .NET 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, Microsoft Windows XP, or Microsoft Windows Server 2003
 * Microsoft Visual Studio .NET
 * Microsoft SQL Server 2000

back to the top

Create the project
Note This sample project does not contain code that performs error handling.   Use the following code to create a table in your instance of Microsoft SQL Server 2000: 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   Use the following code to create a stored procedure in your instance of SQL Server 2000: 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 Microsoft Visual Studio .NET, and then create a new Visual Basic .NET Console Application project. By default, the Module1.vb file is created.  Replace the existing code in the Module1.vb file with the following code.

Note In the following code, modify the connection string for your environment: Imports System Imports System.Data.SqlClient Imports System.Data

Module Module1

Sub Main Try BulkInsertUpdate System.Console.WriteLine(&quot;Successfully inserted and updated data.&quot;) System.Console.Read Catch e As System.Data.SqlClient.SqlException System.Diagnostics.Debug.WriteLine(e.Message) System.Console.WriteLine(e.Message) End Try End Sub Sub BulkInsertUpdate ' Steps: ' 1. Create the data set. ' 2. Update the data set. ' 3. Insert some data. ' 4. Save the changed data as XML, and then send the XML to      ' SQL Server through the stored procedure.

' Declaration Dim objDS As System.Data.DataSet Dim objCon As SqlConnection Dim objCom1 As SqlCommand Dim objAdpt1 As SqlDataAdapter Dim sConn As String sConn = &quot;user id=UserName;password=YourPassword;Database=master;Server=ServerName&quot; objDS = New DataSet objCon = New SqlConnection(sConn) objCon.Open objCom1 = New SqlCommand objCom1.Connection = objCon objAdpt1 = New SqlDataAdapter

' Step 1: Create the data set. CreateDataSetFromEmployee(objDS, objCom1, objAdpt1)

' Step 2: Update the data set. Dim tbl As System.Data.DataTable = objDS.Tables(&quot;Employee&quot;) Dim i As Integer = 0 Dim aRow As DataRow For Each aRow In tbl.Rows i = i + 1 aRow(&quot;FirstName&quot;) = aRow(&quot;FirstName&quot;).ToString & i.ToString aRow(&quot;LastName&quot;) = aRow(&quot;LastName&quot;).ToString & i.ToString Next

' Step 3: Insert some data. Dim ii As Integer For ii = 1 To 5 Step ii + 1 Dim NewRow As DataRow = tbl.NewRow Dim j As Integer = ii + 100 NewRow(&quot;EmployeeId&quot;) = j        NewRow(&quot;FirstName&quot;) = &quot;Fname&quot; + j.ToString NewRow(&quot;LastName&quot;) = &quot;LName&quot; + j.ToString tbl.Rows.Add(NewRow) Next

' 4. Save the changed data as XML, and then send the XML to      ' SQL Server through the stored procedure. ' In your instance of SQL Server, you have already saved a stored ' procedure that accepts this XML and updates the corresponding table.

SaveThroughXML(objDS, objCon) End Sub

Sub SaveThroughXML(ByVal objDS As DataSet, ByVal objCon As SqlConnection) 'Change the column mapping. Dim tbl As DataTable = objDS.Tables(&quot;Employee&quot;) Dim sb As System.Text.StringBuilder = New System.Text.StringBuilder(1000) Dim sw As System.IO.StringWriter = New System.IO.StringWriter(sb)

Dim col As DataColumn For Each col In tbl.Columns col.ColumnMapping = System.Data.MappingType.Attribute Next

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

Dim objCom As SqlCommand = 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 End Sub

Sub CreateDataSetFromEmployee(ByVal objDS As DataSet, ByVal objCom1 As SqlCommand, ByVal objAdpt1 As SqlDataAdapter)

' 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) End Sub End Module  Press F5 to build and run the application. A console window appears with the following message:

Successfully inserted and updated data.

 Press ENTER to close the console window.</ol>

back to the top <div class="references_section">