The Send() Method

Listing 8. The Send() method has several overloads, and you can use it to return tabular data to the caller through a DataReader object instead of a string.

[SqlProcedure(Name = "sp_SqlPipe_SendReader")]
public static void SqlPipeReaderDemo()
{
    // Open a connection to the current database

    using (SqlConnection conn = new SqlConnection())
    {
        // We're sort of already inside of a connection, so all
        // that we need to do is tell SQL Server to use the current
        // context
        conn.ConnectionString = "context connection=true";

        // Don't forget to open it!
        conn.Open();

        SqlCommand cmd = new SqlCommand("SELECT * FROM Production.Product", 
            conn);

        // Instance of reader exists in this scope
        SqlDataReader reader = cmd.ExecuteReader();

        // Send the reader through the SqlPipe.  Caller will receive it
        // as a result set.
        SqlContext.Pipe.Send(reader);
    }
}