HOW TO: ????? ????????? ???? ? ?????? ??????? OpenXML ?? ????? .NET ?? Visual C# .NET

?????? ????????? ?????? ?????????
???? ???????: 315968 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???? | ?? ????

?? ??? ??????

??????

??? ??????? ???? ????? ???? ????? ??????? ????? ????? ?????? ?? Microsoft .NET ?????? ????? ????? ?????? ?????? ??????? ??????? OpenXML. ??? ????? ?? ?? ?????? ??????? ?? ??? ??????? ???? ??????? SqlClient ? ????? ????? ??????? OLEDB ?? ???? ??????? ODBC.

???????

???? ??????? ??????? ??????? ?????? ??? ????? ?????? ???????? ?????? ? ??? ??????? ???? ???????:
  • ????? Microsoft Windows 2000 Professional ?? ???? ??????? Microsoft Windows 2000 Server ?? ???? ??????? Microsoft Windows 2000 Server ?? Microsoft Windows NT 4.0 Server
  • Microsoft Visual Studio .NET
  • Microsoft SQL Server 2000

????? ???????

  1. ?? SQL Server ????? ???? ???????? ????????? ???????? ???????:
    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. ?? SQL Server ????? ????? ???? ???????? ????????? ???????? ???????:
    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. ??? ????? Visual Studio .NET ?? ?? ?????? ????? "????? ???? ????" ???? ?? Visual C# .NET.
  4. ??? ???? ???????? ???????? ??????? ??? Class1 ??????? ???? ??????:
    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>
            [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 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", 
                       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);				
            }
        }
    }
    					
  5. ????? ????? ??????? ??? ?? ????? ?????.
  6. ???? ??????? F5 ?????? ?????? ???????.
  7. ???? ??????? ENTER ?????? ???? ???? ?????? ??? ??????? ????? ?????.
??????: ?? ????? ??? ?????? ?????? ?? ???.

?????

?????? ??? ??????? ?????? ??? ??????? ????? ????? .NET ???? ??? ??? ??????? ????? ?????? ?? "????? ??????? ?? Microsoft:
313480INFO: ??????? ?? .NET ????? ????????

???????

???? ???????: 315968 - ????? ??? ??????: 03/?? ??????/1424 - ??????: 3.5
????? ???
  • Microsoft ADO.NET 1.1
  • Microsoft ADO.NET 1.0
  • 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
????? ??????: 
kbmt kbhowtomaster kbsqlclient kbstoredproc kbsystemdata KB315968 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????315968

????? ???????

 

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