Microsoft KB Archive/316245

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

Article ID: 316245

Article Last Modified on 10/31/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

-



This article was previously published under Q316245



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

This article refers to the following Microsoft .NET Framework Class Library namespaces:
 * System::Data
 * System::Data::SqlClient
 * System::Text

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, 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   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  Open Visual Studio .NET, and create a new Managed C++ application project named &quot;BulkUpdateInsert&quot;.  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.
 * 1) include &quot;stdafx.h&quot;
 * 2) include 


 * 1) using 
 * 2) using 
 * 3) using 
 * 4) using 

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(&quot;Successfully inserted and updated data&quot;); 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 = &quot;user id= ;password= ;Database=mydata;Server=localhost&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. DataTable *tbl = objDS->Tables->Item[&quot;Employee&quot;]; //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[&quot;FirstName&quot;])); str = str->Append(i.ToString); aRow->Item[&quot;FirstName&quot;] = str->ToString; str = new StringBuilder(dynamic_cast<String*>( aRow->Item[&quot;LastName&quot;])); str = str->Append(i.ToString); aRow->Item[&quot;LastName&quot;] = str->ToString; }   //Step 3: Insert some data. for( int i = 1; i <= 5; i++) {        DataRow     *newRow = tbl->NewRow; int j = i+100; newRow->Item[&quot;EmployeeId&quot;] = __box(j); StringBuilder *str = new StringBuilder(&quot;FName&quot;); str = str->Append(j.ToString); newRow->Item[&quot;FirstName&quot;] = str->ToString; str = new StringBuilder(&quot;LName&quot;); str = str->Append(j.ToString); newRow->Item[&quot;LastName&quot;] = 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[&quot;Employee&quot;]; 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 = &quot;sp_UpdateXML&quot;;

objCom->Parameters->Add( new SqlParameter( &quot;@empdata&quot;, 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 = &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); }

</li> Press F5 to build and then run the application.</li> Press ENTER to close the Console window when the application stops running.</li></ol>

Note This example does not perform any error handling.

back to the top

<div class="references_section">