次の方法で共有

TableAdapter and Multiple Result Sets

  • [アーティクル]

Several people asked me whether TableAdapter can be used to retrieve multiple result sets from stored procedures. The quick answer is... No, you can't do that with TableAdapter.Fill() method that gets generated as a part of Typed Dataset. But there's a very simple work-around.

DataAdapter.Fill() & Multiple Result Sets

TableAdapter.Fill() method calls DataAdapter.Fill() to retrieve data from database. And DataSet.Fill() method allows you to retrieve multiple result sets from a stored procedure. In order to retrieve multiple result sets, an overload of DataAdapter.Fill() that takes Dataset as a parameter needs to be used. If you do that, you get back a Dataset that contains multiple data tables filled with result sets returned by your stored procedure.

Here's a simple example that shows you how this works.

Let's say that you have a stored procedure called dbo.spSelectCustomersOrders in Northwind database.

CREATE PROCEDURE spSelectCustomersOrders

AS

BEGIN

    SET NOCOUNT ON;

    SELECT * FROM Customers

    SELECT * FROM Orders

END

GO

Below code will call this stored procedure and fill Dataset with both result sets.

Dim myConn As New System.Data.SqlClient.SqlConnection

Dim myAdapter As New System.Data.SqlClient.SqlDataAdapter

Dim mySelectCommand As New System.Data.SqlClient.SqlCommand

Dim myDataset As New System.Data.DataSet

myConn.ConnectionString = "Data Source=.\SQLExpress;Initial Catalog=Northwind;Integrated Security=True"

mySelectCommand.Connection = myConn

mySelectCommand.CommandText = "dbo.spSelectCustomersOrders"

myAdapter.SelectCommand = mySelectCommand

myAdapter.Fill(myDataset)

For Each table As System.Data.DataTable In myDataset.Tables

Console.WriteLine("Table Name:" & table.TableName)

Next

Output of this code looks like this.

Table Name: Table
Table Name: Table1

As you can see, DataAdapter.Fill() method executed the stored procedure and put each result set into two separate DataTables.

TableAdapter Solution

But why doesn't TableAdapter.Fill() correctly handle multiple result sets? It's because TableAdapter.Fill() calls DataAdapter.Fill() overload that takes DataTable, not Dataset. This can be easily addressed by creating a new Fill method in TableAdapter that calls DataAdapter.Fill() overload that takes Dataset.

Assume we have NorthwindDataset.xsd that contains both Customers and Orders. And let's use the above stored procedure for our new Fill method. Add following code to your partial class file. You can create a partial class file by either double-clicking on the Dataset Designer surface, right-click on the Dataset Designer surface and choose "View Code" or add an empty class file manually.

Namespace NorthwindDataSetTableAdapters

Partial Public Class CustomersTableAdapter

Public Function FillCustomersOrders(ByVal dataSet As NorthwindDataSet) As Integer

Dim multiSelectCommand As New System.Data.SqlClient.SqlCommand

Dim returnValue As Integer

multiSelectCommand.Connection = Me.Connection

multiSelectCommand.CommandText = "dbo.spSelectCustomersOrders"

Me.Adapter.SelectCommand = multiSelectCommand

'' Map auto-created Table1 that holds the second result-set (Orders rows) to

'' Orders DataTable in our Dataset.

Me.Adapter.TableMappings.Add("Table1", "Orders")

returnValue = Me.Adapter.Fill(dataSet)

Return returnValue

End Function

End Class

End Namespace

Couple of key points to note.

First, this new FillCustomersOrders takes Dataset as a parameter. This way, when we call DataAdapter.Fill() we can retrieve both result sets and fill Dataset appropriately.

Second, notice how we use TableMapping to map automatically created DataTable to Orders DataTable in our Dataset. When result sets are retrieved by DataAdapter.Fill() method, each result set is stored in separate DataTables in Dataset. By default, these DataTables are named as Table, Table1, Table2, etc. In order to map these DataTables to typed DataTables defined in our Dataset, we need to use TableMapping. If you open up code behind file for NorthwindDataset.xsd, for example, and browse to InitAdapter() method of one of TableAdapter class, you will see something similar to below.

tableMapping.SourceTable = "Table"

tableMapping.DataSetTable = "Customers"

'' Colum mapping code skipped

...

Me._adapter.TableMappings.Add(tableMapping)

This block of code ensures that what gets returned by DataAdapter.Fill method maps to the correct typed DataTable in our Dataset. In our case with FillCustomersOrders, we know that the second result set contains Orders rows. So we need to create a mapping between Table1 and Orders to make sure Orders DataTable gets filled correctly.

After you add above code to the partial class file, you can call FillCustomersOrders to fill both Customers and Orders with just one round-trip to database.

CustomersTableAdapter.FillCustomersOrders(Me.NorthwindDataSet)

Performance Consideration

I can see how this kind of capability could be helpful in some cases. But you have to remember that this comes with its own issues. Although you might think that avoiding multiple trips to database would help in terms of performance, you could end up hurting performance by retrieving large set of data when you only need a sub-set. A lot of times, it is much better to retrieve smaller set of data, only what you need at the moment, and make another call to database when you need other sets of data. ADO.NET does a pretty good job of optimizing connection pooling so making multiple connections to database doesn't cause performance bottleneck in many cases. Rule of thumb. Only retrieve what you need.

But I can see how retrieving multiple result sets can be helpful in some cases. So, make use of the technique I described here and just make sure you consider performance issues always.

Additional Resources

- Young Joo, Program Manager, Visual Basic

Comments

  • Anonymous
    May 01, 2007
    Hi, when I click on the VS2005 DataSet designer nothing happens.  And when I select View Code, all I see is XML of the dataset schema.  Where do I enter the partial class? Is there supposed to be a .cs (or .vb) file associated with the Typed Dataset in VS2005? I think there was by default in 2003..

  • Anonymous
    November 12, 2009
    Well explained. Good tutorial.