The Magic of Native HTTP Support in SQL 2005

Download the Article
We will trip in this article around one of the new development feature in SQL 2005 which is a capability of delivering full functional web services .The HTTP endpoint is more than just an extension of XML abilities. It can return row sets, scalar values, messages and errors serialized into XML automatically. The technology utilized XML support sessions, and monitors SOAP connection and other database server connections.
 
open SQL Server 2005 to create stored procedures and add them to a HTTP endpoint to create a web service that is immediately deployable. The syntax for the web service is as under:
CREATE PROCEDURE GetEmployees
AS
SELECT *
FROM HumanResources.Employee
---------------------------------------------------
Create ENDPOINT EmployeesList
STATE=STARTED
AS HTTP
(
PATH= '/EmployeesList' ,
AUTHENTICATION=(INTEGRATED),
PORTS= (CLEAR) ,
CLEAR_PORT = 8088
)
FOR SOAP
(
WEBMETHOD 'EmployeesList' ( Name= 'AdventureWorks.dbo.GetEmployees'),
DATABASE = 'AdventureWorks',
WSDL =DEFAULT
)
 
Note:
1- I use here 'AdventureWorks' database if you hav’nt it you can download it from.
http://www.codeplex.com/MSFTDBProdSamples/Release/ProjectReleases.aspx?ReleaseId=4004
2- If both SQL Server 2005 and IIS are both listening to port 80 then we must add CLEAR_PORT = 8088 to change the default port that SQL Server 2005 is using to listen to HTTP Requests,the reference URL will be http://localhost:8088/employeeslist?wsdl

Once this is done, a reference can be added to the web service in C# project and it can be used. The syntax for adding the reference is
 
protected void btnGetEmployees_Click(object sender, EventArgs e)
{
EmployeeSqlServices.EmployeesList proxy = new EmployeeSqlServices.EmployeesList();
proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
Object[] emplist = proxy.CallEmployeesList();
DataSet emplistDs = (DataSet)emplist[0];
DataTable dt = emplistDs.Tables[0];
dgvemplistDs.DataSource = dt;
dgvemplistDs.DataBind();
}
>>> now Run the application and magically see the Employees appear in the DataGridView.

useful links ::
Top 30 Features of SQL Server 2005
CREATE ENDPOINT (Transact-SQL)