This is my first attempt to use the .Net feature of Yukon. I tried this on the CTP versions of both SQL server 2005 and Visual Studio 2005; both are available on microsoft website
This program will return the Count(*) from a table
Pre -Requisites
1. Create a Database TestDB
CREATE DATABASE TestDB
GO
USE TestDB
GO
2. Now create a table 'Authors' and insert some values
3. Create a new C# Class library project
4. Inside that default template , create a new method ->GetCount()
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using Microsoft.SqlServer;
namespace ClassLibrary1
{
public class Class1
{
public Class1() { }
public static int GetCount()
{
using (SqlConnection cn = new SqlConnection("context connection = true"))
{
cn.Open();
int iRows;
SqlCommand sqlCmd = new SqlCommand(
"select count(*) as 'Count' from Authors", cn);
iRows = (int)sqlCmd.ExecuteScalar();
return iRows;
}
}
}
}
5. Compile the CLR stored procedure and build the dll. Note the path; by default it will be in 'My Documents\Visual Studio 2005\Projects' folder.
6. Create a Folder 'TestProjects' under 'C:\' and copy the dll into it.
7. That's all from Visual Studio.Net Side
8. Back to SQL server 2005, Create an assembly
CREATE ASSEMBLY asmClass
FROM 'C:\TestProjects\ClassLibrary1.dll'
GO
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 is a path to a networked file share. When the CREATE ASSEMBLY command is executed, the DLL is copiedinto the master database.
9. Now create a procedure
CREATE PROCEDURE FirstPgm
AS
EXTERNAL NAME
asmClass.[ClassLibrary1.Class1].GetCount
GO
The EXTERNAL NAME statement is new to SQL Server Yukon. In the preceding example, the EXTERNAL NAME statement specifies that the stored procedure will be created using a .NET assembly. An assembly can contain multiple namespaces and methods, and the EXTERNAL NAME statement uses the following syntax to identify the correct code to use fromwithin the assembly:
Assembly Name:[AssemblyNamespace.TypeName]::MethodName
10. Now run the Procedure
declare @x int
Exec @x = First
Print @x
By default clr won't be enabled in SQL server 2005. If you are getting this kind of error while running the above, u need to configure 'Clr' in SQL. For this run the following
EXEC sp_configure 'show advanced options' , '1';
GO
reconfigure;
GO
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;
-- Turn advanced options back off
EXEC sp_configure 'show advanced options' , '0';
GO
reconfigure;
GO
