Archive

Posts Tagged ‘SendResultsStart’

Returning Data from a CLR Stored Procedure

April 27th, 2011 1 comment

The following sample code shows how to return a resultset from a CLR stored procedure.
The main thing to note is that you have to pass an empty SqlDataRecord to the SendResultsStart() method in order to provide feedback on the columns sent back to the caller.

[SqlProcedure]
public static SqlInt32 MyStoredProcedure()
{
    SqlMetaData[] meta = new SqlMetaData[] { new SqlMetaData( "MyColumn", SqlDbType.Int ) };
    Int32[] data = new Int32[] { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 };
    SqlDataRecord[] records = data.Select( x => { SqlDataRecord r = new SqlDataRecord( meta ); r.SetInt32( 0, x ); return r; } ).ToArray();
 
    SqlContext.Pipe.SendResultsStart( new SqlDataRecord( meta ) );
    foreach ( SqlDataRecord r in records )
        SqlContext.Pipe.SendResultsRow( r );
    SqlContext.Pipe.SendResultsEnd();
 
    return records.Length;
}

In case you would like to return data retrieved from the database using a SqlDataReader object, you can directly pass the reader to the SqlContext.Pipe.Send() method:

[SqlProcedure]
public static void MyStoredProcedure()
{
    using ( SqlConnection con = new SqlConnection( "context connection=true" ) )
    {
        con.Open();
        using ( SqlCommand cmd = con.CreateCommand() )
        {
            cmd.CommandText = "SELECT SomeColumn FROM SomeTable WHERE SomeColumn IS NOT NULL";
            using ( SqlDataReader r = cmd.ExecuteReader() )
                SqlContext.Pipe.Send( r );
        }
    }
}
Categories: C#, Software Development, SQL Tags: , , , , , , , , , , ,