Return multiple result in a SQL SP into a dataset(s)

[AussieRules]

Hi,

I have a need to do two selects against to stored proc's in my SQL db.

At the moment, each SP is called and two different dataset are populate.
Thats two round trips to the SQL server.

I can combine the two SP into one, and have one SP that executes two Select
Statements, and effectivly returns two result sets, and I could call this
with one trip to the SQL server from my VB app.

Only problem is then handling the two result sets that SQL is returning to
my VB app.

Can the dataadapter, or some other VB datacontrol handle this, and give me
both datasets ?

Thanks

[Herfried K. Wagner [MVP]]

* "AussieRule s" <so*****@nowher e.com> scripsit:

I have a need to do two selects against to stored proc's in my SQL db.

At the moment, each SP is called and two different dataset are populate.
Thats two round trips to the SQL server.

I can combine the two SP into one, and have one SP that executes two Select
Statements, and effectivly returns two result sets, and I could call this
with one trip to the SQL server from my VB app.

Only problem is then handling the two result sets that SQL is returning to
my VB app.

Can the dataadapter, or some other VB datacontrol handle this, and give me
both datasets ?

ADO.NET group:

<news://msnews.microsof t.com/microsoft.publi c.dotnet.framew ork.adonet>

Web interface:

<http://msdn.microsoft. com/newsgroups/default.asp?url =/newsgroups/loadframes.asp? icp=msdn&slcid= us&newsgroup=mi crosoft.public. dotnet.framewor k.adonet>

--
Herfried K. Wagner
MVP · VB Classic, VB.NET
<http://www.mvps.org/dotnet>

[Armin Zingler]

"AussieRule s" <so*****@nowher e.com> schrieb

I have a need to do two selects against to stored proc's in my SQL
db.

At the moment, each SP is called and two different dataset are
populate. Thats two round trips to the SQL server.

I can combine the two SP into one, and have one SP that executes two
Select Statements, and effectivly returns two result sets, and I
could call this with one trip to the SQL server from my VB app.

Only problem is then handling the two result sets that SQL is
returning to my VB app.

Can the dataadapter, or some other VB datacontrol handle this, and
give me both datasets ?

There's an ADO.NET group:
microsoft.publi c.dotnet.framew ork.adonet
--
Armin

[Fergus Cooney]

Hey Mr Tourist Guide,

I like your new uniform, it's much nicer than that other one you were
wearing.

;-))

Regards,
Fergus

[Herfried K. Wagner [MVP]]

* "Fergus Cooney" <fi*****@post.c om> scripsit:

I like your new uniform, it's much nicer than that other one you were
wearing.

Please tell things like this by mail and don't annoy the community with
posts like this. Thanks!

There is nothing to add to Armin's post.

--
Herfried K. Wagner
MVP · VB Classic, VB.NET
<http://www.mvps.org/dotnet>

[IbrahimMalluf]

It's so much easier to just answer the gentlman's question

Yes you can call one stored procedure that returns 2 or mor result sets and
the DataSet will have an array of all the rowsets returned. The following
code just that:

Dim MyData As New DataSet

Dim MyAdapter As SqlClient.SqlDa taAdapter

Dim MyCommand As SqlClient.SqlCo mmand

Dim MyConnection As New SqlClient.SqlCo nnection(Connec t)

MyCommand = New SqlClient.SqlCo mmand("TableTes t")

MyCommand.Comma ndType = CommandType.Sto redProcedure

MyCommand.Conne ction = MyConnection

MyAdapter = New SqlClient.SqlDa taAdapter(MyCom mand)

MyAdapter.Fill( MyData)

Me.DataGrid1.Da taSource = MyData

The DataGrid shows all three tables. (in this case...non-related to each
other.)

The SP looks like this:

CREATE PROCEDURE [TableTest]

AS

Select * From Persons

Select * From Businesses

Select * From Addresses
GO


Ibrahim Malluf



"AussieRule s" <so*****@nowher e.com> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..

Hi,

I have a need to do two selects against to stored proc's in my SQL db.

At the moment, each SP is called and two different dataset are populate.
Thats two round trips to the SQL server.

I can combine the two SP into one, and have one SP that executes two Select Statements, and effectivly returns two result sets, and I could call this
with one trip to the SQL server from my VB app.

Only problem is then handling the two result sets that SQL is returning to
my VB app.

Can the dataadapter, or some other VB datacontrol handle this, and give me
both datasets ?

Thanks

[Armin Zingler]

"Fergus Cooney" <fi*****@post.c om> schrieb

