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: 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 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: From the Visual Studio 2005 menu, select File New Project. Specify where you want to save the project. Name the project GetPurchase-OrderSoapApp. Click OK. In the Solution Explorer window, right-click References and select Add Web Reference from the context menu. 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. Change the Web reference name listbox to GetPurchaseOrderWS. Click the Add Reference button. Double-click Form1 in Solution Explorer to open the Form Designer. Add a DataGridView control to the form. Uncheck the Enable Adding, Enable Editing, and Enable Deleting checkboxes. Set the Name property to PODataGridView. Add a Label control to the form. Set the Text property to Employee ID:. Add a TextBox control to the form. Set the Name property to employeeIDTextBox. 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. Double-click the goButton to add a click event handler named goButton_Click for the button. 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; } } 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. |