Categories
Uncategorized

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