JavaScript Editor Ajax toolkit     Ajax tutorials 



Main Page

Previous Page
Next Page

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. In the New Project dialog box, select Visual C# in the tree view and Windows Application from the installed templates.

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

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

  5. 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.

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

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

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

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

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

  11. 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.

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

  13. 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;
            }
    }
    

  14. 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.


Previous Page
Next Page


JavaScript Editor Ajax toolkit     Ajax tutorials 
Affordable PACS Systems . Executive Leadership Coaching