CLR Integration in SQL Server 2005

The Common Language Runtime (CLR) of the .Net framework is integrated into SQL Server 2005. The CLR supplies managed code with services such as cross-language integration, code access security, object lifetime management, and debugging and profiling support. CLR provides various functions and services required for program execution, including just-in-time (JIT) compilation, allocating and managing memory, enforcing type safety, exception handling, thread management, and security. For SQL Server users and application developers, CLR integration means the user can now write stored procedures, triggers, user-defined types, user-defined functions (scalar and table-valued), and user-defined aggregate functions using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#.

Advantages of CLR integration are:

• Better programming model:-The .NET Framework languages are in many respects richer than Transact-SQL, offering constructs and capabilities previously not available to SQL Server developers. Developers may also leverage the power of the .NET Framework Library, which provides an extensive set of classes that can be used to quickly and efficiently solve programming problems.
• Improved safety and security: - Managed code runs in a common language run-time environment, hosted by the Database Engine. SQL Server leverages this to provide a safer and more secure alternative to the extended stored procedures available in earlier versions of SQL Server.
• Ability to define data types and aggregate functions: - User defined types and user defined aggregates are two new managed database objects which expand the storage and querying capabilities of SQL Server.
• Streamlined development through a standardized environment: - Database development is integrated into future releases of the Microsoft Visual Studio .NET development environment. Developers use the same tools for developing and debugging database objects and scripts as they use to write middle-tier or client-tier .NET Framework components and services.
• Potential for improved performance and scalability: - In many situations, the .NET Framework language compilation and execution models deliver improved performance over Transact-SQL.

Enabling CLR Integration

The common language runtime (CLR) integration feature is off by default, and must be enabled in order to use objects that are implemented using CLR integration. To enable CLR integration, use the clr enabled option of the sp_configure stored procedure:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Managed Code vs. Transact-SQL

1) Transact SQL is designed for data access and manipulation in the database. But Transact SQL is not a full fledged programming language. Transact SQL does not support arrays, collections, for-each loops, bit shifting, or classes. Managed code has integrated support for these constructs. Depending on the scenario, these features can provide a compelling reason to implement certain database functionality in managed code.

2) Managed code is better suited than Transact-SQL for calculations and complicated execution logic, and features extensive support for many complex tasks, including string handling and regular expressions. With the functionality found in the .NET Framework Library, the user has access to thousands of pre-built classes and routines. These can be easily accessed from any stored procedure, trigger or user defined function.

Transact SQL should be used when the scenario demands more of data access with little or no procedural logic. Managed code should be used for CPU-intensive functions and procedures that feature complex logic. Another factor which plays a role in deciding about whether to use Transact-SQL or managed code is where the code will reside, on the server computer or on the client computer. Both Transact-SQL and managed code can be run on the server. This places code and data close together, and allows the user to take advantage of the processing power of the server. On the other hand, the user may wish to avoid placing processor intensive tasks on your database server. In certain scenarios, the user may wish to take advantage of this processing power by placing as much code as possible on the client. Managed code can run on a client computer, while Transact-SQL cannot.

Building Objects with CLR Integration

We can build database objects using the SQL Server integration with dot net framework common language runtime. Managed code that runs inside of Microsoft SQL Server is referred to as a CLR routine. These routines include:

• Scalar-valued user-defined functions (scalar UDFs)
• Table-valued user-defined functions (TVFs)
• User-defined procedures (UDPs)
• User-defined triggers

In addition to routines, user-defined types (UDTs) and user-defined aggregate functions can also be defined using the .NET Framework.

Note: -Visual Studio .NET 2003 cannot be used for CLR integration programming. SQL Server 2005 includes the .NET Framework 2.0 pre-installed, and Visual Studio .NET 2003 cannot use the .NET Framework 2.0 assemblies.

User Defined Functions

User-defined functions are routines that can take parameters, perform calculations or other actions, and return a result. In Microsoft SQL Server 2005, the user can write user-defined functions in any Microsoft .NET Framework programming language, such as Microsoft Visual Basic .NET or Microsoft Visual C#.There are two types of functions: scalar, which returns a single value, and table-valued, which returns a set of rows.

A) CLR Scalar Valued Functions

A CLR scalar-valued function (SVF) returns a single value, such as a string, integer, or bit value. These functions are accessible to Transact-SQL or other managed code. .NET Framework SVFs are implemented as methods on a class in a .NET Framework assembly. The input parameters and the type returned from a SVF can be any of the scalar data types supported by SQL Server, except varchar, char, rowversion, text, ntext, image, timestamp, table, or cursor. SVFs must ensure a match between the SQL Server data type and the return data type of the implementation method. When implementing a .NET Framework SVF in a .NET Framework language, the SqlFunction custom attribute can be specified to include additional information about the function. The SqlFunction attribute indicates whether or not the function accesses or modifies data, if it is deterministic, and if the function involves floating point operations.

Example:

This example accesses data and returns an integer value. The below code is written in C#.

using Microsoft.SqlServer.Server;
using System.Data.SqlClient;

public class T
{
[SqlFunction(DataAccess = DataAccessKind.Read)]
public static int OrderCount()
{
using (SqlConnection conn
= new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand(
"SELECT COUNT(*) AS 'Order Count' FROM Sales.SalesOrderHeader", conn);
return (int)cmd.ExecuteScalar();
}
}
}

Save the file as “SampleUDF.cs”.Compile it using the C# compiler.(csc.exe)
The C# compiler is found in the default folder of dot net framework, which is,

C:\Windows\Microsoft.NET\Framework\ (version)

/t:library indicates that a library, rather than an executable, should be produced. Executables cannot be registered in SQL Server.

We have to create the assembly and register it with SQLServer to invoke the UDF.

CREATE ASSEMBLY SampleUdf FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\SampleUdf.dll'
WITH PERMISSION_SET = UNSAFE
GO

When creating an assembly into a SQL Server database, the user can specify one of three different levels of security in which your code can run: SAFE, EXTERNAL_ACCESS, or UNSAFE. Safe is the default permission set and works for the majority of scenarios. When code in an assembly runs under the Safe permission set, it can only do computation and data access within the server External_Access addresses scenarios in which the code needs to access resources outside the server, such as files, network, registry, and environment variables. Whenever the server accesses an external resource, it impersonates the security context of the user calling the managed code. Unsafe code permission is for those situations in which an assembly is not verifiably safe or requires additional access to restricted resources, such as the Microsoft Win32 API.

CREATE FUNCTION Count_SalesOrderHeader() RETURNS INT
AS EXTERNAL NAME SampleUdf.T.OrderCount

SELECT dbo.Count_SalesOrderHeader()

Note:-All these operations have used “AdventureWorks” database.

B) CLR Table-valued Functions (TVF)

A table-valued function (TVF) is a user-defined function that returns a table. Data is returned from a TVF through an IEnumberable or IEnumerator object.

Transact-SQL TVFs materialize the results of calling the function into an intermediate table. While CLR TVFs use the streaming alternative. The entire set of results is not materialized into a single table. The streaming model helps the result to be consumed immediately after the first row is available. We do not have to wait for the entire table to be populated. This is an advantage over the TSQL TVFs.

CLR TVFs can be implemented by using the IEnumberable Interface.

Example:

using System;
using System.Data.Sql;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Data.SqlTypes;
using System.Diagnostics;

public class TabularEventLog
{
[SqlFunction(FillRowMethodName = "FillRow")]
public static IEnumerable InitMethod(String logname)
{
return new EventLog(logname, Environment.MachineName).Entries;
}

public static void FillRow(Object obj, out SqlDateTime timeWritten, out SqlChars message, out SqlChars category, out long instanceId)
{
EventLogEntry eventLogEntry = (EventLogEntry)obj;
timeWritten = new SqlDateTime(eventLogEntry.TimeWritten);
message = new SqlChars(eventLogEntry.Message);
category = new SqlChars(eventLogEntry.Category);
instanceId = eventLogEntry.InstanceId;
}
}

In the above code,the information from eventlog is presented as table to the user.
Compile to generate “SampleTVF_CLR.dll”.

Note:-A TVF can only perform data access through a Transact-SQL query in the InitMethod method, and not in the FillRow method. The InitMethod should be marked with the SqlFunction.DataAccess.Read attribute property if a Transact-SQL query is performed.

Now we will build the assembly and create the function in SQL Server.This function will use the TabularEventLog’s Initmethod.

CREATE ASSEMBLY tvfEventLog
FROM'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\SampleTVF_CLR.dll'
WITH PERMISSION_SET = SAFE
GO
CREATE FUNCTION ReadEventLog(@logname nvarchar(100))
RETURNS TABLE
(logTime datetime,Message nvarchar(4000),Category nvarchar(4000),InstanceId bigint)
AS
EXTERNAL NAME tvfEventLog.TabularEventLog.InitMethod
GO

C) User Defined Aggregates

Aggregate functions perform a calculation on a set of values and return a single value. SQL Server has only two built-in aggregate functions, such as SUM or MAX, that operate on a set of input scalar values and generate a single aggregate value from that set. SQL Server integration with the Microsoft .NET Framework common language runtime (CLR) now allows developers to create custom aggregate functions in managed code, and to make these functions accessible to Transact-SQL or other managed code.

A type in a common language runtime (CLR) assembly can be registered as a user-defined aggregate function, as long as it implements the required aggregation contract. This contract consists of the SqlUserDefinedAggregate attribute and the aggregation contract methods. The aggregation contract includes the mechanism to save the intermediate state of the aggregation, and the mechanism to accumulate new values, which consists of four methods: Init, Accumulate, Merge, and Terminate.

SqlUserDefinedAggregate Attribute

A user-defined aggregate provides the query processor additional information about the properties of the aggregation algorithm. There are two required properties on the SqlUserDefinedAggregate attribute that control the serialization format used.They are:

Format: - The serialization format for this type.

MaxByteSize:- The maximum size in bytes needed to store the state for this aggregate during computation.

The query optimizer can use other properties (IsInvariantToDuplicates, IsInvariantToNulls, IsInvariantToOrder, and IsNullIfEmpty) to search for more efficient query execution plans.These properties have to be specified as part of the SqlUserDefinedAggregate attribute definition on the type. By default, all of these properties are set to false.

Aggregation Methods

a)Init

The query processor uses this method to initialize the computation of the aggregation. This method is invoked once for each group that the query processor is aggregating. It reinitializes when SQL Server chooses to reuse an aggregate class instead of creating a new one.

public void Init(); /* needed for empty group */

b)Accumulate

The query processor uses this method to accumulate the aggregate values. This is invoked once for each value in the group that is being aggregated.

public void Accumulate ( input-type value);

c)Merge

This method can be used to merge another instance of this aggregate class with the current instance.

public void Merge( udagg_class value);

d)Terminate

This method completes the aggregate computation and returns the result of the aggregation.

public return_type Terminate();

User Defined Procedures

In the common language runtime (CLR), stored procedures are implemented as public static methods on a class in a Microsoft .NET Framework assembly. The static method can either be declared as void, or return an integer value. If it returns an integer value, the integer returned is treated as the return code from the procedure. For example:

EXECUTE @return_status = procedure_name

The @return_status variable will contain the value returned by the method. If the method is declared void, the return code is 0.If the method takes parameters, the number of parameters in the .NET Framework implementation should be the same as the number of parameters used in the Transact-SQL declaration of the stored procedure.

Writing “Hello World” CLR stored procedure

1) Type the following C# code in a notepad and save it as “HelloWorld.cs”.

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;

public class HelloWorldProc
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void HelloWorld()
{
SqlContext.Pipe.Send("Hello world!\n");
}
}

Returning messages to the client is done through the SqlPipe object, which is obtained by using the Pipe property of the SqlContext class. The SqlPipe object has a Send method. By calling the Send method, the user can transmit data through the pipe to the calling application.

Compile the above file using the command line compiler for Visual C# (csc.exe).

The following command is used to compile the HelloWorld.cs file,
csc /target: library helloworld.cs

2) The next step is to load and run the “HelloWorld” stored procedure in SQL Server. The ability to execute common language runtime (CLR) code is set to OFF by default in SQL Server 2005. The CLR code can be enabled by using the sp_configure system stored procedure. Refer to section “Enabling CLR Integration”.

We will need to create the assembly so we can access the stored procedure. This can be done using CREATE ASSEMBLY TSQL command.

CREATE ASSEMBLY helloworld from 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\HelloWorld.dll' WITH PERMISSION_SET = SAFE

Once the assembly has been created, we can now access our HelloWorld method by using the create procedure statement.

CREATE PROCEDURE hello
AS
EXTERNAL NAME helloworld.HelloWorldProc.HelloWorld

Now we can test the procedure by executing the EXEC statement.

Extended Procedures vs. Managed code

Extended stored procedures can be built to perform functionality not possible with Transact-SQL stored procedures. Extended stored procedures can, however, compromise the integrity of the SQL Server process, while managed code that is verified to be type-safe cannot. Further, memory management, scheduling of threads and fibers, and synchronization services are more deeply integrated between the managed code of the CLR and SQL Server. With CLR integration, the user can have a more secure way than extended stored procedures.

CLR Triggers

Triggers written in a CLR language differ from other CLR integration objects in several significant ways. CLR triggers can:

• Reference data in the INSERTED and DELETED tables
• Determine which columns have been modified as a result of an UPDATE operation
• Access information about database objects affected by the execution of DDL statements.

These capabilities are provided by the SqlTriggerContext class. The SqlTriggerContext class cannot be publicly constructed and can only be obtained by accessing the SqlContext.TriggerContext property within the body of a CLR trigger. The SqlTriggerContext class can be obtained from the active SqlContext by calling the SqlContext.TriggerContext property:

SqlTriggerContext myTriggerContext = SqlContext.TriggerContext;

The SqlTriggerContext class provides context information about the trigger. This contextual information includes the type of action that caused the trigger to fire, which columns were modified in an UPDATE operation, and, in the case of a DDL trigger, an XML Eventdata structure which describes the triggering operation. Once you have obtained a SqlTriggerContext, you can use it to determine the type of action that caused the trigger to fire. This information is available through the TriggerAction property of the SqlTriggerContext class.

CLR triggers can access the inserted and deleted tables through the CLR in-process provider. This is done by obtaining a SqlCommand object from the SqlContext object.For example,

SqlConnection connection = new SqlConnection("context connection = true");
connection.Open();
SqlCommand command = connection.CreateCommand();
command.CommandText = "SELECT * from " + "inserted";

The number of updated columns can be determined by using the ColumnCount property of the SqlTriggerContext object.IsUpdatedColumn method can be used to determine whether the column was updated. This method takes the column ordinal as an input parameter, A True value indicates that the column has been updated.

reader = command.ExecuteReader();
reader.Read();
for (int columnNumber = 0; columnNumber <>

User defined types

The user defined types allows the user to extend the scalar type system of the server. UDTs can contain multiple elements and can have behaviors, differentiating them from the traditional alias data types which consist of a single SQL Server system data type. A user-defined type is implemented as a managed class in any one of the CLR languages, and then registered with SQL Server. A user-defined type can be used to define the type of a column in a table, or a variable or routine parameter in the Transact-SQL language. An instance of a user-defined type can be a column in a table, a variable in a batch, function or stored procedure, or an argument of a function or stored procedure.

UDTs in SQL Server 2005 are well suited to the following:

• Date, time, currency, and extended numeric types
• Geospatial applications
• Encoded or encrypted data

The process of developing UDTs in SQL Server 2005 consists of the following steps:

1. Code and build the assembly that defines the UDT.
2. Register the assembly
3. Create the UDT in SQL Server.Once an assembly is loaded into a host database, use the Transact-SQL CREATE TYPE statement to create a UDT and expose the members of the class or structure as members of the UDT. UDTs exist only in the context of a single database, and, once registered, have no dependencies on the external files from which they were created.
4. Create tables, variables, or parameters using the UDT