HOW TO: Perform Bulk Updates and Inserts Using OpenXML with .NET Providers in Visual C++ .NET (316245)

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

1. In SQL Server, use the following code to create a table:

   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, use the following code to create a stored procedure:

   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. Open Visual Studio .NET, and create a new Managed C++ application project named "BulkUpdateInsert".
4. Paste the following code in the BulkUpdateInsert.cpp file. Overwrite the existing code generated by Visual Studio .NET:
   
   Note Modify the connection string as appropriate for your environment.

   #include "stdafx.h"
   #include <tchar.h>
   
   #using <mscorlib.dll>
   #using <System.dll>
   #using <System.data.dll>
   #using <System.xml.dll>
   
   using namespace System;
   using namespace System::Data;
   using namespace System::Data::SqlClient;
   using namespace System::Text;
   
   void BulkInsertUpdate();
   void CreateDataSetFromEmployee( DataSet *, SqlCommand *,SqlDataAdapter *);
   void SaveThroughXML(DataSet *, SqlConnection *);
   
   // This is the entry point for this application.
   int main(void)
   {
       try
       {
           BulkInsertUpdate();
           System::Console::WriteLine("Successfully inserted and updated data");
           System::Console::Read();
       }catch (SqlException *e)
       {
           System::Diagnostics::Debug::WriteLine(e->Message); 
           Console::WriteLine(e->Message);
       }
   
       return 0;
   }
   
   
   
   void BulkInsertUpdate()
   {
       //Steps:
       //1. Create the dataset.
       //2. Update the dataset.
       //3. Insert some data.
       //4. Save the changed data as XML
       //   and then 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=<username>;password=<strong password>;Database=mydata;Server=localhost";
       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.
       DataTable *tbl = objDS->Tables->Item["Employee"];
       //DataRow aRow;
       int i = 0;
       //DataRow *aRow;
       for(int i=0; i< tbl->Rows->Count; i++)
       { 		
   	DataRow *aRow = tbl->Rows->Item[i];
   	StringBuilder *str = new StringBuilder(dynamic_cast<String*>( aRow->Item["FirstName"]));
   	str = str->Append(i.ToString());		
   	aRow->Item["FirstName"] = str->ToString();		
   	str = new StringBuilder(dynamic_cast<String*>( aRow->Item["LastName"]));
   	str = str->Append(i.ToString());		
   	aRow->Item["LastName"] = str->ToString();     
        }
   	
       //Step 3: Insert some data.
       for( int i = 1; i <= 5; i++)
       {
            DataRow	 *newRow = tbl->NewRow();
            int j = i+100;
            newRow->Item["EmployeeId"] = __box(j);
            StringBuilder *str = new StringBuilder("FName");	
            str = str->Append(j.ToString());
            newRow->Item["FirstName"] = str->ToString();
            str = new StringBuilder("LName");
            str = str->Append(j.ToString());
            newRow->Item["LastName"] = str->ToString();
            tbl->Rows->Add( newRow);
       }
   	
       //Step 4: Save the changed data as XML and then 
       //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->Item["Employee"];
       System::Text::StringBuilder *sb = new System::Text::StringBuilder( 1000);
       System::IO::StringWriter *sw = new System::IO::StringWriter(sb);
   	
       DataColumn *col;
       for(int i=0; i<tbl->Columns->Count; i++)
       {
   	col = tbl->Columns->Item[i];
   	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->Item[0]->Value = sb->ToString();;
       objCom->ExecuteNonQuery();
   }
   
   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. Press F5 to build and then run the application.
6. Press ENTER to close the Console window when the application stops running.

Note This example does not perform any error handling.

back to the top