SQL Server 2005 and .NET

Last month we looked at the new feature of .NET integration into the upcoming version of SQL Server 2005. This time we look at some more innovations in SQL Server by using the .NET functionality. We'd given out the beta version of SQL Server 2005 Express Edition Technical preview in our August 2004 PCQXtreme CD. You can use that along with VS.NET to try the functions we discuss here.

You already know about creating and using UDFs (User Defined Functions) in VB.NET that can be plugged into SQL Server and used in any SQL statement. This time, we'll look at creating some User Defined Stored Procedures and Aggregates (UDPs and UDAs) in SQL Server using .NET, and using them within your SQL applications. We'll also look at some built-in security features that disallows certain functionality unless specifically enabled.

Stored procedure

First we'll create a UDP that writes something to a local file on the hard disk. For this, open VS.NET 2005 and select New>Project>Visual Basic> Database>SQL Server Project. Right click on the project name and select Add>Stored Procedure. This creates a template .NET Stored Procedure in VS.NET. You can add code as you like to this. As example, we'll create a simple SP that will write a supplied string to a local file. The code will be: 

Partial Public Class StoredProcedures

_

Public Shared Sub WriteMsg(ByVal file As String, ByVal msg As String)

Using sq As New System.IO.StreamWriter(file, True)

sq.WriteLine(msg)

End Using

End Sub

End Class

This snippet defines a new stored procedure called WriteMsg, which accepts a filename and a string message as parameters. It then writes the supplied message to the file (creating it if it doesn't exist or appending to it, if it does). Save the file and build the project. Now open SQL Management Studio and connect to the server and the database where you wish to include this stored procedure. Create a new query window using the toolbar. And enter the following SQL statements to register the assembly and the new stored procedure (change the path to point to your assembly). 

CREATE ASSEMBLY SQLCLR From 'C:\SQL\SQLCLR\SQLCLR\bin\SQLCLR.dll'



CREATE PROCEDURE WriteMsg

@file nvarchar(100),

@msg nvarchar(200)

AS EXTERNAL NAME SQLCLR..WriteMsg 

Note that the syntax of the standard stored procedure creation command has been extended by adding the 'AS EXTERNAL' parameter, which refers to the stored procedure in the external assembly. Now try executing the stored procedure as you would normally do with a SQL stored procedure.

EXEC dbo.WriteMsg 'C:\SQLMsg.txt', 'Hello SQL2005!'

This line tries to write a file to the C: root and put the message 'Hello SQL2005!' in it. But, when you try to run this command you will get a security exception from SQL Server. This is because all assemblies within SQL Server have access only to its own resources and not to the file system. For this, you must explicitly allow it. You will need to re-register the assembly with the right permissions. For this, uninstall the procedure and unregister the assembly as follows and re-register it with the permissions
required.

DROP PROCEDURE WriteMsg

DROP ASSEMBLY SQLCLR



CREATE ASSEMBLY SQLCLR From 'C:\SQL\SQLCLR\SQLCLR\bin\SQLCLR.dll' 

WITH PERMISSION_SET=EXTERNAL_ACCESS



CREATE PROCEDURE WriteMsg

@file nvarchar(100),

@msg nvarchar(200)

AS EXTERNAL NAME SQLCLR..WriteMsg

Note the 'WITH PERMISSION_SET' parameter to the create assembly statement. This allows external access to other resources on the machine. Now execute stored procedure as before.

User defined aggregates

Aggregates are special functions that give you a summarized version of their content, and work over multiple records. Examples of SQL aggregates are COUNT and SUM. Using .NET, you can define your aggregate that works over multiple records and returns a summarized view of them. In our eg, we'll create a small aggregate function that returns the values of a specified field back in CSV format. For this, right click on the project and select Add>Aggregate. A template aggregate is created to which add this: 

_

_

Public Class CSVString

Private sb As StringBuilder

Private firstConcat As Boolean = True



Public Sub Init()

sb = New StringBuilder

firstConcat = True

End Sub



Public Sub Accumulate(ByVal value as SqlString)

If firstConcat Then

sb.Append(value)

firstConcat = False

Else

sb.Append(", ")

sb.Append(value)

End If

End Sub



Public Sub Merge(ByVal value as CSVString)

Accumulate(value.ToString)

End Sub



Public Function Terminate() As SqlString

Return sb.ToString()

End Function

Public Overloads Function ToString() As String

Return sb.ToString()

End Function

End Class

The code creates a new aggregate, CSVString. Internally it uses a stringbuilder and a Boolean variable. The first four methods: Init, Accumulate, Merge and Terminate, are all required by an aggregate and you must write the code required to do each. The Init method initializes the aggregate. We set the values to be blank and true for the two main variables respectively. The merge function is called when the aggregate function is hit within an SQL statement. This in turn calls the accumulate method which gets the current value of the field on which the aggregate was called. You can do anything here-for eg, if your aggregate was SUM, you'd add the value to a buffer variable here. In our case, we create a CSV string. The terminate method returns the final aggregate value once all the records are processed. To install and run this aggregate, you will need to deploy the project to SQL Server from VS.NET. In the current beta, the SQL statements for registering .NET aggregate does not function correctly. To use the new aggregate, simply use it as you would use any internal aggregate like SUM. For
eg:

SELECT dbo.CSVString(sName) as AllNames FROM Names

This will return a comma separated value string of all names in the table. We'll continue with this topic next month and create custom data types for SQL Server using .NET.

Vinod Unny 

Enterprise InfoTech