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:WindowsMicrosoft.NETFramework (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:WINDOWSMicrosoft.NETFrameworkv2.0.50727SampleUdf.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.