Web Services and Microsoft SQL Server 2005

Microsoft SQL Server 2005 supports creating Web Services and storing data to be used in Web Services at a few different levels. With the addition of direct support in SQL Server for HTTP, we could think of SQL Server 2005 as a "Web Services server." This reduces the three-tier architecture usually required to support Web Services (database, middle tier, and client) to two-tier architecture, with stored procedures or XQuery/XSLT programs being used as a middle tier.

First of all create a stored procedure which will fetch some data from a table or a set of table. Assume the name of the stored procedure is usp_GetAllContacts() from a table called contacts.

The Stored procedure will be like this.
Create Procedure usp_GetAllContacts
AS
BEGIN
……Select Statement and business logic comes here.
END
Go

After creating this stored procedure next comes, exposing this as web service so that we can consume the same.

• Syntax for creating Web service:

CREATE ENDPOINT endPointName [ AUTHORIZATION login ]
STATE = { STARTED | STOPPED | DISABLED }
AS { HTTP | TCP } (
<protocol_specific_arguments>
)
FOR { SOAP | TSQL | SERVICE_BROKER | DATABASE_MIRRORING } (
<language_specific_arguments>
)

<AS HTTP_protocol_specific_arguments> ::=
AS HTTP (
PATH = '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 } ]
[, COMPRESSION = { ENABLED | DISABLED } ]
)

<AS TCP_protocol_specific_arguments> ::=AS TCP (
LISTENER_PORT = listenerPort
[ , LISTENER_IP = ALL | (<4-part-ip> | <ip_address_v6> ) ]
)

<FOR SOAP_language_specific_arguments> ::=
FOR SOAP(
[ { WEBMETHOD [ 'namespace' .] 'method_alias'
( NAME = 'database.owner.name'
[ , SCHEMA = { NONE | STANDARD | DEFAULT } ]
[ , FORMAT = { ALL_RESULTS | ROWSETS_ONLY } ]
)
} [ ,...n ] ]
[ BATCHES = { ENABLED | DISABLED } ]
[ , WSDL = { NONE | DEFAULT | 'sp_name' } ]
[ , SESSIONS = { ENABLED | DISABLED } ]
[ , LOGIN_TYPE = { MIXED | WINDOWS } ]
[ , SESSION_TIMEOUT = timeoutInterval | NEVER ]
[ , DATABASE = { 'database_name' | DEFAULT }
[ , NAMESPACE = { 'namespace' | DEFAULT } ]
[ , SCHEMA = { NONE | STANDARD } ]
[ , CHARACTER_SET = { SQL | XML }]
[ , HEADER_LIMIT = int ]
)
<FOR SERVICE_BROKER_language_specific_arguments> ::=
FOR SERVICE_BROKER (
[ AUTHENTICATION = {
WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
| CERTIFICATE certificate_name
| WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] CERTIFICATE certificate_name
| CERTIFICATE certificate_name WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
} ]
[ , ENCRYPTION = { DISABLED | SUPPORTED | REQUIRED }
[ ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ]
]
[ , MESSAGE_FORWARDING = { ENABLED | DISABLED* } ]
[ , MESSAGE_FORWARD_SIZE = forward_size ]
)

<FOR DATABASE_MIRRORING_language_specific_arguments> ::=
FOR DATABASE_MIRRORING (
[ AUTHENTICATION = {
WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
| CERTIFICATE certificate_name
} ]
[ [ , ] ENCRYPTION = { DISABLED |SUPPORTED | REQUIRED }
[ ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ]
]
[,] ROLE = { WITNESS | PARTNER | ALL }
)


Example:

CREATE ENDPOINT Contact
STATE = Started
AS HTTP
(
PATH = '/Contact',
PORTS = (CLEAR),
SITE = '*',
AUTHENTICATION = (INTEGRATED)
)
FOR SOAP
(
WEBMETHOD 'urn:autos'.'GetAllContacts'
(NAME = 'TempDB.dbo.GetAllContacts', SCHEMA=DEFAULT, FORMAT = ALL_RESULTS),
WSDL=DEFAULT,
SCHEMA=STANDARD,
DATABASE = 'TempDB’
NAMESPACE = 'urn:autos
)
GO


Through this way we can expose the stored procedure as Web service. This web service can be called by any application which got the feature of consuming web services. You can view the WSDL of this web service by giving the SQL Server URL and the web service name. In this scenario “http://VS2005/Contact?WSDL” will be the URL. You will get the WSDL detail of the web service, if you give this URL to your browser like Internet explorer or any web browser you are using. The ENDPOINT syntax got many features, which I believe you yourself will explore.

Consuming Microsoft SQL Server 2005 web service in a ASP .NET Application
Open the ASP .Net web application in Visual Studio 2005. Open the solution explorer and right click the project. There you can see Add Web reference. Just add the web reference menu and a dialog box will come. In that you type the URL of the web service, for e.g.:- “http://VS2005/Contact?WSDL”. You can see the web methods defined in the concerned web service. Click “Add reference” button and that’s all, you included the web service. It is a straight away method of consuming the web service in visual studio IDE.
Another method is using proxy classes which can be created by the command, wsdl in .net command prompt. The syntax is wsdl “<URL of the web service>”. This will create a class file. We can add the respective class file to our ASP .net web application. This class file will take care of the job for calling the web service. Web methods of the web service and its details like parameters, parameter type, port, authentication type etc are defined in this class file. If you don’t have IDE like visual studio, then this proxy class file can be used for consuming the web service.
In END POINT syntax, we should give much importance to the parameter FORMAT = ALL_RESULTS. By default, the parameter will be “rowset” and in return well get only datasets. If we want to get all the details like SQL message, row count etc then we should use ALL_RESULT. When parameter is ALL_RESULT, it will return array of objects. The T-SQL statement I used to fetch the values from database is something like that

