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.
Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.Data.SqlClient Public partial Class UserDefinedFunctions <SqlFunction(DataAccess = DataAccessKind.Read)> _ Public Shared Function func1() As String ' Open a connection and create a command Dim conn As SqlConnection = New SqlConnection("context connection = true") conn.Open() Dim cmd As SqlCommand = conn.CreateCommand() cmd.CommandText = "SELECT str_val FROM table1 WHERE int_val = 10" ' where table1 is a user table ' Execute this command Dim rd As SqlDataReader = cmd.ExecuteReader() ' Set string ret_val to str_val returned from the query Dim ret_val As String = rd.GetValue(0).ToString() rd.Close() Return ret_val End Function End Class
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.