Urgent : Populate a dataset from a stored proc in .NET 2.0

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi all,

I'm sorry I'm reposting this but the original was urgent and I do need
closure on this.

I'm calling a stored proc which does 4 "selects" and then I populate a
dataset looping through the dr using a dr.NextResult() but the stored proc
may not return any results for one or more Select stetments. When this
happens, a table is not returned so the dataset.Fill() does not populate
conatin 4 tables which then each bind to a grid - the nett result is that
the relevant gridview may not show the correct data.

Now when I do a dr.NextResult(), I don't know which table data is being
returned

How can I call a stored proc passing back a table for each Select
irrespective of whether there is data or not?

Regards
John.
 
How can I call a stored proc passing back a table for each Select
irrespective of whether there is data or not?
Click to expand...

Are you populating a DataReader or a DataSet?

A DataReader has a HasRows property. If it's false, there are no rows, so
move onto the next one.

A DataSet has at least one table, which has a Rows collection. If Rows.Count
is 0, it's empty, so move on to the next one.
 
Hi,

I'm populating a datareader using cm.ExecuteReader()

The problem is with the mutliple selects within my stored proc. I could call
it one time and get four resultsets hence dr.NextResult 4 times but the next
time I call it I may only receive two resultsets hence dr.Nextresult() twice
but now I don't know from which select statements these resultsets
originate.

I need a way for the stored proc to pass back an empty result set from the
select statements that don't return data. At least then I can still bind to
grid and the grid can "be aware" of my empty table structure.

Any ideas?

Regards
John.
 
you should design your proc better, so you can tell.

some ideas:

have the 1 column of each select identity the result set name.
check for existance of a unique col in each result set.
have a dummy select if the select is skipped
before each select do a select returning the next result identification.

-- bruce (sqlwork.com)
 
John said:
I need a way for the stored proc to pass back an empty result set from the
select statements that don't return data. At least then I can still bind
to grid and the grid can "be aware" of my empty table structure.
Click to expand...

I've already told you how to do this by using a DataSet.

1) Return the data from your SP into a DataSet instead of a DataReader

2) Count the number of Tables in the DataSet - each table corresponds to the
results of the SELECT statements in your SP

3) Interrogate the Rows.Count method of each Table in the DataSet - if its
value is 0, then that particular query has not returned any data.

What else do you need to know...?
 
Hi Mark,

A rather late reply but thanks a lot for your suggested solution - I do now
get the table structure whether or not data is contained in it.

Regards
John.
 
Back
Top