hi all
this is my 2nd post and it's in relation to how to make store procedures return an entire table to a application. i am using .NET as my application development platform.
I know how parameters can be passed in and out of store procedures, but this returns single value parameters.
when plain SQL select statements are executed against the database as nonqueries through the ADO/ADO.NET API, an entire recordset/dataset can be returned.
But when i put the select statements in a store procedure, how do i make the select statement return an entire recordset/dataset back to the calling application?
thanks for taking your time to read.
Cheers
jOriginally posted by nano_electronix
hi all
this is my 2nd post and it's in relation to how to make store procedures return an entire table to a application. i am using .NET as my application development platform.
I know how parameters can be passed in and out of store procedures, but this returns single value parameters.
when plain SQL select statements are executed against the database as nonqueries through the ADO/ADO.NET API, an entire recordset/dataset can be returned.
But when i put the select statements in a store procedure, how do i make the select statement return an entire recordset/dataset back to the calling application?
thanks for taking your time to read.
Cheers
j
Hi, maybe I did not understand your problem entirely, but a stored procedure returning a recordset is as simple as
create procedure P as select * from sales
go
exec p
go
Hope, this helps.|||but wat happens when there are multiple select statements
how do you retrieve the results of a store procedure that has multiple select statements which would return multiple tables, which means multiple recordsets in the case of asp
besides given the store procedure you shown there, how would you get it into a recordset/dataset using either asp or asp.net.
for example, in asp.net i make use of dataadapter to retrieve a table into a dataset (which can store multiple table) but if i use store procedure i am not sure how that can be done.
cheers
j|||i think i might know why you don't understand my problem
the sql command that you gave is correct and will return a table of results if it is executed within the query analyser.
wat i am talking about is how would i retrieve the table of results if i were to use the store procedure within an application, how would i retrieve those results into a recordset/dataset (ASP/ASP.NET)
when i want to execute a storeprocedure using ms .net, wat i have to do is use a oledbcommand and make it of type storeprocedure and then execute it as a nonquery, but i have no idea how the result may be returned to the application when the oledbcommand is executed as a nonquery. i've tried to execute the oledbcommand as a reader rather than a nonquery, but that gave me exception when i tried to read data off the datareader that is returned (i don't think that's the way to do it anyhow).
I am sure someone would have run across a time when an application need to use a store procedure to execute a batch of sql commands and at the same time returns results to the application as a table of data.
if i can't do this, the only way that i could achieve the same effect is by creating temporary table and then execute an extra select statement to retrieve the data from the temporary table, this would be quite wasteful.
Please help
J|||Hi
Executing SP which will return a record set within .Net is straight forward. By the way for SQL2000 you should use SQLClient name space functions instead of OleDB for performance reason more then anything.
Use the SQLCommand.ExecuteReader and set the commandtext to the stored procedure and parameters and commandtype = CommandType.StoredProcedure. I enclosed the example from MSDN below for SQL. If you do need OleDB then just change the Sql to OleDb.
Regards
Richard...
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");
SqlCommand salesCMD = new SqlCommand("SalesByCategory", nwindConn);
salesCMD.CommandType = CommandType.StoredProcedure;
SqlParameter myParm = salesCMD.Parameters.Add("@.CategoryName", SqlDbType.NVarChar, 15);
myParm.Value = "Beverages";
nwindConn.Open();
SqlDataReader myReader = salesCMD.ExecuteReader();
Console.WriteLine("{0}, {1}", myReader.GetName(0), myReader.GetName(1));
while (myReader.Read())
{
Console.WriteLine("{0}, ${1}", myReader.GetString(0), myReader.GetDecimal(1));
}
myReader.Close();
nwindConn.Close();|||I had the same problem about a month ago. I think what you're looking for is the datareader. Here's an example of how it works:
Dim connection As New SqlConnection()
Dim cmdSelect As SqlCommand
Dim reader As SqlDataReader
Dim Num As Integer
Num = 12
connection = SqlConnection1
cmdSelect = New SqlCommand("exec sp_getNum @.date = " & Num), connection)
If connection.State <> ConnectionState.Open Then connection.Open()
reader = cmdSelect.ExecuteReader
datalist.DataSource = reader
datalist.DataBind()
reader.Close()
connection.Close()
If you need more tables, just created more readers. Is that what you're looking for?|||Originally posted by nano_electronix
but wat happens when there are multiple select statements
how do you retrieve the results of a store procedure that has multiple select statements which would return multiple tables, which means multiple recordsets in the case of asp
j
I would guess you would want to have one select statement per sproc. and then create a recordset for each executed sproc?|||thanx guys
I'll try the datareader again, but i have tried it exactly the same way as the sample code above, but like i said it gave me exception, i'll give it another try and get back to you guys, hope it work.
Originally posted by nano_electronix
i've tried to execute the oledbcommand as a reader rather than a nonquery, but that gave me exception when i tried to read data off the datareader that is returned (i don't think that's the way to do it anyhow).
J|||hi guys
first of all thanx for very much for all your help. i will sure to come back to this forum for more help later.
i found out where the problem was, it was a datatype conversion problem that gave me the exception, it wasn't the datareader that gave me the exception, i didn't check the exception carefully.
i am using oledb over the managed sql components because i want to try to make this application crossplatform for all databases, i haven't had a chance to try oracle yet, but that's where i am heading.
cheers :)
j
Cemal wrote:
