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

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.

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

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

3. Start Microsoft Visual Studio .NET, and then create a new Visual Basic .NET Console Application project. By default, the Module1.vb file is created.
4. 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("Successfully inserted and updated data.")
            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 = "user id=UserName;password=YourPassword;Database=master;Server=ServerName"
         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("Employee")
         Dim i As Integer = 0
         Dim aRow As DataRow
         For Each aRow In tbl.Rows
            i = i + 1
            aRow("FirstName") = aRow("FirstName").ToString() & i.ToString()
            aRow("LastName") = aRow("LastName").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("EmployeeId") = j
            NewRow("FirstName") = "Fname" + j.ToString()
            NewRow("LastName") = "LName" + 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("Employee")
         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 = "sp_UpdateXML"
   
         objCom.Parameters.Add(New SqlParameter("@empdata", 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 = "Select EmployeeId, FirstName, LastName from Employee"
   
         ' Fill the Orders table.
         objAdpt1.SelectCommand = objCom1
         objAdpt1.TableMappings.Add("Table", "Employee")
         objAdpt1.Fill(objDS)
      End Sub
   End Module

5. Press F5 to build and run the application. A console window appears with the following message:Successfully inserted and updated data.
6. Press ENTER to close the console window.

back to the top