HTTP Endpoint with parameter function to calculates Longitude and Latitude distance in SQL-Server 2005:

I wrote an article for introduction of HTTP Endpoint (web service version of SQL-Server 2005) and have used simple stored procedure in it at:

http://ritesh-a-shah.blogspot.com/2009/03/http-endpoint-in-microsoft-sql-server.html

Or

http://riteshshah.wordpress.com/2009/03/06/http-endpoint-in-microsoft-sql-server-2005-implemented-in-visaul-studio-2008/

If you are new to HTTP Endpoint and yet to create your first HTP Endpoint than please do stop reading this article and go for any of the above link to know more about HTTP Endpoint.

You are reading this line so, now I am assuming that you are aware with basic concept of HTTP Endpoint. Let us move further:

We are going to create one function that will calculate Latitude and Longitude distance in miles. Note: I have searched mathematical formula for calculating distance from internet. I have just used that formula in my function just to demonstrate the use of parameter function in HTTP Endpoint.

CREATE FUNCTION DistanceOfLatLon(

@Latitude1 Float,

@Longitude1 Float,

@Latitude2 Float,

@Longitude2 Float

)

Returns Float

As

Begin

Return 3963.0*acos(

sin(@Latitude1/57.295779513082323)

* sin(@Latitude2/57.295779513082323)

+ cos(@Latitude1/57.295779513082323)

* cos(@Latitude2/57.295779513082323)

* cos((@Longitude2-@Longitude1)/57.295779513082323)

)

End

You can run above function directly in sql server and test its usage by:

SELECT [AdventureWorks].[dbo].[DistanceOfLatLon](33.0,-84.0,33.3,-84.0)

You will get result 20.750219476997 miles

Now we will implement this function in HTTP Endpoint.

CREATE ENDPOINT FindLatLong --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 = '/SQLLatLong', --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 'getLetLong' -- you can define more than one webmethod also to expose

(

NAME = '[AdventureWorks].[dbo].[DistanceOfLatLon]', SCHEMA = STANDARD,

FORMAT = ALL_RESULTS

),

WSDL = DEFAULT, --this will generate WSDL as per request

BATCHES = DISABLED --you could enable BATCHES but it becomes security thread

)

I am assuming that you have created Endpoint in Adventureworks database and you are using “localhost” in your endpoint as a site name. You can test Endpoint by running following URL in any of the internet browser.

http://localhost/SQLLatLong?WSDL

Now, you can implement your HTTP Endpoint in your Windows or Web application. Steps to use HTTP Endpoint in your application, has been given in my previous article. You can find that link at the top of this article.

After adding the reference in your windows application, you can write following code in your button click to print the value of calculation in your label on the same form.

private void button1_Click(object sender, EventArgs e)

{

localhost1.FindLatLong finding = new ForHTTPendPoint.localhost1.FindLatLong();

finding.Credentials = System.Net.CredentialCache.DefaultCredentials;

label1.Text = finding.getLetLong(33.0, -84.0, 33.3, -84.0).ToString();

}

Reference: Ritesh Shah

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