August 23, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

New HTTP Endpoints Create SQL Server 2005 Web Services

  • August 5, 2004
  • By Peter DeBetta
  • Send Email »
  • More Articles »

The Client to Consume Your Web Service

Consuming a Web service created in SQL Server 2005 as an HTTP Endpoint is similar to consuming a Web service created in C#. That's the beauty of Web services and SOAP. Begin a new Windows application in Visual Studio.NET and add the following controls to the form (ControlType: name):

  • Button: btnExecSP
  • DataGridView: dgvProduct
  • ListBox: lstProducts
  • PictureBox: picProduct

You will need to add the Web reference before writing any code. Again, you can browse to http://server/awproducts?wsdl to create the Web reference. Keep in mind that your Web reference object will be named server_AWProducts (my machine is named win2k301 so I browse to http://win2k301/awproducts?wsdl, and my Web reference object is win2k301_AWProducts). You will have to tweak the code to the Web reference name that you create.

Once the Web reference is in place, you can double-click the Button control and add the code in Listing 2.

Listing 2

private void BtnExecSP_Click(System.Object sender, System.EventArgs e)
{
   win2k301_AWProducts.SQLEP_AWProducts proxy = 
      new win2k301_AWProducts.SQLEP_AWProducts();
   proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;

   object[] products = proxy.ProductList();
   System.Data.DataSet resultDS;

   if (products[0].ToString() == "System.Data.DataSet")
   {
      resultDS                  = (System.Data.DataSet)products[0];
      DataTable dt              = resultDS.Tables[0];
      lstProducts.DataSource    = dt;
      lstProducts.DisplayMember = "Name";
      lstProducts.ValueMember   = "ProductID";
   }
}

The first thing you will notice is that the Web method returns an object array. Because SQL Server can actually send back not only result sets, but also messages, errors, and other types (see Table 2 for more details), you should validate that you received what you expected. Listing 2 does a check by using the ToString method on the first element in the returned object array and comparing it to "System.Data.DataSet". If true, you know you can safely cast the object to a DataSet type and work with it from there.

Table 2: Object Return Types

Item from SQL Server Corresponding .NET Object
Results of SELECT statement System.Data.DataSet
Results of a SELECT statement with FOR XML System.Xml.XmlElement
Raised error SqlMessage (from WSDL)
Message SqlMessage (from WSDL)
Output parameter SqlParameter (from WSDL)
Rows affected SqlRowCount (from WSDL)
RETURN statement value System.Int32

Now double-click the ListBox control and add the following code:

private void lstProducts_SelectedIndexChanged(object sender,
                                              System.EventArgs e)
{
   Int32 i = 0;
   try 
   {i = System.Convert.ToInt32(lstProducts.SelectedValue.ToString());}
   catch 
   {return;} //prevents issue when loading listbox

   object e1;
   win2k301_AWProducts.SqlMessage errorMessage;
   System.Data.DataSet resultDS;

   win2k301_AWProducts.SQLEP_AWProducts proxy = 
      new win2k301_AWProducts.SQLEP_AWProducts();
   proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;

   object[] products = proxy.ProductStockInfo(i);
   e1 = products[0];
   switch (e1.ToString())
   {
      case "System.Data.DataSet":
         resultDS = (System.Data.DataSet)products[0];
         DataTable dt = resultDS.Tables[0];
         dgvProduct.DataSource = dt;
         dgvProduct.Refresh();
         break;
      case "Yukon_Http_v1.win2k301_AWProducts.SqlMessage":
         errorMessage = (win2k301_AWProducts.SqlMessage)products[0];
         MessageBox.Show("Error fetching product", "'" 
            + errorMessage.Message + " occurred at '" 
            + errorMessage.Source + "'");
         return;
   }
   Byte[] photo = proxy.ProductPhoto(i);
   picProduct.Image = new Bitmap(new MemoryStream(photo));
}

You should pay attention to a couple of details here. First, this code snippet checks for another type of returned object: SqlMessage. Notice the type name (from the ToString method) is Yukon_Http_v1.server_virtualDirectory.SqlMessage. This type is defined in the WSDL document and is unique to each Web service. The following listing shows a snippet from the WSDL of the SqlMessage complexType:

<xsd:complexType name="SqlMessage">
  <xsd:sequence minOccurs="1" maxOccurs="1">
    <xsd:element name="Class" type="sqlmessage:nonNegativeInteger" />
    <xsd:element name="LineNumber"
                 type="sqlmessage:nonNegativeInteger" />
    <xsd:element name="Message" type="xsd:string" />
    <xsd:element name="Number" type="sqlmessage:nonNegativeInteger" />
    <xsd:element name="Procedure" type="xsd:string" minOccurs="0" />
    <xsd:element name="Server" type="xsd:string" minOccurs="0" />
    <xsd:element name="Source" type="xsd:string" />
    <xsd:element name="State" type="sqlmessage:nonNegativeInteger" />
  </xsd:sequence>
</xsd:complexType>

The last tidbit for this discussion is the Byte array that is returned from the UDF. Because this UDF returned varbinary data, its XSD type is xsd:base64Binary. Thus, it is a Byte array in .NET. SQL Server's int data type is the XSD type xsd:int and Int32 in .NET. Books Online has more information about the mapping of types.

Easy Web Service Creation

HTTP Endpoints provide a way to create various interfaces into SQL Server, including features such as Service Broker, database mirroring, and of course, Web services. It makes the creation of a Web service (that exposes SQL Server data) a snap for experienced programmers and database administrators alike. Enjoy!

About the Author

Peter DeBetta is an independent software consultant who trains exclusively for Wintellect. Peter regularly publishes on the topic of SQL Server and recently wrote Introducing SQL Server 2005 for Developers for Microsoft Press. He also speaks at conferences such as VSLive!, WinSummit, WinDev, and Devscovery.





Page 3 of 3



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel