4. Introduction to Common Language Runtime (CLR) Integration

CLR Integration Design Objectives

Microsoft identifies the design objectives of SQL Server 2005 CLR integration as follows:

Reliability: CLR routines cannot perform operations that compromise the integrity of the Database Engine process, nor are they allowed to overwrite Database Engine memory buffers and internal data structures.
Scalability: SQL Server and the CLR have different models for threading, scheduling, and memory management. The design goal is to ensure scalability when user code calls APIs for threading, synchronization primitives, and memory.
Security: User code running in the database must follow SQL Server authentication and authorization rules for accessing database objects. Additionally, administrators must be able to control access to operating system resources from code running within the database.
Performance: User code running in the database must perform at least as well as equivalent implementations through native Database Engine functionality or T-SQL.

The CLR provides the following services to achieve these design objectives:

Type-safe verification: After assemblies are loaded into the CLR but before they are compiled, the code is verified to ensure access to memory structures only in well-defined ways—code that passes this verification is type-safe.
Application domains: Application domains are execution spaces within a host process where assemblies are loaded, unloaded, and executed. They provide isolation between executing assemblies.
Code access security: CAS applies permissions to code to control the operations that the code can perform and the system resources it can access based on the identity of the code.
Host Protection Attributes (HPA): HPA is a mechanism to annotate .NET-managed APIs with attributes of interest to host CLRs such as SQL Server. The host CLR can deny user code calls to APIs that are on a prohibited list.

SQL Server 2005 hosts the CLR in the Database Engine, effectively acting as the operating system for the CLR. The design goals for SQL Server 2005 CLR integration for reliability, scalability, and security are accomplished as follows:

Reliability

You cannot always recover from critical exceptions in .NET-managed code when a thread abort exception is raised. If there is any shared state in the application domain in which the thread abort exception occurs, the SQL Server-hosted CLR unloads that application domain, thereby stopping database transactions running in it.

Scalability

The CLR calls SQL Server APIs to create threads and calls SQL Server synchronization objects to synchronize threads. All threads and synchronization objects are known to SQL Server, so it can effectively schedule non-CLR threads, detect and remove deadlocks involving CLR synchronization objects, and detect and handle CLR threads that have not yielded in a reasonable amount of time.

The CLR calls SQL Server primitives to allocate and deallocate memory. This lets SQL Server stay within its configured memory limits—SQL Server can reject CLR memory requests when memory is constrained or ask the CLR to reduce its memory use as necessary.

Security

When a SQL Server registered assembly is created or altered, you can specify one of three permissions sets for the assembly: SAFE, EXTERNAL-ACCESS, or UNSAFE. SQL Server uses permission sets to set CAS permissions when the assembly executes. The three permission sets are described in Table 4-1.

Table 4-1. SQL Server CLR routine permission sets

Permission set	Description
`SAFE`	`SAFE` assemblies can access data from local SQL Server databases and can execute computations and business logic not involving resources outside the local databases. `SAFE` assemblies cannot access external system resources such as files, networks, environment variables, or the registry. The `SAFE` permission set can only be applied to code that is verifiably type-safe. It is the default permission set and the most restrictive.
`EXTERNAL-ACCESS`	`EXTERNAL-ACCESS` allows assemblies to access certain external system resources such as files, networks, environment variables, and the registry in addition to the access provided by the `SAFE` permission set. The `EXTERNAL-ACCESS` permission set can only be applied to code that is verifiably type-safe.
`UNSAFE`	`UNSAFE` assemblies have unrestricted access to resources both inside of and outside of SQL Server. An `UNSAFE` assembly can call unmanaged code. Only a database administrator can register an `UNSAFE` assembly.

The SQL Server-hosted CLR imposes the following security-related programming restrictions:

Code marked SAFE or EXTERNAL-ACCESS cannot use static data members and variables.
Calls cannot be made to .NET Framework API types or members annotated with the ExternalProcessMgmt, MayLeakOnAbort, SharedState, or Synchronization host protection attributes (HostProtectionAttribute)—this prevents code in assemblies marked as SAFE or EXTERNAL-ACCESS from calling APIs that might cause resource leaks on termination, that enable sharing state, or that perform synchronization.

Enabling CLR Integration

CLR integration is turned off by default in SQL Server 2005. Use the sp_configure system stored procedure to enable CLR integration, as shown here:

    sp_configure 'clr enabled', 1
    GO
    RECONFIGURE
    GO

The clr enabled server configuration option specifies whether .NET assemblies can be run by SQL Server (0 = do not allow; 1 = allow). The change takes effect immediately after sp_configure and reconfigure are executed—the server does not need to be restarted.

You need ALTER SETTINGS permissions at the server level to enable CLR integration.

Required .NET Namespaces

The components needed to create simple CLR routines are installed with SQL Server 2005 in the .NET Framework assembly named System.Data.dll—part of the base class library of the .NET Framework and located in both the Global Assembly Cache (GAC) and in the <windir>\Microsoft.NET\Framework\<version> directory. The key namespaces in this assembly are described in Table 4-2.

Table 4-2. .NET namespaces for CLR routines

System.Data.dll namespaces	Description
`System.Data`	Classes that comprise the ADO.NET architecture
`System.Data.Sql`	Classes that support SQL Server 2005-specific functionality
`Microsoft.SqlServer.Server`	Classes that support .NET CLR functionality inside SQL Server CLR routines—user-defined functions (UDFs), stored procedures, UDA functions, UDTs, and triggers
`System.Data.SqlTypes`	Classes that support native SQL Server data types
`System.Data.SqlClient`	.NET Frameworks data provider for SQL Server

Types of CLR Routines

SQL Server 2005 CLR integration lets you build database objects using .NET languages. Once a .NET Framework assembly is registered with SQL Server, you can create CLR routines that can be used anywhere a T-SQL equivalent routine can be used. Table 4-3 describes the available CLR routines.

Table 4-3. Types of CLR routines

Database object	.NET Framework assembly type	Description
Scalar-valued function	Public static method	A UDF that returns a single value.
Table-valued function	Public static method	A UDF that returns a table as the result set.
Stored procedure	Public static method	A routine that returns tabular result sets and messages to the client, invokes DDL and DML statements, and returns output parameters.
User-defined aggregate function	Class or structure	A UDA function that operates on values in a set of rows and returns a scalar.
User-defined type	Class or structure	Complex data types complete with methods that extend the scalar type system in SQL Server.
Trigger (DML and DDL)	Public static method	A type of stored procedure that automatically runs when a DML or DDL event occurs.

Hello World Example

This section shows how to create, configure, and use a SQL Server CLR routine by way of a sample CLR stored procedure that returns the text message “Hello world.” This example is followed by an example that shows how to create the same .NET Framework assembly using a command-line compiler.

Follow these steps in Visual Studio 2005 to create the .NET Framework assembly containing the CLR stored procedure:

Select File → New → Project.
Select SQL Server Project in the New Project dialog box, shown in Figure 4-1, name it HelloWorld, specify the location, and click OK.
Figure 4-1. New Project dialog box
Because the stored procedure will not be accessing any data, click Cancel in the Add Database Reference dialog box, shown in Figure 4-2.
Figure 4-2. Add Database Reference dialog box
In Solution Explorer, right-click the HelloWorld project and select Add → Stored Procedure from the context menu, as shown in Figure 4-3.
Figure 4-3. Add Stored Procedure menu item
In the Add New Item dialog box, shown in Figure 4-4, select the Stored Procedure template. Enter the name HelloWorldStoredProcedure.cs and click Add.

Add the following line of code to the HelloWorldStoredProcedure( ) method in HelloWorldStoredProcedure.cs:

    SqlContext.Pipe.Send("Hello world.\n");

The complete code should now be as follows:

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

    public partial class StoredProcedures
    {
        [SqlProcedure]
        public static void HelloWorldStoredProcedure(  )
        {
            SqlContext.Pipe.Send("Hello world.\n");
        }
    };

Figure 4-4. Add New Item dialog box

Build the solution by selecting Build → Build Solution from the Visual Studio 2005 main menu, by clicking the Build Solution button on the Build toolbar, or by right-clicking the HelloWorld project in Solution Explorer and selecting Build from the context menu. The stored procedure is compiled into an assembly called HelloWorld.dll in the bin\Debug subdirectory.

Once the stored procedure is compiled, you need to register the assembly with SQL Server before you can access the CLR stored procedure. This walkthrough and many of the examples in this book use a database called ProgrammingSqlServer2005. Follow these steps to register the assembly with SQL Server:

Right-click the ProgrammingSqlServer2005 database in Object Explorer and select New Query from the context menu, as shown in Figure 4-5.
Register the assembly HelloWorld.dll with the SQL Server assembly name HelloWorld by executing the following T-SQL statement:
```
    USE ProgrammingSqlServer2005
    GO

    CREATE ASSEMBLY HelloWorld
    FROM 'C:\PSS2005\HelloWorld\HelloWorld\bin\Debug\HelloWorld.dll'
```
Figure 4-5. New Query menu item
The SQL Server assembly name and the .NET Framework assembly DLL name do not have to be the same. The SQL Server assembly name must be unique in the database.
You can confirm that the assembly is registered by expanding the Databases → ProgrammingSqlServer2005 → Programmability → Assemblies node in the Object Explorer tree view, as shown in Figure 4-6.
Figure 4-6. Object Explorer Assemblies node
Create a CLR stored procedure called HelloWorldSP based on the HelloWorld StoredProcedure( ) static method in the HelloWorld.dll assembly registered in Step 2. Execute the following query:

    CREATE PROCEDURE HelloWorldSP
    AS
    EXTERNAL NAME HelloWorld.StoredProcedures.HelloWorldStoredProcedure

The EXTERNAL NAME clause has three parts, separated by periods:

The SQL Server registered assembly name (from Step 2)—HelloWorld
The class name in the .NET Framework assembly—StoredProcedures
The name of the public static method implementing the stored procedure—HelloWorldStoredProcedure( )

You can confirm that the stored procedure was created by expanding the Databases → ProgrammingSqlServer2005 → Stored Procedure node in the Object Explorer tree view, as shown in Figure 4-7.

Figure 4-7. Object Explorer Stored Procedures node

You can now use the stored procedure just as you would use any other stored procedure.

Execute the HelloWorldSP stored procedure with the following T-SQL statement:

    EXEC HelloWorldSP

The results follow:

    Hello world.

The results are exactly the same as they would be if you had created and executed the following T-SQL stored procedure:

    CREATE PROCEDURE HelloWorldSP2
    AS
        PRINT 'Hello world.'

Once you have finished with the sample, you can remove the CLR stored procedure and registered .NET Framework assembly by executing the following statements:

    DROP PROCEDURE HelloWorldSP

    DROP ASSEMBLY HelloWorld

Command-Line Compiler

While the examples in this book use Visual Studio 2005, you can create the program files using any text editor and compile them using a .NET command-line compiler. SQL Server 2005 installs .NET Framework redistribution files, including command-line language compilers—for example csc.exe for C# and vbc.exe for VB.NET. The command-line compilers are installed in the directory C:\<windir>\Microsoft.NET\Framework\<version>, where:

<windir>: The directory in which your version of Windows is installed—often WINDOWS or WINNT
<version>: The .NET Framework version

To use the compiler, add the directory containing the compiler to your Path environment system variable defined in the System variables list box accessed through Control Panel → System → Advanced → Environment Variables.

To use the command-line C# compiler to compile the HelloWorldStoredProcedure.cs file created in the previous section, execute the following command:

    csc /target:library /out:HelloWorld.dll HelloWorldStoredProcedure.cs

The /target compiler flag instructs the compiler to build a DLL. The /out flag instructs the compiler to override the default DLL name HelloWorldStoredProcedure.dll with the name HelloWorld.dll. For more information about Visual Studio .NET compilers and compiler flags, consult the Microsoft Developer Network (MSDN).

Once you have compiled the .NET Framework assembly, you register it and CLR routines in the same way as if you had used the Visual Studio 2005 compiler.

DDL Support for CLR Integration

SQL Server introduces new T-SQL statements to create and manage .NET assemblies and UDTs, and enhances other T-SQL statements to create and manage functions, stored procedures, triggers, and UDA functions created from CLR assemblies. These statements are described in Table 4-4.

Table 4-4. New and changed T-SQL statements to support CLR integration

Scope	DDL statement	New T-SQL statement	Description
.NET Framework assembly	`CREATE ASSEMBLY`	Yes	Loads assembly into SQL Server.
	`ALTER ASSEMBLY`	Yes	Changes a loaded assembly.
	`DROP ASSEMBLY`	Yes	Unloads an assembly from SQL Server.
User-defined aggregate function	`CREATE AGGREGATE`	Yes	Creates a UDA function in a SQL Server database from a UDA function implemented as a class in a .NET Framework assembly. The assembly containing the class must first be registered in SQL Server with the `CREATE ASSEMBLY T-SQL` statement.
	`DROP AGGREGATE`	Yes	Removes a UDA function from a SQL Server database.
User-defined type	`CREATE TYPE`	No	Creates a UDT in a SQL Server database from a type implemented as a class or structure in a .NET Framework assembly. The assembly containing the class or structure must first be registered in SQL Server with the `CREATE ASSEMBLY T-SQL` statement.
	`DROP TYPE`	No	Removes a UDT from a SQL Server database.
Stored procedure	`CREATE PROCEDURE`	No	Creates a stored procedure in a SQL Server database from a CLR stored procedure implemented as a method in a .NET Framework assembly. The assembly containing the method must first be registered in SQL Server with the `CREATE ASSEMBLY T-SQL` statement.
	`ALTER PROCEDURE`	No	Changes a stored procedure previously created with the `CREATE PROCEDURE` T-SQL statement.
	DROP PROCEDURE	No	Removes a stored procedure from a SQL Server database.
User-defined function (scalar-valued or table-valued)	`CREATE FUNCTION`	No	Creates a UDF in a SQL Server database from a CLR UDF implemented as a method in a .NET Framework assembly. The assembly containing the method must first be registered in SQL Server with the `CREATE ASSEMBLY T-SQL` statement.
	`ALTER FUNCTION`	No	Changes a UDF previously created with the `CREATE FUNCTION` T-SQL statement.
	`DROP FUNCTION`	No	Removes a UDF from a SQL Server database.
Trigger	`CREATE TRIGGER`	No	Creates a DML or DDL trigger in a SQL Server database from a CLR trigger implemented as a method in a .NET Framework assembly. The assembly containing the method must first be registered in SQL Server with the `CREATE ASSEMBLY T-SQL` statement.
	`ALTER TRIGGER`	No	Changes a trigger previously created with the `CREATE TRIGGER` T-SQL statement.
	`DROP TRIGGER`	No	Removes a trigger from a SQL Server database.

The statements are described in detail in the following subsections. Chapter 5 presents in-depth discussions and examples of creating these objects in C#.

CLR Routine Metadata

Catalog views return metadata used by the SQL Server 2005 Database Engine. The sys.all_objects catalog view returns a row for each user-defined object and system object in the current database. The type column specifies the object type—the CLR routine type values are shown in Table 4-5.

Table 4-5. CLR object type values

sys.all_objects type column value	CLR object type
`AF`	Aggregate function
`FS`	Scalar-valued function
`FT`	Table-valued function
`PC`	Stored procedure
`TA`	Trigger

For example, the following T-SQL statement returns information about all CLR stored procedures in the AdventureWorks database:

    USE AdventureWorks
    GO

    SELECT * FROM sys.all_objects
    WHERE type='PC'

The sys.all_objects catalog view does not return information for DDL triggers. Use the sys.triggers catalog view instead to return information for all DML and DDL triggers in the current database.

The sys.all_objects catalog view does not return information for UDTs. The sys.types catalog view returns information for all system and user-defined types in the current database. The sys.assembly_types catalog view returns information for all CLR UDTs.

SQL Server 2005 provides catalog views that contain information about registered assemblies and CLR functions, stored procedures, triggers, UDTs, and UDA functions defined from registered assemblies. These catalog views are described in Table 4-6 and detailed in the following subsections.

Table 4-6. Catalog views returning metadata about registered .NET Framework assemblies and CLR routines

Catalog view	Description
`sys.assemblies`	A row for each assembly registered in the current database
`sys.assembly_files`	A row for each file that makes up an assembly
`sys.assembly_modules`	A row for each function, CLR stored procedure, or trigger
`sys.assembly_references`	A row for each pair of assemblies directly referencing each other
`sys.assembly_types`	A row for each CLR UDT

sys.assemblies

The sys.assemblies catalog view contains a row for each assembly registered in the current database. Table 4-7 describes the columns in this view.

Table 4-7. sys.assemblies catalog view columns

Column name	Description
`name`	The name of the assembly, unique within the schema.
`principal_id`	The ID of the principal that owns the schema.
`assembly_id`	The assembly ID number, unique within a database.
`permission_set`	The code access permissions for the assembly; one of the following numeric values: `1` = Safe Access `2` = External Access `3` = Unsafe Access
`permission_set_desc`	A description of code access permissions specified by the value of the `permission_set` column; one of the following string values: `SAFE_ACCESS`, `EXTERNAL_ACCESS`, or `UNSAFE_ACCESS`.
`is_visible`	A numeric value indicating the visibility of the assembly: `0` = The assembly can be called only by other assemblies. `1` = The assembly can be used to create CLR UDFs, stored procedures, triggers, UDTs, and UDA functions.
`clr_name`	Canonical string that uniquely identifies the assembly. The string encodes the simple name, version number (`version`), culture (`culture`), public key (`publickeytoken`), and architecture (`processorarchitecture`) of the assembly.
`create_date`	The date that the assembly was created or registered.

The following T-SQL statement returns a result set of all the CLR assemblies registered in the current database:

    SELECT * FROM sys.assemblies;

sys.assembly_files

The sys.assembly_files catalog view contains a row for each file in each registered assembly in the current database. Table 4-8 describes the columns in this view.

Table 4-8. sys.assembly_files catalog view columns

Column name	Description
`assembly_id`	The ID of the assembly to which the file belongs.
`name`	The name of the assembly file.
`file_id`	The ID of the file, unique within an assembly. The root assembly has a file ID of `1`. Files added to the assembly have a file ID of `2` or greater.
`content`	The binary contents of the file.

sys.assembly_modules

The sys.assembly_modules catalog view contains a row for each CLR function (scalar-valued, table-valued, and aggregate), stored procedure, or trigger defined in a .NET Framework assembly in the current database. Table 4-9 describes the columns in this view.

Table 4-9. sys.assembly_modules catalog view columns

Column name	Description
`object_id`	The ID of the module (CLR routine), unique within the database.
`assembly_id`	The ID of the assembly from which the module was created.
assembly_class	`The name of the class within the assembly that defines the module.`
assembly_method	`The name of the method within the assembly_class that defines this module. This value is NULL for aggregate functions.`
null_on_null_input	`Indicates whether the module returns NULL if any arguments are NULL.`
`execute_as_principal_id`	The database principal ID for the execution context. If this value is `NULL`, the execution context is `CALLER`.

sys.assembly_references

The sys.assembly_references catalog view contains a row for each pair of assemblies registered in the current database where one assembly directly references another. Table 4-10 describes the columns in this view.

Table 4-10. sys.assembly_references catalog view columns

Column name	Description
`assembly_id`	The ID of the assembly that has a reference to another assembly
`referenced_assembly_id`	The ID of the assembly being referenced

sys.assembly_types

The sys.assembly_types catalog view contains a row for each UDT in the current database that is defined in a CLR assembly. Table 4-11 describes the columns in this view.

Table 4-11. sys.assembly_types catalog view columns

Column name	Description
`<inherited_columns>`	Columns inherited from `sys.types` catalog view
`assembly_id`	The ID of the assembly from which the UDT was created
`assembly_class`	The name of the class within the assembly that defines the UDT
`is_binary_ordered`	Indicates whether sorting the bytes of the type is equivalent to sorting the type using comparison operators
`is_fixed_length`	Indicates whether the length of the type is the same as the maximum length (`max_length` in `sys.types`)
`prog_id`	The `ProgID` of the type exposed to COM
`assembly_qualified_name`	The assembly qualified type name

Assembly Management

A .NET Framework assembly contains classes and methods that can implement CLR routines in SQL Server 2005. You first have to register the assembly with SQL Server by using the CREATE ASSEMBLY T-SQL statement as you did earlier in the "Hello World Example" section. A registered assembly can be modified using the ALTER ASSEMBLY statement, or removed from the server using the DROP ASSEMBLY statement. These three new T-SQL statements are described in the following subsections.

CREATE ASSEMBLY

The CREATE ASSEMBLY T-SQL statement registers a .NET Framework assembly as an object within SQL Server from which CLR stored procedures, UDFs, triggers, UDA functions, and UDTs can be created.

The CREATE ASSEMBLY syntax is:

    CREATE ASSEMBLY assembly_name
      [ AUTHORIZATION owner_name ]
      FROM { client_assembly_specifier | assembly_bits [,...n] }
      [ WITH PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE } ]

    client_assembly_specifier :: =
      '[\\computer_name\]share_name\[path\]manifest_file_name'
      | '[local_path\]manifest_file_name'

    assembly_bits :: =
      { varbinary_literal | varbinary_expression }

