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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

John - 17 Feb 2006 23:40 GMT

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.

Reply to this Message

Mark Rae - 17 Feb 2006 23:50 GMT

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

Reply to this Message

John - 18 Feb 2006 00:21 GMT

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.

>> How can I call a stored proc passing back a table for each Select
>> irrespective of whether there is data or not?
[quoted text clipped - 6 lines]
> 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.

Reply to this Message

Bruce Barker - 18 Feb 2006 01:24 GMT

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)

> Hi,
>
[quoted text clipped - 25 lines]
>> 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.

Reply to this Message

Mark Rae - 18 Feb 2006 08:33 GMT

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

Reply to this Message

John - 20 Feb 2006 15:41 GMT

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.

>> 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
[quoted text clipped - 11 lines]
>
> What else do you need to know...?

Reply to this Message

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

Free Magazines