Section 8.3. Creating the HTTP Endpoint and Exposing a Web Method Example

8.3. Creating the HTTP Endpoint and Exposing a Web Method Example

The example in this section creates a stored procedure and defines an HTTP SOAP endpoint with a single web method that accesses the stored procedure. A .NET client is created to call the stored procedure via a web service call and to display the results.

Follow these steps to create the endpoint and expose a web method:

1. Create a stored procedure to return purchase orders for a specific employee, or all purchase orders if an employee is not specified. Execute the following query in the AdventureWorks database to create the stored procedure:

        USE AdventureWorks     CREATE PROCEDURE GetPurchaseOrder         @EmployeeID [int] = NULL     WITH EXECUTE AS CALLER     AS     IF @EmployeeID IS NOT NULL         BEGIN             SELECT * FROM Purchasing.PurchaseOrderHeader             WHERE EmployeeID = @EmployeeID         END     ELSE         SELECT * FROM Purchasing.PurchaseOrderHeader 

   
   

2. Create an HTTP SOAP endpoint named GetPurchaseOrderEndpoint to expose this stored procedure as a web method named GetPurchaseOrder. The following T-SQL statement creates the HTTP endpoint with a single web method:

        USE AdventureWorks     CREATE ENDPOINT GetPurchaseOrderEndpoint         STATE = STARTED         AS HTTP (             path = '/sql/GetPurchaseOrder',             AUTHENTICATION = (INTEGRATED),             PORTS = (CLEAR)         )         FOR SOAP(             WEBMETHOD 'GetPurchaseOrder'                 (NAME = 'AdventureWorks.dbo.GetPurchaseOrder',                 SCHEMA = STANDARD),             BATCHES = ENABLED,             WSDL = DEFAULT,             SCHEMA = STANDARD,             DATABASE = 'AdventureWorks',             NAMESPACE = 'http://tempUri.org/'     ) 

   
   

Under Windows XP, you cannot create endpoints using port 80 if IIS is running, because IIS listens on port 80. You can either stop the World Wide Web Publishing service or create the endpoint on a port other than 80 by specifying the CLEAR_PORT clause in the CREATE ENDPOINT or ALTER ENDPOINT statement.

Stop the World Wide Web Publishing service by selecting Start Administrative Tools Services. In the Services dialog box, right-click World Wide Web Publishing and choose Stop from the context menu.

Creating and managing HTTP endpoints is described in detail in the "Creating an HTTP Endpoint" section later in this chapter.

You can confirm that the endpoint has been created by querying the catalog view sys.http_endpoints:

     SELECT * FROM sys.http_endpoints 

You can confirm the existence of the web method by querying the catalog view sys.endpoint_webmethods:

     SELECT * FROM sys.endpoint_webmethods 

Catalog views that contain information about the HTTP endpoints defined in the SQL Server instance are described in the "Endpoint Metadata" section later in this chapter.

This permission controls whether a principal can see metadata for a specific endpoint:

     { GRANT | DENY | REVOKE | }  ON ENDPOINT::endPointName     TO server_principal 

This permission does not give the principal access to the endpoint.

The next step is to consume this web method. The following example uses a C# .NET client. Follow these steps in Visual Studio 2005:

1. From the Visual Studio 2005 menu, select File New Project.

2. Specify where you want to save the project. Name the project GetPurchase-OrderSoapApp. Click OK.

3. In the Solution Explorer window, right-click References and select Add Web Reference from the context menu.

4. In the Add Web Reference dialog box, enter http://localhost/sql/GetPurchaseOrder?wsdl. You can specify a server name instead of localhost if your SQL Server is not local. Figure 8-1 shows that the GetPurchaseOrder( ) method is available, together with sqlbatch( ), which permits ad hoc queries to be executed.

5. Change the Web reference name listbox to GetPurchaseOrderWS. Click the Add Reference button.

6. Double-click Form1 in Solution Explorer to open the Form Designer.

7. Add a DataGridView control to the form. Uncheck the Enable Adding, Enable Editing, and Enable Deleting checkboxes. Set the Name property to PODataGridView.

8. Add a Label control to the form. Set the Text property to Employee ID:.

9. Add a TextBox control to the form. Set the Name property to employeeIDTextBox.

10. Add a Button control to the form. Set the Text property to Go and the Name property to goButton.

    Figure 8-2 shows the completed form.

11. Double-click the goButton to add a click event handler named goButton_Click for the button.

12. Add the following code to the goButton_Click event handler:

     GetPurchaseOrderWS.GetPurchaseOrderEndpoint proxy =     new GetPurchaseOrderWS.GetPurchaseOrderEndpoint(  ); proxy.Credentials = System.Net.CredentialCache.DefaultCredentials; 

    
    

    Figure 8-1. Adding a web reference for GetPurchaseOrder( )

    

    Figure 8-2. Web client application user interface

    

     object[] results; int employeeID = -1; try {     employeeID = int.Parse(employeeIDTextBox.Text); } catch (Exception) { } // execute the GetPurchaseOrder(  ) method passing in either null or // an Employee ID if (employeeID != -1)     results = proxy.GetPurchaseOrder(employeeID); else     results = proxy.GetPurchaseOrder(System.Data.SqlTypes.SqlInt32.Null); // iterate over the array of objects returned from the web service and // handle each of them foreach (object o in results) {     if (o.GetType(  ).IsPrimitive)         MessageBox.Show("SP Return Code = " + o);     else         switch (o.GetType().ToString(  ))         {             case "System.Data.DataSet":                 // cast the object to a DataSet and fill the DataGridView                 PODataGridView.DataSource = ((DataSet)o).Tables[0];                 break;             case "GetPurchaseOrderSoapApp.GetPurchaseOrderWS.SqlRowCount":                 MessageBox.Show("RowCount = " +                 ((GetPurchaseOrderSoapApp.GetPurchaseOrderWS.SqlRowCount)o).Count);                 break;         } } 

    
    

13. Execute the application. Enter a value for the Employee ID (for example, 244), or leave it blank to retrieve all employees. Click the Go button, and the DataGridView is filled with the DataSet returned by the web method. Results for Employee ID = 244 are shown in Figure 8-3.

Figure 8-3. Results for web service example

8.3.1. SQL Batches

Web-service endpoints can be configured to support ad hoc queries. The BATCHES language-specific argument for SOAP in the CREATE ENDPOINT and ALTER ENDPOINT configures this. You execute an ad hoc query by calling the sqlbatch( ) method of the HTTP endpoint, passing in the queries (multiple queries must be separated with semicolons) and any parameters.

In a SOAP SQL batch request, the SOAP <body> element contains a single, <sqlbatch>. This element has two child elements:

<BatchCommands>

Specifies the query, or multiple queries separated by semicolons (;).

<Parameters>

Specifies an optional list of parameters. Each parameter is specified as a <SqlParameter> child element of the <Parameters> element. For each parameter, you must pass the parameter name as the name attribute of the <Parameter> element and the parameter value as a <Value> child element of the <Parameter> element.

The following example demonstrates ad hoc query support by altering the preceding client. This new version returns the purchase orders for an employee using an ad hoc query instead of the GetPurchaseOrder( ) web method. Replace the code for the goButton_Click event handler with the following code:

 int employeeID = -1; try {     employeeID = int.Parse(employeeIDTextBox.Text); } catch (Exception) { } // execute the query only if a valid employee ID is entered if (employeeID != -1) {     GetPurchaseOrderWS.GetPurchaseOrderEndpoint proxy =         new GetPurchaseOrderWS.GetPurchaseOrderEndpoint(  );     proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;     // SQL parameterized command that returns purchase orders for an employee     string commandText = "SELECT * FROM Purchasing.PurchaseOrderHeader " +         "WHERE EmployeeID = @employeeID FOR XML AUTO";     // create the employee ID parameter     GetPurchaseOrderWS.SqlParameter[] parm =         new GetPurchaseOrderWS.SqlParameter[1];     parm[0] = new GetPurchaseOrderWS.SqlParameter(  );     parm[0].name = "employeeID";     parm[0].Value = employeeID;     parm[0].sqlDbType = GetPurchaseOrderWS.sqlDbTypeEnum.Int;     parm[0].direction = GetPurchaseOrderWS.ParameterDirection.Input;     // call the web service sqlbatch method to execute the ad hoc query     object[] results = proxy.sqlbatch(commandText, ref parm);     // iterate over the array of objects returned from the web service and     // handle each of them     foreach (object o in results)     {         if (o.GetType(  ).IsPrimitive)             MessageBox.Show("Return Code = " + o);         else             switch (o.GetType().ToString(  ))             {                 case "System.Xml.XmlElement":                     // retrieve the XmlElement and convert to a DataSet                     System.Xml.XmlElement xmlResult = (System.Xml.XmlElement)o;                     System.Xml.XmlNodeReader xnr =                         new System.Xml.XmlNodeReader(xmlResult);                     DataSet ds = new DataSet(  );                     ds.ReadXml(xnr);                     PODataGridView.DataSource = ds.Tables[0];                     break;                 case "GetPurchaseOrderSoapApp.GetPurchaseOrderWS.SqlRowCount":                     MessageBox.Show("RowCount = " +                         ((GetPurchaseOrderSoapApp.GetPurchaseOrderWS.SqlRowCount)                         o).Count);                     break;             }     } } 

The results are the same as in the previous example, except that you must specify an employee ID.