where:

assembly_name: Specifies the name of the assembly, which must be unique within the database.
AUTHORIZATION owner_name: Specifies the name of the user or role that is the owner of the assembly. If not specified, ownership is assigned to the current user.
FROM: Specifies the .NET Framework assembly to load.
client_assembly_specifier: Specifies the local path or Universal Naming Convention (UNC) network location where the assembly is located and the manifest filename for the assembly. Multimodule assemblies are not supported. Dependent assemblies are either automatically uploaded from the same location or loaded from the current database if owned by the same principal—CREATE ASSEMBLY fails if either is not possible.
assembly_bits: Specifies a list of binary values that make up the assembly and its dependent assemblies. The root-level assembly must be specified first followed by the dependent assemblies in any order.
PERMISSION_SET = { SAFE | EXTERNAL_ACCESS | UNSAFE }: Specifies the code-access security when SQL Server accesses the assembly. If not specified, the default is SAFE.

Multiple versions of the same assembly can be uploaded to the server. These assemblies must have different version numbers or cultures and must be registered using unique assembly names within SQL Server.

ALTER ASSEMBLY

The ALTER ASSEMBLY T-SQL statement modifies the properties of an assembly previously registered using the CREATE ASSEMBLY statement and refreshes the assembly with the latest version.

The ALTER ASSEMBLY syntax is:

    ALTER ASSEMBLY assembly_name
      [ FROM { client_assembly_specifier | assembly_bits [ ,...n ] } ]
      [ WITH assembly_option [ ,...n ] ]
      [ DROP FILE { file_name [ ,...n ] | ALL } ]
      [ ADD FILE FROM
      {
        client_file_specifier [ AS file_name ]
        | file_bits AS file_name
      } [,...n ] ]

    client_assembly_specifier :: =
      '\\computer_name\share-name\[path\]manifest_file_name'
      | '[local_path\]manifest_file_name'

    assembly_bits :: =
      { varbinary_literal | varbinary_expression }

    assembly_option :: =
      PERMISSION_SET { SAFE | EXTERNAL_ACCESS | UNSAFE }
      | VISIBILITY { ON | OFF } ]
      | UNCHECKED DATA

where:

assembly_name: Specifies the name of the registered assembly to alter.
FROM: Specifies the .NET Framework assembly to refresh with the latest copy.
DROP FILE { file_name [ ,...n ] | ALL }: Removes the file associated with the assembly or all files associated with the assembly. DROP FILE executes before ADD FILE if both are specified.
ADD FILE FROM { client_file_specifier [ AS file_name ] | file_bits AS file_name }: Uploads a file that is to be associated with the assembly from the location specified by the client_file_specifier argument or from the binary values that make up the file specified by the file_bits argument. The file_name argument specifies the name to use to store the file in SQL Server. If the file_name argument is not specified with a client_file_specified argument, the filename part of the client_file_specifier is used as the name in SQL Server.
VISIBILITY { ON | OFF }: Specifies whether the assembly can be used to create CLR stored procedures, functions, triggers, UDTs, and UDA functions. Assemblies with VISIBILITY = OFF can be called only by other assemblies . The default VISIBILITY is ON.
UNCHECKED DATA: Alters the assembly even if there are tables with columns or check constraints that reference methods in the assembly or if there are CLR UDTs that are dependent on the assembly and use User-Defined serialization format. Only members of db_owner and db_ddlowner can specify this option.

Other arguments are the same as defined for the CREATE ASSEMBLY statement.

Executing ALTER ASSEMBLY does not affect currently executing sessions running the assembly being altered—they complete using the unaltered assembly. A new application domain is created running the latest bits for new users of the assembly.

If the FROM clause is not specified, the assembly is refreshed with the latest copy of the assembly rebinding CLR routines to the latest implementation in the assembly.

DROP ASSEMBLY

The DROP ASSEMBLY statement removes an assembly previously registered with the CREATE ASSEMBLY statement. The assembly and all of its associated files are removed from the database.

The DROP ASSEMBLY syntax is:

    DROP ASSEMBLY assembly_name
    [ WITH NO DEPENDENTS ]

where:

assembly_name: Specifies the name of the registered assembly to drop
WITH NO DEPENDENTS: Specifies that dependent assemblies are not to be dropped

Executing DROP ASSEMBLY does not affect currently executing sessions running the assembly being dropped—they run to completion. New attempts to invoke code in the assembly fail.

You cannot drop an assembly that is referenced by another assembly or that is used by a CLR function, stored procedure, trigger, UDT, or UDA function.

User-Defined Functions

A user-defined function (UDF ) is a routine written by the user that returns either a scalar value (scalar-valued function) or a table (table-valued function). SQL Server 2005 functions can be created directly from T-SQL statements or from methods in a registered .NET Framework assembly.

UDFs are created, changed, and removed in SQL Server using the CREATE FUNCTION, ALTER FUNCTION, and DROP FUNCTION T-SQL statements. These statements have been enhanced in SQL Server 2005 to support CLR UDF management. The following subsections describe the enhancements.

CREATE FUNCTION

