Introduction
In this article, I will explain how we can publish a SQL Server 2005 stored procedure as a web service and access this web service from a Microsoft VB.NET Windows application.
Background
Microsoft started addressing the need of XML support for SQL Server from the release of SQL Server 2000 version. The initial version of SQL Server 2000 shipped with many XML features. Later on, Microsoft released SQLXML 3.0 which provided the facility to publish TSQL batch statement, SQL stored procedures and SQL functions as webservices.
We no longer require SQLXML 3.0 since SQL Server 2005 version supports this natively.
Using Northwind Database
I have used the Northwind database to create the stored procedure used in this article. 2005 version of the SQL Server doesn't come with Northwind and pub database. However you can download the Northwind and pub database from this link.
The download contains the *.mdf and *.ldf files. You can attach this to your SQL Server 2005.
How to Publish your Stored Procedure as a Webservice?
Publishing your stored procedure as webservice is very easy and involves simple steps.
I created a stored procedure named GetProducts
this will accept product id as parameter. This procedure will retrieve the information from the products
table based on the ProductId
supplied.
Create PROC GetProduct
(
@ProductId int
)
As
Select * from
Products
where
ProductId =@ProductId
In the next step, we will see how we can publish GetProduct
as a webservice. We will use CREATE ENDPOINT TSQL
statement for this. Endpoint
s are objects that represent communication between a server and client or the point through which SQL Server communicates with the outside world.
SQL Server 2005 automatically creates an Endpoint
for each of the four protocols (TCP/IP, Shared Memory, Named Pipe, VIA) that accept TDS connections. The HTTP endpoint
type will enable the SQL Server to provide support for Webservices.
To expose our stored procedure as Webservice, we will create an HTTP endpoint
and publish the stored procedure as a web method to it.
CREATE ENDPOINT WebServiceTest
AUTHORIZATION [sa]
STATE = STARTED
AS HTTP
(
AUTHENTICATION = (INTEGRATED),
PATH = '/MyTestWebservicepath/',
PORTS = (CLEAR),
CLEAR_PORT = 8045,
SITE = '*',
)
FOR SOAP
(
WEBMETHOD 'urn:www.codeproject.com'.'GetProduct'
(
NAME = 'Northwind.dbo.GetProduct',
SCHEMA = STANDARD,
FORMAT = ALL_RESULTS
),
WSDL = DEFAULT,
BATCHES = DISABLED,
SCHEMA = STANDARD,
LOGIN_TYPE = WINDOWS,
SESSION_TIMEOUT = 100,
DATABASE = 'Northwind',
NAMESPACE = 'www.codeproject.com',
CHARACTER_SET = XML
)
You can add any number of stored procedures to an HTTP endpoint
.
Following is the syntax for adding a new stored procedure as web method to an existing Endpoint
:
ALTER ENDPOINT sql_endpoint FOR SOAP
(ADD WEBMETHOD 'YourWebMethod' (name='Northwind.dbo.YourWebMethod'));
For Your Reference
Following is the syntax for the create endpoint
:
CREATE ENDPOINT Name-Of-The-Endpoint
[AUTHORIZATION login information to sqlserver ]
STATE = { STARTED | STOPPED | DISABLED }
AS HTTP
(
PATH = 'PATH-TO-ACCES-WEBSERVICE-(YOUR SERVER NAME\PATH WILL BE THE ACTUAL URL)',
AUTHENTICATION =( { BASIC | DIGEST | INTEGRATED | NTLM | KERBEROS } [ ,...n ] ),
PORTS = ( { CLEAR | SSL} [ ,... n ] )
[ SITE = {'*' | '+' | 'WEBSITE' },]
[, CLEAR_PORT = CLEARPORT ]
[, SSL_PORT = SSLPORT ]
[, AUTH_REALM = { 'REALM' | NONE } ]
[, DEFAULT_LOGON_DOMAIN = { 'DOMAIN' | NONE } ]
[, RESTRICT_IP = { NONE | ALL } ]
[, COMPRESSION = { ENABLED | DISABLED } ]
[, EXCEPT_IP = (ALL | ( 4-part-ip ) | ( "ip_address_v6" ) )
)
FOR SOAP
(
[ { WEBMETHOD [ NAMESPACE.] 'method_alias'
( NAME = 'database.Schema.name'
[ , SCHEMA = { NONE | STANDARD | DEFAULT } ]
[ , FORMAT = { ALL_RESULTS | ROWSETS_ONLY } ]
)
} [ ,...n ] ]
[ BATCHES = { ENABLED | DISABLED } ]
[ , WSDL = { NONE | DEFAULT | 'PROCEDURE NAME' } ]
[ , SESSIONS = { ENABLED | DISABLED } ]
[ , LOGIN_TYPE = { MIXED | WINDOWS } ]
[ , SESSION_TIMEOUT = TIME-INRTERVEL | NEVER ]
[ , DATABASE = { 'DATABASE-NAME' | DEFAULT }
[ , NAMESPACE = { 'namespace' | DEFAULT } ]
[ , SCHEMA = { NONE | STANDARD } ]
[ , CHARACTER_SET = { SQL | XML }]
[ , MAX_SOAP_HEADERS_SIZE = { int | DEFAULT }]
)
Accessing SQL Server Webmethod from the VB.NET Application
We will create a new VB.NET application to access the webservice which we created in this example.
In the newly created application, add a web reference:
Type the URL http://WEBSERVER:[PORT-NUMBER]/PATH?wsdl.
I used http://localhost:8045/MyTestWebservicepath?wsdl since 8045 is my HTTP port and MyTestWebservicepath
is the path parameter mentioned in the create endpoint
statement.
Enter web reference name as Test.
Add a Windows Form to the project and place a Button
and a TextBox
control.
In the button click, write the following code:
//Create the instance of Web service Proxy
Dim MywebTest As New Test.WebServiceTest
//Object array for holding the result from the webserver
Dim dtsetArray() As Object
//Calling the Web Services
dtsetArray = MywebTest.GetEmployeeBasics(TextBox1.Text)
For Each obj As Object In dtsetArray
If obj.GetType Is GetType(DataSet) Then
dt = CType(obj, DataSet)
MsgBox(dt.Tables(0).Rows(0)(1))
End If
Next
The web webservice will return an array of objects which consist of the result dataset
and a SQL resultcount
which will give you the rows count.
Hope this will be a starting point for your SQL Server 2005 webservices.
Please leave your suggestions for improving this article.
History
- 30th September, 2007: Initial post