Creating Native Web Services in SQL Server

CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

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.

Software Requirements

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.

Calling the Native Web Service

Finally, you will call the Employees_Select web method. Drag and drop a GridView control on the default web form. The Load event of the form is shown in Listing 3.

using localhost;
using System.Net;

private void Page_Load(object sender, EventArgs e)
{
   Employees_Select_EndPoint proxy =
      new Employees_Select_EndPoint();
   proxy.Credentials = CredentialCache.DefaultCredentials;
   object[] results=proxy.Employees_Select();
   DataSet ds=(DataSet)results[0];
   GridView1.DataSource = ds;
}

Listing 3: Calling SQL Server Native Web Service

The code imports the namespace for the proxy class—localhost. An object of the proxy class is created in the Load event handler. Note that you have called your endpoint Employees_Select_EndPoint, so the same name is given to the proxy class. Then, the Credentials property of the proxy class is set to the DefaultCredentials property of the CredentialCache class. The CredentialCache resides in the System.Net namespace and allows you to pass user credentials to the proxy. Recollect that when creating the endpoint, you specified the AUTHENTICATION mode as INTEGRATED. The DefaultCredentials property returns the Windows credentials of the current user.

Then, the code calls the Employees_Select() method on the proxy. The return value of Employees_Select() method is an object array that contains two elements. The first element contains the actual return value as returned by the web method, and the second parameter is of type SqlRowCount. The Count property of the SqlRowCount class tells you the number of rows returned by the web method.

The records returned by the SELECT query are received as a DataSet object in .NET applications. Hence, the code type casts the first element of the array to DataSet. Finally, the DataSet is bound to the GridView. Figure 2 shows a sample run of the web form.

Figure 2: The web form in action.

Summary

SQL Server allows you to create native web services. By using this feature, you can expose your data directly on the web. The first step in creating native web services is to create stored procedures or functions that will be called over the web. The CREATE ENDPOINT statement then allows you to create web methods on top of the stored procedures. Finally, the web methods can be called from a client application.

About the Author

Bipin Joshi is the proprietor of BinaryIntellect Consulting, where he conducts premier training programs on a variety of .NET technologies. He wears many hats, including software consultant, mentor, prolific author, webmaster, Microsoft MVP, and a member of ASPInsiders. Having adopted the Yoga way of life, Bipin also teaches Kriya Yoga to interested individuals. He can be reached via his blog at www.bipinjoshi.com.