Overview of CLR Integration Custom Attributes


The common language runtime (CLR) of the .NET Framework allows the use of descriptive keywords, called attributes. These attributes provide additional information for many elements, such as methods and classes. The attributes are saved in the assembly with the metadata of the object, and can be used to describe your code to other development tools or to affect runtime behavior inside SQL Server.

When you register a CLR routine with SQL Server, SQL Server derives a set of properties about the routine. These routine properties determine the capabilities of the routine, including whether the routine can be indexed. For example, setting the DataAccess property to DataAccessKind.Read lets you access data from SQL Server user tables inside a CLR function. The following example shows a simple case in which the DataAccess property is set to facilitate data access from a user table table1.

using System;  
using System.Data;  
using System.Data.Sql;  
using System.Data.SqlTypes;  
using Microsoft.SqlServer.Server;  
using System.Data.SqlClient;  
public partial class UserDefinedFunctions  
    [SqlFunction(DataAccess = DataAccessKind.Read)]  
    public static string func1()  
        // Open a connection and create a command  
        SqlConnection conn = new SqlConnection("context connection = true");  
        SqlCommand cmd = conn.CreateCommand();  
        cmd.CommandText = "SELECT str_val FROM table1 WHERE int_val = 10";  
        // where table1 is a user table  
        // Execute this command   
        SqlDataReader rd = cmd.ExecuteReader();  
        // Set string ret_val to str_val returned from the query  
        string ret_val = rd.GetValue(0).ToString();  
        return ret_val;  

For Transact-SQL routines, SQL Server derives routine properties directly from the routine definition. For CLR routines, the server does not analyze the body of the routine to derive these properties. Instead, you can use custom attributes for classes and class members implemented in a .NET Framework language.

The custom attributes needed for CLR routines, user-defined types, and aggregates are defined in the Microsoft.SqlServer.Server namespace.

Custom Attributes for CLR Routines