returning dataset from stored procedure

  • Thread starter Thread starter Mike P
  • Start date Start date
M

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
 
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?
Click to expand...

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
 
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
 
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
 
Back
Top