Expose Stored Procedures through HTTP Endpoints.

With SQL Server 2005 developers will be able to develop Web services in the database tier, making SQL Server a hypertext transfer protocol (HTTP) listener and providing a new type of data access capability for Web services-centric applications.

To begin, you need to be running Windows 2003 or Windows XP SP2 to use this feature and you don’t need IIS.

My goal is pretty simple. I want to expose a stored procedure, called GetContacts, in my AdventureWorks Database as an XML Web Service without requiring IIS.

CREATE PROCEDURE [dbo].[GetContacts]

AS

BEGIN

    SET NOCOUNT ON;

 

    SELECT TOP 20 [FirstName],[LastName] FROM

       [Person].[Contact] ORDER BY [LastName];

END

Setp1. Create HTTP Endpoint

CREATE ENDPOINT AW_Contacts_Test

    STATE = Started

AS HTTP

    (

        PATH = ‘/MyContacts’,

        AUTHENTICATION = (INTEGRATED),

        PORTS = (CLEAR),

                CLEAR_PORT = 8045,      –used to specify some port for the web service

        SITE = ‘*’

    )

FOR SOAP

    (

        WEBMETHOD ‘GetContacts’

            (NAME = ‘AdventureWorks.dbo.GetContacts’),

        WSDL = DEFAULT,

        DATABASE = ‘AdventureWorks’,

        NAMESPACE = DEFAULT

    )

 

A Path = ‘/Mycontacts’, Site = ‘*’, and Ports = (CLEAR) means that your URL will be to the default host, localhost, not require SSL, and look like the following:

 

http://localhost/Mycontacts?WSDL

 

Setp2. Consuming the Web Service/Stored procedure in ASP.NET

Add a web reference in the web application using the URL: http://localhost/Mycontacts?WSDL and mention the Web Reference name as localhost.

Then write the code to access the web method wherever required as:

localhost.AW_Contacts_Test mySPAsWebService =

new localhost.AW_Contacts_Test();

 

DataSet ds = new DataSet();

//To pass the default credentials as per the app pool

mySPAsWebService.Credentials =

                              System.Net.CredentialCache.DefaultCredentials;

object[] result = mySPAsWebService.GetContacts();

foreach (object obj in result)

{

if(obj.GetType().Equals(typeof(System.Data.DataSet)))           

      ds = (DataSet)obj;

}

 

 //Populating the grid view with the results provided by the stored procedure

 GridView1.DataSource = ds.Tables[0];

 GridView1.DataBind();

Note:  The account being used in the app pool which the web application running under, must have the connect permissions to that created end point.

Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

One Response to Expose Stored Procedures through HTTP Endpoints.

  1. David says:

    Hey man…
    that’s really a cool technique…!

    Thanks
    David B.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s