Section 4.7. ADO.NET In-Process Extensions Supporting CLR Programming

4.7. 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 fourththe SqlDataRecord objectreturns to the caller a custom result set from a stored procedure. These four extensions are discussed in the following subsections.

4.7.1. 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") 

4.7.2. 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 messageequivalent 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.

4.7.3. 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.

4.7.4. 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");