HOW TO : Call a C# function inside Yukon Stored Procedure

Recently I had an interesting requirement in my project to use a C# procedure inside SQL.
Prior to SQL Server 2005, we had to use only Extended store procedures..
But as Yukon is integrated with CLR, you can now call a C# Whidbey function inside SQL Server procedure.

The following is the way you can do it:

1. Start Visual Studio 2005.
2. On the File menu, click New Project.
3. Under Projects, choose Visual C# and under Templates choose SQL Server Project template. Click OK. By default a project named SqlServerProject1 is created.
4. You will now be prompted to specify a connection. Provide the connection of the Yukon server for which you need the C# function.

5. Right click SqlServerProject1 on Solution Explorer and click Add New Item. Choose any template that is provided.
6. Now replace the following code with the existing code provided with the template:

#region Using directives
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlServer;
#endregion
public class MyClass
{
public MyClass()
{
}
public static void GetDepartmentDetailsByNum(int DeptNo)
{
SqlPipe sp = SqlContext.GetPipe();
sp.Send("The Department number is " + DeptNo.ToString() + "\n");
SqlCommand cmd = SqlContext.GetCommand();
cmd.CommandText = "select * from dept where deptnum=" + DeptNo.ToString();
SqlDataReader rdr = cmd.ExecuteReader();
sp.Send(rdr);
}
public static void GetSum(int x, int y)
{
SqlPipe sp = SqlContext.GetPipe();
int z=x+y;
sp.Send(z.ToString());
}
}
7. Now save the project and Build the project.
8. Open SQL Server Management Studio and connect to the Yukon server.
9. Right click the Database where the C# function is needed and then click New Query.
10. In the query editor, paste the following code:

CREATE ASSEMBLY ManagedCsharpProcedure
FROM 'C:\Laksk\SqlServerProject1\bin\Debug\SqlServerProject1.dll'

CREATE PROCEDURE [dbo].[GetSum]
@x int ,@y int
AS
EXTERNAL NAME
SqlManaged.DeptDetails.GetSum
GO

11. Now test the GetSum procedure using the following statement :
exec GetSum 5,4

Thats it !!! You now have a C# method being called in SQL Server. The same method can also be used with recordsets as in the C# function GetDepartmentDetailsByNum that is included in the same class. So on passing the Dept Number, we get the Department details through the C# function.