stored procedure returning DataSets.

  • Thread starter Thread starter Ashish
  • Start date Start date
A

Ashish

hi All,

I have an application which queries the database multiple times and then
creates a dataset from the the datatables returned from each query, i
wanna change this behavior and write a stored procedure which would
return a dataset with all the tables in it, is it possible to return
multiple tables from the stored procedure ??
TIA
-ashish
 
The stored procedure only returns data, not a dataset. The Fill method is
taking that data and filling a table or a DataSet.

You can call multiple SELECT statements in a proc, but 1) I don't think you
can map them back to specific tables in a DataSet 2) It's not really
intended to work like that.

Think about it for a second...You have query1 Select Name, Date from MyTable
Then in Query2 you Have Select User, Password, Id.

How would the dataadapter write this logic back and how would you tell the
dataset that for the first query stick it in Table Profile and in the second
query, stick it into Table UserInfo.

Now, if both tables had the same structure you could make one big result
set, but it's not going to come back in different tables. You can do joins
and the like, server side, but you will be causing yourself tons of
headaches and I don't think joins are what you are after anyway.

So if the goal is to have a collection of tables in a DataSet, you are going
to need to fill them individually.

Just out of curiousity, why don't you want to fill them individuall?

Bill
 
William said:
The stored procedure only returns data, not a dataset. The Fill method is
taking that data and filling a table or a DataSet.

You can call multiple SELECT statements in a proc, but 1) I don't think you
can map them back to specific tables in a DataSet 2) It's not really
intended to work like that.

Think about it for a second...You have query1 Select Name, Date from MyTable
Then in Query2 you Have Select User, Password, Id.

How would the dataadapter write this logic back and how would you tell the
dataset that for the first query stick it in Table Profile and in the second
query, stick it into Table UserInfo.

Now, if both tables had the same structure you could make one big result
set, but it's not going to come back in different tables. You can do joins
and the like, server side, but you will be causing yourself tons of
headaches and I don't think joins are what you are after anyway.

So if the goal is to have a collection of tables in a DataSet, you are going
to need to fill them individually.

Just out of curiousity, why don't you want to fill them individuall?

Bill
Click to expand...
i just wanted to make as less roundtrips to the database as possible, i
know in the older ado we could have something like nextrecordset, i.e we
could get a linked list of recordsets back from stored procedures ( i
may be wrong cuz i really havent tried that, but somewhere i think i
have read that), we are optimizing the way the data is accessed in the
DAL of the application, and i was to create stored procedure i will have
to create 12-13 of them with no real multiple use except the one iam
rtying to, but i guess if its not possible, then i will have to make
12-13 calls to the database , any suggestions if i could create a
dataset like that, optimizing dataccess and improving the response time
is the focus here
thanks
-ashish
 
Ok, let me ammend my last statement then...

The SqlDataReader (or whatever datareader) does have a .NextResult method,
and you can use multiple selects with it. You could walk through the
readers and create Datatables individually from them, thereby populating the
Dataset with multiple tables and doing it in one trip. Behind the scenese,
the DataAdapter uses a DataReader anyway to populate the DataTable, and
while it works faster than doing it yourself, I've not been able to discern
any difference. Although this article is mainly on Parameters, you'll find
a lot in it dealing with your situation.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/gazoutas.asp

HTH,
Bill
 
It is possible to return multiple 'resultsets' from a sproc.

Like this:

Select * from TableA;
Select * from TableB;
Select * from TableC
 
William said:
Ok, let me ammend my last statement then...

The SqlDataReader (or whatever datareader) does have a .NextResult method,
and you can use multiple selects with it. You could walk through the
readers and create Datatables individually from them, thereby populating the
Dataset with multiple tables and doing it in one trip. Behind the scenese,
the DataAdapter uses a DataReader anyway to populate the DataTable, and
while it works faster than doing it yourself, I've not been able to discern
any difference. Although this article is mainly on Parameters, you'll find
a lot in it dealing with your situation.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/gazoutas.asp

HTH,
Bill
Click to expand...
thanks bill, thats what i was looking for
also another question
can i do the same with msaccess also ?, i know ms access does not
support stored procedures but i beleive that we can have parameterized
queries and run them from ado.net
thanks
-ashish
 
I disagree. Although there is room for error if anyone changes the
TableMappings or the order in which the selects run in the sproc, I think
this is a safe and very effective thing to do. We do it quite a bit and it
does reduce the db trips.
 
maybe this helps

DA = New SqlClient.SqlDataAdapter()

DA.TableMappings.Add("LangTab1", "LangKrz")

DA.TableMappings.Add("LangTab2", "LangConst")

DA.TableMappings.Add("LangTab3", "Struktur")

CMD = New SqlClient.SqlCommand("execute StrukturProc 5,5,36", Conn)

DA.SelectCommand = CMD

Conn.Open()

DA.Fill(DSF, "LangTab")



the StrukturProc returs many Tables and the are named to the table mappings
name if the dataset is typed dataset it works as well



hope it helps
 
You are right, I am wrong, got confused on that..when I had the problem I
was thinking of, it was the update logic that was the problem, but I checked
the project again and it did return multiple dataTables....Sorry about that.

TableMappings will definitely do it.
 
Using a DataAdapter to fetch the multiple resultsets should
not be a problem. The DataAdapter is not concerned with whether
the resultsets come from a stored procedure call or a batch of
SELECT queries. Using the DataAdapter's TableMappings
collection, you can map the resultsets to specific DataTables.

In order to submit pending changes, you'd need to configure
a separate DataAdapter per DataTable.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2003 Microsoft Corporation. All rights reserved.
 
Anton said:
maybe this helps

DA = New SqlClient.SqlDataAdapter()

DA.TableMappings.Add("LangTab1", "LangKrz")

DA.TableMappings.Add("LangTab2", "LangConst")

DA.TableMappings.Add("LangTab3", "Struktur")

CMD = New SqlClient.SqlCommand("execute StrukturProc 5,5,36", Conn)

DA.SelectCommand = CMD

Conn.Open()

DA.Fill(DSF, "LangTab")



the StrukturProc returs many Tables and the are named to the table mappings
name if the dataset is typed dataset it works as well



hope it helps
Click to expand...
thanks guys , i will try it out, i also need to support msaccess
database, iam thinking may be i can do it via parameterized queries ,and
using them as stored procedures, is that possible, would the syntax have
to take care of the database ?
TIA
-ashish
 
Right. That's the good news. The bad news is that while this approach
reduces round trips (a good idea), it constructs DataTables that the
DataAdapter does not know how to update. Yes, the DA can update one of these
root tables, but without some pretty interesting UPDATE logic in the
UpdateCommand, it can't change the other tables fetched. This is why we
(generally) build a separate DA for each rowset drawn from a different base
table--IF you want to be able to use the DA to update it.

hth

--
____________________________________
Bill Vaughn
MVP, hRD
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
 
William (Bill) Vaughn said:
Right. That's the good news. The bad news is that while this approach
reduces round trips (a good idea), it constructs DataTables that the
DataAdapter does not know how to update. Yes, the DA can update one of these
root tables, but without some pretty interesting UPDATE logic in the
UpdateCommand, it can't change the other tables fetched. This is why we
(generally) build a separate DA for each rowset drawn from a different base
table--IF you want to be able to use the DA to update it.
Click to expand...

Thats basically correct. But it is not that difficult to build your own
update commands and then it works fine again. But do not use the
commandbuilder to do so, because the commandbuilder is causing a roundtrip
as well.
 
Back
Top