Asp. NET to access XML data using the SQLXML Web service

Asp.net|sql|web|web Service |xml| Access | data

Introduction using SQLXML Web Services to access XML data directly from your asp.net application

SQLXML is an additional set of tools that extend SQL Server's existing support for retrieving and storing XML data. With SQLXML 3.0, you can now use SQL Server to present Web services. Web Services in SQLXML allow users to execute stored procedures, user-defined functionality, and they support templates.

In this article, you'll see how to present a stored procedure that serves as a Web service and builds a simple ASP.net client based on a Web form and accesses and tests a Web service. You should have a basic understanding of SQL Server 2000 and IIS, including how to install virtual directories in IIS and how to set user licenses in SQL Server. You can find related topics in SQL Server Books Online and SQLXML 3.0 documentation. Before that, you need an instance of SQL Server 2000, a Windows 2000 server running IIS, Microsoft MSXML 4.0 XML Parser, SQLXML 3.0 toolbox, and Visual Studio. NET (VS. NET).

Configuring Web Services

The demo application used in this article is the Northwind sample database installed by default on the SQL Server server. We're going to start with the issues we're going to discuss here, but we have to follow the instruction in the SQLXML 3.0 technical materials that are included in the download of SQLXML 3.0 installer. First, execute the process program labeled creating the Nwind Virtual Directory in the SQLXML 3.0 literature. To the Configure IIS Support MMC embedded application under the SQLXML 3.0 program group. Select the default server URL, go to the Action menu and select New, then click the virtual directory. Create a virtual directory named Nwind under IIS to support SQLXML applications that access the Northwind database. Configure security settings to support virtual directory application access to the Northwind database. In the Settings option, select the Allowpost option. This allows HTTP POST requests to be supported in order to support the SQLXML network. Under the virtual directory, you can configure different types of SQLXML applications, including templates, schemas and dbobjects, Support Template execution, XPath queries for mapping schema files, and direct access to different database objects. These different types of applications under the virtual directory are called the virtual name type. There is also a SOAP virtual name type used to identify the Web service sent using a SOAP message. Create a SOAP virtual name type and name it MyWebService (see Figure 1). Now you need to follow the steps described in the section labeled Step 2: Configuring the Virtual Name under the topic Initial Setup for sending SOAP Requests.

Type MyWebService as the name of the new virtual directory application and select the virtual name type as soap (see figure I). This creates a reference to the Web service extension of the Northwind database.

Configure MyWebService

Create a WSDL (Web Service Definition Language) file and a SQL Server configuration (. SSC) configuration file for your Web service. The SSC file describes the virtual name type configuration, and SQLXML uses it to generate the WSDL file. The WSDL file describes the Web services and the methods that your application can invoke when using Web services.

After you set up a SOAP virtual name type, select the configuration options in the Virtual Name tab under the IIS Virtual Directory Administration Tool SQLXML 3.0. To set up a stored procedure method map for your SOAP virtual name type, select the Custordersdetail stored procedure below the sp/template option, and then select the Save option. After completing this step, browse your nwind virtual directory and look for a file called soap.wsdl, which is an XML-formatted WSDL file that describes the services you configure. We can open it in any text-editing program, and the file might look like this:

<xsd:element name= "Custordersdetail"
<xsd:complexType>
<xsd:sequence>
<xsd:element minoccurs= "0" maxoccurs= "1"
Name= "OrderID" type= "Xsd:int"
Nillable= "true"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name= "Custordersdetailresponse"
<xsd:complexType>
<xsd:sequence>
<xsd:element minoccurs= "1" maxoccurs= "1"
Name= "Custordersdetailresult"
Type= "Sqlresultstream:sqlresultstream"/>
<xsd:element name= "ReturnValue"
Type= "Xsd:int" nillable= "true"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>


This fragment of Custordersdetail WSDL file (soap.wsdl) generated by SQLXML shows us the configuration of the Custordersdetail method, including the definition of input and output parameters.

Constructing asp.net clients

