Develop SQL Server UDF using .NET

Develop SQL Server UDF using .NET

April 14th, 2009|Technical|

SQL Server .NET data provider

If you’re familiar with ADO.NET, you may wonder exactly how CLR database objects connect to the database. After all, ADO.NET makes its database connection using client-based .NET data providers such as the .NET Framework Data Provider for SQL Server, which connects using networked libraries. While that’s great for a client application, going through the system’s networking support for a database call isn’t the most efficient mode for code that’s running directly on the server. To address this issue, Microsoft created the new SQL Server .NET Data Provider. The SQL Server .NET Data Provider establishes an in-memory connection to the SQL Server database.

Assemblies

SQL Server 2005 introduces the ability to write Stored Procedures, Functions, Triggers, User Defined Types and User Defined Aggregations using any of the available .Net languages such as VB.NET or C#. This article will show you how to create a very simple “Hello World” stored procedure using VB.NET or C# to help introduce this feature and demonstrate how to load a .Net assembly into SQL Server 2005

If you’re not using Visual Studio 2005 or you want to perform the deployment process manually, then you need to copy the .NET DLL to a common storage location of your choice. Then, using SQL Server Management Studio, you can execute a T-SQL CREATE ASSEMBLY statement that references the location of the .NET DLL, as you can see in the following listing:

CREATE ASSEMBLY MyCLRDLL
FROM ‘SERVERNAMECodeLibraryMyCLRDLL.dll’

The CREATE ASSEMBLY command takes a parameter that contains the path to the DLL that will be loaded into SQL Server. This can be a local path, but more often it will be a path to a networked file share. When the CREATE ASSEMBLY is executed, the DLL is copied into the master database.

If an assembly is updated or becomes deprecated, then you can remove the assembly using the DROP ASSEMBLY command as follows:

DROP ASSEMBLY MyCLRDLL

Because assemblies are stored in the database, when the source code for that assembly is modified and the assembly is recompiled, the assembly must first be dropped from the database using the DROP ASSEMBLY command and then reloaded using the CREATE ASSEMBLY command before the updates will be reflected in the SQL Server database objects.

You can use the sys.assemblies view to view the assemblies that have been added to SQL Server 2005 as shown here:

SELECT * FROM sys.assemblies

Since assemblies are created using external files, you may also want to view the files that were used to create those assemblies. You can do that using the sys.assembly_files view as shown here:

SELECT * FROM sys.assembly_files

Following link has step by step instructions on creating a user defined stored procedure in .net
and using it in SQL Server

http://www.sqldbatips.com/showarticle.asp?ID=22