Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
seeker62
Starting Member
40 Posts |
 Posted - 2013-09-19 : 10:21:03
|
| I have a stored procedure that has many select statements in it. I want to call and execute the procedure in my c# code and put data returned by each select statement into separate grids. I could just write the select into the c# code and then execute as a dataset making that dataset the datasource for whatever grid but that defeats the purpose of stored procedures. Can I capture the data returned by each select in the stored procedure and have it put into a grid when the c# code is ran. Thanks in advance. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-19 : 10:28:57
|
| If you are using classic ADO.Net, you can use NextResult method of SqlDataReader to move to the next result set. Look for the section "Retrieving Multiple Result Sets using NextResult" here: http://msdn.microsoft.com/en-us/library/haa3afyz.aspx |
 |
|
|
seeker62
Starting Member
40 Posts |
Posted - 2013-09-19 : 15:37:58
|
| Thanks for your reply. I now am using the following code: while (reader.Read()) { ds.Load(reader, LoadOption.OverwriteChanges,ds.Tables[0], ds.Tables[0]); if (resultset == 1) ugLookUpResults1.DataSource = ds.Tables[0]; else if (resultset == 2) //ugLookUpResults.DataSource = ds.Tables[0]; ugLookUpResults2.DataSource = ds.Tables[0]; resultset++; reader.NextResult();when it tries to go to nextResult it says that the reader is closed. Where in the while loop does it close and how can I keep it open until through. reader is IDataReader not SqlDataReader as in the example you referred to. |
|
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-09-19 : 15:53:18
|
| If you are using Load method you should not use the NextResult method. The Load method automatically advances to the next result set. See here: http://msdn.microsoft.com/en-us/library/skef0k7a.aspxAfter each result set, the Load method moves on to the next result set within the reader, until there are no more result setsIDataReader is fine. Load accepts anything that implements IDataReader |
|
|
|
seeker62
Starting Member
40 Posts |
Posted - 2013-09-19 : 17:56:45
|
| thanks again for quick response. The load does not seem to place data in the tables in the array section. following is code: ds.Tables.Add("Table1"); ds.Tables.Add("Table2"); ds.Tables.Add("Table3"); . . . . . . . . ds.Load(reader, LoadOption.OverwriteChanges, ds.Tables[1], ds.Tables[2], ds.Tables[3]);I then make each table a datasource to a grid that shows on screen. but the show empty.ugLookUpResults1.DataSource = ds.Tables[1];ugLookUpResults2.DataSource = ds.Tables[2]; |
|
|
|
|
|
|
stored procedure with many select