Geeks With Blogs
Ashraf Abdallah blog

1.      differences between TSQL statements AND .CLR code:




.NET Managed Code

If you required complex programmatic logic to complete a task

If you want to use .NET Framework Basic Classes

If it is required functionality that will be CPU intensive ( because of compiled Nature of managed code any functionality that is CPU intensive will run more efficient  than if were implemented using T-SQL )

SQL Statements

If you want  Create Data base object, this because T-SQL optimize for set based operations on data


2.      The functions in .NET must be "public static" ( SQL unable to create objects fro the class (DLL Class you build))

3.      Create  a Library Class Called (SQLDLL) in .NET

4.      To enable the CLR in SQL ( New Query ) and write


-- Enable For the CLR

sp_configure 'clr enabled',1





5.       to create a schema





6.      To Create an Assembly



      FROM 'C:\Documents and   Settings\Administrator\Desktop\SQLDLL\SQLDLL\bin\Debug\SQLDLL.dll'


Or : On Assemblies- right Click – new assemblies – browes the path then Ok (but you cant choose the Assemply Name)


a.      After this statement, even if u delete the Class Library it will not affect the any query depends on this class because it saved on assemblies in the data base its self.


7.      To Create a Function ( the same function in Class Library in .NET)


-- Creat function ( the same function in Class Library in .NET)



AS EXTERNAL NAME asmSQLDLL.[SQLDLL.Class1].TestPross -- case Senstive needed as Written in .NET

-- the Practes becuase if u used reserved word



8.      To execute the Function


--Executing the function




9.      You cant delete the assembly because there a function used depends on it, its Forbidden by SQL


Creating a Database Solution in .NET and Deploying (For SQL):

1.      File – New - choose  C#- database,  then choose your Connection or add reference for a new connection

2.      To add an user defined Function (by example)

a.      On Project- add user defined Function

b.      You notice that the there is function ready with sqlstring data type ( because it will transfer from a string data type in .NET to nvarchar in SQL)

3.      To give the Function name needed in the SQL


public partial class UserDefinedFunctions


    [Microsoft.SqlServer.Server.SqlFunction(Name= "GetLongDate")]

    public static SqlString FB()


        // Put your code here

        return new SqlString("Hello");




4.      To add the 2 – 7 steps on the SQL-T

a.      On Build – Deploy

b.      On Database refresh your BD



Posted on Sunday, May 7, 2006 2:09 PM Web development | Back to top

Comments on this post: Notes about Implementing Managed Code (CLR Integration) in SQL Server 2005

# re: Notes about Implementing Managed Code (CLR Integration) in SQL Server 2005
Requesting Gravatar...
u mean that i can use a DLL Build in .Net And use it as SP in SQL Server 2005, Wooo
Left by loai Al-shaksheer on May 11, 2006 4:37 AM

Your comment:
 (will show your gravatar)

Copyright © Ashraf Abdallah | Powered by: