Skip to content

executing-a-diffgram-by-using-sqlxml-managed-classes.md

Latest commit

 

History

History
179 lines (153 loc) · 7.6 KB

executing-a-diffgram-by-using-sqlxml-managed-classes.md

File metadata and controls

179 lines (153 loc) · 7.6 KB
title Executing a DiffGram by Using SQLXML Managed Classes
ms.custom
ms.date 03/17/2017
ms.prod sql
ms.prod_service database-engine, sql-database
ms.reviewer
ms.technology xml
ms.topic reference
helpviewer_keywords
DiffGrams [SQLXML], Managed Classes
SQLXML Managed Classes, DiffGrams
Managed Classes [SQLXML], DiffGrams
SQLXML, Managed Classes
ms.assetid 81c687ca-8c9f-4f58-801f-8dabcc508a06
author MightyPen
ms.author genemi
monikerRange =azuresqldb-current||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

Executing a DiffGram by Using SQLXML Managed Classes

[!INCLUDEappliesto-ss-asdb-xxxx-xxx-md] This example shows how to execute a DiffGram file in the [!INCLUDEmsCoName] .NET Framework environment to apply data updates to [!INCLUDEssNoVersion] tables using SQLXML Managed Classes (Microsoft.Data.SqlXml).

In this example, the DiffGram updates customer information (CompanyName and ContactName) for customer ALFKI.

<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql" sql:mapping-schema="DiffGramSchema.xml">  
  <diffgr:diffgram   
           xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"   
           xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">  
    <DataInstance>  
      <Customer diffgr:id="Customer1"   
                msdata:rowOrder="0" diffgr:hasChanges="modified"   
                CustomerID="ALFKI">  
          <CompanyName>Bottom Dollar Markets</CompanyName>  
          <ContactName>Antonio Moreno</ContactName>  
      </Customer>  
    </DataInstance>  
    <diffgr:before>  
     <Customer diffgr:id="Customer1"   
               msdata:rowOrder="0"   
               CustomerID="ALFKI">  
        <CompanyName>Alfreds Futterkiste</CompanyName>  
        <ContactName>Maria Anders</ContactName>  
      </Customer>  
    </diffgr:before>  
  </diffgr:diffgram>  
</ROOT>

The <before> block includes a <Customer> element (diffgr:id="Customer1"). The <DataInstance> block includes the corresponding <Customer> element with same id. The <customer> element in the <NewDataSet> also specifies diffgr:hasChanges="modified". This indicates an update operation, and the customer record in the Cust table is updated accordingly. Note that if the diffgr:hasChanges attribute is not specified, the DiffGram processing logic ignores this element and no updates are performed.

The following is code for a C# tutorial application that shows how to use the SQLXML Managed Classes to execute the above DiffGram and update two tables (Cust, Ord) you will also create in the tempdb database.

using System;  
using System.Data;  
using Microsoft.Data.SqlXml;  
using System.IO;  
class Test  
{  
   static string ConnString = "Provider=SQLOLEDB;Server=MyServer;database=tempdb;Integrated Security=SSPI;";  
   public static int testParams()  
   {  
      SqlXmlAdapter ad;  
      // Need a memory stream to hold diff gram temporarily  
      MemoryStream ms = new MemoryStream();  
      SqlXmlCommand cmd = new SqlXmlCommand(ConnString);  
      cmd.RootTag = "ROOT";  
      cmd.CommandStream = new FileStream("MyDiffgram.xml", FileMode.Open, FileAccess.Read);  
      cmd.CommandType = SqlXmlCommandType.DiffGram;  
      cmd.SchemaPath = "DiffGramSchema.xml";  
      // Load data set  
      DataSet ds = new DataSet();  
      ad = new SqlXmlAdapter(cmd);  
      ad.Fill(ds);  
      ad.Update(ds);  
      return 0;  
   }  
   public static int Main(String[] args)  
   {  
      testParams();  
      return 0;  
   }  
}

To test the application

  1. Ensure that the .NET Framework is installed on your computer.

  2. Save the following XSD schema (DiffGramSchema.xml) in a folder:

    <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"  
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">  
 <xsd:annotation>  
  <xsd:documentation>  
    Diffgram Customers/Orders Schema.  
  </xsd:documentation>  
  <xsd:appinfo>  
       <sql:relationship name="CustomersOrders"   
                  parent="Cust"  
                  parent-key="CustomerID"  
                  child-key="CustomerID"  
                  child="Ord"/>  
  </xsd:appinfo>  
 </xsd:annotation>  
 <xsd:element name="Customer" sql:relation="Cust">  
  <xsd:complexType>  
    <xsd:sequence>  
      <xsd:element name="CompanyName"    type="xsd:string"/>  
      <xsd:element name="ContactName"    type="xsd:string"/>  
       <xsd:element name="Order" sql:relation="Ord" sql:relationship="CustomersOrders">  
        <xsd:complexType>  
          <xsd:attribute name="OrderID" type="xsd:int" sql:field="OrderID"/>  
          <xsd:attribute name="CustomerID" type="xsd:string"/>  
        </xsd:complexType>  
      </xsd:element>  
    </xsd:sequence>  
    <xsd:attribute name="CustomerID" type="xsd:string" sql:field="CustomerID"/>  
  </xsd:complexType>  
 </xsd:element>  
</xsd:schema>

  3. Create these tables in the tempdb database.

    CREATE TABLE Cust(  
        CustomerID  nchar(5) Primary Key,  
        CompanyName nvarchar(40) NOT NULL ,  
        ContactName nvarchar(60) NULL)  
GO  

CREATE TABLE Ord(  
   OrderID    int Primary Key,  
   CustomerID nchar(5) Foreign Key REFERENCES Cust(CustomerID))  
GO

  4. Add this sample data:

    INSERT INTO Cust(CustomerID, CompanyName, ContactName) VALUES  
     (N'ALFKI', N'Alfreds Futterkiste', N'Maria Anders')  
INSERT INTO Cust(CustomerID, CompanyName, ContactName) VALUES  
     (N'ANATR', N'Ana Trujillo Emparedados y helados', N'Ana Trujillo')  
INSERT INTO Cust(CustomerID, CompanyName, ContactName) VALUES  
     (N'ANTON', N'Antonio Moreno Taquería', N'Antonio Moreno')  

INSERT INTO Ord(OrderID, CustomerID) VALUES(1, N'ALFKI')  
INSERT INTO Ord(OrderID, CustomerID) VALUES(2, N'ANATR')  
INSERT INTO Ord(OrderID, CustomerID) VALUES(3, N'ANTON')

  5. Copy the DiffGram above and paste it into a text file. Save the file as MyDiffGram.xml in the same folder used in step 1.

  6. Save the C# code (DiffgramSample.cs) that is provided above in the same folder in which the DiffGramSchema.xml and MyDiffGram.xml were stored in previous steps.

    [!NOTE]
    You will need to update the name of the [!INCLUDEssNoVersion] instance in the connection string from 'MyServer' to the actual name of your installed instance of [!INCLUDEssNoVersion].

    If you store the files in a different folder, you will have to edit the code and specify the appropriate directory path for the mapping schema.

  7. Compile the code. To compile the code at the command prompt, use:

    csc /reference:Microsoft.Data.SqlXML.dll DiffgramSample.cs

    This creates an executable (DiffgramSample.exe).

  8. At the command prompt, execute DiffgramSample.exe.

See Also

DiffGram Examples (SQLXML 4.0)