CLR Integration in SQL Server 2005
——————————————

Introduction to CLR:

The .NET Framework Common Language Runtime (CLR) is an environment that executes compiled code written in programming languages such as C# and VB.NET. The code is compiled to a file called an assembly that contains the compiled code together with an assembly manifest. The manifest contains metadata about the assembly, including types, methods, and inheritance relationships. Code running within the CLR is called managed code.
The CLR provides services such as automatic garbage collection, security support, and runtime type checking. Because the compiled code is executed by the CLR rather than directly by the operating system, managed code applications are platform- and language-independent.

The Common Language Runtime, popularly known as CLR is the heart and soul of the .NET Framework.

The CLR Provides a number of services that include:
–Loading and execution of programs
–Common Type Systems
–Compilation of IL into Native Code executable code
–Providing Metadata
–Memory Management
–Enforcement of Security
–Interoperability with other systems
–Managing Exceptions and Errors

Common Type System ( CTS )

The .Net Framework provides multiple support using the feature known as Common type system that is build into the CLR.The CTS Supports variety of types and operations found in most programming languages and therefore calling one language from another does not require type conversions.

Common Language Specification ( CLS )
CLS is the subset of CTSThe CLS defines a set of rules that enables interoperability on the .Net platform

Microsoft Intermediate Language ( MSIL )
Contains instructions for loading, storing, initializing and calling methodsMetadata
Metadata is machine-readable information about a resource, or “data about data”.In .NET metadata includes type definitions, version information, external assembly references and other standard informationManaged Code
The Code that satisfies the CLR at runtime in order to execute is referred to as managed code.Execution of CLR:
——————

Steps to enable the CLR IntegrationTo Enable the CLR Integration
sp_configure ‘clr enabled’,1

To Create Assembly
CREATE ASSEMBLY MyFirstCLR
FROM ‘C:\MyFirstCLR.dll‘
WITH PERMISSION_SET = SAFE

MyFirstCLR is the assembly name

Assembly SpecificationSAFE:

This is the default level and it is the most restrictive. This means that your code does not need any external resources in addition to the operation. Safe code can access data from the local SQL Server databases or perform computations and business logic.

EXTERNAL_ACCESS:

This level signifies that certain external resources, such as files, networks, Web services, environmental variables, and the Registry, are accessible. UNSAFE:

This level, which you should try very hard to avoid, specifies that your code is allowed to do anything. In other words, you are requesting to be free of any granular-level control, and thus have the same permissions as an extended stored procedure. using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void GetEmployeeDetails()
{
SqlCommand command = new SqlCommand();
//Use the context connection
command.Connection = new SqlConnection(“Context connection=true”);
command.Connection.Open();
//Define the T-SQL to execute
string sql =”select * from Employee”;
command.CommandText = sql.ToString();
//Get the data
SqlContext.Pipe.ExecuteAndSend(command);
//Close the Connection
command.Connection.Close();}
};