VS2010 Convert datasets to ODP.net

Question

We have a solution in VS2010. In some projects we have a dataset. All datasets contains more then 1 DataTables and tableAdapters. The solution was developed using SYSTEM.DATA.ORACLECLIENT. Now we want to convert to ODP.net and we have to use ORACLE.DATAACCESS.CLIENT. We did on forums how to do this. So we have already done : - References "SYSTEM.DATA.ORACLECLIENT" on project levels are removed. - References "ORACLE.DATAACCESS" are added. - All "Using SYSTEM.DATA.ORACLECLIENT;" are changed into "using Oracle.DataAccess.Client;" - In some connectionstrings we removed "Unicode=true" - We did NOT do anything yet with adding BindingName = true in the OracleCommand - We did NOT do anything yet with changing dbtypes like VarChar, VarNumeric, ...

I get no build errors but when running the solution I get an error about "Value does not fall within the expected range".

Now I have the feeling that my datasets are not 100% converted to ODP.net. So I need some help.

How to check or indeed convert datasets to OPD.net (Oracle.DataAccess.Client) ? What about properties like "Hierarchical Update", "DeleteDBDirectMethods", ... A dataset is represented by 4 files : .cs, Designer.cs, .xsc and .xss. The code in these file seems ok : I mean I see Oracle.DataAccess.Client.

Who can help me ?

Answer 1

I followed the advice given in this post on the Oracle forums:

I was trying to convert from using the System.Data.OracleClient adapter to use ODP.NET so I just went directly into the dataset xsd and changed the provider to point to Oracle.DataAccess.Client and it regenerated the code with the correct provider.

This worked for me - open the XSD using the XML (Text) editor, then find this line:

<Connection ... Provider="System.Data.OracleClient">

Change it to this:

<Connection ... Provider="Oracle.ManagedDataAccess.Client">

When you rebuild, all code in the .Designer.cs file will be updated to use Oracle.ManagedDataAccess.

Answer 2

We came across this issue too after converting our Data Sets (XSD's) from using System.Data.OracleClient to Oracle.DataAccess.Client.

The issue was that the Data Set designer was still using "Number" as the data type for the ProviderType. Oracle does not support the "Number" data type. We had to change the ProviderType in each of our Data Sets for each Table Adapter and each collection of Parameters for every query in the Table Adapter. We used "Decimal" for the ProviderType, and it seems to work fine. Don't forget to check the auto-generated "Select", "Update", and "Delete" queries too.

We haven't changed the BindByName = true either.

Hopefully this will help.