By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,786 Members | 1,142 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,786 IT Pros & Developers. It's quick & easy.

returning dataset from stored procedure

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
"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

P: n/a
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

P: n/a
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.