469,282 Members | 2,125 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,282 developers. It's quick & easy.

returning dataset from stored procedure

Is it possible to return a dataset from a stored procedure, or would you
need to write the SQL in your .cs file to return the dataset?
Any assistance would be really appreciated.
Cheers,

Mike

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 16 '05 #1
3 51244
"Mike P" <mr*@telcoelectronics.co.uk> wrote in message
news:ey****************@tk2msftngp13.phx.gbl...

Mike,
Is it possible to return a dataset from a stored procedure, or would you
need to write the SQL in your .cs file to return the dataset?


It's actually the same thing! Supposing you have a stored proc like:

CREATE PROC uspTestProc
AS
SELECT * FROM tblTest
GO

In your C# code, you might have a string variable called strSQL, so the
following two statements are functionally identical:

string strSQL = "EXEC uspTestProc";
string strSQL = "SELECT * from tblTest";

Of course, there are many good reasons for using stored procedures instead
of in-line SQL. Notably, stored procedures are pre-compiled by the server
engine so almost always execute more quickly, and they also go a long way to
help preventing SQL injection attacks.

Mark
Nov 16 '05 #2
You can't return an actual ADO.NET DataSet "object" from a stored procedure
but that is probably not what you're asking.

Yes you can return data from a stored procedure and have it go straight to a
DataSet. SQL Server and Oracle both support this. IMHO I think working
with SQL Server is easier.

SQL Server example -----
Create your stored procedure:
create procedure dbo.MyStoredProcedure
as
begin
select * from Orders
end
GO

Create your C# code:
SqlCommand myCommand = new SqlCommand("dbo.MyStoredProcedure");
myCommand.CommandType = CommandType.StoredProcedure;

// create SqlConnection
SqlConnection myConnection = new SqlConnection("your connection string
here");
myCommand.Connection = myConnection;
SqlDataAdapter da = new SqlDataAdapter(myCommand);

DataSet data = new DataSet();
da.Fill(data);

Oracle is a little different. If you need further help for Oracle search
the newsgroups for "ref cursor". I think I have an example out there
somewhere for returning data from an Oracle stored procedure.

HTH
--
C Addison Ritchie, MCSD.NET
Ritch Consulting, Inc.

"Mike P" <mr*@telcoelectronics.co.uk> wrote in message
news:ey****************@tk2msftngp13.phx.gbl...
Is it possible to return a dataset from a stored procedure, or would you
need to write the SQL in your .cs file to return the dataset?
Any assistance would be really appreciated.
Cheers,

Mike

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 16 '05 #3
Sorry about that...I was just getting confused with output parameters
thinking that they were the only way you can return data...as you can
probably tell, I don't have much experience when it comes to writing
stored procedures :)
Cheers,

Mike

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 16 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Michael | last post: by
2 posts views Thread by Dino L. | last post: by
1 post views Thread by Telemaco | last post: by
9 posts views Thread by Nikolay Petrov | last post: by
1 post views Thread by Mike P | last post: by
1 post views Thread by Crazy Cat | last post: by
9 posts views Thread by fniles | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.