NASýL YAPýLýR: Toplu güncelleþtirmeleri ve ekleri açýk XML, Visual C# .NET, .NET saðlayýcýlarý ile kullanarak gerçekleþtir

Üste

Gereksinimler

• Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Server veya Microsoft Windows NT 4.0 Server Geliþmiþ...
• Microsoft Visual Studio. NET'i
• Microsoft SQL Server 2000

Üste

Proje oluþturma

1. SQL Server'da aþaðýdaki kodu bir tablo oluþturun:

   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'da bir saklý yordam aþaðýdaki kodla oluþturun:

   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'i baþlatýn ve sonra da Visual C# .NET ile yeni bir konsol uygulamasý) projesi oluþturun.
4. Class1 <a0>Konsol</a0> uygulamasýnýn aþaðýdaki kodu yapýþtýrý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>
           [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. Baðlantý dizesi ortamýnýza uygun olarak deðiþtirin.
6. Oluþturmak ve bu uygulamayý çalýþtýrmak için F5 tuþuna basýn.
7. Konsol penceresi, uygulamayý çalýþtýran durduðunda kapatmak için ENTER tuþuna basýn.

Üste