Browse DevX
Sign up for e-mail newsletters from DevX


Take Advantage of SQLXML with ASP.NET  : Page 3

Use SQLXML's Web services to access XML data directly from your ASP.NET applications.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Invoking the Web Service
After creating the ASP.NET project, you need to add the code to invoke the Web Service. You want the application to invoke the Web service, call the CustOrdersDetail method, and process the returned XML result set when users click the button. Add the following code to the btnRequest_Click event.

(C# code)

   private void btnRequest_Click(object sender, 
      System.EventArgs e)
      int iOrderID;
      int returnValue = 0;
      int j = 0;
      iOrderID = Convert.ToInt32(txtOrderID.Text);
      localhost.MyWebService proxy = 
         new localhost.MyWebService();
      object[] results;
      results = proxy.CustOrdersDetail(iOrderID, 
         out returnValue);
      System.Xml.XmlElement result;
      result = (System.Xml.XmlElement) results[j];
      txtResponse.Text = result.OuterXml.ToString();
(VB.Net code)

   Private Sub btnRequest_Click _
      (ByVal sender As System.Object, _
      ByVal e As System.EventArgs) _
      Handles btnRequest.Click
      Dim iOrderID As Int32
      Dim returnValue As Int16 = 0
      Dim j As Int16
      Dim proxy As New localhost.MyWebService()
      Dim results As New Object()
      Dim result As System.Xml.XmlElement
      iOrderID = Convert.ToInt32(txtOrderID.Text)
      results = proxy.CustOrdersDetail _
         (iOrderID, returnValue)
      result = results(j)
      txtResponse.Text = result.OuterXml.ToString()
   End Sub
Calling the CustOrdersDetail method of the Web service executes the stored procedure of the same name. The method returns an array of Objects—the objects themselves may be XmlElement or SqlMessage types. These types are defined in the WSDL file associated with your Web service. Objects of XmlElement type include the results returned from the execution of the stored procedure, user-defined function, or template from the SQLXML server. SqlMessage objects include any error messages returned from the SQLXML server. You can reference the XmlElement type in the object array as follows:

(C# code)

      result = (System.Xml.XmlElement) results[j];
      txtResponse.Text = result.OuterXml.ToString();
(VB.Net code)

      result = results(j)
      txtResponse.Text = result.OuterXml.ToString()
The code retrieves the base element of the object array and casts it to an XmlElement object type. Next, it displays the XML result set on the Web Form by setting the Text property of the txtResponse control to the value of the OuterXml property of the result XmlElement object. To execute the sample code, enter the input parameter 10250 (the value corresponds to an OrderID key in the Northwind database.) into the txtOrderID text box.. When you click the button, the returned XmlElement contains the following XML data:

      <row ProductName="Jack's New England Clam Chowder" 
         Quantity="10" Discount="0" 
         ExtendedPrice="77" />
      <row ProductName="Manjimup Dried Apples" 
         UnitPrice="42.4" Quantity="35" Discount="15" 
         ExtendedPrice="1261.4" />
         ProductName="Louisiana Fiery Hot Pepper Sauce" 
         UnitPrice="16.8" Quantity="15" Discount="15" 
         ExtendedPrice="214.2" />
Note that the <row> elements under the parent tag <SqlXml> contain the attributes ProductName, UnitPrice, Quantity, Discount, and ExtendedPrice, which correspond directly to the fields selected and calculated by the CustOrdersDetail stored procedure. If you test the procedure in the SQL Query Analyzer application using the command exec CustOrdersDetail '10250', you'll see that the data (though not the format) in the result set is identical to the data contained in the XmlElement.

SQLXML also enables template execution, which lets you use XPath queries to retrieve data from an underlying XML result set. While template support can assist greatly in developing a useful application, it does not provide all the flexibility needed to effectively leverage XML data.

SQLXML: Simple, but Limited
SQLXML has limitations in comparison with a full VS.Net Web service. For example, SQLXML-generated Web services can't apply an XSLT stylesheet to the XML data or process the data programmatically to output HTML directly to your client applications. Essentially, the SQLXML approach requires that you consolidate your business and data tiers in order to effectively encapsulate the logic from your client applications. The result can be applications that are difficult to maintain and aren't easily ported to new tools or platforms.

Nonetheless, as the sample application demonstrates, by following the configuration steps outlined, and writing less than twenty lines of code, you can call Web services that return XML data to your client application. SQLXML is an ideal solution for creating applications to view online catalog information, sales order history, or any other tool that can benefit from remote retrieval of raw XML data. This technique will prove to be effective for applications that focus primarily on data retrieval and manipulation rather then the extensive application of business rules and logic. Because the returned data is already in XML form, you can use XSLT extensively to sort, format, and enhance the output of your data.

Randy Holloway is the Director of Systems Development for Growing Family, Inc., the leading provider of infant photography services in North America. He also speaks on technology related topics and is a contributor to several software development magazines.
Comment and Contribute






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



Thanks for your registration, follow us on our social networks to keep up-to-date