• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1375
  • Last Modified:

VB.NET: SqlDataReader + DataTable : Copying returned multiple recordset into a single recordsets

In order to improve performance on my SQL Azure database, I want to combine several queries together.

SELECT * FROM Table1;
SELECT * FROM Table2;
SELECT * FROM Table3;

I have three DataTable objects that I would like to copy the returned data.

Dim dtTable1 As New DataTable
Dim dtTable2 As New DataTable
Dim dtTable3 As New DataTable

When I load the DataTable object with the recordset, it appears to include all returned recordsets, rather than just one.

Example:

dtTable1.Load(Rs)

Rs.NextResult
dtTable2.Load(Rs)

Rs.NextResult
dtTable3.Load(Rs)

After several recordsets are copied into the DataTables, it errors.

A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll

If I run the queries separately, I don't receive the error.  I am trying to accomplish:

  o Single, quick query to the Azure server using 1 query
  o Update the DataTable objects with the returned recordsets, separately

Is there a way to "copy" out a single recordset to a new object without the other recordsets included?

0
Officedoxs
Asked:
Officedoxs
1 Solution
 
DhaestCommented:
Don't you have the possibility to query the tables at once by using a union ?

>> A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Where do you get that error ?
0
 
CodeCruiserCommented:
Given that the Load method of DataTable internal calls the NextResult,
http://platinumdogs.wordpress.com/2009/05/14/sqldatareader-datatable-and-multiple-resultsets/

you have to use an alternative. I think you have two options

1) Use DataSet.Load
2) Use a DataAdapter.Fille(DataSet) which would create separate DataTable objects for each resultset.
0
 
OfficedoxsAuthor Commented:
From the referenced link, I can use the DataTable object successfully.  It's just that I need to remove the Rs.NextResult lines in my code, the .Load does it automatically.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now