- Creating Stored Procedures
- Returning Result Sets
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Returning Result Sets
Delving deeper, let’s take a look at how to access a table and return results from a CLR stored procedure using ADO.NET. Once you master this, you will easily be able to do other query operations such as update, modify, add, or remove records. Looking at the code in Listing 1.2, you can see that it’s not much more complicated than sending a simple message (as with the first CLR procedure):
Listing 1.2 Returning results from a CLR stored procedure using C#
using System;
using System.Data;
using System.Collections;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
namespace ResultsetCLR.SqlServer
{
public static class SProc
{
public static void proc1()
{
using (SqlConnection conn = new SqlConnection("context
connection = true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM
dbo.Widgets", conn);
SqlContext.Pipe.ExecuteAndSend(cmd);
conn.Close();
}
}
}
}
You’ll have to include the System.Data.SqlClient Namespace for this code to work. A new connection is opened and a command type argument is created with the SQL SELECT statement for querying the results. The SqlContext method is used again, but this time with the ExecuteAndSend property because the command must be executed at the CLR level before sending the results back to SQL server. To execute other queries, just change the query statement for the command argument. If you are doing an INSERT, UPDATE, or DELETE statement, use the cmd.ExecuteNonQuery() property (not SqlContext.Pipe.ExecuteAndSend) to invoke the query because you are only modifying the records, not returning any results to the client.