using System;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.Collections;

namespace Roman.SqlAssemblies
{
    public class Raspred
    {
        [SqlProcedure]
        public static void GetRaspred()
        {
            try
            {
                using (SqlConnection cn = new SqlConnection("context connection=true"))
                {
                    cn.Open();
                    string sql = "SELECT 10,1,'11_1' union select 20,2,'22_2' union select 30,3,'33_3'";
                    SqlCommand cmd = new SqlCommand(sql, cn);
                    ArrayList dbRecs = new ArrayList();
                    SqlDataReader sqlDr =cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    if (sqlDr.HasRows)
                    {
                        foreach (DbDataRecord rec in sqlDr)
                        {
                            dbRecs.Add(rec);
                        }
                    }

                    SqlPipe pipe = SqlContext.Pipe;
                    bool firstrow = true;
                    foreach (DbDataRecord rec in dbRecs)
                    {
                        SqlDataRecord row = new SqlDataRecord(new SqlMetaData("col1", SqlDbType.Int),
                            new SqlMetaData("col2", SqlDbType.Int), new SqlMetaData("col3", SqlDbType.VarChar,  50 ));
                        row.SetInt32( 0 ,rec.GetInt32( 0 ));
                        row.SetInt32( 1 , rec.GetInt32( 1 ));
                        row.SetString( 2 , rec.GetString( 2 ));
                        if (firstrow)
                        {
                            pipe.SendResultsStart(row);
                            firstrow = false;
                        }
                        else
                            pipe.SendResultsRow(row);
                    }
                    pipe.SendResultsEnd();
                }
            }
            catch (Exception e)
            {
                SqlPipe pipe = SqlContext.Pipe;
                pipe.Send("Error occurred executing command:");
                pipe.Send(e.Message);
                pipe.Send(e.StackTrace);
            }
        }
    }

    
}

CREATE ASSEMBLY Raspred
FROM 'd:\csharpclasses\Raspred.dll'
WITH PERMISSION_SET = SAFE;
GO
CREATE PROCEDURE GetRaspred
AS
EXTERNAL NAME Raspred.[Roman.SqlAssemblies.Raspred].GetRaspred;