You might be aware with Aggregate function like MIN, MAX, SUM, COUNT etc. there are many types of Aggregate function supported by Microsoft SQL Server 2005 along with CLR Aggregate function. This is new and amazing feature which helps you create your own customize AGGREGATE function with the power of CLR and use it in SQL Server 2005. Isn’t it amazing? Yes, It is. Let us have a journey of creating CLR SQL Server Aggregate function to implement it in SQL Server 2005.

I have created COUNT aggregate function. It is actually inbuilt function but this is to give demo of creating aggregate function in SQL-Server 2005.

We will first create new project from Visual Studio 2008:

1) File->New->Project

2) From Visual C# tree on left panel select Database option

3) Select “SQL Server Project” from right side panel.

4) Give the name to your project. I gave “TestAggregate”

5) Click “OK”

6) Now from your solution explorer , right click on project name, select “Add->New ->Add Item”

7) Select “Aggregate” (as we are going to create aggregate function)

8) Give name to it. I gave “TestAGGR” then click “ADD” button.

After following above steps, you will see TestAGGR.cs file with some necessary namespace and methods.

Here is the brief description of those methods.

INIT(): will be executed once per aggregation

Accumulate(): will accumulate core logic and run per records.

Merge(): merge data from different method together.

Terminate(): finally returns the value.

Along with above methods, you can create methods as per your needs. Below is the copy of my TestAGGR.CS file. Have a look

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

[Serializable]

[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]

public struct TestAGGR

{

public void Init()

{

count = 0;

}

public void Accumulate(int Value)

{

count += 1;

}

public void Merge(TestAGGR value)

{

Accumulate(value.Terminate());

}

public int Terminate()

{

return count;

}

// This is a place-holder member field

private int count;

public int Getcount()

{

return count;

}

}

Once you done the above code in visual studio, build the project and copy the TestAggregate.dll from your BIN folder of your project and put it in “D”drive.

Now, we have done with Visual Studio and we will be moving to SQL-Server 2005 SSMS.

--enable your server for clr enable

--it is disable by default

sp_configure 'clr enable', 1

reconfigure

--create assembly of your DLL file

create assembly TestAggregate

authorization dbo

from 'D:\TestAggregate.dll'

with permission_set = safe

go

--create aggregate function of your DLL

CREATE AGGREGATE TestAggregate(@val Int)

RETURNS Int

EXTERNAL NAME TestAggregate.TestAGGR

go

--run your query and enjoy result

SELECT dbo.testaggregate(run) FROM PERSONALSCORE

I was Inspired to write this article by TECHNET and MSDN website of MICROSOFT.

http://technet.microsoft.com/en-us/library/ms131056.aspx#

http://msdn.microsoft.com/en-us/library/91e6taax(VS.80).aspx#

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