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:
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.
Figure 1: Object Browsing the Web Service in Visual Studio.NET 2005
Table 1: Arguments Used in the AS HTTP Clause
|PATH||The virtual URL path on the server where the Web service will reside|
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.