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]

"John" <a@b.c> wrote in message
news:O%****************@TK2MSFTNGP14.phx.gbl...

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.

"Mark Rae" <ma**@markN-O-S-P-A-M.co.uk> wrote in message
news:ux**************@TK2MSFTNGP12.phx.gbl...

"John" <a@b.c> wrote in message
news:O%****************@TK2MSFTNGP14.phx.gbl...

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.

[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)
"John" <a@b.c> wrote in message news:eN*************@TK2MSFTNGP15.phx.gbl...

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.

"Mark Rae" <ma**@markN-O-S-P-A-M.co.uk> wrote in message
news:ux**************@TK2MSFTNGP12.phx.gbl...

"John" <a@b.c> wrote in message
news:O%****************@TK2MSFTNGP14.phx.gbl...

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.

[Mark Rae]

"John" <a@b.c> wrote in message news:eN*************@TK2MSFTNGP15.phx.gbl...

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.

"Mark Rae" <ma**@markN-O-S-P-A-M.co.uk> wrote in message
news:ee**************@TK2MSFTNGP10.phx.gbl...

"John" <a@b.c> wrote in message
news:eN*************@TK2MSFTNGP15.phx.gbl...

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