After you configure the SQLXML Web service, you need to develop a client application to access the service and perform any operations on it. First, create a new C # or vb.net asp.net Web application and name it webserviceclient. You need to add 3 controls to the default Web form: A button with an ID btnrequest, and two TextBox controls, one for ID Txtorderid and the other for ID txtresponse. Set the TextMode property of the Txtresponse TextBox control to MultiLine to support XML replies that are returned from the Web service. The form should look like Figure 2.

After adding the control, you will need to add a reference to the Web service you just created. In order to do this, in vs. NET, click the References folder in the Solution Explorer window, right-click on the References folder, and add the Web reference menu option. (See Figure 3)

The URL you select refers to the Web service you created previously. Note that the server name I use is localhost, refer to my local instance of IIS, the virtual directory path is Nwind, and for the Northwind database virtual directory, the WEB service name is MyWebService. If you do not use a local server, you will need to replace it with your server name or IP address in the URL.

Calling Web Services

After you create the ASP.net project, you need to add code to invoke the Web service. You want the application to invoke the Web service, you can call the Custordersdetail method, and process the XML result set that is returned when the user clicks 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

The Custordersdetail method that invokes the Web service executes a stored procedure of the same name. This method returns an array of objects, which themselves may be xmlelement or sqlmessage types. These types are defined in your Web service-related WSDL file. Objects of the XmlElement type include results from the execution of stored procedures, user-defined functions, or the return of templates on the SQLXML server. The Sqlmessage object includes any error messages returned from the SQLXML server. You can refer to the XmlElement type in the array of pairs like the code listed below:

(C # code)

result = (System.Xml.XmlElement) results[j];
Txtresponse.text = result. Outerxml.tostring ();
(vb.net code)
result = Results (j)
Txtresponse.text = result. Outerxml.tostring ()

This code retrieves the underlying element in the object array and casts it to the XmlElement object type. It then shows the XML result set on the Web form, by setting the Text property on the Txtresponse control to the value of the OuterXml property of the XmlElement object. When executing the sample code, enter the parameter 10250 (the value of the OrderID key in the Northwind database) in the Txtorderid text box. )。 . When you click on the button, the returned XmlElement contains the following XML data:

<SqlXml>
<row productname= "Jack ' s New England Clam Chowder"
Unitprice= "7.7"
Quantity= "Ten" discount= "0"
Extendedprice= "77"/>
<row productname= "Manjimup dried Apples"
Unitprice= "42.4" quantity= "15" discount= "
Extendedprice= "1261.4"/>
<row
Productname= "Louisiana Fiery hot Pepper sauce"
Unitprice= "16.8" quantity= "discount=" 15 "
Extendedprice= "214.2"/>
</SqlXml>

Note the <row> element <SqlXML> under the parent tag contains attributes ProductName, unitprice,quantity, discount, and ExtendedPrice, Corresponds to the selected and computed fields of the custordersdetail stored procedure. If you are testing the program in the SQL Query Analyzer application by using the command-line execution Custordersdetail ' 10250 ', you will see the data that corresponds to the result set of the data contained in the XmlElement. SQLXML can also execute templates, allowing you to use XPath queries to retrieve data from a basic XML result set. Template support can be very helpful in developing a useful application, but it does not provide all the flexibility needed to effectively enhance the XML data.

SQLXML: Simple But limited

SQLXML is very limited compared to the full vs.net Web service. For example, a Web service that generates SQLXML cannot apply an XSLT stylesheet to XML data or programmatic processing data to directly output HTML to your client application. Essentially, the SQLXML method requires you to associate your transaction layer with the data tier to effectively encapsulate the program logic from your client application. The data returned may be difficult to maintain and not easily migrated to new tools or platforms.

However, just like the sample application, you just follow the outline of the constructor steps and write less than 20 lines of code, and you can invoke the Web service to return the XML data to your client application. SQLXML is an ideal solution for creating browse online catalog information, sales order history, or any tool that can be used to retrieve remote raw XML data. This technology will prove to be appropriate for those applications where the primary transaction is centralized data retrieval and operation, rather than for extending the business application rules and logic. Because the returned data is already in XML form, you can use XSLT sorting to format your data to increase output.