SQL Server Stored Procedures – Returning Multiple Tables to ADO.NET

how to use Multiple ResultSets with the ADO.NET SqlDataReader class. In John’s article he illustrates how you can execute multiple SQL select statements in batch and access all of the results of each SQL select statement with SqlDataReader.NextResult(). The MSDN library documentation for SqlDataReader.NextResult() indicates that it “advances the data reader to the next result, when reading the results of batch Transact-SQL statements.” You can also utilize SqlDataReader.NextResult() with SQL Server stored procedures.

In addition, you can access multiple ResultSets with SqlDataAdapter.Fill(), which is what this blog focuses on.

If a single stored procedure issues several SELECT statements, they are all returned to the caller by default. If we convert John’s example of running 4 queries in batch against the Northwind database to a stored procedure, we now have:

/* Test stored procedure. Returns 4 ResultSets */
CREATE PROCEDURE spSomeStoredProcedure AS
BEGIN

SELECT CategoryName FROM Categories ORDER BY CategoryName
SELECT Top 10 CompanyName FROM Customers ORDER BY CompanyName
SELECT LastName FROM Employees ORDER BY LastName

SELECT Top 10 ProductName FROM Products ORDER BY ProductName

END

GO

Using SqlDataAdapter.Fill() to access all of your returned tables in ADO.NET is really easy:

SqlConnection conn = new SqlConnection(connection);
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = new SqlCommand(“exec spSomeStoredProc”, conn);
adapter.Fill(dataset);

// dataset.Tables[0] – refers to resultset obtained from first SQL query
// in stored procedure. dataset.Tables[1] – refers to resultset obtained

// from second SQL query. Etc.

The ordinal position of each DataTable in dataset.Tables matches the order that the queries are executed within the stored procedure. This technique can and should be used to minimize network roundtrips between your application server and database server (in addition to minimizing all of the marshalling and additional overhead required per remote database query execution).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: