Introduction to access XML data directly from your ASP.NET application using SQLXML's Web Services
SQLXML is an additional tool for extending the support of the SQL server to retrieve and store XML data. With SQLXML 3.0, you can use the SQL server to show the web service. Web services in SQLXML allow users to perform stored procedures, user-defined features, and they support templates.
In this article, you will see how to show a stored procedure as a web service and build a simple ASP.NET web form-based client and to test the WEB service. You should have basic understanding of SQL Server 2000 and IIS, including how to install virtual directories in IIS and how to set up user licensing in SQL Server. You can find related topics in SQL Server 2000 Books Online and SQLXML 3.0 Documentation. Prior to this, you need an instance of SQL Server 2000, a Windows 2000 server running IIS, Microsoft's MSXML 4.0 XML syntax analysis program, SQLXML 3.0 toolbox, and Visual Studio .NET (VS.NET).
Configuring web services
The exemplary application used herein is the Northwind sample database that is installed by default the SQL Server server. We have to start this article to discuss issues, but we must follow the teaching instructions in the SQLXML 3.0 technical data included in the downloaded SQLXML 3.0 Installer. First, execute the procedure for labeled Creating The Nwind Virtual Directory in SQLXML 3.0 literature. To the Configure IIS Support MMC embedded application under the SQLXML 3.0 program group. Select the URL of the default server to the Action menu and select New, then click the virtual directory. Create a virtual directory named NWind to support the SQLXML app that accesses the Northwind database. Configure security settings to support virtual directory applications to access the Northwind database. In the Settings option, select the AllowPost option. This allows the HTTP POST request to support the SQLXML network. In a virtual directory, you can configure different types of SQLXML applications, including templates, Schemas, and DBObjects, support template execution, direct access to XPath queries that map Schema files, and different database objects. These different types of applications in a virtual directory are called the Virtual Name type. There is also a SOAP virtual name type that uses the web service sent by the SOAP message. Create a SOAP virtual name type and name it MyWebService (see Figure 1). Now you need to do it in the steps tagged as STEP 2: Configuring The Virtual Name Under Topic Initial Setup for sending soap requests.
Type MyWebService as the name of the new virtual directory application and select the virtual name type to SOAP (see Figure 1). This creates a reference to the WEB service extension of the Northwind database.
Configuring MyWebService creates a WSDL (Web Service Definition Language) file for your Web service and a SQL Server Configuration (.ssc) profile. The SSC file describes the virtual name type configuration, and SQLXML uses it to generate a WSDL file. The WSDL file describes the Web service and how your application can call when using a web service. After setting a SOAP virtual name type, select the configuration options in the Virtual Name tab of SQLXML 3.0's IIS Virtual Directory. In order to set a stored procedure method mapping for your SOAP virtual name type, select the CustRDERSDetail stored procedure under the sp / template option, then select the Save option. After completing this step, browse your NWind virtual directory and look for files named soap.wsdl, this file is a WSDL file in XML format, describing your configured service. We can open in any text editing program, files may like this:
After adding controls, you will need to add a reference to the Web service you just build. In order to do this, in VS. NET, click the References folder in the Solution Explorer window, use the right click on the References folder, then the Add Web Reference menu option. (See Figure 3) The URL you selected refers to your previously created web services. Note that the server I use is Localhost, refer to my IIS's local instance, the virtual directory path is NWIND, for the Northwind database virtual directory, the web service is named MyWebService. If you don't use a local server, you will need to use your server name or IP address in the URL. Calling a Web Service After creating an ASP.NET project, you need to add a code to call the web service. You want the application to call the web service, you can call the CustOrDETAIL method, and process the XML result set returned after 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.ClickDim iOrderID As Int32Dim returnValue As Int16 = 0Dim j As Int16Dim proxy As New localhost.MyWebService () Dim results As New Object () Dim result As System.Xml.XmlElementiOrderID = Convert.ToInt32 (txtOrderID.Text) results = proxy.CustOrdersDetail _ (iOrderID, returnValue) result = results (j) txtResponse.Text = result.OuterXml .Tostring () End Sub Call the custom of the Web service to perform the stored procedure of the same name. This method returns an object array, and those objects themselves may be XMLELEMENT or SQLMESSAGE types. These types are defined in the WSDL file in your Web service. The XMLEMENT type object includes the result of returning the template from the modular version of the modal version of the execution store, user-defined function or SQLXML server. The SQLMessage object includes any error message returned from the SQLXML server. You can reference the XMLELEMENT type in the logarithmic array 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 Segment code retrieves the infrastructure in an object array and enforces it to the XMLELEMENT object type. Then, it shows the value of the XML set on the web form, which is the value of the OuterXML property of the XMLELEML property by setting the Text property on the TXTRESPONSE control. When performing sample code, enter parameters 10250 in the TXTORDERID text box (value corresponding to the ORDERID key in the northwind database.). When you click on the button, the following XMLElement contains the following XML data:
