Strongly typed datasets: Can table adapter handle multiple resultsets?

[Max2006]

Hi,

I am using Visual Studio 2008 and strongly typed datasets.

My stored procedure returns 2 result sets (it has two select statements in
it). how can I receive and transfer both result sets into two strongly
typed datatables with only one database call?

Any help would be appreciated,
Max

 

[Linda Liu[MSFT]]

Hi Max,

Firstly, a TableAdapter is usually used to fill a DataTable at one time. In
your scenario, you'd like to fill two TableTables with only one database
call. So it's not appropriate to use a TableAdapter to do this. I suggest
that you use a DataAdapter.

Secondly, if the SelectCommand of a DataAdapter returns mulitple result
sets at one time, the DataAdapter use table mappings to fill corresponding
DataTables in a DataSet. By default, the first result set will be filled to
a DataTable named "Table", and the second result set will be filled to a
DataTable named "Table1" and so on.

The following is a sample. It assumes that there're two tables named
"Student" and "Teacher" and a store procedure called "SelectST" that
retrieve all rows from the two database tables in DB. The code snippet
below creates a SqlDataAdapter and fill a strong-typed DataSet containing a
"Student" DataTable and a "Teacher" DataTable using the SqlDataAdapter.

SqlDataAdapter sqlDa = new SqlDataAdapter();
SqlCommand selectCmd = new SqlCommand();
selectCmd.CommandText = "SelectST";
selectCmd.CommandType = CommandType.StoredProcedure;
selectCmd.Connection = this.sqlConnection1;

sqlDa.SelectCommand = selectCmd;
// add table mappings to the SqlDataAdapter
sqlDa.TableMappings.Add("Table", "Student");
sqlDa.TableMappings.Add("Table1", "Teacher");

// DataSet1 is a strong-typed DataSet
DataSet1 ds = new DataSet1();
this.sqlConnection1.Open();
// after this line code is executed, the two DataTables(Student and
Teacher) are filled with the corresponding result sets
sqlDa.Fill(ds);
this.sqlConnection1.Close();

Hope this helps.
If you have any question, please feel free to let me know.


Sincerely,
Linda Liu
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Max2006]

Hi Linda,

I found the TableAdapter solution here:

http://blogs.msdn.com/vbteam/archive/2007/03/13/tableadapter-multiple-result-sets.aspx

Thank you for reply,
Max

 

[Cor Ligthert[MVP]]

Max,

But the tableadapter handles in this case everytime single resultsets and
therefore it is another situation then your question.
In this case you need two SQL procedures (if it is an SP or a dynamic one is
not so important in this case).

Therefore there is in my idea only one correct answer to your question, and
that is the one Linda gave you.

(With the table adapter you don't even need mapping, Fill is always adding
to a dataset, never creating a new one or clean it. That you have to do
explicitily. If a new resultset with a new name is returned it created
direct a new DataTable with the given name.

Funny is that the page gives as well direct an answer to an in my idea
completely wrong reply from another MSFT in this newsgroup today.

Cor

 

[Max2006]

Hi Cor,

Thank you for reply.

I agree that we cannot configure TableAdaper code to handle multiple
resultset and we have to extend it.

However the idea of adding an overload Fill method to the partial class to
extend the table mapping is a nice workaround. Would you be agree?

Thanks again,
Max

 

[Roland]

Hi

I though it would be nice to have the solution here instead of going to
another site

I have encountered an instance where I absolutely needed to fill 2 tables
with the same SP as I needed to search Suppliers and return also linked
SupplierContacts in a paging mode.

If you have a generic table adapter that return multiple tables, it will
return a definition as this:
Table
Table1
Table2
etc

If you use the designer, and fill using your table adapter it will return
something like this
Supplier
Table1
Table2

This is not very helpful.

Here is how you do it.

DataSet: dsSuppliers
Supplier
SupplierContacts

Under Supplier Table Adapter, I created a SP and uses the FILL
SP:
Select * from suppliers
Select * from SupplierContacts

And here is the code on my dsSupplier

1. Overloads the fill
Namespace dsSuppliersTableAdapters
Partial Public Class SupplierTableAdapter
Public Overloads Function Fill(ByVal ds As dsSuppliers) As Integer
If Me.ClearBeforeFill Then
ds.Supplier.Clear()
ds.SupplierContacts.Clear()
End If

Me.Adapter.SelectCommand = Me.CommandCollection(0) ' SP is the
first command in the SupplierTableAdapter
Me.Adapter.TableMappings.Add("Table1", "SupplierContacts")

Return Me.Adapter.Fill(ds)
End Function
End Class
End Namespace

2. Standard use of FILL
Partial Class dsSuppliers
Public Sub FillSuppliersAndContacts()
Dim ta As New dsSuppliersTableAdapters.SupplierTableAdapter
ta.Fill(Me)
End Sub
End Class

Cheers