New HTTP Endpoints Create SQL Server 2005 Web Services, Page 2

Providing a Source for Data

The first order of business is to create the code components that will be the source of information for the endpoint. I create two stored procedures and one user-defined function (UDF), as shown in Listing 1:

Listing 1

USE AdventureWorks
GO
CREATE PROCEDURE prProductList
AS
SELECT   Product.ProductID, Product.ProductNumber, Product.Name
FROM     Production.Product AS Product
         INNER JOIN Production.ProductInventory AS Inventory
             ON Product.ProductID = Inventory.ProductID
         INNER JOIN Production.Location AS Location
             ON Inventory.LocationID = Location.LocationID
WHERE    Product.ListPrice > 0
 AND     Location.LocationID = 6 --Products from Misc Storage only
ORDER BY Product.Name
GO
CREATE PROCEDURE prProductStockInfo
    @ProductID int
AS
IF (SELECT ListPrice FROM Production.Product 
    WHERE ProductID = @ProductID) = 0 
 BEGIN
    RAISERROR ('Product not available for retail sale', 11, 1)
 END
ELSE
 BEGIN
    SELECT  Product.ProductID, Product.ProductNumber, 
            Product.Name, Product.Weight, 
            Product.WeightUnitMeasureCode,
            Product.ListPrice, Inventory.Quantity
    FROM    Production.Product AS Product
        INNER JOIN Production.ProductInventory AS Inventory
            ON Product.ProductID = Inventory.ProductID
        INNER JOIN Production.Location AS Location
            ON Inventory.LocationID = Location.LocationID
    WHERE   Product.ProductID = @ProductID
     AND    Location.LocationID = 6 --Misc Storage
 END
GO
CREATE FUNCTION dbo.fnProductPhoto (@ProductID int)
RETURNS varbinary(MAX)
AS
 BEGIN
    DECLARE @largePhoto varbinary(max)

    SELECT  @largePhoto = ProdPhoto.LargePhoto 
    FROM    Production.ProductPhoto AS ProdPhoto
    INNER JOIN Production.ProductProductPhoto ProdProdPhoto
        ON ProdPhoto.ProductPhotoID = ProdProdPhoto.ProductPhotoID
    WHERE   ProdProdPhoto.ProductID = @ProductID

    RETURN (@largePhoto)
 END

The first procedure returns a list of products (product ID, name, and number) from the AdventureWorks database. The second procedure returns more details about a product, including inventory levels. The UDF returns a single varbinary value containing the product photo.

Creating the Endpoint

Once you have your procedures and functions set up, you can create the endpoint (Web service). The following code creates the endpoint on my server:

CREATE ENDPOINT SQLEP_AWProducts
    STATE = STARTED
AS HTTP
(
    PATH = '/AWproducts',
    AUTHENTICATION = (INTEGRATED),
    PORTS = (CLEAR),
    SITE = 'win2k301'
)
FOR SOAP
(
    WEBMETHOD 'ProductList'
        (NAME='AdventureWorks.dbo.prProductList'),
    WEBMETHOD 'ProductStockInfo'
        (NAME='AdventureWorks.dbo.prProductStockInfo'),
    WEBMETHOD 'ProductPhoto'
        (NAME='AdventureWorks.dbo.fnProductPhoto'),
    BATCHES = DISABLED,
    WSDL = DEFAULT,
    DATABASE = 'AdventureWorks',
    NAMESPACE = 'http://Adventure-Works/Products'
)

Believe it or not, you are now ready to consume this Web service. Before doing that, though, examine the CREATE ENDPOINT statement. Starting from the top, the first thing you will notice is the name of the endpoint, SQLEP_AWProducts, which is how you refer to the Web service within your client code (see Figure 1). PATH is the virtual path on the server for the Web service. So, for this example, you would access the Web service at http://win2k301/AWproducts, but it would be called SQLEP_AWProducts in your .NET application code. Table 1 provides explanations for this and other arguments used in the AS HTTP clause.

Click here for a larger image.

Figure 1: Object Browsing the Web Service in Visual Studio.NET 2005

Table 1: Arguments Used in the AS HTTP Clause

Argument	Description
PATH	The virtual URL path on the server where the Web service will reside
AUTHENTICATION	The method of authentication; can be BASIC, DIGEST, or INTEGRATED You should try to use INTEGRATED when possible, since it is the most secure. It will try to use Kerberos-based authentication if possible (otherwise, NTLM). DIGEST is not as secure as INTEGRATED. You should use it only if INTEGRATED authentication is not possible. BASIC authentication is the least secure. You should use it only if you can't implement either INTEGRATED or DIGEST authentication methods. BASIC requires SSL (see PORTS below).
PORTS	CLEAR (HTTP - port 80 by default) SSL (HTTPS - port 443 by default)
SITE	The name of the server on which the Web service is running

The STATE argument can have one of three values:

• STARTED—listening and responding
• DISABLED—neither listening nor responding
• STOPPED—listening, but returns errors to client requests

You can change the state of an existing Web service using the ALTER ENDPOINT statement. For example, to disable the endpoint, you can execute:

ALTER ENDPOINT SQLEP_AWProducts STATE = DISABLED

The FOR SOAP clause determines which methods the Web service will expose, which other features are exposed (dynamic SQL, for example), and other characteristics of the Web service. In this example, three methods are exposed: the two stored procedures and the UDF shown in Listing 1. WEBMETHOD requires the name of the exposed method as its first argument. As you can see in the code, it does not have to match the name of the actual stored procedure or UDF that it is exposing. The next argument of WEBMETHOD is the fully qualified NAME of the object you are exposing.

The BATCHES argument specifies whether or not the Web service supports ad hoc queries. WSDL indicates how the WSDL document generation will occur; DEFAULT indicates that the WSDL document will be created automatically. SQL Server 2005 allows you to implement custom WSDL generators using the CLR-based abilities of SQL Server 2005 (see Books Online for more details). DATABASE is the name of the database that will be accessed from the endpoint (endpoints are defined at the server level). And finally, NAMESPACE is, well, the namespace for the endpoint.

You have now officially created a Web service using SQL Server 2005. Congratulations.