I am able to get data with an TableAdapter using OracleClient (ODP.NET) and typing SQL statements.
Now I want to just call a stored procedure from the database instead of calling a command like "select * from table". But I cant choose "Use existing stored procedures", just "Use SQL statements" in the TableAdapter Configuration Wizard. So how to call
a procedure?
I am able to get data with an TableAdapter using OracleClient (ODP.NET) and typing SQL statements.
Now I want to just call a stored procedure from the database instead of calling a command like "select * from table". But I cant choose "Use existing stored procedures", just "Use SQL statements" in the TableAdapter Configuration Wizard. So how to call
a procedure?
How did you create the TableAdapter with ODP.NET? ODP.NET is not selectable in Server Explorer, and you can't drag/drop from Oracle Explorer onto a dataset. I can't figure this out for the life of me.
I have also been trying to build a TableAdapter/Data Table(typed DataSets) using Oracle stored procedured but can't seem to figure it out. The articles mentioned above do not mention anything about Table Adapters.
You have a DataSet with various DataTables and associated TableAdapters that match the tables in your Oracle database. If you look at the code behind you will see that the in the connection strings setting you have
Provider="System.Data.OracleClient". This is where I get confused.... why can't you just change the provider
to Oracle.DataAccess.Client? I have several ideas why but no clear answer to that question. If you know this can be done please let me know how.
Problem:
Whe you try to modify or create a new TableAdapter you want to add a stored procedure through the wizard. When the wizard comes up the option, "Use existing stored procedure", is grayed out.
Solution:
Add a query to your TableAdapter and then set the appropriate parameters by hand.
Parameters:
<div>CommandText- name of stored procedure in database</div>
<div>CommandType- stored procedure</div>
<div>ExecuteMode- Nonquery or scalar</div>
<div>Name- name of function in dataset</div>
<div>Parameters- add parameters that match your parameters in the stored procedure in your database (this is the hard part)</div>
I followed exactly as mathgeek1729 said, but even then couldn't use my stored procuedures... I'd like to call a procedure which receives some parameters and returns a cursor.
1) Changing the ExecuteMode to Nonquery or scalar tries to force the returned value to an integer, and in execution (preview data) nothing is returned after launched the procedure.
2) I tried leaving the ExecuteMode as Reader, and everything goes fine during the testing with the preview data, but then when I went to my business layer I realized I wasn't able to call to none of my queries nor procedure from the tableAdapter, and worse,
aftyer compiling and getting the following error message: Custom tool error: Unexpected number of DataSet classes found in user validation file.
Do not manually modify the class-structure of this file. This for my XSD file.
nodh
Member
40 Points
8 Posts
TableAdapter/DataSet calling Stored Procedure
Jul 25, 2006 10:59 AM|LINK
Hi!
I am able to get data with an TableAdapter using OracleClient (ODP.NET) and typing SQL statements.
Now I want to just call a stored procedure from the database instead of calling a command like "select * from table". But I cant choose "Use existing stored procedures", just "Use SQL statements" in the TableAdapter Configuration Wizard. So how to call a procedure?
Caddre
All-Star
26581 Points
5308 Posts
Re: TableAdapter/DataSet calling Stored Procedure
Jul 25, 2006 12:29 PM|LINK
Try the link below for code sample from Oracle. Hope this helps.
http://www.oracle.com/technology/pub/articles/mastering_dotnet_oracle/williams_sps.html
Gift Peddie
nodh
Member
40 Points
8 Posts
Re: TableAdapter/DataSet calling Stored Procedure
Jul 25, 2006 12:39 PM|LINK
Um, wait, this is not what I want to do!?
I want just to call a Stored Procedure in the database from Visual Studio when desinging/creating a Typed DataSet.
Caddre
All-Star
26581 Points
5308 Posts
Re: TableAdapter/DataSet calling Stored Procedure
Jul 25, 2006 12:51 PM|LINK
You could try using RefCursor because I don't think you can do it exactly as you want. That is covered below. Hope this helps.
http://www.oracle.com/technology/pub/articles/mastering_dotnet_oracle/williams_refcursors.html
Gift Peddie
VinnySem
Member
20 Points
4 Posts
Re: TableAdapter/DataSet calling Stored Procedure
Sep 15, 2006 07:19 PM|LINK
Hi!
I am able to get data with an TableAdapter using OracleClient (ODP.NET) and typing SQL statements.
Now I want to just call a stored procedure from the database instead of calling a command like "select * from table". But I cant choose "Use existing stored procedures", just "Use SQL statements" in the TableAdapter Configuration Wizard. So how to call a procedure?
How did you create the TableAdapter with ODP.NET? ODP.NET is not selectable in Server Explorer, and you can't drag/drop from Oracle Explorer onto a dataset. I can't figure this out for the life of me.
Caddre
All-Star
26581 Points
5308 Posts
Re: TableAdapter/DataSet calling Stored Procedure
Sep 15, 2006 07:37 PM|LINK
Try the link below for details about ODP.NET. Hope this helps.
http://www.oracle.com/technology/tech/windows/odpnet/index.html
http://cshay.blogspot.com/
Gift Peddie
alajoie
Member
40 Points
8 Posts
Re: TableAdapter/DataSet calling Stored Procedure
Sep 20, 2006 01:18 PM|LINK
Has anyone figure this out.
I have also been trying to build a TableAdapter/Data Table(typed DataSets) using Oracle stored procedured but can't seem to figure it out. The articles mentioned above do not mention anything about Table Adapters.
Help!!
Caddre
All-Star
26581 Points
5308 Posts
Re: TableAdapter/DataSet calling Stored Procedure
Sep 20, 2006 10:58 PM|LINK
I have not seen TableAdapter based code either but I found code using stored procs on the Mono Project site. Hope this helps.
http://www.mono-project.com/Oracle
Gift Peddie
mathgeek1729
Member
10 Points
2 Posts
Re: TableAdapter/DataSet calling Stored Procedure
Sep 28, 2006 05:00 PM|LINK
Assumptions:
You have a DataSet with various DataTables and associated TableAdapters that match the tables in your Oracle database. If you look at the code behind you will see that the in the connection strings setting you have Provider="System.Data.OracleClient". This is where I get confused.... why can't you just change the provider to Oracle.DataAccess.Client? I have several ideas why but no clear answer to that question. If you know this can be done please let me know how.
Problem:
Whe you try to modify or create a new TableAdapter you want to add a stored procedure through the wizard. When the wizard comes up the option, "Use existing stored procedure", is grayed out.
Solution:
Add a query to your TableAdapter and then set the appropriate parameters by hand.
Parameters:
Manotas
Member
540 Points
131 Posts
Re: TableAdapter/DataSet calling Stored Procedure
Mar 07, 2007 05:29 PM|LINK
Hello,
Hope you could help me!!!
I followed exactly as mathgeek1729 said, but even then couldn't use my stored procuedures... I'd like to call a procedure which receives some parameters and returns a cursor.
1) Changing the ExecuteMode to Nonquery or scalar tries to force the returned value to an integer, and in execution (preview data) nothing is returned after launched the procedure.
2) I tried leaving the ExecuteMode as Reader, and everything goes fine during the testing with the preview data, but then when I went to my business layer I realized I wasn't able to call to none of my queries nor procedure from the tableAdapter, and worse, aftyer compiling and getting the following error message: Custom tool error: Unexpected number of DataSet classes found in user validation file.
Do not manually modify the class-structure of this file. This for my XSD file.
Did you find another walkaround???
Oracle tableadapter