Lm th? no ?: Th?c hi?n v?i s? l?ng l?n thng tin C?p Nh?t v ph? trang b?ng cch s? d?ng OpenXML v?i.Nh cung c?p m?ng t?i Visual C#.NET

D?ch tiu ? D?ch tiu ?
ID c?a bi: 315968 - Xem s?n ph?m m bi ny p d?ng vo.
Bung t?t c? | Thu g?n t?t c?

? Trang ny


Bi vi?t t?ng b?c ny m t? cch s? l?ng l?n ph? trang v C?p Nh?t v?i Microsoft khc nhau.Cc nh cung c?p m?ng d? li?u b?ng cch s? d?ng cc OpenXML phng php. M?c d cc m?u trong bi vi?t ny s? d?ng cc nh cung c?p qu?n l? SqlClient, b?n c?ng c th? s? d?ng OLEDB ho?c nh cung c?p qu?n l? ODBC.

Yu c?u

Danh sch sau v?ch ra ?c ? ngh? ph?n c?ng, ph?n m?m, c s? h? t?ng m?ng v gi d?ch v? m b?n c?n:
  • Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, ho?c my ch? Microsoft Windows NT 4.0
  • Microsoft Visual Studio.NET
  • Microsoft SQL Server 2000

T?o ra cc d? n

  1. Trong SQL Server, t?o ra m?t b?ng v?i o?n m? sau:
    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Employee]') 
    and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [dbo].[Employee]
    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]
  2. Trong SQL Server, t?o ra m?t th? t?c ?c lu tr? v?i o?n m? sau:
    CREATE PROC sp_UpdateXML @empdata nText
     DECLARE @hDoc int   
     exec sp_xml_preparedocument @hDoc OUTPUT,@empdata   
    --This code updates old data.
     UPDATE Employee 
       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
  3. B?t ?u Visual Studio.NET, v sau t?o ra m?t d? n m?i giao di?n i?u khi?n ?ng d?ng trong Visual C#.NET.
  4. Sao chp v dn o?n m? sau vo Class1 c?a ?ng d?ng giao di?n i?u khi?n:
    using System;
    using System.Data.SqlClient;
    using System.Data;
    namespace ConsoleApplication1
        /// <summary>
        /// Summary description for Class1
        /// </summary>
        class Class1
            /// <summary>
            /// Main entry point for the application
            /// </summary>
            static void Main(string[] args)
                        System.Console.WriteLine("Successfully inserted and updated data");
                catch (System.Data.SqlClient.SqlException e)
                    System.Diagnostics.Debug.WriteLine (e.Message); 
            static void BulkInsertUpdate()
                //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.
                System.Data.DataSet         objDS;
                SqlConnection               objCon;
                SqlCommand                  objCom1;
                SqlDataAdapter              objAdpt1;
                String                      sConn;
                sConn = "user id=myUser;password=YourPassword;" + 
                objDS = new DataSet();
                objCon = new SqlConnection(sConn);
                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)
                    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 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["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", 
                objCom.Parameters[0].Value = sb.ToString();;
            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");
  5. S?a ?i cc chu?i k?t n?i nh l thch h?p cho mi tr?ng c?a b?n.
  6. B?m phm F5 ? xy d?ng v ch?y cc ?ng d?ng.
  7. B?m phm ENTER ? ng c?a s? giao di?n i?u khi?n khi ?ng d?ng ng?ng ch?y.
LU ?: V d? ny khng bao g?m b?t k? qu?n l? l?i.


? bi?t thm chi ti?t v? vi?c s? d?ng.NET qu?n l? cc nh, cung c?p Click vo s? bi vi?t d?i y ? xem bi vi?t trong c s? ki?n th?c Microsoft:
313480 Thng bo: L? tr?nh.NET d? li?u cc nh cung c?p

Thu?c tnh

ID c?a bi: 315968 - L?n xem xt sau cng: 27 Thang Tam 2011 - Xem xt l?i: 2.0
p d?ng
  • Microsoft ADO.NET 1.1
  • Microsoft Visual C# .NET 2003 Standard Edition
  • Microsoft Visual C# .NET 2002 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
T? kha:
kbhowtomaster kbsqlclient kbstoredproc kbsystemdata kbmt KB315968 KbMtvi
My d?ch
QUAN TRONG: Bi vi?t ny ?c d?ch b?ng ph?n m?m d?ch my c?a Microsoft ch? khng ph?i do con ng?i d?ch. Microsoft cung c?p cc bi vi?t do con ng?i d?ch v c? cc bi vi?t do my d?ch ? b?n c th? truy c?p vo t?t c? cc bi vi?t trong C s? Ki?n th?c c?a chng ti b?ng ngn ng? c?a b?n. Tuy nhin, bi vi?t do my d?ch khng ph?i lc no c?ng hon h?o. Lo?i bi vi?t ny c th? ch?a cc sai st v? t? v?ng, c php ho?c ng? php, gi?ng nh m?t ng?i n?c ngoi c th? m?c sai st khi ni ngn ng? c?a b?n. Microsoft khng ch?u trch nhi?m v? b?t k? s? thi?u chnh xc, sai st ho?c thi?t h?i no do vi?c d?ch sai n?i dung ho?c do ho?t ?ng s? d?ng c?a khch hng gy ra. Microsoft c?ng th?ng xuyn c?p nh?t ph?n m?m d?ch my ny.
Nh?p chu?t vo y ? xem b?n ti?ng Anh c?a bi vi?t ny:315968

Cung cp Phan hi


Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com