SQL Server In-Process Specific Extensions to ADO.NET
There are four main functional extensions to ADO.NET, found in the Microsoft.SqlServer.Server namespace, that are specifically for in-process use: SqlContext, SqlPipe, SqlTriggerContext, and SqlDataRecord.
For more information, see this same topic in SQL Server 2005 Books Online.
SqlContext
Managed code is invoked in the server when a user calls a procedure or function, when a method is called on a common language runtime (CLR) user-defined type, or when a user's action fires a trigger defined in any of the .NET Framework languages. Because execution of this code is requested as part of a user connection, access to the caller's context from the code running in the server is required. In addition, certain data access operations may only be valid if run under the caller's context. For example, access to inserted and deleted pseudo-tables used in trigger operations is only valid under a caller's context.
The caller's context is abstracted in a SqlContext object (defined in Microsoft.SqlServer.Server), which provides access to the following components:
SqlPipe: The SqlPipe object represents the "pipe" through which results flow to the client.
SqlTriggerContext: The SqlTriggerContext object can only be retrieved from within a CLR trigger. It provides information about the operation that caused the trigger to fire and a map of the columns that were updated.
Determining Context Availability
The SqlContext class can be queried to see if the currently executing code is running in-process. This is accomplished by checking the IsAvailable property of the SqlContext object. The IsAvailable property is read-only, and returns True if the calling code is running inside Microsoft SQL Server and if other SqlContext members can be accessed. If the IsAvailable property returns False, all the other SqlContext members throw an InvalidOperationException, if used. If IsAvailable returns False, any attempt to open a connection object that has “context connection=true” in the connection string fails.
Retrieving Windows Identity
CLR code executing inside SQL Server is always invoked in the context of the process account. If the code should perform certain actions using the calling user’s identity instead of the SQL Server process identity, then an impersonation token should be obtained through the WindowsIdentity property of the SqlContext object. The WindowsIdentity property returns a WindowsIdentity instance representing the Microsoft Windows identity of the caller, or null if the client was authenticated using SQL Server Authentication. Only assemblies marked with EXTERNAL_ACCESS or UNSAFE permissions can access this property.
After obtaining the WindowsIdentity object, callers can impersonate the client account and perform actions on their behalf.
The identity of the caller is only available through WindowsIdentity if the client that initiated execution of the stored-procedure or function connected to the server using integrated security. If SQL Server Authentication was used instead, this property is null and the code is unable to impersonate the caller.
SqlPipe
In previous versions of SQL Server, it is very common to write a stored procedure (or an extended stored procedure) that sends results or output parameters to the calling client.
In a Transact-SQL stored procedure any SELECT statement that returns zero or more rows sends the results to the connected caller's "pipe."
For CLR database objects running in SQL Server, results can be sent to the connected pipe using the send methods of the SqlPipe object. The SqlPipe object is obtained by calling the Pipe property of the SqlContext object. The SqlPipe class is conceptually similar to the Response class found in ASP.NET
Returning Tabular Results and Messages
The SqlPipe has a Send method, which sends data straight to the client. It can send a string message (which surfaces on the client as an informational message, equivalent to PRINT in Transact-SQL), a single-row result set using SqlDataRecord, or a multi-row result set using a SqlDataReader.
The SqlPipe object also has an ExecuteAndSend method. This method can be used to execute a command (passed as a SqlCommand object) and send results directly back to the caller. It can only take a SqlCommand that is associated with the context connection; it cannot take a command that is associated with the non-context connection.
Returning Custom Result Sets
Managed stored procedures can send result sets to the client that do not come from a SqlDataReader. The SendResultsStart method, along with SendResultsRow and SendResultsEnd, allows stored procedures to send custom result sets to the client.
SendResultsStart takes a SqlDataRecord as an input. It marks the beginning of a result set and uses the record metadata to construct the metadata that describes the result set. It does not send the value of the record with SendResultsStart. All the subsequent rows, sent using SendResultsRow, must match that metadata definition.
Note
After calling the SendResultsStart method, only SendResultsRow and SendResultsEnd can be called. Calling any other method in the same instance of SqlPipe causes an InvalidOperationException. SendResultsEnd sets SqlPipe back to the initial state in which other methods can be called.
SqlTriggerContext
The SqlTriggerContext class provides context information about the trigger. This contextual information includes the type of action that caused the trigger to fire, which columns were modified in an UPDATE operation, and, in the case of a data definition language (DDL) trigger, an XML EventData structure which describes the triggering operation.
SqlDataRecord
The SqlDataRecord object represents a single row of data, along with its related metadata.
Managed stored procedures may send to the client result sets that are not from a SqlDataReader. The SqlDataRecord class, along with SendResultsStart, SendResultsRow, and SendResultsEnd methods of the SqlPipe object, allows stored procedures to send custom result sets to the client.