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");
conn.Open();
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();
rd.Close();
return ret_val;
}
}
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.