Hey Mr Tourist Guide,
Hey Mr Offtopic,
I like your new uniform, it's much nicer than that other one you were
wearing.

;-))

It's less nicer. Before I said "please". Now I'm not as friendly anymore.
--
Armin

[news.microsoft.com]

Boy, sorry to have started all of this!

Will be a better girl next time........

(but), since you where kind enough to answer me, I have a littel question to
close this off.

It would seem that your code returns the result sets into a single VB.net
dataset. I need to either be able to access the different results sets
within the one dataset, or have each result set put into a seperate dataset
somehow, all within one round trip to the sql server

"IbrahimMal luf" <Ib*****@malluf .com> wrote in message
news:#R******** *****@TK2MSFTNG P11.phx.gbl...

It's so much easier to just answer the gentlman's question

Yes you can call one stored procedure that returns 2 or mor result sets and the DataSet will have an array of all the rowsets returned. The following
code just that:

Dim MyData As New DataSet

Dim MyAdapter As SqlClient.SqlDa taAdapter

Dim MyCommand As SqlClient.SqlCo mmand

Dim MyConnection As New SqlClient.SqlCo nnection(Connec t)

MyCommand = New SqlClient.SqlCo mmand("TableTes t")

MyCommand.Comma ndType = CommandType.Sto redProcedure

MyCommand.Conne ction = MyConnection

MyAdapter = New SqlClient.SqlDa taAdapter(MyCom mand)

MyAdapter.Fill( MyData)

Me.DataGrid1.Da taSource = MyData

The DataGrid shows all three tables. (in this case...non-related to each
other.)

The SP looks like this:

CREATE PROCEDURE [TableTest]

AS

Select * From Persons

Select * From Businesses

Select * From Addresses
GO


Ibrahim Malluf



"AussieRule s" <so*****@nowher e.com> wrote in message
news:%2******** ********@TK2MSF TNGP10.phx.gbl. ..

Hi,

I have a need to do two selects against to stored proc's in my SQL db.

At the moment, each SP is called and two different dataset are populate.
Thats two round trips to the SQL server.

I can combine the two SP into one, and have one SP that executes two

Select

Statements, and effectivly returns two result sets, and I could call this with one trip to the SQL server from my VB app.

Only problem is then handling the two result sets that SQL is returning to my VB app.

Can the dataadapter, or some other VB datacontrol handle this, and give me both datasets ?

Thanks

[Fergus Cooney]

Hi Armin,

Nooooo, sob, sob, I'm toooo tired..... :-(

LOL. Ok. Last try. ;-)

As it looks to me, it's gone from a command to a suggestion. That is a
major change. Perhaps I was being a bit strong with the compliment, but I
wanted to applaud that change.

Saying 'please' suggests that someone is doing something for you. Please
is an asking word. That's why I talk about Policeman. He says 'go there' and
he adds 'please' to make it polite - but it's actually a <command>.

In the command version - "This is the wrong place, please go there" - the
please can actually be a pointy stick to add emphasis. Believe it or not, that
is how it can sound, and <will> to some people (maybe just a few, maybe more).

The Tourist Guide doesn't issue a command - in fact they aren't even
making a request. There's therefore no need to say please. The Guide approach
is to <offer> something - an alternative. That's what you were doing with that
simple line. 'Please' isn't used when offering something unless it's of the
'please be nice to me and accept the gift' type. And that's not appropriate
here because it's take or leave it - your choice - and I don't mind, I'm just
letting you know.

=============== =============== ==========

This is what you said.

|| There's an ADO.NET group:
|| microsoft.publi c.dotnet.framew ork.adonet

It's sparse, but it's perfect!!

The OP is not wrong to be here. Nothing suggests that. They are not
unwelcome. Nothing suggests that either. They are not being told they <won't>
get an answer here, for nothing suggests <that>. There's no negative in it at
all, that I can see.

On the other hand, there is useful information - if they care to use it -
another place to try <in addition> to being here. It's a perfect, and neutral,
offering.
The old style.

|| This is a VB.NET group. Please turn to xyz.

One interpretation:
OP. You are in the wrong place. There is nothing for you here. I command
you to go to xyz (but politely, of course).
To me, the difference between the two is glaring. And while not everyone
will construe the above interpretation, enough will. But you won't know it
because there will be scant feedback - very, very few will tell you directly.
You'll get apologetic gestures from some. The others will just carry away
'unhappy' thoughts.

Hence ;-)) with the new version.

=============== =============== ==========

How does this sound to you?

Armin, please change the way that you speak to people.

