SqlXml Updategram Runner

• Download source - 299.1 KB

Introduction

As a BizTalk developer, I need to test the Updategram frequently, so I think it will be good to create a Visual Studio Extension that can run the Updategram file against SQL server connection.

Background

The extension works with SQLXML to execute the Updategram file.

Using the Code

This tip utilizes [VSIX] rather than normal Add-in approach because I want to learn the new VSIX project template and also I want to list my extension in Visual Studio gallery.

The idea behind the extension is very simple; which takes the active document file location in Visual Studio and passes it to the SqlXmlCommand object.

private void MenuItemRunCallback(object sender, EventArgs e)
  {
      bool proceed = false;
      String inputFilepath = "";
      if (dte.ActiveDocument != null)
      {
          if (Path.GetExtension(dte.ActiveDocument.FullName).EndsWith("xml"))
          {
              proceed = true;
              inputFilepath = dte.ActiveDocument.FullName;
          }
      }
      if (!proceed)
      {
          MessageBox.Show("to run updategram please select a valid XML file  ",
                          "invalid file ",
                          MessageBoxButtons.OK, MessageBoxIcon.Error);
          return;
      }

      try
      {
          FileStream FileStrm = new FileStream(inputFilepath, FileMode.Open);
          SqlConnectionStringBuilder objSB1 = new
                              SqlConnectionStringBuilder(dcd.ConnectionString);
          strDatabase = objSB1.InitialCatalog;
          strServer = objSB1.DataSource;
          connectionString = "Provider=SQLNCLI11;"
                  + "Server=" + strServer + ";"
                  + "Database=" + strDatabase + ";"
                  + "Integrated Security=SSPI;"
                  + "DataTypeCompatibility=80;";
          SqlXmlCommand cmd = new SqlXmlCommand(connectionString);
          owP.OutputString("Connect to " + connectionString + "\r\n\r\n");
          cmd.CommandStream = FileStrm;
          owP.OutputString("Open file  " + inputFilepath + "\r\n\r\n");
          cmd.CommandType = SqlXmlCommandType.UpdateGram;

          Stream outStrm = cmd.ExecuteStream();
          StreamReader reader = new StreamReader(outStrm);
          owP.OutputString("================Outputs====================\r\n\r\n");
          owP.OutputString(reader.ReadToEnd() + "\r\n\r\n");
          FileStrm.Close();
      }
      catch (Exception ex)
      {

          owP.OutputString("================Exception====================\r\n");
          owP.OutputString(ex.Message + "\r\n\r\n");
      }
      finally
      {
          owP.Activate();
      }
  }

How To Use It?

The user can open a valid updategram XML, then select Run As Updategram from SqlXml Updategram Runner from Tools menu.

Sample Updategram File

The below file will update the Orders and Order Details from Northwind database.

Note that the file should have urn:schemas-microsoft-com:xml-updategram namespace and it uses updg:at-identity to maintain the identity fields.

<ns0:req xmlns:ns0="http://Northwind/Orders" xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
  <updg:sync>
    <updg:after>
      <ns0:Orders
          updg:at-identity="io"     
          CustomerID	="VINET"
          EmployeeID	="5"
          OrderDate	="2002-09-24"
          RequiredDate	="2002-09-24"
          ShippedDate	="2002-09-24"
          ShipVia	="3"
          Freight	="32.38"
          ShipName	="Test Ship"
          ShipAddress	=" Ship Address"
          ShipCity	="Aqaba"
          ShipPostalCode	="51100"
          ShipCountry	="Jordan"
       />
      <ns0:_x005b_Order_x0020_Details_x005d_
        OrderID="io"	ProductID="11"	
        UnitPrice="15"	Quantity="12"	Discount="0"/>
      <ns0:_x005b_Order_x0020_Details_x005d_
        OrderID="io"	ProductID="42"	
        UnitPrice="114"	Quantity="10"	Discount="0"/>
    </updg:after>
  </updg:sync>
</ns0:req> 

Where to Download

• http://visualstudiogallery.msdn.microsoft.com/3a3e29b1-c854-4473-814d-856eb222707f