Prizes & Awards
My Profile
Active Members
TodayLast 7 Days
more...
Birthday Greetings
|
Learn Windows 7: How to Install Windows Vista ? This article is about how to install Windows Vista in computer and system requirement for Windows Vista.
Resources » Articles » Databases »
SQL Server 2005 and CLR Integration
|
This article introduces you to the CLR integration on SQL Server 2005 known as Yukon.
Introduction
Microsoft SQL Server 2005 features the integration of CLR component of the .Net framework allowing the developers to seemingly use the power of .Net languages like C#, VB .Net, J# etc in writing stored procedures, triggers, user-defined types, and user-defined function.
Note: dot net Framework 2.0 is pre-installed with MS SQL Server 2005.
As per Microsoft this integration will lead to appalling set of benefits to the developers –
1) .Net being religiously richer than the T-SQL will offer the rich set of classes that ships with .Net to solve problems programmatically which other wise would be impossible or less possible.
2)The managed code of .Net allows improved security for the database objects than the extended stored procedures available in the earlier versions of SQL Server.
3)Two newly introduced database objects namely User defined types and User defined aggregates expand the storage and querying capabilities of SQL Server.
4)Microsoft has vision to integrate database development in to the Visual Studio for .Net allowing the same tools for developing and debugging the database objects as they would do for middle-tier or client-tier .Net framework components and services.
5)Many time .Net language compilation and execution models deliver improved performance over T-SQL.
Advantages of the CLR integration
T-SQL is specifically designed for direct data access and manipulation in the database. It is not suited for programming. For instance, T-SQL cannot be used where arrays, collections, “for each” construct, or classes are used. All the pre-built classes and routines in the .Net framework can be easily accessed from any Stored Procedure, trigger or user-defined function. (The classes that are not appropriate for the server side use are not included in the CLR code of SQL Server. For e.g., windowing classes)
One of the other advantages on the CLR is the type safety, or the assurance that the code accesses types only in well-defined permissible ways. Before the managed code is run, the CLR confirms whether the code is safe. For instance, the code is checked to ensure that no memory is read that has not been written previously and also ensures that the code does not manipulate the unmanaged memory.
Building Database Objects with CLR Integration
The managed code that runs in the SQL Server host environment is referred to as “.Net Framework Routine”. These routines include –
- Scalar-valued User defined functions (Scalar UDFs)
- Table-Valued user-defined functions (TVFs)
- User-defined Procedures (UDPs)
- User-defined data types (UDTs)
- User-defined Triggers
With the exception of UDTs, all the above routines have the same structure in the managed code – these are mapped to public, static methods of a class. UDTs are mapped to the entire .Net class. Each of these routines has a T-SQL declaration and can be used anywhere in SQL Server that the T-SQL can be used.
Let us start with practical CLR integration
The components that are required for CLR database objects are installed with SQL Server 2005. This is implemented through an assembly “sqlaccess.dll” that can be found in the Binn directory of the installed path. The namespaces that are available in the dll are System, System.Data, System.Data.Sql, System.Data.SqlServer, System.Data.SqlTypes.
Let us try our first stored procedure in .Net.
Copy the below VB.Net code to a text editor and save as WelcomeCLR.vb:
Imports System
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlServer
Imports System.Data.SqlTypes
Public Class WelcomeCLR
Public Shared Sub ShowMessage()
SqlContext.GetPipe().Send ("Welcome to new integrated world of CLR in SQL Server")
End Sub
End Class
The above example has one single shared (static) method on a public class. This uses two classes used for creating managed database objects – SqlContext and SqlPipe class (GetPipe() method returns a SqlPipe object) to output a simple text message. This method can be declared as a stored procedure in SQL Server and run as if it’s a T-SQL procedure. Let us compile this piece of code, load it into SQL Server and run it as a T-SQL procedure.
Compiling the .Net Stored Procedure
The .Net framework redistribution files that was installed on fly by SQL Server 2005 includes the vbc.exe, a command line compiler for VB .Net programs. To compile the code, we need to move to the folder –
%winpath%\Microsoft .Net\Framework\{Version}
My PC has the following path –
C:\WINDOWS\Microsoft.NET\Framework\v2.0.40607
From the following path we can compile our piece of code by the use of following command switches –
>vbc /target:library F:\Mahesh\SQL2K5\SPs\WelcomeCLR.Vb /reference:"c:\Program Files\
Microsoft SQL Server\MSSQL.1\MSSQL\Binn\SqlAccess.dll
This command will invoke the VB .Net compiler to build the library as indicated by the /target switch and referencing to assembly SqlAccess.dll
Loading and Running the .Net stored procedure in SQL Server
We can now test the managed stored procedure in SQL Server. To do this, open the SQL Server management studio with a new query window. Connect to a appropriate database. I created a new database and am using the same to do my small tests by using the script Create Database [Yukon_Usage] use Yukon_Usage
To load and run the managed SP in SQL Server, first create an Assembly, by using
Create Assembly
WelcomeCLR from 'F:\Mahesh\SQL2K5\SPs\WelcomeCLR.dll' With permission_set = Safe
Once this is successful, we can access the Shared Method by creating a T-SQL procedure,
Create Procedure WelcomeMessage
as
external name WelcomeCLR.WelcomeCLR.ShowMessage
Once the procedure is created, we can execute this as a normal T-SQL procedure, exec WelcomeMessage
The output in the output window would be -
Welcome to new integrated world of CLR in SQL Server
We can remove the example by using Drop Procedure WelcomeMessage Drop Assembly WelcomeCLR
Summary
CLR Integration on SQL Server will open up a new spectrum of oppurtunities for the developers to integrate the advanced OO techniques of .Net in SQL Server.
|
|
Responses to the resource: "SQL Server 2005 and CLR Integration"
|
No responses found. Be the first to respond and make money from revenue sharing program.
|
|