Migration from System.Data.OracleClient do Devart.Data.Oracle

In previous blog posts I wrote about using Devart dotConnect for Oracle as an EntityFramework provider. Once you did this, you have Devart assemblies linked to your project, why not to use them for connecting to database with simple ADO.NET too? (if you have such a code too – I have a lot).

Microsoft has System.Data.OracleClient assembly to connect to Oracle, but it is discontinued already, it’s great time to migrate to something else. Devart is a good alternative. Basically you can have two type of code when using ado.net

1. Using ADO.NET directly (coding against classes OracleConnection etc.)
2. Using DB agnostic code that is connecting to DB using ADO.NET (coding against abstractions that a data framework is converting to particular Oracle or other DB specific classes). We use Spring Data Access, similar is with Enterprise Library Data Access Application Block and others.

My first thought was that this will be easy, I replace the OracleClient assembly with Devart.Data.Oracle and change the namespace and that’s it. Everything will work fine. The reality was a bit different, below are the issues I run into. 

1. If you are using ADO.NET directly, you need to change OracleType to OracleDbType and OracleType.DateTime to OracleDbType.Date.
2. There are differences in connection strings where Devart has more capabilities for configuration (like specify OracleHome etc. see Devart EF Provider). In connection string you need to change System.Data.OracleClient to Devart.Data.Oracle. Example of connection string is here:                     <add name="APDataConnection" connectionString="Data Source=*; User ID=*; Password=*;Home=oraclient11g_home1;Persist Security Info=True;Number Mappings = (NUMBER, 0, 38, System.Decimal);" providerName="Devart.Data.Oracle"/>
3. OracleClient has different default behavior when calling stored procedures than Devart.Data.Oracle. A Stored procedure can have defaults defined on it’s arguments. It means when this argument is not provided, the default is applied. When calling from OracleClient, you don’t need to provide the arguments that have defaults and it doesn’t matter in which order you pass the arguments to stored procedure (they are passed by default by name). With devart the arguments are by default passed by position not by name. This means you need to pass each argument to every procedure in the same order as the stored procedure expects it and you need to provide the arguments with defaults too from the ado.net code. If you omit the defaulted arguments you get a wrong number of arguments message, which is good. If you change the order of parameters you get totally messy error messages, or just wrong data which are binded to wrong variables. This could really bite. With missing defaults there is no workaround, you need to check all your stored procedures and check if something is missing or not. With the parameter passing order, Devart fortunately has a property on OracleCommand class PassParametersByName which you can set to true and the ordering problem passes away. Devart says, that it’s default setting is because of performance, OracleClient makes queries to database before running the stored procedure to get it’s specification for defaulted arguments, the same is with passing parameters with name, when the server is asked for the stored procedure specification for the argument names.
4. Devart has different data type mappings than oracleClient. OracleClient maps Oracle NUMBER data type to Decimal .NET type. Devart maps it to different data types depending on the precision of the NUMBER column (Number(1)-Number(9) to Int32, Number(10-20) to double, Number without size to Decimal). Again this is better for performance, but if you have legacy code which expects Decimal everywhere, than this is a problem. Devart allows you to specify in connection string custom data type mapping strategy, that solves this issue.                                 <add name="APDataConnection" connectionString="Data Source=*; User ID=*; Password=*;Home=oraclient11g_home1;Persist Security Info=True;Number Mappings = (NUMBER, 0, 38, System.Decimal);" providerName="Devart.Data.Oracle"/>
   Number Mappings here means that all Number types with size from 0 to 38 will be mapped to Decimal type.
   Devart data type mappings are here: http://www.devart.com/linqconnect/docs/oracle_datatypemapping.html
   System.Data.OracleClient mapping is here:
   http://msdn.microsoft.com/en-us/library/yk72thhd%28v=vs.110%29.aspx
   More on Devart connection string configuration possibilities here:
   http://www.devart.com/dotconnect/oracle/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleConnection~ConnectionString.html
5. Devart can return the number of affected rows only for INSERT, UPDATE and DELETE DML commands, in all the other cases it returns -1. We used these return values sometimes in our api to indicate whether the operation was successful or not. With these 3 DML commands it is ok. We have several usages of oracle’s MERGE command too, OracleClient can return the correct number of affected rows, devart returns -1. We changed our API where MERGE is used to not count on the return value.
6. All the above applies to using Devart directly with ADO.NET. With Spring Data we run into problem with the provider itself, it is not a supported provider by default in spring. Spring has an extension mechanism for providers, where you can register a new data provider. This is available only in higher versions of Spring.NET (1.3+). You can define an xml resource file in an assembly where you specify the provider and than register it in spring configuration. An example of provider definition is here: Devart Spring Provider Definition . Below is the registration of the provider:
   <spring>
   <context>
   <resource uri="config://spring/objects"/>
   </context>
   <parsers>
   <parser type="Spring.Data.Config.DatabaseNamespaceParser, Spring.Data"/>
   </parsers>
   <objects xmlns=“http://www.springframework.net“ xmlns:db="http://www.springframework.net/database">
   <db:additionalProviders resource="assembly://Aegon.Xing.Modules.Devart/Aegon.Xing.Modules.Devart/DevartSpringDataProviderDefinition.xml"/><objects>
7. When using Spring Data you don’t have access to OracleCommand type where you can set the PassParametersByName property. In the devart provider definition xml I used a custom class that derives from OracleCommand class and registering this custom class instead of Devart OracleCommand class. In this custom class I set the PassParametersByName property to true. This way every usage of Spring Data with Devart has this property turned on.
   Section from provider registration xml:
   <!--<constructor-arg name="commandType" value="Devart.Data.Oracle.OracleCommand, Devart.Data.Oracle, Version=8.2.103.0, Culture=neutral, PublicKeyToken=09af7300eec23701"/>-->
           <constructor-arg name="commandType"   value="Aegon.Xing.Modules.Devart.OracleCommandWithCustomInit, Aegon.Xing.Modules.Devart"/>
   
   
   The OracleCommandWithCustomInit Class looks like:
   public class OracleCommandWithCustomInit : OracleCommand
   {
            public OracleCommandWithCustomInit() : base()
            {
               PassParametersByName = true;
            }
   }

And we are at the end of issues. I had a bunch of integration tests, which were extremely valuable during the migration. Hope you have too. The migration was 2-3 times longer than I expected at the end (3 weeks – but doing lot of other things during these weeks too:)).

At the end we use Devart for all our data access to oracle (classic ado.net, spring.data, and entity framework).