How to return data records from a CLR Stored Procedure

Before reading this article I recommend you please go through the previous two article, where I have already discussed what/why/how to use CLR Stored Procedures.

Lets get started on this one !

Below is a sample code where first I am fetching all products with price is less than 200$ using the

string commandText = "SELECT * FROM Products WHERE PRICE < " + price.ToString();

Using this result-set I am sending back only the following columns as a result-set

– Product Name (as string)

– Price (with a little formatting by adding a ‘$’ symbol to the price eg: $23)

CLR Stored Procedure C# Code

public partial class StoredProcedures  
{  
    [Microsoft.SqlServer.Server.SqlProcedure]  
    public static void GetProductsByPrice(int price)  
    {  
        SqlConnection connection = new SqlConnection("context connection=true");  
        connection.Open();

        string commandText = "SELECT * FROM Products WHERE PRICE < " + price.ToString();

        SqlCommand command = new SqlCommand(commandText, connection);  
        SqlDataReader reader = command.ExecuteReader();

        // Create the record and specify the metadata for the columns.  
        SqlDataRecord record = new SqlDataRecord(  
        new SqlMetaData("Product Name", SqlDbType.NVarChar, 100, 1033, SqlCompareOptions.None),  
        new SqlMetaData("Price", SqlDbType.NVarChar, 100, 1033, SqlCompareOptions.None));

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

        while (reader.Read())  
        {  
            // Set values for each column in the row.  
            record.SetString(0, reader["ProductName"].ToString());  
            record.SetString(1, "$" +reader["Price"].ToString());

            // Send the row back to the client.  
            SqlContext.Pipe.SendResultsRow(record);  
        }

        // Mark the end of the result-set.  
        SqlContext.Pipe.SendResultsEnd();  
    }  
};

Output