The CREATE FUNCTION T-SQL statement has been enhanced in SQL Server 2005 to let you create a CLR scalar-valued or table-valued UDF from a UDF implemented as a method in a .NET Framework assembly. You must first register the assembly using the CREATE ASSEMBLY statement. The CREATE FUNCTION syntax for creating CLR UDFs follows:

    CREATE FUNCTION [ schema_name. ] function_name (
      [ { @parameter_name [AS] [ type_schema_name. ] scalar_parameter_data_type }
      [ ,...n ] ] )
    RETURNS { scalar_return_data_type | TABLE clr_table_type_definition }
      [ WITH clr_function_option [ [,] ...n ] ]
      [ AS ] EXTERNAL NAME method_specifier [ ; ]

    method_specifier ::=
      assembly_name.class_name.method_name

                  clr_table_type_definition ::=
      ( { column_name data_type }[ ,...n ] )

    clr_function_option ::=
      [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
      | [ EXECUTE_AS_Clause ]

where:

[ schema_name. ] function_name

Specifies the name of the CLR UDF to create.

@parameter_name [AS] [ type_schema_name. ] scalar_parameter_data_type

Defines zero or more parameters for the UDF:

@parameter_name: Specifies the name of a parameter for the function. Specify a parameter using an ampersand (@) as the first character. DEFAULT parameters cannot be specified for CLR UDFs.
[ type_schema_name. ] scalar_parameter_data_type: Specifies the parameter data type and optionally its schema. This can be any scalar data type supported by SQL Server except timestamp. The return value data type can also be a CLR UDT. char, varchar, and ntext data types cannot be specified for CLR scalar-valued UDFs—use nchar and nvarchar instead.

scalar_return_data_type

Specifies the data type of the return value of a scalar-valued UDF. This can be any scalar data type supported by SQL Server except text, ntext, image, and timestamp. The return value data type can also be a CLR UDT. char and varchar data types cannot be specified for CLR scalar-valued UDFs—use nchar and nvarchar instead.

clr_table_type_definition

Defines the CLR table returned from a table-valued UDF:

column_name: Specifies the name of the column in the table.
data_type: Specifies the data type of the column—timestamp and UDTs are not supported.

clr_function_option

Specifies the OnNullCall attribute of a scalar-valued function:

RETURNS NULL ON NULL INPUT: Specifies that SQL Server does not execute the function and returns NULL as the result of the function if any of the input arguments are NULL.
CALLED ON NULL INPUT: Specifies that SQL Server executes the function even if one or more input arguments are NULL.

If not specified, the default is CALLED ON NULL INPUT.

The CREATE FUNCTION value of the OnNullCall attribute takes precedence over the method’s OnNullCall attribute if specified in the .NET code.

EXECUTE_AS_Clause: Specifies the security execution context for the UDF.

method_specifier

Specifies the method in the .NET Framework assembly that implements the UDF:

assembly_name: Specifies the name of the registered assembly that contains the method implementing the CLR UDF.
class_name: Specifies the name of the class in the assembly that implements the CLR UDF. The class name can be namespace-qualified, in which case it must be enclosed in brackets ([]).
method_name: Specifies a public static method of the class that implements the CLR UDF functionality.

ALTER FUNCTION

The ALTER FUNCTION T-SQL statement has been enhanced in SQL Server 2005 to let you modify a CLR UDF previously created using the CREATE FUNCTION statement. The ALTER FUNCTION syntax for creating CLR UDFs follows:

    ALTER FUNCTION [ schema_name. ] function_name
      ( { @parameter_name [AS] [ type_schema_name. ] scalar_parameter_data_type }
      [ ,...n ] )
    RETURNS { scalar_return_data_type | TABLE <clr_table_type_definition> }
      [ WITH clr_function_option [ ,...n ] ]
      [ AS ] EXTERNAL NAME method_specifier

                  method_specifier ::=
        [ assembly_schema. ] assembly_name.class_name.method_name

                  clr_table_type_definition :: =
    ( { column_name data_type } [ ,...n ] )

    clr_function_option ::=
    [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
    | [ EXECUTE_AS_Clause ]

The arguments are the same as for the CREATE FUNCTION statement discussed in the preceding section.

DROP FUNCTION

The DROP FUNCTION T-SQL statement removes one or more UDFs previously created using the CREATE FUNCTION statement. The SQL Server 2005 DROP FUNCTION statement is the same as in SQL Server 2000. It now supports removing CLR UDFs.

The DROP FUNCTION syntax is:

    DROP FUNCTION { [ schema_name. ] function_name } [ ,...n ]

DROP FUNCTION will fail if T-SQL functions or views in the database created with SCHEMABINDING or indexed computed columns reference this function.

Stored Procedures

A stored procedure is a saved collection of T-SQL statements or a reference to a CLR method that optionally takes and returns arguments and returns one or more result sets of data. SQL Server 2005 stored procedures can be created directly from T-SQL statements or from methods in registered .NET Framework assemblies.

Stored procedures are created, updated, and removed using the CREATE PROCEDURE, ALTER PROCEDURE, and DROP PROCEDURE T-SQL statements. These statements have been enhanced in SQL Server 2005 to support CLR stored procedure management. The following subsections describe these enhancements.

CREATE PROCEDURE

The CREATE PROCEDURE T-SQL statement has been enhanced in SQL Server 2005 to let you create a CLR stored procedure from a stored procedure implemented as a method in a .NET Framework assembly. You must first register the assembly using the CREATE ASSEMBLY statement as you did earlier in the "Hello World Example" section. The enhancements supporting CLR stored procedures are highlighted in the CREATE PROCEDURE syntax that follows:

    CREATE PROC [ EDURE ] [schema_name.] procedure_name
      [ { @parameter [ type_schema_name. ] data_type }
      [ VARYING ] [ = default ] [ [ OUT [ PUT ] ] [ ,...n ]
      [ WITH <procedure_option> [ ,...n ]
      [ FOR REPLICATION ]
    AS { sql_statement [ ...n ] | method_specifier }

    procedure_option ::=
      [ ENCRYPTION ]
      [ RECOMPILE ]
      [ EXECUTE_AS_Clause ]

    sql_statement ::=
      { [ BEGIN ] statements [ END ] }

    method_specifier ::= EXTERNAL NAME assembly_name.class_name.method_name

where the new arguments are as follows:

method_specifier

Specifies the method in the .NET Framework assembly that implements the stored procedure:

assembly_name: Specifies the name of the registered assembly that contains the method implementing the stored procedure.
class_name: Specifies the name of the class in the assembly that implements the stored procedure. The class name can be namespace-qualified, in which case it must be enclosed in brackets ([ ]).
method_name: Specifies the name of the public static method implementing the CLR stored procedure.

ALTER PROCEDURE

The ALTER PROCEDURE T-SQL statement has been enhanced in SQL Server 2005 to let you modify a CLR stored procedure previously created using the CREATE PROCEDURE statement. The enhancements supporting CLR stored procedures are highlighted in the ALTER PROCEDURE syntax that follows:

    ALTER PROC [ EDURE ] [schema_name.] procedure_name
      [ { @parameter [ type_schema_name. ] data_type }
      [ VARYING ] [ = default ] [ [ OUT [ PUT ] ] [ ,...n ]
      [ WITH procedure_option [ ,...n ] ]
      [ FOR REPLICATION ]
    AS
      { sql_statement [ ...n ] | method_specifier }

    procedure_option ::=
      [ ENCRYPTION ]
      [ RECOMPILE ]
      [ EXECUTE_AS_Clause ]

    sql_statement ::=
      { [ BEGIN ] statements [ END ] }

    
                     method_specifier ::= EXTERNAL NAME [ assembly_schema. ] assembly_name.class_name.method_name

where the new arguments are as follows:

method_specifier

Specifies the method in the .NET Framework assembly that implements the stored procedure:

assembly_schema: Specifies the schema name for the assembly. If not specified, assembly_name must match an assembly in either the current user’s schema or in the dbo schema.
assembly_name: Specifies the name of the registered assembly that contains the method that implements the stored procedure.
class_name: Specifies the name of the class in the assembly that implements the stored procedure. The class name can be namespace-qualified, in which case it must be enclosed in brackets ([ ]).
method_name: Specifies the name of the public static method implementing the CLR stored procedure.

DROP PROCEDURE

The DROP PROCEDURE T-SQL statement removes one or more stored procedures previously created using the CREATE PROCEDURE statement. The SQL Server 2005 DROP PROCEDURE statement is the same as in SQL Server 2000. It now supports removing CLR stored procedures.

The DROP PROCEDURE syntax is:

    DROP PROCEDURE { [ schema_name. ] procedure } [ ,...n ]

Metadata

The sys.procedures catalog view contains a row for each stored procedure in the current database. The view inherits columns from the sys.objects and sys.all_objects catalog views, so you can limit the rows returned to CLR stored procedures by filtering on the type column, as shown in the following statement:

    SELECT * FROM sys.procedures
    WHERE type='PC'

The WHERE clause specifying the type PC returns CLR stored procedures. Specifying type P returns SQL stored procedures.

User-Defined Aggregate Functions

User-defined aggregate (UDA functions compute a value over a group in a result set. SQL Server 2005 UDA functions can be created directly from T-SQL statements or from a class in a registered .NET Framework assembly.

UDA functions are created and removed in SQL Server using the CREATE AGGREGATE and DROP AGGREGATE T-SQL statements. These statements have been enhanced in SQL Server 2005 to support CLR UDA function management. The following subsections describe the enhancements.

CREATE AGGREGATE

The CREATE AGGREGATE T-SQL statement creates a UDA function from an implementation by a class in a registered assembly. The assembly must first be registered using the CREATE ASSEMBLY statement.

The CREATE AGGREGATE syntax is:

    CREATE AGGREGATE [ schema_name. ] aggregate_name
    ..(@parameter input_sqltype )
    RETURNS return_sqltype
    EXTERNAL NAME assembly_name [ .class_name ]

    input_sqltype ::=
    ..system_scalar_type | { [ udt_schema_name. ] udt_type_name }

    return_sqltype ::=
    ..system_scalar_type | { [ udt_schema_name. ] udt_type_name }

where:

[ schema_name. ] aggregate_name: Specifies the name of the CLR aggregate function to create.
@parameter input_sqltype: Specifies the name of a parameter in the CLR aggregate function. The name must be prefixed with an ampersand (@). A parameter can specify a constant only, and not the names of database objects such as table names and columns names.
EXTERNAL NAME assembly_name [ .class_name ]: Specifies the registered .NET Framework assembly and optionally the name of the class in the assembly that implements the CLR aggregate function. If class_name is not specified, it defaults to aggregate_name.
system_scalar_type: Specifies a SQL Server scalar data type for the input or return value.
[ udt_schema_name. ] udt_type_name: The name of a CLR UDT in SQL Server. If the schema name is not specified, it defaults to the schema of the current user.

DROP AGGREGATE

The DROP AGGREGATE T-SQL statement removes a UDA function previously created using the CREATE AGGREGATE statement.

The DROP AGGREGATE syntax follows:

    DROP AGGREGATE [ schema_name. ] aggregate_name

where:

[ schema_name. ] aggregate_name: Specifies the name of the UDA function to remove.

The DROP AGGREGATE statement does not execute if there are views, functions, or stored procedures created with schema binding that reference the UDA function.

User-Defined Types

SQL Server 2000 supports user-defined types (UDTs ), also known as alias types. You create these by using the sp_addtypes system stored procedure. They are derived from SQL Server built-in data types and optionally have integrity constraints called rules.

SQL Server 2005 extends UDT functionality by letting you define CLR UDTs from a class in a registered .NET Framework assembly. A CLR UDT can store multiple items and expose methods, properties, and attributes. You can use a UDT as the data type for a column in a table, as a T-SQL variable, or as a parameter for stored procedures or functions.

UDTs are created and removed in SQL Server using the CREATE TYPE and DROP TYPE T-SQL statements. These statements have been enhanced in SQL Server 2005 to support CLR UDT management. The following subsections describe the enhancements.

CREATE TYPE

The CREATE TYPE statement has been enhanced in SQL Server 2005 to let you create a CLR UDT from a type implemented as a class or structure in a .NET Framework assembly. You must first register the assembly using the CREATE ASSEMBLY statement. The enhancements supporting CLR UDTs are highlighted in the CREATE TYPE syntax that follows:

    CREATE TYPE [ schema_name. ] type_name
      { FROM base_type
      [ ( precision [ , scale ] )  ]
      [ NULL | NOT NULL ]
      | EXTERNAL NAME 
                  assembly_name 
                  [ .
                  class_name 
                  ] }

where:

[ schema_name. ] type_name: Specifies the name of the CLR UDT to create.
assembly_name: Specifies the name of the registered assembly that implements the CLR UDT.
class_name: Specifies the name of the class that implements the CLR UDT. The class name can be namespace-qualified, in which case it must be enclosed in brackets ([ ]).

DROP TYPE

The DROP TYPE T-SQL statement removes a UDT previously created using the CREATE TYPE statement. The SQL Server 2005 DROP TYPE statement is the same as in SQL Server 2000. It now supports removing CLR UDTs .

The DROP TYPE syntax is:

    DROP TYPE [ schema_name. ] type_name

where:

[ schema_name. ] type_name: Specifies the name of the UDT to remove

DROP TYPE will not execute if there are tables in the database with columns of the UDT, if there are columns of the sql_variant data type that contain the UDT, or if there are functions, stored procedures, or triggers in the database created with the WITH SCHEMABINDING clause that use variables or parameters of the UDT.

Metadata

The sys.assembly_types catalog view contains a row for all CLR UDTs in the current database. The view inherits all columns from the sys.types catalog view.

Triggers

A trigger is a type of stored procedure that executes in response to one or more specific database events. DML triggers execute when data is modified using T-SQL DML statements such as INSERT, UPDATE, or DELETE. DDL triggers execute when database objects are modified using T-SQL DDL statements such as CREATE, ALTER, and DROP. SQL Server 2005 DML and DDL triggers can be created directly from T-SQL statements or from methods in a registered .NET Framework assembly.

Triggers are created, changed, and removed using the CREATE TRIGGER, ALTER TRIGGER, and DROP TRIGGER T-SQL statements. These statements have been enhanced in SQL Server 2005 to support CLR trigger management. The following subsections describe the enhancements.

CREATE TRIGGER

The CREATE TRIGGER T-SQL statement has been enhanced in SQL Server 2005 to let you create a CLR DML or DDL trigger from a trigger implemented as a method in a .NET Framework assembly. You must first register the assembly using the CREATE ASSEMBLY statement. The enhancements supporting CLR triggers are highlighted in this section.

The CREATE TRIGGER syntax for DML triggers is:

    CREATE TRIGGER [ schema_name. ]trigger_name
      ON { TABLE | VIEW }
      [ WITH dml_trigger_option [ ,...n ] ]
      { FOR | AFTER | INSTEAD OF }
      { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
      [ WITH APPEND ]
      [ NOT FOR REPLICATION ]
    AS { sql_statement [ ...n ] | EXTERNAL NAME method specifier }

    dml_trigger_option ::=
        [ ENCRYPTION ]
        [ EXECUTE AS Clause ]

    
                     
                        method_specifier                       ::=
         
                        assembly_name.class_name.method_name

The CREATE TRIGGER syntax for DDL triggers is:

    CREATE TRIGGER trigger_name
      ON { ALL SERVER | DATABASE }
      [ WITH <ddl_trigger_option> [,...n ] ]
      { FOR | AFTER } { event_type | event_group } [ ,...n ]
    AS { sql_statement [ ...n ] | EXTERNAL NAME <method specifier> }

    ddl_trigger_option ::=
      [ ENCRYPTION ]
      [ EXECUTE AS Clause ]

    
                     
                        method_specifier                       ::=
        
                        assembly_name.class_name.method_name

where the new arguments for both DML and DDL triggers are:

method_specifier

Specifies the method in the .NET Framework assembly that implements the trigger:

assembly_name: Specifies the name of the registered assembly that contains the method that implements the trigger.
class_name: Specifies the name of the class in the assembly that implements the CLR trigger. The class name can be namespace-qualified, in which case it must be enclosed in brackets ([]).
method_name: Specifies the public static method of the class that implements the CLR trigger functionality.

ALTER TRIGGER

The ALTER TRIGGER T-SQL statement has been enhanced in SQL Server 2005 to let you modify a CLR DML or DDL trigger previously created using the CREATE TRIGGER statement. The enhancements supporting CLR triggers are highlighted in this section.

The ALTER TRIGGER syntax for DML triggers follows:

    ALTER TRIGGER schema_name.trigger_name
      ON ( TABLE | VIEW )
      [ WITH dml_trigger_option [ ...,n ] ]
      ( FOR | AFTER | INSTEAD OF )
      { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
      [ NOT FOR REPLICATION ]
    AS { sql_statement [ ...n ] | EXTERNAL NAME method specifier }

    dml_trigger_option ::=
      [ ENCRYPTION ]
      [ EXECUTE AS Clause ]

    method_specifier ::= [ assembly_schema. ] assembly_name.class_name.method_name

The ALTER TRIGGER syntax for DDL triggers follows:

    ALTER TRIGGER trigger_name
      ON { DATABASE | ALL SERVER }
      [ WITH ddl_trigger_option [ ...,n ] ]
      { FOR | AFTER } { event_type [ ,...n ] | event_group }
    AS { sql_statement | EXTERNAL NAME method specifier }

    ddl_trigger_option ::=
      [ ENCRYPTION ]
      [ EXECUTE AS Clause ]

    method_specifier ::= [ assembly_schema. ] assembly_name.class_name.method_name

The new arguments for both DML and DDL triggers are the same as for the CREATE TRIGGER statement discussed in the preceding section.

DROP TRIGGER

The DROP TRIGGER T-SQL statement removes one or more DML or DDL triggers previously created using the CREATE TRIGGER statement. The SQL Server 2005 DROP TRIGGER statement is the same as in SQL Server 2000. It now supports removing CLR triggers.

The DROP TRIGGER syntax for DML triggers is:

    DROP TRIGGER schema_name.trigger_name [ ,...n ]

The DROP TRIGGER syntax for DDL triggers is:

    DROP TRIGGER trigger_name [ ,...n ]
    ON { DATABASE | ALL SERVER }

Metadata

The sys.triggers catalog view contains a row for each trigger in the current database. You can limit the rows returned to CLR triggers by filtering on the type column, as shown in the following statement:

    SELECT * FROM sys.triggers
    WHERE type='TA'

ADO.NET In-Process Extensions Supporting CLR Programming

ADO.NET has four main in-process functional extensions that are used when programming .NET Framework routines. The SqlContext object provides access to context information, to a SqlPipe object for sending results to the client, and to a SqlTriggerContext object that provides information about the operation that caused a trigger to fire. The fourth—the SqlDataRecord object—returns to the caller a custom result set from a stored procedure. These four extensions are discussed in the following subsections.

SqlContext Object

Managed code is invoked in the server whenever a CLR routine is executed. Code running on the server executes in the context of the caller connection, so the CLR code needs access to the caller context. The SqlContext class in the Microsoft.SqlServer.Server namespace abstracts the context of the caller and provides access to the context components through its public static properties, described in Table 4-12.

Table 4-12. SqlContext public properties

Property	Return type	Description
`IsAvailable`	`bool`	Indicates whether the code that is executing is running inside SQL Server. If `true`, other members of `SqlContext` can be accessed. If `false`, all other properties will throw `InvalidOperationException` when accessed, and any attempts to open a connection using the `context connection = true` attribute in the connection string will fail.
`Pipe`	`SqlPipe`	A path for messages and result sets to flow to the client.
`TriggerContext`	`SqlTriggerContext`	Provides access to information about the operation that caused a DML or DDL trigger to fire. Also provides a map of the updated columns. You can retrieve `TriggerContext` only within a CLR trigger.
`WindowsIdentity`	`System.Security.Principal.WindowsIdentity`	Provides access to an impersonation token representing the Windows identity of the caller if the client that initiated execution of the stored procedure or function connected to SQL Server using integrated authentication. `null` is returned if the caller was authenticated using SQL Server authentication and the code cannot impersonate the caller. The SQL Server process account is the context for all CLR code invoked inside of SQL Server. The impersonation token is used to let the code perform actions using the identity of the caller instead of the identity of the process account. Only assemblies marked with `EXTERNAL_ACCESS` or `UNSAFE` permission can access the `WindowsIdentity` property.

You obtain an in-process connection using the new connection context connection string keyword. For example:

    SqlConnection conn = new SqlConnection("context connection=true")

SqlPipe Object

Use the SqlPipe object to send messages and result sets from a CLR stored procedure to the calling client. The SqlPipe object cannot be directly instantiated. You obtain the SqlPipe object using the Pipe property of the SqlContext object within the body of a CLR routine, as shown in the "Hello World Example" section earlier in this chapter. The SqlPipe class has the public properties and methods described in Table 4-13.

Table 4-13. SqlPipe public properties and methods

Property	Description
`IsSendingResults`	Indicates whether the pipe is in the process of sending a result set, blocking it from use.
Method
`ExecuteAndSend( )`	Executes a command specified as a `SqlCommand` object argument. The results are sent directly back to the client.
`Send( )`	Three overloads send one of the following to the client: `string` (informational message—equivalent to T-SQL `PRINT` statement) `SqlDataRecord` object (single-row result set) `SqlDataReader` object (multiple-row result set)
`SendResultsEnd( )`	Marks the end of a custom result set from a stored procedure initiated by the `SendResultsStart( )` method. Sets the `SqlPipe` object back to a state where other methods can be called on it. This method can be called only after `SendResultsStart( )` is called.
`SendResultsRow( )`	Sends a row of data contained in a `SqlDataRecord` object to the client. This method can be called only after `SendResultsStart( )` is called. Each row must conform to the `SqlDataRecord` argument describing the row that is supplied to the `SendResultsStart( )` method.
`SendResultsStart( )`	Marks the start of a custom result set from a stored procedure. This method takes a `SqlDataRecord` argument to construct the metadata that describes the result set. All rows in the result set subsequently sent to the client using the `SendResultsRow( )` method must conform to this metadata.

SqlTriggerContext Object

The SqlTriggerContext class provides context information about the CLR DML or DDL trigger. The SqlTriggerContext object cannot be directly instantiated. You obtain the SqlTrigger object using the TriggerContext property of the SqlContext object within the body of a CLR trigger. The SqlTriggerContext class has the public properties and methods described in Table 4-14.

Table 4-14. SqlTriggerContext public properties and methods

Property	Description
`ColumnCount`	The number of columns potentially affected by the `UPDATE` operation that caused the DML trigger to fire.
`EventData`	A `SqlXml` object containing XML describing the triggering operation for a DDL trigger.
`TriggerAction`	The type of action that caused the trigger to fire. This is one of the `TriggerAction` enumeration values.
`IsUpdatedColumn( )`	Indicates whether a column specified by its ordinal was modified by the `UPDATE` operation that caused the DML trigger to fire.

SqlDataRecord Object

The SqlDataRecord class represents a single row of data together with its metadata. The class allows stored procedures to return custom result sets to the client using the Send( ) or SendResultsRow( ) methods of the SqlPipe object.

You instantiate a SqlDataRecord object by passing to the constructor a SqlMetaData object array that contains an element of metadata for each column in the row. Each SqlMetaData object defines a column name, column type, and possibly other column attributes. For example, the following code defines a SqlDataRecord containing two columns:

    SqlMetaData[] md = new SqlMetaData[2];
    md[0] = new SqlMetaData("intCol", SqlDbType.Int);
    md[1] = new SqlMetaData("stringCol", SqlDbType.NVarChar, 50);
    SqlDataRecord row = new SqlDataRecord(md);

The SqlDataRecord class has accessor methods that let you get and set column values. This is similar to a DataReader except that you can write column values in addition to reading them. For example, the following code fills the two columns in the SqlDataRecord object defined in the preceding example:

    row.SetSqlInt32(0, 1);
    row.SetSqlString(1, "Record 1");

Custom Attributes for CLR Routines

The .NET CLR is extended using attributes—descriptive keywords saved in the assembly metadata that provide additional information for programming constructs. The custom attributes used with SQL Server 2005 CLR routines are defined in the Microsoft.SqlServer.Server namespace. Table 4-15 describes custom attributes used with SQL Server CLR routines.

Table 4-15. Custom attributes for CLR routines

Attribute	CLR routine	Description
`SqlFacet`	UDT	Specifies details about the return type of a UDT.
`SqlFunction`	UDF	Indicates that the method should be registered as a UDF.
`SqlMethod`	UDT	Specifies the determinism and data access properties of methods in a UDT.
`SqlProcedure`	Stored procedure	Indicates that the method should be registered as a stored procedure.
`SqlTrigger`	Trigger	Indicates that the method should be registered as a trigger.
`SqlUserDefinedAggregate`	UDA	Indicates that the method should be registered as a UDA.
`SqlUserDefinedType`	UDT	Indicates that the class or structure should be registered as a UDT.

These attributes are discussed in detail in Chapter 5 in the specific sections about the .NET Framework routines that use them.

SQL Server Data Types in the .NET Framework

The System.Data.SqlTypes namespace is part of the base class library of the .NET Framework. The namespace provides data types that map closely to native SQL Server data types. There are differences between SqlTypes data types and .NET Framework data types:

SqlTypes data types support NULL values while .NET Framework data types do not. All arithmetic and bitwise operators and most functions return NULL if any SqlTypes operands or arguments are NULL.
SqlTypes provides a SqlBoolean data type that represents a tristate logical value—true, false, and null (unknown value).
The .NET Framework Decimal data type and the corresponding SQL Server Decimal data type have different maximum values. The Decimal data type assumes maximum precision, whereas the SqlDecimal data type and the SQL Server Decimal data type have the same maximum precision, scale, and semantics.
Exceptions are thrown for all overflow and underflow errors and divide-by-zero errors when using SqlTypes data types. This behavior is not guaranteed with .NET Framework data types.

Table 4-16 lists SQL Server data types and their equivalents in the System.Data.SqlTypes namespace and in the .NET Framework.

Table 4-16. SQL Server, System.Data.SqlTypes, and .NET Framework data type equivalents

SQL Server data type	System.Data.SqlTypes data type	.NET Framework data type
`varbinary`	`SqlBytes`, `SqlBinary`	`Byte[]`
`binary`	`SqlBytes`, `SqlBinary`	`Byte[]`
`image`	None	None
`varchar`	None	None
`char`	None	None
`nvarchar`	`SqlChars`, `SqlString`	`String`, `Char[]`
`nchar`	`SqlChars`, `SqlString`	`String`, `Char[]`
`text`	None	None
`ntext`	None	None
`uniqueidentifier`	`SqlGuid`	`Guid`
`rowversion`	None	`Byte[]`
`bit`	`SqlBoolean`	`Boolean`
`tinyint`	`SqlByte`	`Byte`
`smallint`	`SqlInt16`	`Int16`
`int`	`SqlInt32`	`Int32`
`bigint`	`SqlInt64`	`Int64`
`smallmoney`	`SqlMoney`	`Decimal`
`money`	`SqlMoney`	`Decimal`
`numeric`	`SqlDecimal`	`Decimal`
`decimal`	`SqlDecimal`	`Decimal`
`real`	`SqlSingle`	`Single`
`float`	`SqlDouble`	`Double`
`smalldatetime`	`SqlDateTime`	`DateTime`
`datetime`	`SqlDateTime`	`DateTime`
`sql_variant`	None	`Object`
User-defined type (UDT)	None	Same class bound to the type in the registered assembly or dependent assembly
`table`	None	None
`cursor`	None	None
`timestamp`	None	None
`xml`	`SqlXml`	None

Testing and Debugging CLR Routines

SQL Server 2005 lets you debug both T-SQL database objects and CLR routines. SQL Server 2005 does not ship with a debugger, so you must use Visual Studio 2005.

Follow these steps to use the Visual Studio 2005 debugger to step through source code for registered CLR assemblies when executing T-SQL statements using .NET Framework routines:

Add the PDB file—a file containing debugging and project state information—to the registered assembly. For example, to add the PDB file to the HelloWorld stored procedure project in the "Hello World Example" section earlier in this chapter, execute the following T-SQL statement:
```
    ALTER ASSEMBLY HelloWorld
    ADD FILE FROM 'C:\PSS2005\HelloWorld\HelloWorld\bin\Debug\HelloWorld.pdb'
```
In the Visual Studio 2005 IDE, select Debug → Attach to Process.
Check the “Show processes from all users” checkbox in the Attach to Process dialog box.
Select sqlservr.exe from the Available Processes list box. Click the Attach button and close the dialog box.
Set a breakpoint in the source code at the following line:
```
    SqlContext.Pipe.Send("Hello world.\n");
```
Execute the stored procedure from SQL Server Management Studio:
```
    exec HelloWorldSP
```
Execution will stop at the breakpoint you set in the Visual Studio IDE.

Chapter 4. Introduction to Common Language Runtime (CLR) Integration

CLR Integration Design Objectives

Enabling CLR Integration

Required .NET Namespaces

Types of CLR Routines

Hello World Example

Command-Line Compiler

DDL Support for CLR Integration

CLR Routine Metadata

sys.assemblies

sys.assembly_files

sys.assembly_modules

sys.assembly_references

sys.assembly_types

Assembly Management

CREATE ASSEMBLY

ALTER ASSEMBLY

DROP ASSEMBLY

User-Defined Functions

CREATE FUNCTION

ALTER FUNCTION

DROP FUNCTION

Stored Procedures

CREATE PROCEDURE

ALTER PROCEDURE

DROP PROCEDURE

Metadata

User-Defined Aggregate Functions

CREATE AGGREGATE

DROP AGGREGATE

User-Defined Types

CREATE TYPE

DROP TYPE

Metadata

Triggers

CREATE TRIGGER

ALTER TRIGGER

DROP TRIGGER

Metadata

ADO.NET In-Process Extensions Supporting CLR Programming

SqlContext Object

SqlPipe Object

SqlTriggerContext Object

SqlDataRecord Object

Custom Attributes for CLR Routines

SQL Server Data Types in the .NET Framework

Testing and Debugging CLR Routines

Don’t leave empty-handed

It’s yours, free.

Check it out now on O’Reilly