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

  • Thread starter Thread starter AussieRules
  • Start date Start date
A

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
 
* "AussieRules said:
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 ?
Click to expand...

ADO.NET group:

<
Web interface:

<http://msdn.microsoft.com/newsgroup...roup=microsoft.public.dotnet.framework.adonet>
 
AussieRules said:
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 ?
Click to expand...

There's an ADO.NET group:
microsoft.public.dotnet.framework.adonet
 
Hey Mr Tourist Guide,

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

;-))

Regards,
Fergus
 
* "Fergus Cooney said:
I like your new uniform, it's much nicer than that other one you were
wearing.
Click to expand...

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

Dim MyCommand As SqlClient.SqlCommand

Dim MyConnection As New SqlClient.SqlConnection(Connect)

MyCommand = New SqlClient.SqlCommand("TableTest")

MyCommand.CommandType = CommandType.StoredProcedure

MyCommand.Connection = MyConnection

MyAdapter = New SqlClient.SqlDataAdapter(MyCommand)

MyAdapter.Fill(MyData)

Me.DataGrid1.DataSource = 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
 
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

IbrahimMalluf said:
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.SqlDataAdapter

Dim MyCommand As SqlClient.SqlCommand

Dim MyConnection As New SqlClient.SqlConnection(Connect)

MyCommand = New SqlClient.SqlCommand("TableTest")

MyCommand.CommandType = CommandType.StoredProcedure

MyCommand.Connection = MyConnection

MyAdapter = New SqlClient.SqlDataAdapter(MyCommand)

MyAdapter.Fill(MyData)

Me.DataGrid1.DataSource = 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











AussieRules said:
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
Click to expand...
Click to expand...
 
don't appologize...we've been experiencing internal behavioral anomolies for
almost a week now. *we* need to be better girls and boys...not you. i
appologize to you on behalf of those who continue to *act* like wee lads and
lasses.

to answer your second question, the single dataset will contain the combined
returned data from the sproc. you can access each on an individual basis
either by name or index...all within the same resource...the dataset.

hth,

steve

| 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
|
| | >
| >
| > 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.SqlDataAdapter
| >
| > Dim MyCommand As SqlClient.SqlCommand
| >
| > Dim MyConnection As New SqlClient.SqlConnection(Connect)
| >
| > MyCommand = New SqlClient.SqlCommand("TableTest")
| >
| > MyCommand.CommandType = CommandType.StoredProcedure
| >
| > MyCommand.Connection = MyConnection
| >
| > MyAdapter = New SqlClient.SqlDataAdapter(MyCommand)
| >
| > MyAdapter.Fill(MyData)
| >
| > Me.DataGrid1.DataSource = 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
| >
| >
| >
| >
| >
| >
| >
| >
| >
| >
| >
| > | > > 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
| > >
| > >
| >
| >
|
|
 
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.public.dotnet.framework.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.public.dotnet.framework.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.
 
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
 
|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.

ahhh...someone's never been in the military. brevity coupled with
succinctness are golden...all else is a waste of time in that context (and
an assurity of a length of service devoid of promotion). poets are praised
for folding very complex ideas into the smallest of passages...with style
and grace. compressing content into a few words in the fashion of a joke is
called wit...and is the hallmark of intellect. in long or short form, the
point of writing/speaking is to fully convey a message. however, be aware
that the more verbose an explanation, the more work that has to be done to
keep an audience attentive. ambiguity is simply the apparent absence of
cohesion or reason for being...or the confliction of the same.

your comments may actually be beneficial in explaining to armin (and others)
the differences between commands and suggestions being that this group
sponsers mainly "english as a second language" participants. he either said
it the best way he knew how or he said exactly what he meant. but at least
we are thinking about what we're say now...i've been in other ngs. that
literally say "this is the blah ng...if you didn't catch that once you got
here, you may take this response as your first indication to get the f*ck
out!"

i'd say all-round, either asking or suggesting where to direct questions
makes a much more attactive environment...differences to either approach
become minimalistic as well.

but that's just my $0.02 usd.

cheers oh fe/au guru. ;^)

steve
 
Fergus Cooney said:
He says 'go there' and
he adds 'please' to make it polite - but it's actually a <command>.
Click to expand...

You are right. I am wrong. All the English teachers I've ever had were
wrong. They haven't been to an English speaking country at all. The've never
studied English. They not even went to school. That's why they were wrong
when teaching me the meaning of words like "please" and "thank you".


You are ridiculous.

For me: EOT
 
oh armin...lol

you know, i was trying to give you guys an *easy out* on this one. i think
your english is fine! in fact, i think we can safely rule out the
possibility of one of these options:

|he either said it the best way he knew
|how or he said exactly what he meant.

the more i read your post below, the more i rofl.

;^)

steve


| > He says 'go there' and
| > he adds 'please' to make it polite - but it's actually a <command>.
|
| You are right. I am wrong. All the English teachers I've ever had were
| wrong. They haven't been to an English speaking country at all. The've
never
| studied English. They not even went to school. That's why they were wrong
| when teaching me the meaning of words like "please" and "thank you".
|
|
| You are ridiculous.
|
| For me: EOT
|
| --
| Armin
|
| | http://learn.to/quote
| http://www.plig.net/nnq/nquote.html
|
 
Hi Fergus,
We (Herfried, Armin, you and I) are speaking a kind of Germanic language

German is not complete a Germanic language like Dutch and the Scandinavian
languages are, either is English, but in the way you told it, Bitte,
Alstublieft and Please are as far as I know without difference. It is your
face an your sound that makes the difference.

But that we don't have to tell each other.

Cor
 
* "Cor said:
We (Herfried, Armin, you and I) are speaking a kind of Germanic language

German is not complete a Germanic language like Dutch and the Scandinavian
languages are, either is English, but in the way you told it, Bitte,
Alstublieft and Please are as far as I know without difference. It is your
face an your sound that makes the difference.

But that we don't have to tell each other.
Click to expand...

IMO German "slang" is _very_ similar to the English language.
 
Back
Top