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
 
Search WWH ::




Custom Search