The first step is to produce a .NET assembly containing classes that hold the methods you wish to call from your SQL Server procedures. This assembly is then registered within SQL Server, and specific procedures are created to link with the specific methods within the assembly. It is important to note that the signatures of both the methods you want to call, and the stored procedures you use to link, must be the same.
Users of the database can then query these new procedures as they would any other form; however, SQL Server executes the .NET method and outputs its results.
This process is outlined on the diagram below, in which the method "HelloWorld" takes a string as a parameter and prefixes it with "Hello" to produce its string output.
So, the workplan for writing such procedures is as follows:
Create a method you wish to host within SQL Server 2005.
Compile this class to create an assembly.
Register this assembly within SQL Server 2005.
Create a new function referencing the method you wish to call.
Step 1: Create a Method you Wish to Host Within SQL Server 2005
Open up Visual C# Express 2005, and select New -> Project from the menu. You should be presented with the following dialog:
A Class Library allows us to create an assembly rather than an executable application. Make sure this is selected, and type in a name for the project. Here, I've called it "SQLServerHostTest," but I'm sure you can think of a snappier title!
You'll now have a fresh, clean class within a new assembly, to which you can add the following methods to be hosted:
using System;
using System.Collections.Generic;
using System.Text;
namespace SQLServerHostTest
{
public class HostFunctions
{ public static string HelloWorld(string Name)
{
//prefix the Name variable with Hello
return "Hello" + Name;
}
}
}
It should be noted that any method you wish to host within SQL Server 2005 has to be marked both public and static (or public shared in VB.NET), as only one instance of the method will be used.
Step 2: Compile to Create an Assembly
We can now compile the class library to create an assembly. Click F6 or click the Build -> Build Solution menu to achieve this. As assemblies are registered within SQL Server 2005 through file locations, you will need to know the exact location of the outputted assembly. To set the output path manually, click the menu "Project -> SQLServerHostTest Properties..," select the Build tab, and enter a path in the "Output Path" text box.
Step 3: Register this Assembly Within SQL Server 2005
While you can manually code the execution of SQL statements in C#, you can also use the QA Replacement Application to connect to your SQL Server and execute statements very easily, get more information on any errors you might receive, and view output results.
The actual command that registers an assembly with SQL Server 2005 is:
CREATE ASSEMBLY [ASSEMBLYNAME] FROM '[ASSEMBLYLOCATION]'
The [ASSEMBLYNAME] can be any name you wish, but it's good practice to prefix these definitions with "asm" in the same way that primary key objects should be prefixed with "pk".
The [ASSEMBLYLOCATION] is the full file path of the assembly you wish to reference.
Hence, we need to execute the following SQL statement:
CREATE ASSEMBLY asmHelloWorld FROM 'c:\SqlServerHostTest.dll'
Using QA, enter the details of your database (Note: Your SQL Server is accessed through the YOURCOMPUTERNAME\SQLSERVERINSTANCE notation, rather than just "localhost").
You can now enter the SQL statement and click the Execute Text button to execute the statement on SQL Server 2005.
Note: You might receive an error that reads as follows:
"CREATE ASSEMBLY failed because method 'get_Value' on type 'SQLServerHostTest.Properties.Settings' in safe assembly 'SQLServerHostTest' is storing to a static field. Storing to a static field is not allowed in safe assemblies."
Often, C# Express will add to your library classes that are not compatible with SQL Server hosting. If you receive this error, manually compile your class into an assembly using the following command line:
csc /out:c:\SqlServerHostTest.dll /t:library Class1.cs
Step 4: Create a new Function that References the Method you Wish to Call
Now that the assembly is registered with SQL Server, we can create a new function to wrap our call to the "HelloWorld" method in the assembly.
The command that achieves this is the standard CREATE FUNCTION command, which will already be familiar to users of SQL Server. However, we now need to add a reference to our assembly at the end of the declaration:
CREATE FUNCTION dbo.clrHelloWorld ( @name as nvarchar(200) )
RETURNS nvarchar(200)
AS EXTERNAL NAME [asmHelloWorld].[SQLServerHostTest.HostFunctions].[HelloWorld]
In Beta 2 of SQL Server 2005, the syntax used to reference an assembly was changed so that instead of using colons, it utilised the more traditional dot notation. The AS EXTERNAL NAME initially takes the name of the assembly we registered (in this case asmHelloWorld), followed by the full class name including Namespaces (in our case, SQLServerHostTest.HostFunctions), and ends it with the method we wish to call: "HelloWorld."
Also, notice how the signature of the function corresponds with the signature of the "HelloWorld" method. The NVARCHAR type is used to relate to our string types.
Test The Procedure
Now that our new function has been created, we can test our progress. As our function returns a string type, we can use a simple Select statement to retrieve our result.
SELECT dbo.clrHelloWorld('welcome')
This returns the expected result:
Hello wecome
If error occuring during the function running
Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option.
In SqlServer
You need to run RECONFIGURE.
sp_configure
'clr enabled', 1
go
reconfigurego
Excute query afterwards SELECT dbo.clrHelloWorld('welcome') this query working fine
