"In the middle of difficulty lies opportunity" -- Albert Einstein

SQL Server 2005 is emerging from its final BETA cycle to become a full-fledged product offering very soon. The SQL Server BETA 2 offering, available from MSDN Subscriber Downloads, is both full - featured and fully functional at this point. Only a few items remain, mostly revolving around tuning and final optimization, and some user tweaks and minor bug fixes.

The list of new features available for use with SQL Server 2005 is long and exciting. For those interested in a good synopsis of these, I refer you to an excellent article on MSDN by Matt Nunn.

Native WebServices Support

One of the most interesting features that I'll explore is that you can now create WebServices in the database tier directly, without resorting to "add ons" or even the use of IIS at all. One of the many extensions to Transact SQL is the new CREATE ENDPOINT statement, which allows the developer to create an endpoint directly from SQL Server 2005, which hooks right into the Kernel Mode HTTP.SYS driver, exposing functions and stored procedures written either in T-SQL or native CLR methods/classes.

In this article, we will explore a sample US Zipcode database (which I've stripped down to only California and Florida to make the download size of the solution and sample MDF/LDF files more realistic). In this database I have a series of T-SQL Stored procedures that use the latitude and longitude provided with each zip code to perform calculations such as the number of miles distance between two zip codes. Using the CREATE ENDPOINT statement, we will expose these methods as WebService methods. Finally, we will create a small Visual Studio 2005 Winforms application that gets the WSDL from our HTTP endpoint, creates a WebReference, and has a series of test buttons and textboxes with which to enter zip code values and see the results of each method displayed in a DataGridView control on the form.

NOTE: If you need a free, complete US Zipcode database that includes Longitude / Latitude information, you can get one here.

Project Requirements

To work with the material in this article, you will need Visual Studio 2005 Beta 2, and SQL Server 2005 Beta 2 Developer Edition. This will not work with SQLEXPRESS as it does not support HTTP protocol or the CREATE ENDPOINT statement.

To start out with this kind of operation, we first need a database to work with. If you are following the example I've given, download the zip file below, unzip in the folder of your choice, and in SQL Server Management Studio, right click on the Database node in Object Explorer, choose Attach, and attach the zipcodes.mdf database file that is included. If you now check this new database under the Programmability / Stored Procedures node, you will see that my stored procedures are already there. (I've also included sql scripts for the ones used in this article)

Using the CREATE ENDPOINT Statement

The key T-SQL operation that exposes a database CLR method or stored procedure as an HTTP-addressable WebMethod is CREATE ENDPOINT. While this is a complex statement with a lot of options, the basics of using it to enable a webservice is not that difficult, and so I will show the working sample first:

In a nustshell, we say CREATE ENDPOINT, give it a name, supply optional authorization (for editing), declare its state (STARTED=its "on"), declare the type (HTTP for a WebService), give it a virtual path (this one would translate to "http://localhost/sql/zipcodes"), provide the authentication type (required), the listening ports (80 is default for WebService), and the site name, then "FOR SOAP" says we want SOAP XML with WSDL enabled, and next we declare each WebMethod that we want exposed, a name for it, and the underlying fully-qualified name of the stored procedure or function to be run for it. Finally, we specify WSDL; in this case I'm enabling BATCHES (custom sql queries), and the optional name of the underlying database.

After you run this against the Zipcodes database, you should be able to open your browser to "http://localhost/sql/zipcodes?WSDL" and see the generated WSDL contract right out of SQL Server 2005. But don't bother looking for the "sql" Virtual Directory in IIS, because it won't be there. SQL Server 2005 talks directly to the HTTP.SYS driver.

When an endpoint is created, only members of the sysadmin role and the owner of the endpoint can connect to the endpoint. You must grant connect permission for users to access your endpoint; this is accomplished by executing the following statement:

Clients on non-Microsoft platforms can connect to SQL Server by using either BASIC or SQL Auth. However, using BASIC or SQL Auth requires the channel to be secure, so users can connect only on ports that also have SSL enabled. SSL is one of the options in the CREATE ENDPOINT statement. In passing, I would guess that Database Administrators and Network Admins will be pleased with this new enhanced level of security, although of course in practice, there is nothing to stop an enterprising developer - type from granting connect authorization to the ASP.NET or IUSR_<MACHINENAME> account either.

What WebService method calls return

Depending on how you've configured your CREATE ENDPOINT statment(s) and WSDL mappings, WebService calls to these WebService proxies return an object array that has a number of elements including the row count, errrors, and finally, if your method returns a resultset, a DataSet as well. IN my sample implementation, I am not really interested in exploring all this extra baggage so I simply iterate over the object array with a foreach statement, testing for the first object of type DataSet, and assign that to my DataGridView control's DataSource property. In this way, I can re-use the same DataGridView control for every Webmethod call on the Form. The technique for making such a call, once one has added the required WebReference to the project, is as follows:

private void button1_Click(object sender, EventArgs e)
{
  localhost.zipcodes q = new localhost.zipcodes();
  q.Credentials = System.Net.CredentialCache.DefaultCredentials;
  DataSet ds = new DataSet();
  object[] o = q.DistanceBetweenzZipCodes(txtZip1.Text, txtZip2.Text);
foreach (object oo in o)
 {
    if(oo is DataSet) ds = (DataSet)oo;
 }

  this.dataGridView1.DataSource = ds.Tables[0];
}

NOTE: When using this sample database, please bear in mind that the zip codes run from 32003 through 34997 in Florida, and from 90001 through 96162 in California. Test entries with zipcodes that don't exist in the provided sample database will create errors.

Conclusion

SQL Server 2005 opens up a whole new paradigm for distributed and cross-platform scalable application development. The ability to expose business objects at the database tier directly into WebServices is an enahancement that is sure to get lots of play in the near future.

download the Visual Studio 2005 Solution that accompanies this article