Creating Native Web Services in SQL Server
If you are a .NET developer, by this time you probably worked with ASP.NET Web Services. However, ASP.NET is not the only framework that allows you to develop web services. SQL Server 2005 and 2008, for example, allow you to create web services. SQL Server native web services can come in handy when you want to expose your application data over the web irrespective of the type of client applications that consume it. This way, you need not develop another layer of ASP.NET web services. This not only simplifies your architecture but also reduces deployment efforts. This article throws light on how this feature of SQL Server can be harnessed to expose your data directly over HTTP without having to worry about another web service layer or other network connectivity issues.
To work through the examples presented in this article, you should have:
- SQL Server 2005 or 2008 Developer or Enterprise Editions. The express edition doesn't support creation of native web services.
- Northwind sample database with Employees table.
- Visual Studio 2005/2008 or Visual Web Developer (Express editions will do).
Creating a Stored Procedure
The first step in creating a native web service is to create stored procedures or functions that you want to call over HTTP. As an example, you will create a stored procedure named Employees_Select that retrieves all the records from the Employees table. The complete T-SQL script of the stored procedure is given in Listing 1.
CREATE PROCEDURE Employees_Select AS SELECT EmployeeID, FirstName, LastName, BirthDate FROM Employees
Listing 1: Creating the Employees_Select stored procedure
The Employees_Select stored procedure fetches the EmployeeID, FirstName, LastName, and BirthDate columns of the Employees table.
Creating an HTTP Endpoint
The next step is to create an HTTP endpoint for your stored procedure. An HTTP endpoint is an interface through which the client applications can access the web service. By default, endpoints are created on port 80. If the same machine is running any other application, say Internet Information Services (IIS), that is already using the same port, you will receive an error when creating the endpoint. The CREATE ENDPOINT allows you to define an HTTP endpoint. Listing 2 illustrates how this is done.
CREATE ENDPOINT Employees_Select_EndPoint STATE=STARTED AS HTTP ( PATH = '/SQL/Employees_Select', AUTHENTICATION=(INTEGRATED), PORTS = (CLEAR) ) FOR SOAP ( WEBMETHOD 'Employees_Select' ( NAME='Northwind.dbo.Employees_Select', SCHEMA=STANDARD), WSDL=DEFAULT, DATABASE='Northwind' )
Listing 2: Creating an HTTP Endpoint
The CREATE ENDPOINT statement above uses many options:
- The CREATE ENDPOINT statement first specifies an endpoint name to be created. You name your endpoint Employees_Select_EndPoint
- The clause specifies the state of the endpoint. The possible states are STARTED, STOPPED, and DISABLED. Because you wany to use the web service, you specify it as STARTED.
- The clause specifies that this endpoint will be used over an HTTP channel. Alternatively, you could have used TCP as a transport protocol.
- Then, the script gives some more information about the transport protocol. The PATH clause specifies the URL that identifies the location of the endpoint on the host computer. In your case, you specify it as /sql/Employees_Select.
- The AUTHENTICATION mode that will be used while consuming this endpoint will be INTEGRATED.
- The PORTS clause specifies listening port types associated with the endpoint. The value of CLEAR indicates that the incoming request must come over HTTP. If you specify SSL instead, the request must come over HTTPS.
- The FOR SOAP clause indicates that the payload of the web service will be in SOAP format.
- The WEBMETHOD clause specifies the name of web method being exposed.
- The WEBMETHOD clause must be accompanied by the NAME of the web method. The NAME consists of three parts: name of the database, name of the owner, and name of the stored procedure or function that you intend to expose as a web-callable method.
- The SCHEMA clause governs whether an inline schema information will be returned in the SOAP responses. The value of STANDARD indicates that the schema will not be returned.
- The WSDL clause specifies whether WSDL (Web Service Description Language) document generation is supported for this endpoint. If set to NONE, no WSDL response is generated. If set to DEFAULT, a WSDL response is generated and returned for WSDL queries submitted to the endpoint.
- Lastly, the DATABASE clause specifies the name of the database.
To actually create the Employees_Select_EndPoint endpoint, execute the script from Listing 2 above in SQL Server Management Studio.
Creating a Proxy for the Endpoint
In the previous section, you created an Employees_Select_EndPoint endpoint that exposes the Employees_Select web method. Now, develop a simple web site that consumes the Employees_Select web method.
Use Visual Studio to create a new web site. Before you consume the web method in the client application, however, you need to create a proxy for it. To create the proxy, right-click on the newly created web site in the solution explorer and choose "Add Web Reference...". Doing so will open a dialog, as shown in Figure 1.
Figure 1: Adding a web reference
Notice the URL specified in the "Add Web Reference" dialog. This URL is constructed using the PATH option of CREATE ENDPOINT statement. At the end of the URL, you need to append the WSDL query string parameter. This way, SQL Server will return the WSDL document for your web service. See how the "Add Web Reference" dialog displays the Employees_Select web method. Click the "Add Reference" button to create a proxy for the web service.