“SELECT ID, FIRSTNAME, LASTNAME from CONTACTS for xml auto, ELEMENTS XSINIL, ROOT('Contacts')”

This will give us a result like this:
<Contacts xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<Contact>
<Id>5</Id>
<FirstName>Rama</FirstName>
<LastName>Krishnan</LastName>
</Contact>
</Contacts>

C# code to read this information is:-
XmlElement objXmlElement = null;
foreach (object result in objObject)
{
if (result is XmlElement)
{
objXmlElement = (XmlElement)result;
}
}

DataSet objDataSet = new DataSet();
Stream objXmlStream = new MemoryStream(ASCIIEncoding.Default.GetBytes(objXmlElement.InnerXml));
objDataSet.ReadXml(objXmlStream, XmlReadMode.Auto);

After fetching the data into the dataset you can use this to directly bind it to a data grid or combo box
objDatagrid.DataSource = objDataSet;
objDataGrid.DataBind();


All values will be populated in the data grid.

• Deleting Web Service in SQL Server

DROP ENDPOINT <Web Service Name>

• Altering Web Service in SQL Server

ALTER ENDPOINT endPointName
[ AFFINITY = { NONE | <64bit_integer> | ADMIN } ]
[ STATE = { STARTED | STOPPED | DISABLED } ]
AS { TCP | HTTP } (
<protocol specific items>
)
FOR { SOAP | TSQL | SERVICE_BROKER | DATABASE_MIRRORING } (
<language specific items>
)

<AS HTTP_protocol_specific_arguments> ::=
AS HTTP (
PATH = 'url'
, PORTS = ( { CLEAR | SSL } [ ,...n ] )
[ SITE = { '*' | '+' | 'webSite' } , ]
[ , CLEAR_PORT = clearPort ]
[ , SSL_PORT = SSLPort ]
, AUTHENTICATION = ( { BASIC | DIGEST | NTLM | KERBEROS | INTEGRATED } [ ,...n ] )
[ , AUTH_REALM = { 'realm' | NONE } ]
[ , DEFAULT_LOGON_DOMAIN = { 'domain' | NONE } ]
[ , COMPRESSION = { ENABLED | DISABLED } ]
)

<AS TCP_protocol_specific_arguments> ::=
AS TCP (
LISTENER_PORT = listenerPort
[ , LISTENER_IP = ALL | (<4-part-ip> | <ip_address_v6> ) ]
)

<FOR SOAP_language_specific_arguments> ::=
(
[ { ADD WEBMETHOD [ 'namespace' .] 'method_alias'
( NAME = 'database.owner.name'
[ , SCHEMA = {NONE | STANDARD | DEFAULT } ]
[ , FORMAT = { ALL_RESULTS | ROWSETS_ONLY } ]
)
} [ ,...n ] ]
[ { ALTER WEBMETHOD [ 'namespace' .] 'method_alias'
( NAME = 'database.owner.name'
[ , SCHEMA = {NONE | STANDARD | DEFAULT} ]
[ , FORMAT = { ALL_RESULTS | ROWSETS_ONLY } ]
)
} [ ,...n] ]
[ { DROP WEBMETHOD [ 'namespace' .] 'method_alias' } [ ,...n ] ]
[ BATCHES = { ENABLED | DISABLED } ]
[ , WSDL = { NONE | DEFAULT | 'sp_name' } ]
[ , SESSIONS = { ENABLED | DISABLED } ]
[ , SESSION_TIMEOUT = int ]
[ , DATABASE = { 'database_name' | DEFAULT }
[ , NAMESPACE = { 'namespace' | DEFAULT } ]
[ , SCHEMA = { NONE | STANDARD } ]
[ , CHARACTER_SET = { SQL | XML } ]
)

<FOR SERVICE_BROKER_language_specific_arguments> ::=
FOR SERVICE_BROKER (
[ AUTHENTICATION = {
WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
| CERTIFICATE certificate_name
| WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ] CERTIFICATE certificate_name
| CERTIFICATE certificate_name WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
} ]
[ , ENCRYPTION = { DISABLED | SUPPORTED | REQUIRED }
[ ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ]
]

[ , MESSAGE_FORWARDING = ENABLED | DISABLED* ]
[ , MESSAGE_FORWARD_SIZE = forwardSize
)

<FOR DATABASE_MIRRORING_language_specific_arguments> ::=
FOR DATABASE_MIRRORING (
[ AUTHENTICATION = {
WINDOWS [ { NTLM | KERBEROS | NEGOTIATE } ]
| CERTIFICATE certificate_name
} ]
[ [ , ] ENCRYPTION = { DISABLED |SUPPORTED | REQUIRED }
[ ALGORITHM { RC4 | AES | AES RC4 | RC4 AES } ]
]
[ , ] ROLE = { WITNESS | PARTNER | ALL }
)


Happy Programming!!!!