Geeks With Blogs
Ashraf Abdallah blog

1.      differences between TSQL statements AND .CLR code:

 

Features

 

.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

GO

RECONFIGURE

GO

 

5.       to create a schema

 

CREATE SCHEMA BARAA

GO

 

6.      To Create an Assembly

 

CREATE ASSEMBLY asmSQLDLL

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

GO

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)

CREATE FUNCTION BARAA.SQLTestPross ()

RETURNS NVARCHAR(20)

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

-- the Practes becuase if u used reserved word

GO

 

8.      To execute the Function

 

--Executing the function

SELECT BARAA.SQLTestPross ()

GO

 

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 1:21 PM Web development | Back to top


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

No comments posted yet.
Your comment:
 (will show your gravatar)


Copyright © Ashraf Abdallah | Powered by: GeeksWithBlogs.net