Database Reference
In-Depth Information
C H A P T E R 20
Using the CLR in SQL Server
For many years, writing business logic has been technology- and software-specific, especially in terms of
databases. For example, if you wanted to create a stored procedure or other database object that
required complex SQL code, the only way was to code the T-SQL logic in a database and write the calling
code in a programming language like C#, as shown in previous chapters where we created a stored
procedure using T-SQL in SQL Server and then wrote the calling code in C#.
This approach is still very popular, but there is an easier way that allows C# programmers to take
control and code all the database-oriented objects such as stored procedures, functions, and triggers in
the .NET language of their choice such as C# unlike done previously using T-SQL.
In this chapter, I'll cover the following:
•
Introducing SQL CLR
•
Choosing between T-SQL and SQL CLR
•
Enabling SQL CLR integration
•
Creating a SQL CLR stored procedure
•
Deploying a SQL CLR stored procedure into SQL Server
•
Executing the SQL CLR stored procedure
Introducing SQL CLR
The SQL Common Language Runtime (CLR) is a tiny version of .NET CLR that is integrated into SQL
Server 2005 and newer. SQL CLR offers a choice to developers when it comes to dealing with complex
business logic in conjunction with a database, especially when T-SQL makes it less than a pleasure to
work with.
SQL CLR is a smaller version of .NET CLR, which mainly serves the purpose of a runtime execution
engine by providing support for memory management and code execution of deployed .NET SQL CLR
assemblies. An
assembly
is a .NET term that refers to a DLL or EXE file that consists of
metadata
(data
about data) and a
manifest
(data about assemblies).
The following types of objects can be created with SQL CLR integration:
•
Stored procedures
•
User-defined aggregates
•
Triggers