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.

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.



About the Author

Bipin Joshi

Bipin Joshi is a blogger and writes about apparently unrelated topics - Yoga & technology! A former Software Consultant by profession, Bipin has been programming since 1995 and has been working with the .NET framework ever since its inception. He has authored or co-authored half a dozen books and numerous articles on .NET technologies. He has also penned a few books on Yoga. He was a well known technology author, trainer and an active member of Microsoft developer community before he decided to take a backseat from the mainstream IT circle and dedicate himself completely to spiritual path. Having embraced Yoga way of life he now codes for fun and writes on his blogs. He can also be reached there.

Comments

  • mulberry black hetty hobo bag

    Posted by hekdbidxntest on 11/22/2012 05:55am

    ugg amorosa no sabemos cuánto tiempo estuvo upthere Tal vez uno de los desarrollos más atrevido en estilo mucho este año, más que las botas de la rodilla son quizás un toque dramático y al gusto de la mayoría de las chicas no incluidos directamente ugg baratas de la industria del estilo. A pesar de no contar con los talones, estas botas son una contraprestación que ase, sexy, y un solo cien% vanguardia de la moda. Podrían ser usados, así, así es que el http://www.botasuggaustraliavip.com deseo de poner en ellos: con botas ugg mini faldas, más que su par preferido de jeans ajustados, con pantalones cortos, o más de polainas.. Tutto è un movimento ondulatorio perfetto, ven l'argento vivo, come un serpente, vedi Una Caviglia, Gomito un, seno de la ONU, ginocchio un, e tutto si fonde in un gigantesco insieme, Provocante, con magnifici occhi sorridenti, bocca leggermente piegata en giù, labbra atteggiate in modo che Sembrano scoppiare in una risata alla tua sensazione di impotenza. vestirsi E sanno, ei loro Lunghi capelli incendiano l'aria. Troppo di tutto, Accidenti .

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Is your sales and operations planning helping or hurting your bottom line? Here are 5 useful tips from the experts at Quintiq to guide you to a better S&OP strategy.

  • Live Event Date: July 30, 2014 @ 11:00 a.m. ET / 8:00 a.m. PT You may already know about some of the benefits of Bluemix, IBM's open platform for developing and deploying mobile and web applications. Check out this upcoming eSeminar that focuses on building an Android application using the MobileData service, with a walk-through of the real process and workflow used to build and link the MobileData service within your application. Join IBM's subject matter experts as they show you the way to build a base …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds