How to return data records from a CLR Stored Procedure

Yasser Shaikh
Nov 18, 2012 · 2 min read

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

Yasser Shaikh's blog

It's supposed to be automatic, but actually you have to push this button.

Yasser Shaikh

Written by

Yasser Shaikh's blog

It's supposed to be automatic, but actually you have to push this button.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade