Returning Result Sets from a Stored Procedure

A good way to get both the advantages of performance from stored procedures and the functionality of a high level language is to use the DataTable class. The DataTable class gets the records returned by stored procedure SELECT query and stores the results in cache memory.

Doing so fetches and returns the data significantly faster than using traditional ADO.NET to return data from inline SQL calls — especially for larger sets.

Let's start with something simple: a stored procedure with a SELECT query that returns all the records in the authors table of the pubs database. The stored procedure, GetAuthors, contains only one SQL SELECT statement and no other code or parameters. To get and contain the results from this stored procedure, we use ADO.NET to setup a SQL adapter and a dataset, as shown in listing 1 below.

Listing 1: Returning results from a simple select query in a stored procedure using C# ADO.NET with the DataTable class

1 SqlConnection conn =
 new SqlConnection("Data Source=<host name>;uid=xaxaxa;pwd=axaxax;Database=pubs");
2   conn.Open();
  
3    SqlCommand  cmd = new SqlCommand("SelectAuthor", conn);
4    cmd.CommandType = CommandType.StoredProcedure;    
    
5      DataTable myDataSet = new DataTable(); 
6      SqlDataAdapter adapter = new SqlDataAdapter(cmd);
7       adapter.Fill(myDataSet);
            
8         foreach(DataRow myRow in myDataSet.Rows){
9          %> <br> <%
10         foreach (DataColumn myColumn in myDataSet.Columns){
11              Response.Write(myRow[myColumn] + " ");
12         }
13      }
14    conn.Close();

When executted, the code looks like the screen in Figure 1: all the rows of the dataset, with some basic HTML formatting.

The dataset results of the Authors table

On lines 1 and 2, we setup a connection. On lines 3 and 4 we tell ADO.NET that is will be a stored procedure called SelectAuthor. A new DataTable type, called myDataSet, is created on line 5. myDataSet holds the results returned from the query as one table with rows and columns in cache memory. Line 6 sets up a SqlDataAdapter type, called adapter, which executes the stored procedure returning the results in myDataSet using the Fill function on line 7. Lines 9 thru 13 use Rows and Columns method of the myDataSet type to index into the results and print them out by row and column, giving you the results shown in figure 1.

The example above uses a simple SELECT query returning all records in the table. But what if we want to be more specific, by returning only author records for a certain state? This can be accomplished by passing in a parameter to the stored procedure. We still get the benefits of having the stored procedure return the record set as a virtual table to cache memory, but this time based on a query parameter.

To add the parameter, we use the Add method of the Parameters object, as in listing 2 below:

Listing 2: Returning results from a simple select query in a stored procedure using C# ADO.NET

1 SqlConnection conn =
 new SqlConnection("Data Source=<host name>;uid=xaxaxa;pwd=axaxax;Database=pubs");
    
2   conn.Open();
    
3       SqlCommand  cmd = new SqlCommand("SelectAuthor", conn);
4       command.Parameters.Add("@state", "CA")
5       cmd.CommandType = CommandType.StoredProcedure;    
    
6           DataTable myDataSet = new DataTable(); 
7           SqlDataAdapter adapter = new SqlDataAdapter(cmd);
8           adapter.Fill(myDataSet);
            
9             foreach(DataRow myRow in myDataSet.Rows){
10                   %> <br> <%
11                   foreach (DataColumn myColumn in myDataSet.Columns){
12                                   Response.Write(myRow[myColumn] + " ");
13                   }
14            }
15    conn.Close();

For this to work, you have to be sure to add the input parameter to your stored procedure. The stored procedure should look something like this:

CREATE Procedure [SelectAuthor]
@state varchar
AS
SELECT * FROM authors where state = @state
GO

This is just a simple overview. To extend on this, you could also use the DataSet class. It, too, holds data returned from queries as DataTable does. However, DataSet is more flexible, as it can hold multiple table collections in one data set. It also contains properties such as the SELECT method, which allows you to further refine your query to populate a table in the collection without having to use input or output parameters to a stored procedure (such as with the DataTable object). Think of a DataSet as a virtual database instead of a virtual table. It contains properties for accessing constraints, and schemas among others that are common to databases.