CARA: Melakukan pembaruan massal dan menyisipkan menggunakan OpenXML dengan.Penyedia bersih di Visual C#.NET

Kembali ke atas

Persyaratan

• Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, atau Microsoft Windows NT 4.0 Server
• Microsoft Visual Studio.NET
• Microsoft SQL Server 2000

Kembali ke atas

Membuat proyek

1. Dalam SQL Server, membuat tabel dengan kode berikut:

   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. Dalam SQL Server, membuat prosedur yang disimpan dengan kode berikut:

   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. Mulai Visual Studio.NET, dan kemudian membuat proyek aplikasi konsol baru dalam Visual C#.NET.
4. Copy dan paste kode berikut ke Class1 aplikasi konsol:

   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. Memodifikasi rangkaian sambungan yang sesuai untuk lingkungan Anda.
6. Tekan tombol F5 untuk membangun dan menjalankan aplikasi.
7. Tekan tombol ENTER untuk menutup jendela konsol ketika aplikasi berhenti berjalan.

Kembali ke atas