returning dataset from stored procedure

[Mike P]

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

 

[Mark Rae]

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

 

[C Addison Ritchie]

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

 

[Mike P]

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