Altering results prior to using SQLContext.Pipe.Send() in a .NET sproc

Question

Is it possible to edit the data returned from command.ExecuteReader, and then return that to SqlContext.Pipe.Send()? Are there any forseeable issues (I have to reset the cursor to the beginning)?

I have a .NET stored procedure that will query a table like this

(code from MSDN)

public class StoredProcedures 
{
   /// <summary>
   /// Execute a command and send the resulting reader to the client
   /// </summary>
   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void SendReaderToClient()
   {
      using(SqlConnection connection = new SqlConnection("context connection=true")) 
      {
         connection.Open();
         SqlCommand command = new SqlCommand("select FirstName,LastName, PictureURL from myTable", connection);
         SqlDataReader r = command.ExecuteReader();
         //QUESTION: Can I modify "r" here, and return it below?
         SqlContext.Pipe.Send(r);
      }
   }
}

Answer 1

You can describe your own result set with SendResultStart, then send each row with SendResultsRow:

  using(SqlConnection connection = new SqlConnection("context connection=true")) 
  {
     // Create the record and specify the metadata for the columns.
     // This record describes a result with two columns:
     //  Name NVARCHAR(4000)
     //  URL VARCHAR(4000)
     //
     SqlDataRecord record = new SqlDataRecord(
       new SqlMetaData("Name", SqlDbType.NVarChar, 4000),
       new SqlMetaData("URL", SqlDbType.VarChar, 4000),
       ...);

     // Mark the begining of the result-set.
     SqlContext.Pipe.SendResultsStart(record);

     connection.Open();
     SqlCommand command = new SqlCommand("select Name, Picture from myTable", connection);
     using (SqlDataReader rdr = command.ExecuteReader())
     {
        while(rdr.Read ())
        {
            // Transform the current row from rdr into the target record
            string nameDb = rdr.GetString(0);
            string urlDb = rdr.GetString(1);

            // do the transformations:
            string nameResult = String.Format("<h2>{0}</h2>", nameDb);
            string awt = ComputeTheAWT(urlDb);
            string urlResult = FormatURL (urlDb, awt);

            // Assign the record properties
            record.SetString(0, nameResult);
            record.SetString(1, urlResult);

            // send the record
            SqlContext.Pipe.SendResultsRow(record);
        }
     }
     SqlContext.Pipe.SendResultsEnd ();
  }