How to: Perform bulk updates and inserts using the. NET provider in C#.net OpenXML

https://support.microsoft.com/zh-cn/kb/315968

How to: Perform bulk updates and inserts using the. NET provider in C#.net OpenXML

• Email
• Print

Note: This article was completed by Microsoft automatic machine translation software without human intervention. Microsoft is pleased to be able to provide you with manually translated and machine translation articles to enable you to access all Knowledge Base articles in your language. However, the article by machine translation is not always perfect. It may have problems with vocabulary, grammar or grammar, just as a foreigner can always make such mistakes when speaking Chinese. Although we often upgrade our machine translation software to improve the quality of our translations, we do not guarantee the accuracy of machine translation and are not responsible for any direct, or indirect, problems caused by the mistranslation of the content or the customer's misuse of it.
Click here to view the English version of this article: 315968for a Microsoft Visual C + + +.net version of this article, see 316245.
the following Microsoft.NET Framework class library namespace references this article:

• System.Data
• System.Data.SqlClient

In this task

• SUMMARY
• • Requirements
  • Create the Project
• REFERENCES

Profile This next step guide describes how to perform bulk insertions with The OpenXML method of updating with other microsoft.net data providers. Although the examples in this article use the SqlClient managed provider, you can also use OLE DB or an ODBC managed provider.

Back to top of pageRequirementsThe following list lists the recommended hardware, software, network infrastructure, and service packs that are required:

• Microsoft Windows 2000 Professional Edition, 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 top of pageCreate a project

2.   if exists (SELECT * from dbo.sysobjects WHERE id = ob ject_id (N ' [dbo].[ Employee] ') and OBJECTPROPERTY (ID, N ' isusertable ') = 1) drop table [dbo]. [Employee] Gocreate TABLE [dbo]. [Employee]  ([EmployeeId] [int] not NULL, [FirstName] [varchar] (+) COLLATE sql_latin1_general_cp1_ci_as NULL, [LastName] [varchar] (+) COLLATE sql_latin1_general_cp1_ci_as NULL) on [Primary]go  

4. Use the following code in SQL Server to create a stored procedure:

   CREATE PROC sp_UpdateXML @empdata nTextAS  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))  XMLEmployeeWHERE    Employee.EmployeeId = XMLEmployee.EmployeeId--This code inserts new data.Insert Into Employee SELECT   EmployeeId, FirstName, LastNameFROM       OPENXML (@hdoc, ‘/NewDataSet/Employee‘,1)WITH (EmployeeId Integer, FirstName varchar(100),  LastName varchar(100))  XMLEmployeeWhere XMLEmployee.EmployeeId Not IN (Select EmployeeID from Employee)EXEC sp_xml_removedocument @hDocGO

6. Visual Studio. NET, start, and then create a new Console Application project in Visual C#.net.
7. copy and paste the following code into the class1 in the console application:

   Using system;using system.data.sqlclient;using system.data;namespace consoleapplication1{//<summary>//Su  Mmary description for Class1//</summary> class Class1 {//<summary>//Main entry            Point for the application//</summary> [STAThread] static void Main (string[] args) {                    try {bulkinsertupdate ();                    System.Console.WriteLine ("successfully inserted and updated data");            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 = "User Id=myuser;password=yourpassword;" + "database=yourdatabase;            Server=yourserver ";            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["Employee"];            DataRow Arow;            int i = 0; foreach (DataRow arow in TBL. Rows) {                i++; arow["FirstName"] = arow["FirstName"].                ToString () + i; arow["LastName"] = arow["LastName"].            ToString () + i;            }//step 3:insert some data. for (int II = 1; II <= 5; ii++) {DataRow NewRow = tbl.                NewRow ();                int j = ii+100;                newrow["EmployeeId"] = j;                newrow["FirstName"] = "Fname" + j;                newrow["LastName"] = "LName" + j; Tbl.            Rows.Add (NewRow); }//step 4:save the changed data as XML,//and send the XML to SQL Server through the stored proce            Dure.            In SQL Server, you wrote a stored procedure, which//accepts this XML and updates the corresponding table.        Savethroughxml (objDS, Objcon); } static void Savethroughxml (DataSet objds, SqlConnection objcon) {//change The column mapping F            Irst.          DataTable          TBL = objds.tables["Employee"];            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 = "Sp_updatexml";            OBJCOM.PARAMETERS.ADD (New SqlParameter ("@empdata", 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 = "Select EmployeeId, firstname,lastname from Employee";            Fill the Orders table.            Objadpt1.selectcommand = objCom1;            OBJADPT1.TABLEMAPPINGS.ADD ("Table", "Employee");        Objadpt1.fill (OBJDS); }    }}

9. Modify the appropriate connection string for your environment.
11. Press the F5 key to build and run the application.
13. Press the ENTER key to close the console window when the application stops running.

Note: This example does not contain any error handling.

How to: Perform bulk updates and inserts using the. NET provider in C#.net OpenXML