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

 11

 

—Draw One button and dataGridView in your webform.

2

 

— 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.

3

 

— 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.

4

 

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

 

Advertisements

4 Responses to “HTTP EndPoint in Microsoft SQL-Server 2005 –Implemented in Visaul Studio 2008”

  1. anshu malhotra Says:

    very well describe in a very concise
    manner
    good job
    thanx a lot

  2. neeta das Says:

    This article is very helpful.
    All the steps are very well described

  3. Shaheaz Says:

    Amazing i learnt a lot


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

%d bloggers like this: