"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.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. 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:
USE ZIPCODES
GO
-- add stored procedures here
with typical T-SQL
-- or, they can be added separately before running this script
IF EXISTS
(
SELECT name from sys.http_endpoints
WHERE name = 'zipcodes'
)
DROP ENDPOINT zipcodes
GO
CREATE ENDPOINT zipcodes -- create the Http Endpoint
AUTHORIZATION sa --
with optional authorization for owner
STATE = STARTED --
the state of the endpoint
AS HTTP ( -- can be http, tcp,
...
path='/sql/zipcodes', -- the virtual path
AUTHENTICATION=(INTEGRATED), -- type of authentication
PORTS=(CLEAR), -- which ports (clear=all)
SITE ='*' -- site can be '*" or
'myserver'
)
FOR SOAP( -- type of
protocol, in this case, SOAP for a webservice
WEBMETHOD 'http://tempuri.org/'.'DistanceBetweenzZipCodes'
--
define the webmethod(s) and fully qualified sproc
(name='zipcodes.dbo.DistanceBetweenZipcodes',
SCHEMA = STANDARD ),
WEBMETHOD 'http://tempuri.org/'.'GetCityStateByZipCode'
(name='zipcodes.dbo.GetCityStateByZipCode',
SCHEMA = STANDARD
),
WEBMETHOD 'http://tempuri.org/'.'GetRecordsByAreaCode'
(name='zipcodes.dbo.GetRecordsByAreaCode',
SCHEMA = STANDARD ),
WEBMETHOD 'http://tempuri.org/'.'GetZipsByCityState'
(name='zipcodes.dbo.GetZipsByCityState',
SCHEMA = STANDARD
),
WEBMETHOD 'http://tempuri.org/'.'ZipRecordsByRange'
(name='zipcodes.dbo.ZipRecordsByRange',
SCHEMA = STANDARD
),
WSDL = DEFAULT, -- Generate WSDL upon request , e.g. http://localhost/sql/zipcodes?WSDL
BATCHES=ENABLED, -- enable batch (custom)
queries
DATABASE='ZIPCODES' -- underlying database
)
GO |
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:
use master
GRANT CONNECT ON ENDPOINT::zipcodes TO privilegedUser
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
|