Export (0) Print
Expand All
9 out of 9 rated this helpful - Rate this topic

SQL Server CLR Integration

This chapter is excerpted from ADO.NET 3.5 Cookbook, Second Edition by Bill Hamilton, published by O'Reilly Media

Introduction

The .NET Framework Common Language Runtime (CLR) environment executes compiled code written in programming languages such as C# and Visual Basic. The code is compiled to a file, or assembly, that contains the compiled code and an assembly manifest. The manifest contains metadata about the assembly, including types, methods, and inheritance relationships. Code running within the CLR is called managed code.

The CLR provides services such as automatic garbage collection, security support, and runtime type checking. Because the compiled code is executed by the CLR rather than directly by the operating system, managed code applications are platform and language independent.

SQL Server 2005 and later versions host the CLR in the Database Engine. This is called CLR integration. CLR integration lets you create database objects such as functions, stored procedures, triggers, user-defined types (UDTs), and user-defined aggregate (UDA) functions in programming languages supported by the CLR. Managed code running in SQL Server-hosted CLR is referred to as a CLR routine.

Prior to SQL Server 2005, the main way that SQL Server was extended was using extended stored procedures that let you create external routines using programming languages such as C. Extended stored procedures are used like regular stored procedures, however, they can have performance problems such as memory leaks and can cause the server to become unreliable. CLR integration lets you extend SQL Server with the safety and reliability of T-SQL and with the flexibility of extended stored procedures.

Managed code uses code access security (CAS) to control what operations assemblies can perform. CAS secures the code running within SQL Server and prevents the code from adversely affecting the operating system or the database server. Generally, you should use T-SQL when the code in the routines primarily performs data access. CLR routines are best for CPU-intensive calculations and for supporting complex logic that would otherwise be difficult to implement using T-SQL. The components needed to develop CLR routines are installed with SQL Server 2005 and later.

Although these versions of SQL Server ship with the .NET Framework and commandline compilers for C# and VB.NET, as well as Business Intelligence Studio that lets you build Analysis Services and Reporting Services projects using the same IDE as Visual Studio, you need to install Visual Studio 2005 or later to create and compile CLR routines using the Visual Studio IDE.

CLR integration is turned off by default. 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.

Alternatively, you can use the SQL Server Surface Area Configuration tool to control whether CLR integration is enabled.

SQL Server 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 12.1, "Types of CLR routines" describes the types of CLR routines you can build. The solutions in this chapter show you how to build each of these types of SQL Server CLR routines.

Table 12.1. 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.

Triggers (DML and DDL)

Public static method

A type of stored procedure that automatically runs when a DML or DDL event occurs.


SQL Server 2005 introduced 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 briefly described in Table 12.2, "New and changed T-SQL statements to support CLR integration".

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

Scope

DDL statement

New .NET 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.

DROP AGGREGATE

Yes

The assembly containing the class must first be registered in SQL Server with the CREATE ASSEMBLY T-SQL statement.

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.


For more information about assembly or CLR routine management, see SQL Server Books Online.

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 con-text 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 12.3, "SqlContext public properties".

Table 12.3. SqlContext public properties

Property

Return type

Description

IsAvailable

bool

Indicates whether the code 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 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. The SqlPipe class has the public properties and methods described in Table 12.4, "SqlPipe public property and methods".

Table 12.4. SqlPipe public property 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 12.5, "SqlTriggerContext public properties and method".

Table 12.5. SqlTriggerContext public properties and method

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.

Method

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 CLR routines are defined in the Microsoft.SqlServer.Server namespace. Table 12.6, "Custom attributes for CLR routines" describes custom attributes used with SQL Server CLR routines.

Table 12.6. 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.


Problem

You need to create a CLR stored procedure.

Solution

The solution shows how to create four types of CLR stored procedures:

  • A stored procedure that returns a result set

  • A stored procedure that returns an output parameter, message, and return code

  • A stored procedure that returns a dynamically created result set with a single row

  • A stored procedure that returns a dynamically created result set containing multiple rows

The first solution is a CLR stored procedure that takes an input parameter @ShiftID and returns a result set of employees working that shift from the HumanResources.Employees table in AdventureWorks. Follow these steps:

  1. Create a new SQL Server project in Visual Studio named ClrStoredProcedure.

  2. Create a stored procedure item in the project; name it SpEmployeesInShift.cs.

  3. The C# code in SpEmployeesInShift.cs in the project ClrStoredProcedure is shown in Example 12.1, "Stored procedure: SpEmployeesInShift.cs".

    Example 12.1. Stored procedure: SpEmployeesInShift.cs

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    
    public partial class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void SpEmployeeInShift(int shiftID)
    
        {
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                conn.Open(  );
                SqlCommand cmd = new SqlCommand(
                    "SELECT e.* FROM HumanResources.Employee e " +
                    "JOIN HumanResources.EmployeeDepartmentHistory h " +
                    "ON e.EmployeeID = h.EmployeeID " +
                    "WHERE h.ShiftID = " + shiftID, conn);
                SqlContext.Pipe.ExecuteAndSend(cmd);
            }
        }
    };
    

    The SpEmployeeInShift() method implements the stored procedure and is annotated with the SqlProcedure attribute.

    The tabular result set is returned to the client using the ExecuteAndSend( ) method of the SqlPipe object that executes a command and sends the tabular result set directly to the client. The method takes a single parameter that is a SqlCommand object associated with the context connection. Alternatively, you can send a tabular result set to the client using either the Send(SqlDataReader) or Send(SqlDataRecord) method of the SqlPipe object. The following line of code replaces the ExecuteAndSend() method used in this example with the Send(SqlDataReader) method:

    	SqlContext.Pipe.Send(cmd.ExecuteReader( ));
    

    The Send() methods lets you manipulate the data before you send it to the client but is slightly slower because of additional overhead.

  4. Build the solution.

  5. Register the assembly and create the stored procedure by executing the following T-SQL statement in SQL Server Management Studio, replacing <path> appropriately:

    	USE AdventureWorks
    	GO
    
    	CREATE ASSEMBLY ClrStoredProcedure
    	FROM '<path>\ClrStoredProcedure\bin\Debug\ClrStoredProcedure.dll'
    	GO
    
    	CREATE PROCEDURE ClrSpEmployeeByShift
    	    @shiftID int
    	AS EXTERNAL NAME ClrStoredProcedure.StoredProcedures.SpEmployeeInShift
    
  6. Execute the stored procedure from SQL Server Manager:

    	EXEC ClrSpEmployeeByShift @shiftID=1
    

    Partial results are shown in Figure 12.1, "Partial results for ClrSpEmployeeByShift stored procedure solution".

    Figure 12.1. Partial results for ClrSpEmployeeByShift stored procedure solution

    Partial results for ClrSpEmployeeByShift stored procedure solution

The second solution shows how to build a CLR stored procedure that returns an output parameter, a message, and a return code. Follow these steps:

  1. Add a stored procedure item to the existing project ClrStoredProcedure and name it SpOutputParameter_Message_ReturnCode.

  2. The C# code in SpOutputParameter_Message_ReturnCode.cs in the project ClrStoredProcedure is shown in Example 12.2, "Stored procedure: SpOutputParameter_Message_ReturnCode.cs".

    Example 12.2. Stored procedure: SpOutputParameter_Message_ReturnCode.cs

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    
    public partial class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static int SpOutputParameter_Message_ReturnCode(out int outVal)
        {
            outVal = 10;
            SqlContext.Pipe.Send("Test message.");
            return 5;
        }
    };
    

    Notice that the return value of the stored procedure is int rather than void and that it has output argument named outVal of data type int.

  3. Build the solution.

  4. Update the assembly registration in SQL Server and create the new stored procedure by executing the following T-SQL statement:

    	USE AdventureWorks
    	GO
    
    	ALTER ASSEMBLY ClrStoredProcedure
    	FROM '<path>\ClrStoredProcedure\bin\Debug\ClrStoredProcedure.dll'
    	GO
    
    	CREATE PROCEDURE ClrSpOutputParameter_Message_ReturnCode
    	    @outputVal int OUT
    	AS EXTERNAL NAME
    	    ClrStoredProcedure.StoredProcedures.SpOutputParameter_Message_ReturnCode
    
  5. Execute the stored procedure:

    	DECLARE @returnCode int
    	DECLARE @outVal int
    
    	EXEC @returnCode = ClrSpOutputParameter_Message_ReturnCode @outVal OUTPUT
    
    	PRINT 'Return code = ' + CAST(@returnCode AS CHAR(5))
    	PRINT 'Output value @outVal = ' + CAST(@outVal AS CHAR(5))
    

Results are shown in Figure 12.2, "Output for SpOutputParameter_Message_ReturnCode stored procedure solution".

Figure 12.2. Output for SpOutputParameter_Message_ReturnCode stored procedure solution

Output for SpOutputParameter_Message_ReturnCode stored procedure solution

The third solution shows how to build a CLR stored procedure that returns a result set containing a single row of data created dynamically by the stored procedure. Follow these steps:

  1. Add astored procedure item to the existing project ClrStoredProcedure and name it SpDynamicResultSet.

  2. The C# code in SpDynamicResultSet.cs in the project ClrStoredProcedure is shown in Example 12.3, "Stored procedure: SpDynamicResultSet.cs".

    Example 12.3. Stored procedure: SpDynamicResultSet.cs

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    
    public partial class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void SpDynamicResultSet(  )
    
        {
            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);
            row.SetSqlInt32(0, 1);
            row.SetSqlString(1, "Record 1");
            SqlContext.Pipe.Send(row);
        }
    };
    

    The method uses the SqlMetaData class to define the schema of the result set row. The row is created as an instance of the SqlDataRecord class. The row values are filled using the Set() methods of SqlDataRecord. The Set() methods take two arguments-an ordinal specifying the column number and the value. Finally, an overload of the SqlPipe.Send() method is used to return the instance of the SqlDataRecord class as the result set row.

    You cannot extend this example to return a result set containing multiple rows since a new result set is returned each time the Send() method is called. The fourth solution in this section shows how to return a dynamically created result set containing multiple rows.

  3. Build the solution.

  4. Update the assembly registration in SQL Server and create the new stored procedure by executing the following T-SQL statement:

    	USE AdventureWorks
    	GO
    
    	ALTER ASSEMBLY ClrStoredProcedure
    	FROM '<path>\ClrStoredProcedure\bin\Debug\ClrStoredProcedure.dll'
    	GO
    
    	CREATE PROCEDURE ClrSpDynamicResultSet
    	AS EXTERNAL NAME
    	    ClrStoredProcedure.StoredProcedures.SpDynamicResultSet
    
  5. Execute the stored procedure:

    	EXEC ClrSpDynamicResultSet
    

Results are shown in Figure 12.3, "Results for SpDynamicResultSet_Result stored procedure solution".

Figure 12.3. Results for SpDynamicResultSet_Result stored procedure solution

Results for SpDynamicResultSet_Result stored procedure solution

The fourth solution shows how to build a CLR stored procedure that returns a result set containing a two rows of data created dynamically by the stored procedure. Follow these steps:

  1. Add a stored procedure item to the existing project ClrStoredProcedure and name it SpDynamicResultSet2.

  2. The C# code in SpDynamicResultSet2.cs in the project ClrStoredProcedure is shown in Example 12.4, "Stored procedure: SpDynamicResultSet2.cs".

    Example 12.4. Stored procedure: SpDynamicResultSet2.cs

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    
    public partial class StoredProcedures
    {
        [Microsoft.SqlServer.Server.SqlProcedure]
        public static void SpDynamicResultSet2(  )
        {
            // set up the meta data for the 2-column row
            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);
            
            SqlContext.Pipe.SendResultsStart(row);
    
            // create and send five records
            for (int i = 1; i <= 5; i++)
            {
                row.SetSqlInt32(0, 1);
                row.SetSqlString(1, "Record " + i);
                SqlContext.Pipe.SendResultsRow(row);
            }
    
            SqlContext.Pipe.SendResultsEnd(  );
        }
    };
    

    The SendResultsStart(), SendResultsRow(), and SendResultsEnd() methods of the SqlPipe class are used to send dynamically created result sets containing multiple rows. The SendResultsStart() method takes a SqlDataRecord argument that it uses metadata from to infer the schema of the result set. The SendResultsRow() method is called for each row to return in the result set. It can be called any time after SendResultsStart() is called and before SendResultsEnd() is called marking the end of the result set.

  3. Build the solution.

  4. Update the assembly registration in SQL Server and create the new stored procedure by executing the following T-SQL statement:

    	USE AdventureWorks
    	GO
    
    	ALTER ASSEMBLY ClrStoredProcedure
    	FROM '<path>\ClrStoredProcedure\bin\Debug\ClrStoredProcedure.dll'
    	GO
    
    	CREATE PROCEDURE ClrSpDynamicResultSet2
    	AS EXTERNAL NAME
    	    ClrStoredProcedure.StoredProcedures.SpDynamicResultSet2
    
  5. Execute the stored procedure:

    	EXEC ClrSpDynamicResultSet2
    

Results are shown in Figure 12.4, "Results for SpDynamicResultSet2 stored procedure solution".

Figure 12.4. Results for SpDynamicResultSet2 stored procedure solution

Results for SpDynamicResultSet2 stored procedure solution

Discussion

Stored procedures are routines that return tabular result sets, messages, and output parameters to the client and invoke DML and DDL statements. A CLR stored procedure is implemented as a public static method of a class in a .NET Framework assembly. The method is either void or returns an integer that is the return code from the stored procedure. A method declared void implicitly returns a stored procedure return code of 0.

You identify a stored procedure by annotating the method that implements the stored procedure with the SqlProcedure attribute. The SqlProcedure attribute indicates that the method should be registered as a stored procedure. The SqlProcedure attribute has the following syntax:

	SqlProcedure [ ( procedure-attribute [ ,... ] ) ]

	procedure-attribute::=
	  Name = "procedure name"

Where:

Name

Specifies the name of the stored procedure.

Arguments to the stored procedure method can be any native SQL Server data type that has an equivalent in managed code. CLR stored procedures can return information to the client as messages, tabular result sets, and output parameters. Send messages and tabular result sets using one of the Send( ) methods of the SqlPipe object or using the ExecuteAndSend( ) method of the SqlPipe object. Output parameters are arguments that are passed in the same way as other output arguments (i.e., using the out keyword in C#).

Problem

You need to create a CLR scalar-valued function.

Solution

The solution creates a scalar-valued function that is a CLR stored procedure that returns the total for a specific sales order by summing the LineTotal values in the Sales.SalesOrderDetail table in AdventureWorks for a specified sales order ID. Follow these steps:

  1. Create a new SQL Server project in Visual Studio and name it ClrScalarValuedFunction.

  2. Create a user-defined function item in the project. Name the item SumLineTotal.cs.

  3. The C# code in SumLineTotal.cs in the project ClrScalarValuedFunction is shown in Example 12.5, "File: SumLineTotal.cs".

    Example 12.5. File: SumLineTotal.cs

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    
    public partial class UserDefinedFunctions
    {
        [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
        public static SqlMoney SumLineTotal(int salesOrderID)
        {
            using (SqlConnection connection =
                new SqlConnection("context connection=true"))
            {
                connection.Open(  );
                SqlCommand cmd = new SqlCommand(
                    "SELECT SUM(LineTotal) " +
                    "FROM Sales.SalesOrderDetail " +
                    "WHERE SalesOrderID=" + salesOrderID, connection);
                return (decimal)cmd.ExecuteScalar(  );
            }
        }
    };
    

    Notice that the function returns data type SqlMoney and accepts an argument salesOrderID of data type int. The actual return value is decimal, which is compatible with the SQL Server Money data type. The function reads data from SQL Server, so the DataAccess property of the SqlFunction attribute is set to DataAccessKind.Read.

  4. Build the solution.

  5. Register the assembly and create the scalar-valued function by executing the following T-SQL statement in SQL Server Management Studio, replacing <path> appropriately:

    	USE AdventureWorks
    	GO
    
    	CREATE ASSEMBLY ScalarValuedFunction
    	FROM '<path>\ClrScalarValuedFunction\bin\Debug\ClrScalarValuedFunction.dll'
    	GO
    
    	CREATE FUNCTION udfSumLineTotal(@salesOrderID int)
    	RETURNS MONEY
    	AS EXTERNAL NAME ScalarValuedFunction.UserDefinedFunctions.Sum
    
  6. Execute the scalar-valued function to return the line total sum for sales order with SaleOrderID = 43660 by executing the following T-SQL statement:

    	SELECT dbo.udfSumLineTotal(43660)
    

    Results are shown in Figure 12.5, "Results for SumLineTotal scalar-valued function solution".

    Figure 12.5. Results for SumLineTotal scalar-valued function solution

    Results for SumLineTotal scalar-valued function solution

Discussion

A scalar-valued function (SVF) is a user-defined function (UDF) that returns a single value. Scalar-valued functions can take arguments and return values of any scalar data type supported by SQL Server except rowversion, text, ntext, image, timestamp, table, or cursor. An SVF is implemented as a static method of a class in a .NET Framework assembly. The return value of the method must be compatible with the SQL Server data type that the method returns.

You identify a .NET SVF or table-valued function (TVF) by annotating the method where you implement the function with the SqlFunction attribute. In addition to indicating that the method should be registered as a function, the SqlFunction attribute can be used to define characteristics of the function. The SqlFunction attribute has the following syntax:

	SqlFunction [ ( function-attribute [,...] ) ]

	function-attribute::=
	    IsDeterministic = {true | false}
	  | DataAccess = { DataAccessKind.None | DataAccessKind.Read }
	  | SystemDataAccess = { SystemDataAccessKind.None | SystemDataAccessKind.Read }
	  | IsPrecise = { true | false }
	  | FillRowMethodName = string
	  | Name = string
	  | TableDefinition = string

Where:

IsDeterministic

Specifies whether the function always returns the same output values for the same set of input values and the same database state. This allows the server to do performance optimizations. The default value is false.

DataAccess = { DataAccessKind.None | DataAccessKind.Read }

Specifies the type of data access the function requires if it accesses data on the local SQL Server or on a remote server if transaction integration is required. The DataAccess argument takes one of two values of the DataAccessKind enumeration:

DataAccessKind.None

The function does not access data.

DataAccessKind.Read

The function only reads data.

The DataAccess property should be set to DataAccessKind.Read if a T-SQL statement is executed inside a CLR SVF or TVF routine.

User-defined functions cannot insert, update, or delete data.

SystemDataAccess = { SystemDataAccessKind.None | SystemDataAccessKind.Read }

Specifies the type of data access the function requires if it accesses data stored in the system catalogs or virtual system tables. The SystemDataAccess argument takes one of the two values of the SystemDataAccessKind enumeration:

SystemDataAccessKind.None

The function does not access data. This is the default value.

SystemDataAccessKind.Read

The function only reads data.

IsPrecise

Specifies whether the return value of the function depends on imprecise calculations involving single or double data types (float or real in SQL Server). This property is used to determine whether the computed columns using the function can be indexed. The default value is false.

FillRowMethodName

Specifies the name of the method used by a table-valued function to fill a row of data in the table returned by the function. Fill row methods are discussed in the section called "Creating a Table-Valued Function".

Name

Specifies the name with which the function should be registered in SQL Server.

TableDefinition

Specifies the layout of the table returned by a table-valued function.

Problem

You need to create a CLR table-valued function.

Solution

This solution creates, registers, and executes a table-valued function that returns a table containing the Name, Length, and ModifiedDate for each file in a specified directory. Follow these steps:

  1. Create a new SQL Server project in Visual Studio and name it ClrTableValuedFunction.

  2. Create a user-defined function item in the project. Name the item ReadDirectoryFileInfo.cs.

  3. The C# code in ReadDirectoryFileInfo.cs in the project ClrTableValuedFunction is shown in Example 12.6, "File: ReadDirectoryFileInfo.cs".

Example 12.6. File: ReadDirectoryFileInfo.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.IO;

public partial class UserDefinedFunctions
{
    [SqlFunction(FillRowMethodName = "FillRow", TableDefinition =
    "FileName nvarchar(256), Size int, DateModified datetime")]
    public static IEnumerator ReadDirectoryFileInfo(string path)
    {
        return new DirectoryLoader(path);
    }

    private static void FillRow(object obj, out SqlString fileName,
    out SqlInt64 fileLength, out SqlDateTime dateModified)
    {
        if (obj != null)
        {
            DirectoryEntry de = (DirectoryEntry)obj;
            fileName = de._fileName;
            fileLength = de._fileLength;
            dateModified = de._fileDateModified;
        }
        else
        {
            fileName = SqlString.Null;
            fileLength = SqlInt64.Null;
            dateModified = SqlDateTime.Null;
        }
    }
}

public class DirectoryLoader : IEnumerator
{
    // array that stores the directory entries
    private FileInfo[] fia;
    private int index = -1;
    public DirectoryLoader(string path)
    {
        string[] files = Directory.GetFiles(path);
        fia = new FileInfo[files.Length];
        for (int i = 0; i < files.Length; i++)
            fia[i] = new FileInfo(files[i]);
    }

    public object Current
    {
        get
        {
            if (index != -1)
                return new DirectoryEntry(fia[index].Name,
                fia[index].Length, fia[index].LastWriteTime);
            else
                return null;
        }
    }

    public bool MoveNext(  )
    {
        if (index == fia.Length - 1)
            return false;
        index++;
        return true;
    }

    public void Reset(  )
    {
        index = -1;
    }
}

public class DirectoryEntry
{
    internal string _fileName;
    internal long _fileLength;
    internal DateTime _fileDateModified;
    public DirectoryEntry(string fileName, long fileLength,
    DateTime fileDateModified)
    {
        _fileName = fileName;
        _fileLength = fileLength;
        _fileDateModified = fileDateModified;
    }
};

The code contains three classes-UserDefinedFunctions, which implements the TVF, and two helper classes:

UserDefinedFunctions

The method ReadDirectoryFileInfo( ) implements the TVF. It is annotated with the SqlFunction attribute described in the section called "Creating a Scalar-Valued Function" The SqlFunction attribute identifies the public method FillRow( ) as the method that SQL Server uses to map the current enumerator element to a row in the table that is returned from the TVF. The SqlFunction attribute also specifies the TableDefinition property, which defines the record in the table returned from the TVF.

DirectoryLoader

The enumerator that creates a collection of directory entries for a path specified as an argument to its constructor. The contents of the directory are stored in a FileInfo array named fia. The Current property of the enumerator returns a DirectoryEntry instance containing the filename, file length, and date modified.

DirectoryEntry

Defines a class used to store the current element in the directory enumerator.

Follow these steps:

  1. Build the solution.

  2. In SQL Server Management Studio, register the assembly and create the table-valued function by executing this T-SQL statement, replacing <path> appropriately:

    	USE AdoDotNet35Cookbook
    	GO
    
    	ALTER DATABASE AdoDotNet35Cookbook
    	SET TRUSTWORTHY ON
    	GO
    
    	CREATE ASSEMBLY ClrTableValuedFunction
    	FROM '<path>\ClrTableValuedFunction\bin\Debug\ClrTableValuedFunction.dll'
    	WITH PERMISSION_SET = EXTERNAL_ACCESS
    	GO
    
    	CREATE FUNCTION udfReadDirectoryFileInfo(@path nvarchar(256))
    	RETURNS TABLE
    	    (FileName nvarchar(256), Length bigint, DateModified datetime)
    	AS
    	EXTERNAL NAME ClrTableValuedFunction.UserDefinedFunctions.ReadDirectoryFileInfo
    

    Notice that the assembly is registered with EXTERNAL_ACCESS permission set to allow it to access the filesystem.

  3. Execute the table-valued function with the following T-SQL statement:

    	SELECT * FROM udfReadDirectoryFileInfo('c:\')
    

    Results are shown in Figure 12.6, "Results for ReadDirectoryFileInfo table-valued function solution".

    Figure 12.6. Results for ReadDirectoryFileInfo table-valued function solution

    Results for ReadDirectoryFileInfo table-valued function solution

Discussion

A table-valued function (TVF) is a UDF that returns a table. A TVF is implemented as a method of a class in a .NET Framework assembly that returns data as an IEnumerable or IEnumerator object. The columns of the return table cannot include timestamp columns or nonUnicode string data columns such as char, varchar, and text.

CLR TVFs are similar to their T-SQL counterparts-the main difference is that a T-SQL TVF temporarily stores results in an intermediate table, whereas a CLR TVF streams results back to the consumer. As a result, a T-SQL TVF supports constraints and unique indexes on the result set, whereas a CLR TVF can be consumed incrementally once the first row is available-the result set does not have to be fully materialized before returning values.

Enumerators

The IEnumerator interface supports simple iteration over a nongeneric collection. It is the base interface for all nongeneric enumerators. An enumerator can read the data in the underlying collection but cannot be used to modify the data. IEnumerator has one public property, Current, and two public methods, MoveNext( ) and Reset( ). Initially the enumerator is positioned before the first element in the collection.

  • The Current property returns an object containing the current element in the collection. You must advance the enumerator from its initial position to the first element in the collection by calling MoveNext( ) before reading the value of the Current property. Reading the Current property when the enumerator is not positioned on an element in the collection (before the first element or after the last element) returns an InvalidOperationException.

  • The MoveNext( ) method advances the enumerator to the next element in the collection. MoveNext( ) returns true if the enumerator was successfully advanced and false if the enumerator has passed the end of the collection.

  • The Reset( ) method sets the enumerator to the initial position before the first element in the collection. The IEnumerable interface has a single method, GetEnumerator( ), which returns an IEnumerator object.

Problem

You need to create a CLR aggregate function.

Solution

This solution creates, registers, and executes a user-defined aggregate function that returns the sum of a SqlMoney column in a table. Follow these steps:

  1. Create a new SQL Server project in Visual Studio and name it ClrAggregateFunction.

  2. Create an aggregate item in the project. Name the item SumMoney.cs.

  3. The C# code in SumMoney.cs in the project ClrAggregateFunction is shown in Example 12.7, "SumMoney.cs".

    Example 12.7. SumMoney.cs

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    
    [Serializable]
    [Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
    public struct SumMoney
    {
        private SqlMoney sum;
    
        public void Init(  )
        {
            sum = 0;
        }
    
        public void Accumulate(SqlMoney Value)
        {
            sum += Value;
        }
    
        public void Merge(SumMoney Group)
        {
            sum += Group.sum;
        }
    
        public SqlMoney Terminate(  )
        {
            return sum;
        }
    }
    
  4. Build the solution.

  5. Register the assembly and create the aggregate function by executing the following T-SQL statement in SQL Server Management Studio, replacing <path> appropriately:

    	USE AdventureWorks
    	GO
    
    	CREATE ASSEMBLY ClrAggregateFunction
    	FROM '<path>\ClrAggregateFunction\bin\Debug\ClrAggregateFunction.dll'
    	GO
    
    	CREATE AGGREGATE udfSumMoney
    	    ( @Value money )
    	RETURNS money
    	EXTERNAL NAME ClrAggregateFunction.SumMoney
    
  6. Execute the aggregate function on the Sales.SalesOrderHeader table in AdventureWorks:

    	SELECT dbo.udfSumMoney(SubTotal), dbo.udfSumMoney(TaxAmt),
    	    dbo.udfSumMoney(Freight), dbo.udfSumMoney(TotalDue)
    	FROM Sales.SalesOrderHeader
    

    Results are shown in Figure 12.7, "Results for SumMoney user-defined aggregate function solution".

    Figure 12.7. Results for SumMoney user-defined aggregate function solution

    Results for SumMoney user-defined aggregate function solution

Discussion

A user-defined aggregate (UDA) function returns a scalar result that is the result of a calculation on values in a set of rows. Examples of such functions include built-in SQL Server aggregate functions such as SUM, AVG, MIN, and MAX. A CLR UDA function is implemented as a structure or class in a .NET Framework assembly. A CLR UDA function can be invoked in T-SQL statements with the same rules that apply to system aggregate functions.

To implement a CLR UDA function, you have to write only the code that implements the accumulation logic-iteration over the result set and computing accumulated values are managed by the query processor. Specifically, you must implement an aggregation contract that defines mechanisms to save the intermediate state of the aggregation and to accumulate new values. This aggregation contract consists of four methods:

public void Init()

Invoked once for each group that the query processor is aggregating to initialize the aggregate computation. This method should clean up previous uses of the instance, because the query processor can choose to reuse an instance of an aggregate class to compute aggregates for multiple groups.

public void Accumulate(input_type value)

The query processor invokes this method to accumulate aggregate values. The method is invoked for each value in the group being accumulated. The input_type argument is the managed SQL Server data type equivalent to the native SQL Server data type specified by the argument.

public void Merge(udagg_class value)

Used to merge a second instance of this aggregate class with the current instance. The query processor can invoke this method to merge partial computations of an aggregate on group partitions.

public return_type Terminate( )

Completes the aggregation and returns the result. The return_type is a managed SQL Server data type equivalent to the return_sqltype specified in the CREATE AGGREGATE T-SQL statement used to create the CLR aggregate function.

You identify a UDA function by annotating the implementing class with the SqlUserDefinedAggregate attribute, which indicates that a class should be registered as a UDA function. The SqlUserDefinedAggregate attribute has the following syntax:

	SqlUserDefinedAggregate [ (aggregate-attribute [,...] ) ]

	aggregate-attribute::=
	  Format = {Native | UserDefined}
	  IsInvariantToDuplicates = {true | false}
	  IsInvariantToNulls = {true | false}
	  IsInvariantToOrder = {true | false}
	  IsNullIfEmpty = {true | false}
	  | MaxByteSize = n

Where:

Format = {Native | UserDefined}

Specifies the serialization format for the type-either Native or UserDefined. Native serialization uses a simple algorithm to efficiently serialize the type. Native serialization is recommended for simple types containing only fields of the following types: bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double, SqlByte, SqlInt16, SqlInt32, SqlInt64, SqlDateTime, SqlSingle, SqlDouble, SqlMoney, and SqlBoolean.

Native serialization can also contain UDTs that use Native serialization. Native serialization has the following requirements:

  • All the fields of the type must be blittable-data types that have a common representation in both managed and unmanaged memory and therefore do not need to be converted when passed between managed and unmanaged code. The following types from the System namespace are blittable: Byte, SByte, UInt16, Int32, UInt32, Int64, IntPtr, and UIntPtr. One-dimensional arrays of blittable types and formatted value types containing only blittable types are also blittable.

  • The type must not specify the MaxByteSize property.

  • The type must not have any fields that are not serialized.

UserDefined serialization controls the serialization through code and has the following requirements:

  • You must specify the MaxByteSize property of the SqlUserDefinedAggregate attribute.

  • The class or structure implementing the type must implement the Read() and Write() methods of the IBinarySerializable interface to read and write the byte stream.

IsInvariantToDuplicates

Specifies whether the aggregate is invariant to duplicates. For example, MAX and MIN are invariant to duplicates, and AVG and SUM are not.

IsInvariantToNulls

Specifies whether the aggregate is invariant to nulls. For example, MAX and MIN are invariant to nulls, and COUNT is not (since nulls are included in the count).

IsInvariantToOrder

Specifies whether the aggregate is invariant to the order of the values. Specifying true gives the query optimizer more flexibility in choosing an execution plan and can result in improved performance.

IsNullIfEmpty

Specifies whether the aggregate returns a null reference if no values are accumulated. Otherwise the value that the initialized value of the variable returned by the Terminate() method is returned.

MaxByteSize

The maximum size of the UDT instance. MaxByteSize must be specified if the Format property is set to UserDefined.

Problem

You need to create a CLR user-defined type.

Solution

The following example creates, registers, and uses a user-defined type that defines a polygon and implements a single method that returns the area of the polygon as a double. Follow these steps:

  1. Create a new SQL Server project in Visual Studio and name it ClrType.

  2. Create a user-defined type item in the project. Name the item Polygon.cs.

  3. The C# code in Polygon.cs in the project ClrType is shown in Example 12.8, "File: Polygon.cs".

    Example 12.8. File: Polygon.cs

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using System.Data.SqlTypes;
    using Microsoft.SqlServer.Server;
    
    [Serializable]
    [Microsoft.SqlServer.Server.SqlUserDefinedType(Format.Native)]
    public struct Polygon : INullable
    {
        private bool isNull;
        private int numberSides;
        private double sideLength;
    
        public override string ToString()
        {
            if (this.isNull)
                return "null";
            else
                return string.Format("{0} sides each {1} units long",
                    numberSides, sideLength);
        }
    
        public bool IsNull
        {
            get
            {
                return isNull;
            }
        }
    
        public static Polygon Null
        {
            get
            {
                Polygon p = new Polygon();
                p.isNull = true;
                return p;
            }
        }
    
        public static Polygon Parse(SqlString s)
        {
            if (s.IsNull || s.Value.ToLower().Equals("null"))
                return Null;
            string[] sa = s.ToString().Split(',');
            if (sa.Length != 2)
                return Null;
    
            Polygon p = new Polygon();
            try
            {
                p.numberSides = int.Parse(sa[0]);
                p.sideLength = double.Parse(sa[1]);
                if (p.numberSides > 2 & p.sideLength > 0)
                    return p;
                else
                    return Null;
            }
            catch (Exception)
            {
                return Null;
            }
        }
    
        public int NumberSides
        {
            get { return numberSides; }
            set
            {
                if (value > 2)
                {
                    numberSides = value;
                    isNull = false;
                }
                else
                    isNull = true;
            }
        }
    
        public double SideLength
        {
            get { return sideLength; }
            set
            {
                if (value > 0)
                {
                    sideLength = value;
                    isNull = false;
                }
                else
                    isNull = true;
            }
        }
    
        [SqlMethod]
        public double Area()
        {
            if (!isNull)
                return .25 * numberSides * Math.Pow(sideLength, 2) *
                (1 / Math.Tan(Math.PI / numberSides));
            else
                return 0;
        }
    
        [SqlMethod(IsMutator = true, OnNullCall = false)]
        public void SetValue(int numberSides, double sideLength)
        {
            if (numberSides > 2 & sideLength > 0)
            {
                this.numberSides = numberSides;
                this.sideLength = sideLength;
                this.isNull = false;
            }
            else
                isNull = true;
        }
    }
    

    The UDT is implemented as a struct marked with both a Serializable attribute and a SqlUserDefinedType attribute specifying Native serialization. A UDT must support both XML and binary serialization.

    The UDT contains two private fields-numberSides and sideLength. The NumberSides and SideLength properties are used to get and set the value of these fields.

    The UDT implements the IsNullable interface with the method IsNull(), which simply returns the value of a private field, isNull, that keeps track of whether the polygon UDT is null. The UDT also implements the Null() method, which instantiates and returns a null instance of the Polygon UDT.

    The UDT implements the required ToString() and Parse() methods. The ToString() method displays the value of the polygon as a string. The Parse() method converts a string to the Polygon UDT and is used by the SQL Server CONVERT and CAST functions.

    The UDT implements two methods. The Area() method returns the area of the polygon. The SetValue() method changes the number of sides and the length of the sides in the Polygon UDT.

  4. Build the solution.

  5. Register the assembly and create the aggregate function by executing the following T-SQL statement in SQL Server Management Studio, replacing <path> appropriately:

    	USE AdoDotNet35Cookbook
    	GO
    
    	CREATE ASSEMBLY ClrType
    	FROM '<path>\ClrType\bin\Debug\ClrType.dll'
    	GO
    
    	CREATE TYPE Polygon
    	EXTERNAL NAME ClrType
    
  6. Execute the following T-SQL statements that demonstrate the new Polygon type:

    	DECLARE @p Polygon
    	SET @p = CONVERT(Polygon, '5, 4.2')
    	PRINT @p.IsNull
    	PRINT @p.ToString()
    	PRINT @p.NumberSides
    	PRINT @p.SideLength
    	PRINT @p.Area()
    	SET @p.SetValue(7, 3)
    	PRINT @p.ToString()
    	PRINT @p.Area()
    

    Results are shown in Figure 12.8, "Output for Polygon user-defined type solution".

    Figure 12.8. Output for Polygon user-defined type solution

    Output for Polygon user-defined type solution

Discussion

In addition to supporting native and simple types as in previous versions of SQL Server, SQL Server lets you define CLR user-defined types (UDTs). This lets you extend the built-in data types and define complex data types. A CLR UDT can be used in all contexts where a SQL Server system type can be used.

A CLR UDT is implemented as a class in a .NET Framework assembly. You identify a CLR UDT by annotating the class that implements the UDT with the SqlUserDefinedType attribute, which indicates that a class should be registered as a UDT. The SqlUserDefinedType attribute has the following syntax:

	SqlUserDefinedType [ ( udt-property [,...] ) ]

	udt-property::=
	    Format = { Native | UserDefined }
	  | MaxByteSize= n
	  | IsByteOrdered= { true | false }
	  | ValidationMethod = string
	  | IsFixedLength = { true | false }
	  | Name = string

Where:

Format = { Native | UserDefined }

The serialization format of the UDT. For more information about these two values, see the Format property for the SqlUserDefinedAggregate attribute in the section called "Creating an Aggregate Function".

If the UDT is defined in a class rather than a structure, and if the Format property is Native, a StructLayout attribute must be specified and set to LayoutKind. Sequential. This forces the members in the class to be serialized in the same order in which they appear in the class.

MaxByteSize

Specifies the maximum size of an instance of the UDT between 1 and 8,000 bytes. You must specify MaxByteSize if the Format property is set to UserDefined.

Do not specify MaxByteSize if the Format property is set to Native.

IsByteOrdered

Specifies how binary comparisons are performed on the UDT by SQL Server. When IsByteOrdered is true, the UDT is ordered in the same way as its serialized binary representation and can be used to order the data. The following features are supported on the UDT column in a table when IsByteOrdered is true:

  • Creating an index on the column

  • Creating primary and foreign key constraints, and CHECK and UNIQUE constraints on the column

  • Using the column in T-SQL ORDER BY, GROUP BY, and PARTITION BY clauses

  • Using comparison operators in T-SQL statements on the column

ValidationMethod

Specifies the method used to validate instances of the UDT when the data is deserialized from a binary value. The converted method returns a Boolean indicating whether the UDT instance is valid.

The database engine automatically converts binary values to UDT values. The database engine prevents invalid values in the database by checking whether values are appropriate for the serialization format of the type and that the value can be deserialized. Default checking might be inadequate when, for example, UDT values are constrained by a value set or a range.

IsFixedLength

Specifies whether all instances of the UDT are the same length. If the IsFixedLength property is true, all instances of the UDT must have the length, in bytes, specified by the MaxByteSize property. The property is used only when the Format property is set to UserDefined.

Name

Specifies the name of the type.

When a field, method, or property is referenced as part of a query, the T-SQL type of the return value is inferred from the return type. The SqlFacet attribute can be used to return additional information about the return type of a nonvoid UDT expression-the SqlFacet attribute does not constrain the specific values that can be stored in the type. The syntax of the SqlFacet attribute is as follows:

	SqlFacet[(facet-attribute [,...])]

	facet-attribute::=
	    IsFixedLength = { true | false }
	  | MaxSize= { n }
	  | Precision = { n }
	  | Scale = { n }
	  | IsNullable = { true | false }

Where:

IsFixedLength

Specifies whether the return type is a fixed length. IsFixedLength must be set to false if the MaxSize property is set to -1. The default value is false.

MaxSize

Specifies the maximum size of the return type in bytes for binary types and characters for character field types. The default is 4,000 for Unicode character types and 8,000 for binary types. The value -1 indicates a large character or binary type.

Precision

Specifies the precision (number of digits in the number) of the return type as a value from 1 to 38. This property is used only with numeric types. Scale must be specified if Precision is specified. The default value is 38.

Scale

Specifies the scale (number of digits to the right of the decimal point) of the return type as a value from 0 to 38. This property is used only with numeric types. Precision must be specified if Scale is specified. The default value is 0.

IsNullable

Indicates whether the value of the return type can be null. The default is true. The properties specified for the SqlFacet attribute must be compatible with the return type. Table 12.7, "Allowable SqlFacet properties by return type" shows SqlFacet properties that can be specified for each return type.

Table 12.7. Allowable SqlFacet properties by return type

Type

IsFixedLength

MaxSize

Precision

Scale

IsNullable

SqlBoolean

N

N

N

N

Y

SqlByte

N

N

N

N

Y

SqlInt16

N

N

N

N

Y

SqlInt32

N

N

N

N

Y

SqlInt64

N

N

N

N

Y

SqlSingle

N

N

N

N

Y

SqlDouble

N

N

N

N

Y

SqlDateTime

N

N

N

N

Y

SqlMoney

N

N

N

N

Y

SqlGuid

N

N

N

N

Y

SqlDecimal

N

N

Y

Y

Y

SqlString

Y

Y

N

N

Y

SqlBinary

Y

Y

N

N

Y

SqlXml

N

N

N

N

Y

SqlBytes

Y

Y

N

N

Y

SqlChars

Y

Y

N

N

Y

SqlUtcDateTime

N

N

N

N

Y

SqlDate

N

N

N

N

Y

SqlTime

N

N

N

N

Y

Embedded UDTs

N

N

N

N

Y

string

Y

Y

N

N

Y

Byte[]

Y

Y

N

N

Y

Char[]

Y

Y

N

N

Y

decimal

N

N

Y

Y

N


You must do the following when you define a CLR UDT:

  • Annotate the class with the SqlUserDefinedType attribute.

  • Specify the Serializable attribute, indicating that the UDT can be serialized.

  • Implement the System.Data.SqlTypes.INullable interface so that the UDT can recognize a null value. This means that the UDT must implement a static IsNull property that returns a Boolean indicating whether the instance of the UDT is null.

  • Implement a public static property named Null that returns a null instance of the UDT.

  • Implement public static ToString() and Parse() methods to convert to and parse from a string representation of the type. The Parse() method takes a single argument of type SqlString.

  • Implement the IXmlSerializable interface if all public fields and properties are XML serializable or marked with the XmlIgnore attribute. The IXmlSerializable interface provides custom XML serialization and deserialization by explicitly defining how an object is serialized and deserialized by the XmlSerializer class. The IXmlSerializable interface has three methods: GetSchema(), ReadXml(), and WriteXml().

  • Implement Read() and Write() methods if user-defined serialization is specified by implementing the IBinarySerialize interface.

A CLR UDT has the following restrictions:

  • Public names cannot exceed 128 characters in length and must conform to SQL Server naming rules for identifiers.

  • Only fields, properties, and methods defined in the type are callable from T-SQL. SQL Server is not aware of the inheritance hierarchy among UDTs.

  • Members other than the class constructor cannot be overloaded.

  • Static members must be declared either as constants or as read-only when the assembly permission is specified as SAFE or EXTERNAL_ACCESS.

The SqlMethod attribute is used to define characteristics of a UDT method or property. The syntax of the SqlMethod attribute is as follows:

	SqlMethod [ ( method-attribute [ ,... ] ) ]

	method-attribute::=
	    function_attribute
	  | IsMutator = { true | false }
	  | OnNullCall = { true | false }
	  | InvokeIfReceiverIsNull= { true | false }

Where:

function_attribute

The SqlMethod attribute inherits all properties of the SqlFunction attribute discussed in the section called "Creating a Scalar-Valued Function".

IsMutator

Specifies whether the method can modify the UDT instance. SQL Server looks for the IsMutator property of the SqlMethod attribute on void public methods in the UDT. If the IsMutator property is true on a void method, SQL Server marks the method as a mutator-a method that causes state change in the instance.

Mutator methods are not allowed in queries-their use is restricted to assignment statements or data modification statements. The default value of the IsMutator property is false.

OnNullCall

Specifies whether the method is evaluated if one or more null arguments are supplied. If false, the method returns null without evaluating the method if one or more of the arguments are null.If true, the method is evaluated regardless of whether arguments are null. The default value is true.

InvokeIfReceiverIsNull

Specifies whether SQL Server should invoke the method on a null reference. A value of true invokes the method on a null reference. The default value is false.

Problem

You need to create a CLR DML trigger.

Solution

The solution uses two tables named Volume and VolumeAudit. Execute the following T-SQL batch to create the tables:

	USE AdoDotNet35Cookbook
	GO

	CREATE TABLE Volume
	(
	    ID int NOT NULL,
	    Length float NOT NULL,
	    Width float NOT NULL,
	    Height float NOT NULL,
	    Volume float NOT NULL CONSTRAINT DF_Area_Area DEFAULT ((0)),
	    CONSTRAINT PK_Volume PRIMARY KEY CLUSTERED
	    (
	        ID ASC
	    )
	)
	GO

	CREATE TABLE VolumeAudit
	(
	    Action varchar(50) NOT NULL,
	    Description varchar(max) NOT NULL
	)

The solution creates update, insert, and delete DML triggers that log updates, inserts, and deletes to a table named Volume. These events are logged to a table named VolumeAudit. The example then registers the triggers and shows the results of executing DML statements against the Volume table. Follow these steps:

  1. Create a new SQL Server project in Visual Studio and name it ClrDmlTrigger.

  2. Create a trigger item in the project. Name the item VolumeTriggers.cs.

  3. The C# code in VolumeTriggers.cs in the project ClrDmlTrigger is shown in Example 12.9, "File: VolumeTriggers.cs".

    Example 12.9. File: VolumeTriggers.cs

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using Microsoft.SqlServer.Server;
    
    public partial class Triggers
    {
        [SqlTrigger(Target = "Volume", Event = "FOR INSERT")]
        public static void InsertTrigger()
        {
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                SqlDataAdapter da =new SqlDataAdapter("SELECT * FROM INSERTED",
                    conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
    
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                conn.Open();
                foreach (DataRow row in dt.Rows)
                {
                    int id = (int)row[0];
                    double length = (double)row[1];
                    double width = (double)row[2];
                    double height = (double)row[3];
                    double volume = length * width * height;
                    string audit = string.Format("ID = {0}, Length = {1}, " +
                        "Width = {2}, Height = {3}",
                        id, length, width, height);
    
                    cmd.CommandText = "INSERT INTO VolumeAudit VALUES ('INSERTED', '" +
                        audit + "')";
                    cmd.ExecuteNonQuery();
    
                    cmd.CommandText = "UPDATE Volume SET Volume = " + volume +
                        " WHERE ID = " + id;
                    cmd.ExecuteNonQuery();
    
                    SqlPipe pipe = SqlContext.Pipe;
                    pipe.Send("Row inserted: " + audit);
                }
            }
        }
    
        [SqlTrigger(Target = "Volume", Event = "FOR UPDATE")]
        public static void UpdateTrigger()
        {
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM DELETED",
                    conn);
                DataTable dtDel = new DataTable();
                da.Fill(dtDel);
                da = new SqlDataAdapter("SELECT * FROM INSERTED", conn);
                DataTable dtIns = new DataTable();
                da.Fill(dtIns);
    
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                conn.Open();
                for (int i = 0; i < dtDel.Rows.Count; i++)
                {
                    DataRow rowDel = dtDel.Rows[i];
                    int delId = (int)rowDel[0];
                    double delLength = (double)rowDel[1];
                    double delWidth = (double)rowDel[2];
                    double delHeight = (double)rowDel[3];
                    double delVolume = (double)rowDel[4];
                    string delAudit = string.Format("ID = {0}, Length = {1}, " +
                        "Width = {2}, Height = {3}, Volume = {4}",
                        delId, delLength, delWidth, delHeight, delVolume);
    
                    DataRow rowIns = dtIns.Rows[i];
                    int insId = (int)rowIns[0];
                    double insLength = (double)rowIns[1];
                    double insWidth = (double)rowIns[2];
                    double insHeight = (double)rowIns[3];
                    double insVolume = insLength * insWidth * insHeight;
                    string insAudit = string.Format("ID = {0}, Length = {1}, " +
                        "Width = {2}, Height = {3}, Volume = {4}",
                        insId, insLength, insWidth, insHeight, insVolume);
                        cmd.CommandText = "UPDATE Volume SET Volume = " + insVolume +
                        " WHERE ID = " + insId;
                    cmd.ExecuteNonQuery();
    
                    cmd.CommandText = "INSERT INTO VolumeAudit VALUES " +
                    "('UPDATED', 'Original: " + delAudit + "; " + "New: " +
                    insAudit + "')";
                    cmd.ExecuteNonQuery();
    
                    SqlPipe pipe = SqlContext.Pipe;
                    pipe.Send("Row updated: Original: " + delAudit + "; " + "New: " +
                        insAudit);
                }
            }
        }
    
        [SqlTrigger(Target = "Volume", Event = "FOR DELETE")]
        public static void DeleteTrigger()
        {
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM DELETED",
                    conn);
                DataTable dt = new DataTable();
                da.Fill(dt);
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                conn.Open();
                foreach (DataRow row in dt.Rows)
                {
                    int id = (int)row[0];
                    double length = (double)row[1];
                    double width = (double)row[2];
                    double height = (double)row[3];
                    double volume = (double)row[4];
                    string audit = string.Format("ID = {0}, Length = {1}, " +
                        "Width = {2}, Height = {3}, Volume = {4}",
                        id, length, width, height, volume);
                    cmd.CommandText =
                        "INSERT INTO VolumeAudit VALUES ('DELETED', '" + audit + "');";
                        cmd.ExecuteNonQuery();
                    cmd.ExecuteNonQuery();
    
                    SqlPipe pipe = SqlContext.Pipe;
                    pipe.Send("Row deleted: " + audit);
                }
            }
        }
    }
    

    Each of the three triggers is marked with the SqlTrigger attribute that specifies the Volume table as the target of the trigger together with the event that causes each trigger to execute.

  4. Build the solution.

  5. Register the assembly and create the aggregate function by executing the following T-SQL statement in SQL Server Management Studio, replacing <path> appropriately:

    	USE AdoDotNet35Cookbook
    	GO
    
    	CREATE ASSEMBLY VolumeTriggers
    	FROM '<path>\ClrDmlTrigger\bin\Debug\ClrDmlTrigger.dll'
    	GO
    
    	CREATE TRIGGER VolumeInsertTrigger
    	ON Volume
    	FOR INSERT
    	AS
    	EXTERNAL NAME VolumeTriggers.Triggers.InsertTrigger
    	GO
    
    	CREATE TRIGGER VolumeUpdateTrigger
    	ON Volume
    	FOR UPDATE
    	AS
    	EXTERNAL NAME VolumeTriggers.Triggers.UpdateTrigger
    	GO
    
    	CREATE TRIGGER VolumeDeleteTrigger
    	ON Volume
    	FOR DELETE
    	AS
    	EXTERNAL NAME VolumeTriggers.Triggers.DeleteTrigger
    	GO
    
  6. Execute the following T-SQL statements to insert two rows into the Volume table:

    	INSERT INTO Volume (ID, Length, Width, Height) VALUES (1, 2.2, 3.4, 5.7)
    	INSERT INTO Volume (ID, Length, Width, Height) VALUES (2, 6, 2, 5.4)
    

    Results are shown in Figure 12.9, "Output for VolumeTriggers CLR DML trigger solution".

    Figure 12.9. Output for VolumeTriggers CLR DML trigger solution

    Output for VolumeTriggers CLR DML trigger solution

    The output is generated by the following code in the insert DML trigger:

    	pipe.Send("Row inserted: " + audit);
    

    Figure 12.10, "Results for VolumeTriggers CLR DML trigger solution" shows the rows in the VolumeAudit table created by the DML triggers.

    Figure 12.10. Results for VolumeTriggers CLR DML trigger solution

    Results for VolumeTriggers CLR DML trigger solution

    There are four records-two inserted by the insert DML trigger and two inserted by the update DML trigger when the insert DML trigger updates the Volume field.

    Tip
    Although the update trigger on the Volume table updates the Volume table, the query is not recursive as long as the RECURSIVE_TRIGGERS database option is set to OFF-this is the default. You can check the status of all database options by executing the following T-SQL statement:
    	SELECT * FROM sys.databases
    
    The is_recursive_triggers_on column contains the setting of the RECURSIVE_TRIGGERS option for each database. You can change the value of a database option using the ALTER DATABASE statement. For example, execute the following T-SQL statement to change the recursive trigger behavior for the AdoDotNet35Cookbook database to ON:
    	ALTER DATABASE AdoDotNet35Cookbook
    	SET RECURSIVE_TRIGGERS ON
    
  7. Execute the following T-SQL statement to update the first of the two rows previously inserted into the Volume table:

    	UPDATE Volume
    	SET Length = 1, Width = 4, Height = 7.2
    	WHERE ID = 1
    

    Results are shown in Figure 12.11, "Output for VolumeTriggers CLR DML trigger update row".

    Figure 12.11. Output for VolumeTriggers CLR DML trigger update row

    Output for VolumeTriggers CLR DML trigger update row

    Figure 12.12, "Results for VolumeTriggers CLR DML trigger update row" shows the row added to the VolumeAudit table by the update DML trigger.

    Figure 12.12. Results for VolumeTriggers CLR DML trigger update row

    Results for VolumeTriggers CLR DML trigger update row
  8. Execute this T-SQL statement to delete the two rows from the Volume table:

    	DELETE FROM Volume
    

    Results are shown in Figure 12.13, "Output for VolumeTriggers CLR DML trigger delete rows".

    Figure 12.13. Output for VolumeTriggers CLR DML trigger delete rows

    Output for VolumeTriggers CLR DML trigger delete rows

    Figure 12.14, "Results for VolumeTriggers CLR DML trigger delete rows" shows the rows added to the VolumeAudit by the delete DML trigger.

    Figure 12.14. Results for VolumeTriggers CLR DML trigger delete rows

    Results for VolumeTriggers CLR DML trigger delete rows

Discussion

A trigger is a type of stored procedure that executes automatically when an event occurs. SQL Server has two types of triggers:

Data Manipulation Language (DML) trigger

Executes when INSERT, UPDATE, and DELETE commands modify data in a table or view.

Data Definition Language (DDL) trigger

Executes in response to a DDL statement that is often used to make database schema changes. Examples include the CREATE, ALTER, and DROP statements.

A CLR trigger is implemented as a public static void method in a .NET Framework assembly. You identify a CLR DML trigger by marking the method that implements the trigger with the SqlTrigger attribute, which indicates that a method should be registered as a DML trigger. The SqlTrigger attribute has the following syntax:

	SqlTrigger [ ( trigger-attribute [ ,... ] ) ]

	trigger-attribute::=
	    Target = "table-name"
	  | Event = "trigger-type update-action [, ...]"

	trigger-type::=
	    FOR | AFTER | INSTEAD OF

	update-action::=
	    UPDATE | DELETE | INSERT

Where:

Target = "table-name"

Specifies the table to which the trigger applies.

trigger-type

Specifies the type of trigger.

update-action

Specifies the DML action that activates the trigger-UPDATE, DELETE, or INSERT.

You can use the TriggerAction property of the SqlTriggerContext class instead of the SqlTrigger attribute. This is discussed in the section called "Creating a DDL Trigger", next.

Problem

You need to create a CLR DDL trigger.

Solution

The solution creates a DDL trigger that executes when CREATE TABLE and DROP TABLE DDL statements are executed and logs the events to a table named Log.

The solution uses a single table named Log. Execute the following T-SQL statement to create the table:

	USE AdoDotNet35Cookbook
	GO

	CREATE TABLE Log
	(
	    LogID int IDENTITY(1,1) NOT NULL,
	    LogEntry varchar(max) NOT NULL,
	    CONSTRAINT PK_Log PRIMARY KEY CLUSTERED
	        ( LogID ASC )
	)

Follow these steps:

  1. Create a new SQL Server project in Visual Studio and name it ClrDdlTrigger.

  2. Create a trigger item in the project. Name the item LogTableActivityTrigger.cs.

  3. The C# code in LogTableActivityTrigger.cs in the project ClrDdlTrigger is shown in Example 12.10, "File: LogTableActivityTrigger.cs".

    Example 12.10. File: LogTableActivityTrigger.cs

    using System;
    using System.Data;
    using System.Data.SqlClient;
    using Microsoft.SqlServer.Server;
    
    public partial class Triggers
    {
        public static void LogTableActivityTrigger()
        {
            SqlTriggerContext tc = SqlContext.TriggerContext;
            using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
    
                if (tc.TriggerAction == TriggerAction.CreateTable ||
                    tc.TriggerAction == TriggerAction.DropTable)
                {
                    cmd.CommandText = "INSERT INTO Log VALUES " +
                        "('" + tc.EventData.Value + "')";
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
    

    A single DDL trigger is defined in the Triggers class. The trigger checks the TriggerAction property of the SqlTriggerContext and then logs the EventData for the event that caused this trigger to fire. In this example, it is not necessary to check the trigger context, as all events for which the trigger is registered execute the same code to log the event. You could use the TriggerAction property to perform different actions for each of the different events that a DDL trigger is registered to handle.

  4. Build the solution.

  5. Register the assembly and create the aggregate function by executing the following T-SQL statement in SQL Server Management Studio, replacing <path> appropriately:

    	USE AdoDotNet35Cookbook
    	GO
    
    	CREATE ASSEMBLY ClrDdlTrigger
    	FROM '<path>\ClrDdlTrigger\bin\Debug\ClrDdlTrigger.dll'
    	GO
    
    	CREATE TRIGGER LogTableActivityTrigger
    	ON DATABASE
    	FOR CREATE_TABLE, DROP_TABLE
    	AS
    	EXTERNAL NAME ClrDdlTrigger.Triggers.LogTableActivityTrigger
    
  6. Execute the following T-SQL statement to create and then drop a table named TestTable to demonstrate the DDL trigger:

    	USE AdoDotNet35Cookbook
    	GO
    
    	CREATE TABLE TestTable
    	(
    	     TestID int NOT NULL,
    	     CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED
    	     ( TestID ASC )
    	)
    	GO
    
    	DROP TABLE TestTable
    	GO
    

Table 12.8, "Log table" shows the Log table that now contains two rows detailing the DDL CREATE_TABLE and DROP_TABLE events.

Table 12.8. Log table

LogID

LogEntry

1

<EVENT_INSTANCE>
  <EventType>CREATE_TABLE</EventType>
  <PostTime>2007-11-03T17:25:45.407</PostTime>
  <SPID>52</SPID>
  <ServerName>CTSUSNJY9779A</ServerName>
  <LoginName>CTSUSNJY9779A\bill</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>AdoDotNet35Cookbook</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>TestTable</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
      ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON"
      ENCRYPTED="FALSE" />
   <CommandText>CREATE TABLE TestTable
     (
       TestID int NOT NULL,
       CONSTRAINT PK_TestTable PRIMARY KEY CLUSTERED
       ( TestID ASC )
      )
    </CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>

2

<EVENT_INSTANCE>
  <EventType>DROP_TABLE</EventType>
  <PostTime>2007-11-03T17:25:45.827</PostTime>
  <SPID>52</SPID>
  <ServerName>CTSUSNJY9779A</ServerName>
  <LoginName>CTSUSNJY9779A\bill</LoginName>
  <UserName>dbo</UserName>
  <DatabaseName>AdoDotNet35Cookbook</DatabaseName>
  <SchemaName>dbo</SchemaName>
  <ObjectName>TestTable</ObjectName>
  <ObjectType>TABLE</ObjectType>
  <TSQLCommand>
    <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
      ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON"
      ENCRYPTED="FALSE" />
    <CommandText>DROP TABLE TestTable</CommandText>
  </TSQLCommand>
</EVENT_INSTANCE>


Discussion

A trigger is a type of stored procedure that executes automatically when an event occurs. SQL Server has two types of triggers:

Data Manipulation Language (DML) trigger

Executes when INSERT, UPDATE, and DELETE commands modify data in a table or view.

Data Definition Language (DDL) trigger

Executes in response to a DDL statement that is often used to make database schema changes. Examples include the CREATE, ALTER, and DROP statements.

A CLR trigger is implemented as a method of a class in a .NET Framework assembly.

A CLR trigger is implemented as a public static void method in a .NET Framework assembly. Instead of using the SqlTrigger attribute as described in the section called "Creating a DML Trigger" to define events for which a DDL trigger executes, the SqlTriggerContext is used to get context information about the trigger. This SqlTriggerContext class cannot be instantiated directly-call the TriggerContext property of the SqlContext class to get an instance.

The SqlTriggerContext class has a TriggerAction property that indicates the action that caused a trigger to fire. For DML triggers, the value can be TriggerAction.Update, TriggerAction.Insert,or TriggerAction.Delete. There are many DDL trigger actions-see Microsoft SQL Server Books Online for a complete list.

Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft. All rights reserved.