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

[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.

 

[Mark Rae]

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

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.

 

[John]

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.

 

[Bruce Barker]

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)

 

[Mark Rae]

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.

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...?

 

[John]

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.