HTTP EndPoint in Microsoft SQL-Server 2005 --Implemented in Visaul Studio 2008

HTTP EndPoint in Microsoft SQL-Server 2005 --Implemented in Visaul Studio 2008
You must have heard the terms “Web Service”. This is not something new. People used to read and write many things about this term from year of 2000 for Microsoft Technology. It was inbuilt tool in .NET Framework but it is bit new concept in SQL-Server as it has been implemented from Microsoft SQL-Server 2005. “Web Service” is known as “HTTP ENDpoint” in SQL-Server. It has opened the door for cross platform compatibility.

If you wants to built WebService then you have to have IIS server but in case of HTTP EndPoint, you don’t need any IIS server rather it is directly hooked up with kernel mode driver “http.sys”. Yes, it has some security concerns and issues but there are also some mechanisms to prevent the security thread.

You can use SQL-Server function or else SQL-Server Stored Procedure along with HTTP Endpoint. You can create two type of end point in SQL-Server 2005. 1.)TCP Endpoint and 2.) HTTP Endpoint.
Let us see how we can define HTTP Endpoint and can use it in Windows application in C#.NET.

First let us create one simple stored procedure for Adventure Works database.

--SP will be used in HTTP Endpoint and will return

--record set to calling application

USE AdventureWorks
GO

CREATE PROC Testing
AS

SELECT Name,GroupName FROM HumanResources.Department

This is very simple stored procedure one can ever make. J Now it is time to implement this stored procedure in ENDpoint.

CREATE ENDPOINT Test_END_Point --This will create HTTP Endpoint
AUTHORIZATION sa --this is something absolutely optional authoraization for db owner STATE = STARTED -- State could be STARTED, STOPPED and DISABLE

AS HTTP -- You can create HTTP or TCP endpoint
(

PATH = '/SQLENDPOINT', --virtual path, will be used in adding reference in web or windows app.

AUTHENTICATION = (INTEGRATED), --authentication type for endpoint

PORTS = (CLEAR), -- PORT coulbe be all (CLEAR) or may be SSL

SITE = 'localhost' --site name, in this case "localhost" as I am running it locally

)

FOR SOAP --protocol type

(

WEBMETHOD 'getVal' -- you can define more than one webmethod also to expose
(

NAME = 'AdventureWorks.dbo.Testing', SCHEMA = STANDARD,

FORMAT = ROWSETS_ONLY

),

WSDL = DEFAULT, --this will generate WSDL as per request
BATCHES = DISABLED --you could enable BATCHES but it becomes security thread

)

As soon as you will done with creating this Endpoint, we will move forward to implement this in windows application.
Here are the steps to implement HTTP Endpoint in your Windows application in Visual Studio 2008.

---Create one new project from File->New->Project

---Draw One button and dataGridView in your webform.

--- Add reference of HTTP Endpoint in your application. Right click on your project name in Solution Explorer and click on “Add Service reference”, you will see on dialog box and you will have to click on “Advance” button at left bottom corner and you will see another dialog box like below.

---Click on “Add Web Reference” button from above dialog box and you will see final dialog box from where you will have to find your “HTTP Endpoint” you created in SQL-Server 2005.

Once you final add the reference, you will have to code following snippet under the click event of your button.
private void button1_Click(object sender, EventArgs e)
{
localhost.Test_END_Point tEnd = new ForHTTPendPoint.localhost.Test_END_Point();
tEnd.Credentials = System.Net.CredentialCache.DefaultCredentials;
dataGridView1.DataSource = tEnd.getVal().Tables[0];
}
Hey, you just finished your first HTTP Endpoint. What are you waiting for? Runt it now!!!!
Reference: Ritesh Shah