Does that sound like I'm asking you nicely or does it come across as a
command, with every expectation of being obeyed, despite the please? It could
be either - it's ambiguous. But, whether taken as a plea, a request or a
demand, it's still me wanting you to do something <for me>. I'm not offering a
choice that is intended to be useful <to you>.
Now how about this one?

Armin, I appreciate that you are a man of few words. ;-) It might be
useful to ponder on how the fewer words you use, the more ambiguous things may
be. And to ponder on how some things come across as commands and others as
offered suggestions.

There may be as much desire on my part as in the previous version, but I'm
offering it to you to use as you please. There is of course hope in my heart,
but also hopefully it doesn't sound like a command requiring compliance. It
may sound that way, of course, but the choice of words is intended to lessen
the chances of that considerably.

=============== =============== ==========

So if you can't use 'please' to make it polite, what can you add that will
turn the volume up on the friendliness scale?

LOL, I won't give myself as an example because that volume level may blow
your speakers.

But here's a form that Jay uses.

|| Dave,
|| Have you tried asking this "down the hall" in the
|| microsoft.publi c.dotnet.framew ork.interop newsgroup?
||
|| Unfortunately I suspect most regulars in this newsgroup
|| do not do enough interop to offer much assistance.
|| ...
|| Hope this helps
|| Jay

There are several ways in which this conveys friendliness.
Use of the OP's name (even when it's a ridiculous one).

A suggestion (and most obviously so) of somewhere else to try. I
wonder what image "down the hall" conveys to you? For me it fits both my views
of this place - as a drop-in help centre and as a place of learning.

A regret (with no loss of face on Jay's part) that help will very
possibly not be forthcoming

The possibility of no help is <qualified> - no absolutes. It's true to
say that we <may> be able to help but not necessarily. It's false to say that
we can (though knowing who lives here helps), and false to state that we can't
(the right person may have just logged on).

A sign-off that says "I'm here to be helpful"

All together there is no possibility of taking this the wrong way. No
ambiguity because there are plenty of parts and words - all complementing each
other.

=============== =============== ==========

This is my last outpouring on the subject - the only thing that I could
add would be more examples of how other people have redirected.

The thing is, as noted above - you are a man of few words. Asking you to
spend your time creating a niceness for someone who you are 'getting rid of'
is not something I can do, if that is how you see it. Why should you be nice
to a pest? But do you see them that way? Somehow I don't think so. But it does
boil down to how important the feelings of the OPs are versus the effort
required to be helpful in a nice way.

I'm not suggesting that you start calling people by their name, though it
is a politeness, even a friendliness. I'm not suggesting that you sign off
with a HTH or a regards or anything. I'm not etc, the other parts. These are
all options. You may decide that none are of use to. You might think it
desirable but would take too much time.

I've given plenty of (eat more than you can carry for $1.99!!) food for
thought. You've read all my words on this in the past and I thank you for your
consideration then and now. You may not see the use in making changes... at
this time.

One possible difficulty that I anticipate is due to this having been such
a massively blown-up issue. [And not helped by the fact of a simultaneous
(completely over the top) debacle taking place]. This means that if you do
decide to make any changes in how the world sees Armin, there will be a level
of self-consciousness which may be uncomfortable. There may also be an issue
with making changes because of Fergus which may also be uncomfortable. I don't
know how you are with such things, but I'm aware that many people would feel
that way.

To make it easier, perhaps, I'm going to block messages from you for a
week or so*. That way you can stay or change without needing to think of me
watching. When I unblock your messages (after all ;-) I want to keep gaining
from your solutions) I will make no further comments on this matter - neither
compliments** nor moans.

Regards,
Mr Offtopic,

* I'll wait for any reply and then put the block on. Maybe you'll tell me it's
not necessary but I'll do it anyway just for a week or so.

** If you eventually come to realise that the 'rewards' from the OPs have
increased, I will not be the slightest bit surprised. You won't need my
appreciation.

[Fergus Cooney]

Hi AussieRules, Steve,

Aussie, I echo Steve's words. As one of the boys throwing his toys around
the playground it is for me to say sorry to you (and, by way of this
opportunity, to others), and to regret that you have felt uncomfortable. As an
innocent bystander, please accept my apologies and my pledge that normal
service has been resumed.

Steve, thank you for your words last night and now. They were/are
appreciated. ;-) After a quick blush of shame, and a brushing off and
smoothing down of attitudes.

There <is> a longish and seriously toned off-topic message appearing in
this thread (just posted). It is nothing to do with the 'fun and games' of
this last weekend and, as it's me disguised as an adult talking to another
adult, I hope it's not seen as contradicting the words above.

Regards both, and all,
Fergus