Executing a DiffGram by Using SQLXML Managed Classes

Article
12/03/2008

This example shows how to execute a DiffGram file in the Microsoft .NET Framework environment to apply data updates to SQL Server 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

To test this example, you must have the .NET Framework installed on your computer.

Save this 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>

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

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')

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.
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 SQL Server instance in the connection string from 'MyServer' to the actual name of your installed instance of SQL Server.

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.
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).
At the command prompt, execute DiffgramSample.exe.

Share via

Executing a DiffGram by Using SQLXML Managed Classes

See Also

Reference

Help and Information

Additional resources