January 27, 2021
Hot Topics:

Creating Native Web Services in SQL Server

  • By Bipin Joshi
  • Send Email »
  • More Articles »

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.


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.

Page 2 of 2

This article was originally published on August 25, 2008

Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Thanks for your registration, follow us on our social networks to keep up-to-date