- Programming with SqlClient
- Context: The SqlContext Class
- Connections
- Commands: Making Things Happen
- Obtaining Results
- Transactions
- Pipe
- Creating and Sending New Rowsets
- Using the WindowsIdentity
- Calling a Web Service from SQLCLR
- Exception Handling
- SqlTriggerContext
- SqlClient Classes That You Can’t Use on the Server
- Where Are We?
This chapter is from the book
This chapter is from the book
This chapter is from the book
Pipe
In the section on results earlier in this chapter, we mentioned that you have a choice of processing results in your procedural code as part of its logic or returning the results to the caller. Consuming SqlDataReaders or the stream of XML in procedural code makes them unavailable to the caller; you cannot process a cursorless mode result more than once. The code for in-process consumption of a SqlDataReader is identical to SqlClient; you call Read() until no more rows remain. To pass a resultset back to the client, you need to use a special class, SqlPipe.
The SqlPipe class represents a channel back to the client; this is a TDS (Tabular Data Stream) output stream if the TDS protocol is used for client communication. You obtain a SqlPipe by using the static SqlContext. Pipe property. Rowsets, single rows, and messages can be written to the pipe. Although you can get a SqlDataReader and return it to the client through the SqlPipe, this is less efficient than just using a new special method for the SqlPipe class: ExecuteAndSend. This method executes a SqlCommand and points it directly to the SqlPipe. Listing 4-15 shows an example.
Listing 4-15: Using SqlPipe to return rows to the client
public static void getAuthorsByState(SqlString state)
{
SqlConnection conn = new SqlConnection("context connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "select * from authors where state = @state";
cmd.Parameters.Add("@state", SqlDbType.VarChar);
cmd.Parameters[0].Value = state;
SqlPipe pipe = SqlContext.Pipe;
pipe.ExecuteAndSend(cmd);
}
In addition to returning an entire set of results through the pipe, SqlPipe’s Send method lets you send an instance of the SqlDataRecord class. You can also batch the send operations however you like. An interesting feature of using SqlPipe is that the result is streamed to the caller immediately, as fast as you are able to send it, taking into consideration that the client stack may do row buffering. This may improve performance at the client because you can process rows as fast as they are sent out the pipe. Note that you can combine executing a command and sending the results back through SqlPipe in a single operation with the ExecuteAndSend convenience method, using a SqlCommand as a method input parameter.
SqlPipe also contains methods for sending scalar values as messages and affects how errors are exposed. We’ll talk about error handling practices next. The entire set of methods exposed by SqlPipe is shown in Table 4-5.
Table 4-5: Methods of the SqlPipe Class
|
Method |
What It Does |
|
ExecuteAndSend(SqlCommand) |
Executes command, returns results through SqlPipe |
|
Send(String) |
Sends a message as a string |
|
Send(SqlDataReader) |
Sends results through SqlDataReader |
|
Send(SqlDataRecord) |
Sends results through SqlDataRecord |
|
SendResultsStart(SqlDataRecord) |
Starts sending results |
|
SendResultsRow(SqlDataRecord) |
Sends a single row after calling SendResultsStart |
|
SendResultsEnd() |
Indicates finished sending rows |
There is also a boolean property on the SqlPipe class, IsSendingResults, that enables you to find out whether the SqlPipe is busy. Because multiple active resultsets are not supported when you’re inside SQL Server, attempting to execute another method that uses the pipe while it’s busy will procedure an error. The only exception to this rule is that SendResultsStart, SendResultsRow, and SendResultsEnd are used together to send results one row at a time.
SqlPipe is available for use only inside a SQLCLR stored procedure. Attempting to get the SqlContext.Pipe value inside a user-defined function returns a null instance. This is because sending rowsets is not permitted in a user-defined function. Within a stored procedure, however, you can not only send rowsets through the SqlPipe by executing a command that returns a rowset, but also synthesize your own. Synthesizing rowsets involves the use of two server-specific classes we haven’t seen before: SqlDataRecord and SqlMetaData.