473,238 Members | 1,940 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,238 software developers and data experts.

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 51670
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Michael | last post by:
This one's really got me. I have a VB.NET (version 1.1.4322) project that provides an easy way to execute stored procedures on a generic level. When I run the code on computer A (running SQL...
2
by: Dino L. | last post by:
How can I run stored procedure (MSSQL) ?
1
by: Telemaco | last post by:
Ho la necessità di inserire i risultati di una stored procedure in un dataset. Mi spiego meglio .... Ho costruito una store procedure che incapsula una query di lettura su un database sqlserver...
1
by: Peter Rilling | last post by:
To minimize database calls, I would like to have a single stored procedure return all the data necessary to display on my page. This information is related from several different tables and what I...
7
by: Peter D.C. | last post by:
Hi I want to update data hold in several textbox controls on an asp.net form. But it seems like it is the old textbox values that is "re-updates" through a stored procedure who updates a SQL...
9
by: Nikolay Petrov | last post by:
How to fill DataSet from stored procedure?
1
by: Mike P | last post by:
How do you return a dataset from a stored procedure so that you can iterate through the rows returned? Thanks in advance, Mike
1
by: Crazy Cat | last post by:
I created a non result returning query using the Dataset Designer that calls a stored procedure in Visual Basic .NET Professional 2005 and then saved the dataset file. I can see the query in the...
9
by: fniles | last post by:
I am using VB.NET 2003 and SQL2000 database. I have a stored procedure called "INSERT_INTO_MYTABLE" that accepts 1 parameter (varchar(10)) and returns the identity column value from that table....
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.