I know this post is several months old now, but I thought I put this here for someone seaching for this error: "Unexpected number of DataSet classes found in user validation file"
In my case the solution was to delete the auto-generated files for the typed dataset (everything except the .xsd file). When I rebuilt my solution the error was gone.
It is possible to use Oracle stored procedures from the Visual Studio Dataset designer. It took quite awhile, but here is what I had to do to get it to work:
Make sure you are using the Oracle ODP.NET data source. You need to download this from the Oracle site. It's basically the Oracle 11g client and includes a bunch of stuff for working with .net and Visual Studio.
Set up a Data Connection in the Server Explorer to test your connection. Make sure to use Oracle ODP.NET for the datasource, older version of the Oracle Client won't work with stored procs.
If you're working with stored procs that do SELECTS and return resultsets, you'll need to use a ref cursor as an output parameter of the stored procedure.
I haven't been able to get it to return single values however, only resultsets, still trying to hash that out.
Here's an extract of how I'm getting it to return a single value.
HTH
Dim ConStr As String = My.Settings.ConnectionString
Dim connection As OracleClient.OracleConnection = New OracleClient.OracleConnection(ConStr)
Dim cmd As OracleCommand = New OracleCommand("DETAILED_DEFINITIONS.CountDetailedDef", connection)
cmd.CommandType = CommandType.StoredProcedure
' allNodeNb
Dim p_allNodeNb As OracleParameter = New OracleParameter("allNodeNb", allNodeNb)
p_allNodeNb.DbType = DbType.Int32
p_allNodeNb.Direction = ParameterDirection.Input
cmd.Parameters.Add(p_allNodeNb)
' create return parameter object
Dim p_totalCount As OracleParameter = New OracleParameter("totalCount", OracleType.VarChar, 20)
p_totalCount.Direction = ParameterDirection.Output
cmd.Parameters.Add(p_totalCount)
Try
connection.Open()
cmd.ExecuteOracleScalar()
Catch ex As Exception
Finally
If (connection IsNot Nothing) Then
connection.Close()
End If
End Try
Return p_totalCount.Value
I've seen code similar to what you posted, and I think that would work fine if I wasn't using generated code (i.e. Visual Studio datasets and tableAdapters).
From what I've been able to tell so far, the generated code has a problem with any return values other than integers. It always raises a casting exception; something like "casting OracleString into System.string". I've posted this issue to the Oracle ODP.net
forums as well, but haven't gotten a solution yet.
Has anyone else had any success getting single return values from an Oracle stored procedure using TableAdapters?
The code I posted before it is in the code-behind file of my dataset.
Try for instance, right-clicking on a tableAdapter, and you'll see View Code, which should show you the partial class where you could insert the call to your StoProcedures.
Imports System.Data.OracleClient
Imports Oracle.DataAccess
Partial Class Definitions
Partial Class DEFINITIONSDataTable
Private Sub DEFINITIONSDataTable_DEFINITIONSRowChanging(ByVal sender As System.Object, ByVal e As DEFINITIONSRowChangeEvent) Handles Me.DEFINITIONSRowChanging
End Sub
End Class
Public Shared Function CountDetailedDef(ByVal nodeType As String, _
ByVal word As String, _
ByVal langNb As Integer, _
ByVal nodeNb As Integer, _
ByVal glsTypeNb As Integer, _
ByVal allNodeType As String, _
ByVal allLangNb As Integer, _
ByVal allNodeNb As Integer) As Integer
Dim ConStr As String = My.Settings.ConnectionString
Dim connection As OracleClient.OracleConnection = New OracleClient.OracleConnection(ConStr)
Dim cmd As OracleCommand = New OracleCommand("DETAILED_DEFINITIONS.CountDetailedDef", connection)
cmd.CommandType = CommandType.StoredProcedure
.
.
.
End function
End Class
It should works.... and you could explicity cast the returned types if needed !!
How does your method in the partial class get called? I understand that you can use a partial class to write custom data access code that is in a separate file so that if Visual Studio regenerates code from the Dataset designer the custom code won't get
overwritten. So your suggestion seems to be a good one, I just can't seem to put it all together.
I'm using a business logical layer, and from there I'm calling the methids through the dataset. (As you saw, I'm writting my calls to StoProc from the dataset class).
If you want to call them directly from an ObjectDataSource or other object, I'd suggest you to write your methods in the TableAdapter's code, it make them available as any other of its methods... something like:
Namespace DEFINITIONSTableAdapters
Partial Class DEFINITIONS_DETAILSTableAdapter
Public Function Test() As Integer
Return 1
End Function
End Class
End Namespace
After that add stored function to QueriesTableAdapter using this connection. In function`s Parameters collection automatically generates 3 parameters: RETURN_VALUE1, RETURN_VALUE and Input parameter. Just delete RETURN_VALUE1 parameter and rename RETURN_VALUE
to RETURN_VALUE1 and place it first, because function use first parameter as return parameter.
Now you can use stored function as simple SelectQuery.
This sounds like the right thread I was looking for, but didn't get the answer I was expecting, so I'll post this here in case someone was searching for the answer to this question.
Here is how I did this with the XSD Designer.
With a "Children" table, and adding a query to list me all children for a parent, here is the sample proc which is in a package.
PROCEDURE ListAllChildrenByParentId (
cur_CHILDREN OUT T_CURSOR,
p_ParentID IN NUMBER
);
The object call expects an object for the placeholder where the cursor is, so I just created some stupid object to be there for it.
object stupid;
this.ChildrenTableAdapter.FillByParentId(this.myDS.ChildrenTable, out stupid, _parentId);
batman++
Member
373 Points
70 Posts
Re: TableAdapter/DataSet calling Stored Procedure
Jul 31, 2007 03:44 PM|LINK
I know this post is several months old now, but I thought I put this here for someone seaching for this error: "Unexpected number of DataSet classes found in user validation file" In my case the solution was to delete the auto-generated files for the typed dataset (everything except the .xsd file). When I rebuilt my solution the error was gone.
elblanke
Member
6 Points
3 Posts
Re: TableAdapter/DataSet calling Stored Procedure
Apr 15, 2008 06:58 PM|LINK
It is possible to use Oracle stored procedures from the Visual Studio Dataset designer. It took quite awhile, but here is what I had to do to get it to work:
- Make sure you are using the Oracle ODP.NET data source. You need to download this from the Oracle site. It's basically the Oracle 11g client and includes a bunch of stuff for working with .net and Visual Studio.
- Set up a Data Connection in the Server Explorer to test your connection. Make sure to use Oracle ODP.NET for the datasource, older version of the Oracle Client won't work with stored procs.
- If you're working with stored procs that do SELECTS and return resultsets, you'll need to use a ref cursor as an output parameter of the stored procedure.
I haven't been able to get it to return single values however, only resultsets, still trying to hash that out.Oracle tableadapter Oracle Package ref cursor
Manotas
Member
540 Points
131 Posts
Re: TableAdapter/DataSet calling Stored Procedure
Apr 16, 2008 08:11 AM|LINK
Here's an extract of how I'm getting it to return a single value.
HTH
Dim ConStr As String = My.Settings.ConnectionString Dim connection As OracleClient.OracleConnection = New OracleClient.OracleConnection(ConStr) Dim cmd As OracleCommand = New OracleCommand("DETAILED_DEFINITIONS.CountDetailedDef", connection) cmd.CommandType = CommandType.StoredProcedure ' allNodeNb Dim p_allNodeNb As OracleParameter = New OracleParameter("allNodeNb", allNodeNb) p_allNodeNb.DbType = DbType.Int32 p_allNodeNb.Direction = ParameterDirection.Input cmd.Parameters.Add(p_allNodeNb) ' create return parameter object Dim p_totalCount As OracleParameter = New OracleParameter("totalCount", OracleType.VarChar, 20) p_totalCount.Direction = ParameterDirection.Output cmd.Parameters.Add(p_totalCount) Try connection.Open() cmd.ExecuteOracleScalar() Catch ex As Exception Finally If (connection IsNot Nothing) Then connection.Close() End If End Try Return p_totalCount.Valueelblanke
Member
6 Points
3 Posts
Re: TableAdapter/DataSet calling Stored Procedure
Apr 16, 2008 02:07 PM|LINK
Manotas,
I've seen code similar to what you posted, and I think that would work fine if I wasn't using generated code (i.e. Visual Studio datasets and tableAdapters).
From what I've been able to tell so far, the generated code has a problem with any return values other than integers. It always raises a casting exception; something like "casting OracleString into System.string". I've posted this issue to the Oracle ODP.net forums as well, but haven't gotten a solution yet.
Has anyone else had any success getting single return values from an Oracle stored procedure using TableAdapters?
Manotas
Member
540 Points
131 Posts
Re: TableAdapter/DataSet calling Stored Procedure
Apr 17, 2008 08:08 AM|LINK
Dude,
Me too I'm using Datasets and TabeAdapters.
The code I posted before it is in the code-behind file of my dataset.
Try for instance, right-clicking on a tableAdapter, and you'll see View Code, which should show you the partial class where you could insert the call to your StoProcedures.
Imports System.Data.OracleClient Imports Oracle.DataAccess Partial Class Definitions Partial Class DEFINITIONSDataTable Private Sub DEFINITIONSDataTable_DEFINITIONSRowChanging(ByVal sender As System.Object, ByVal e As DEFINITIONSRowChangeEvent) Handles Me.DEFINITIONSRowChanging End Sub End Class Public Shared Function CountDetailedDef(ByVal nodeType As String, _ ByVal word As String, _ ByVal langNb As Integer, _ ByVal nodeNb As Integer, _ ByVal glsTypeNb As Integer, _ ByVal allNodeType As String, _ ByVal allLangNb As Integer, _ ByVal allNodeNb As Integer) As Integer Dim ConStr As String = My.Settings.ConnectionString Dim connection As OracleClient.OracleConnection = New OracleClient.OracleConnection(ConStr) Dim cmd As OracleCommand = New OracleCommand("DETAILED_DEFINITIONS.CountDetailedDef", connection) cmd.CommandType = CommandType.StoredProcedure . . . End function End ClassIt should works.... and you could explicity cast the returned types if needed !!
Cheers
elblanke
Member
6 Points
3 Posts
Re: TableAdapter/DataSet calling Stored Procedure
Apr 17, 2008 02:52 PM|LINK
How does your method in the partial class get called? I understand that you can use a partial class to write custom data access code that is in a separate file so that if Visual Studio regenerates code from the Dataset designer the custom code won't get overwritten. So your suggestion seems to be a good one, I just can't seem to put it all together.
Thanks for your help.
Manotas
Member
540 Points
131 Posts
Re: TableAdapter/DataSet calling Stored Procedure
Apr 18, 2008 08:49 AM|LINK
I'm using a business logical layer, and from there I'm calling the methids through the dataset. (As you saw, I'm writting my calls to StoProc from the dataset class).
If you want to call them directly from an ObjectDataSource or other object, I'd suggest you to write your methods in the TableAdapter's code, it make them available as any other of its methods... something like:
Let me know if that answer your question !!
swingnchad
Member
2 Points
1 Post
Re: TableAdapter/DataSet calling Stored Procedure
Nov 04, 2008 09:30 PM|LINK
Thanks batman! Deleting the auto generated files worked. Saved me a lot of time.
Alsa
Member
4 Points
2 Posts
Re: TableAdapter/DataSet calling Stored Procedure
Jul 29, 2009 02:24 PM|LINK
Hi all!
Maybe it`s late but I found easier solution.
Specially for oracle stored procedures and functions i`m using OleDb provider. ConnectingString:
<add name="OleDb" connectionString="Provider=MSDAORA;Data Source=DATA_SOURCE;Password=PASSWORD;User ID=USER_ID" providerName="System.Data.OleDb"/>
After that add stored function to QueriesTableAdapter using this connection. In function`s Parameters collection automatically generates 3 parameters: RETURN_VALUE1, RETURN_VALUE and Input parameter. Just delete RETURN_VALUE1 parameter and rename RETURN_VALUE to RETURN_VALUE1 and place it first, because function use first parameter as return parameter.
Now you can use stored function as simple SelectQuery.
Hope it helps!
devThis
Member
24 Points
5 Posts
Re: TableAdapter/DataSet calling Stored Procedure
Sep 09, 2009 10:41 PM|LINK
This sounds like the right thread I was looking for, but didn't get the answer I was expecting, so I'll post this here in case someone was searching for the answer to this question.
Here is how I did this with the XSD Designer.
With a "Children" table, and adding a query to list me all children for a parent, here is the sample proc which is in a package.
PROCEDURE ListAllChildrenByParentId (
cur_CHILDREN OUT T_CURSOR,
p_ParentID IN NUMBER
);
The object call expects an object for the placeholder where the cursor is, so I just created some stupid object to be there for it.
object stupid;
this.ChildrenTableAdapter.FillByParentId(this.myDS.ChildrenTable, out stupid, _parentId);
Hope this helps.