1
I am developing an application where the information will be persisted in two databases, one MS SQL Server and one Oracle.
I have no option to use both banks in the same DBMS, because the Oracle database is from another application and another server under which I have no control and the SQL server is our company’s standard DBMS.
The application will initially persist in a MS SQL Server database and at a certain point in the usage flow will need to persist some information (not necessarily the same) in the Oracle database.
It is an ASP.Net MVC 5 application with Entity Framework 6.1.3.
My architecture is divided into layers, following the following structure:
1. Presentation (Solution folder)
|-- Presentation.Web (Projeto Asp.net MVC 5)
2. Domain (Solution folder)
|-- Domain.SqlServer (Projeto class library)
|-- Domain.Oracle (Projeto class library)
3. Infraestructure (Solution folder)
|-- Crosscutting (Solution folder)
|-- Infraestructure.Crosscutting.IoC (Projeto class library)
|-- Data (Solution folder)
|-- Infraestructure.Data.SqlServer (Projeto class library)
|-- Infraestructure.Data.Oracle (Projeto class library)
All references between "assemblies" will be created by a dependency injection "container", probably using "simple injector".
My approach to the Sql Server database is "code first" with "Migrations". For the Oracle database I will create entity classes, entity configuration, context and etc manually although the database already exists.
Follow below as is the presentation layer web.config:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
<section name="Oracle.ManagedDataAccess.Client" type="OracleInternal.Common.ODPMSectionHandler, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</configSections>
<appSettings>
<add key="webpages:Version" value="3.0.0.0" />
<add key="webpages:Enabled" value="false" />
<add key="ClientValidationEnabled" value="true" />
<add key="UnobtrusiveJavaScriptEnabled" value="true" />
</appSettings>
<entityFramework>
<!--<defaultConnectionFactory type="Oracle.ManagedDataAccess.EntityFramework.OracleConnectionFactory, Oracle.ManagedDataAccess.EntityFramework" />-->
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework"/>
<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
<provider invariantName="Oracle.ManagedDataAccess.Client" type="Oracle.ManagedDataAccess.EntityFramework.EFOracleProviderServices, Oracle.ManagedDataAccess.EntityFramework, Version=6.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</providers>
</entityFramework>
<system.data>
<DbProviderFactories>
<remove invariant="Oracle.ManagedDataAccess.Client"/>
<add name="ODP.NET, Managed Driver" invariant="Oracle.ManagedDataAccess.Client" description="Oracle Data Provider for .NET, Managed Driver" type="Oracle.ManagedDataAccess.Client.OracleClientFactory, Oracle.ManagedDataAccess, Version=4.121.2.0, Culture=neutral, PublicKeyToken=89b483f429c47342"/>
</DbProviderFactories>
</system.data>
<Oracle.ManagedDataAccess.Client>
<version number="*">
<dataSources>
<dataSource alias="OracleDataSource" descriptor="(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=Servidor)(PORT=Porta)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=NomeServico)))" />
</dataSources>
</version>
</Oracle.ManagedDataAccess.Client>
<connectionStrings>
<add name="SqlServerDbContext" providerName="System.Data.SqlClient" connectionString="Server=Servidor; Database=BancoDeDados; Uid=Usuario; Pwd=Senha;" />
<add name="OracleDbContext" providerName="Oracle.ManagedDataAccess.Client" connectionString="User Id=Usuario; Password=Senha; Data Source=OracleDataSource;" />
</connectionStrings>
<system.web>
<compilation debug="true" targetFramework="4.6" />
<httpRuntime targetFramework="4.6" />
<authentication mode="Windows" />
<authorization>
<deny users="?" />
</authorization>
</system.web>
<runtime>
<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
<dependentAssembly>
<assemblyIdentity name="Newtonsoft.Json" culture="neutral" publicKeyToken="30ad4fe6b2a6aeed" />
<bindingRedirect oldVersion="0.0.0.0-9.0.0.0" newVersion="9.0.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Web.Optimization" publicKeyToken="31bf3856ad364e35" />
<bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="1.1.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="WebGrease" publicKeyToken="31bf3856ad364e35" />
<bindingRedirect oldVersion="0.0.0.0-1.6.5135.21930" newVersion="1.6.5135.21930" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Web.Helpers" publicKeyToken="31bf3856ad364e35" />
<bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Web.Mvc" publicKeyToken="31bf3856ad364e35" />
<bindingRedirect oldVersion="1.0.0.0-5.2.3.0" newVersion="5.2.3.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="System.Web.WebPages" publicKeyToken="31bf3856ad364e35" />
<bindingRedirect oldVersion="1.0.0.0-3.0.0.0" newVersion="3.0.0.0" />
</dependentAssembly>
<dependentAssembly>
<assemblyIdentity name="Antlr3.Runtime" publicKeyToken="eb42632606e9261f" culture="neutral" />
<bindingRedirect oldVersion="0.0.0.0-3.5.0.2" newVersion="3.5.0.2" />
</dependentAssembly>
</assemblyBinding>
</runtime>
</configuration>
I also installed "Oracle Developer Tools for Visual Studio 2013" and in both the presentation layer and the Oracle data layer I installed the following nuget packages:
- Entityframework 6.1.3
- Oracle.Manageddataaccess 12.1.2400
- Oracle.ManagedDataAccess.Entityframework 12.1.2400
While I had not started the Oracle part configuration the Sql Server data layer was working properly. I created the entire model and generated the database with "Migrations", however after starting to configure Oracle I can no longer update the Sql Server model. Whenever I try to add a new model configuration with Add-Migration returns me the following message:
The Entity Framework Provider type 'Oracle.ManagedDataAccess.Entityframework.Eforacleproviderservices, Oracle.ManagedDataAccess.Entityframework, Version=6.121.2.0, Culture=neutral, Publickeytoken=89b483f429c47342' Registered in the application config file for the ADO.NET Provider with invariant name 'Oracle.ManagedDataAccess.Client' could not be Loaded. Make sure that the Assembly-Qualified name is used and that the Assembly is available to the running application. See http://go.microsoft.com/fwlink/? Linkid=260882 for more information.`
Has anyone ever been through a similar situation and can give me a hint of what might be wrong?
You are configuring the SQL and Oracle part in separate Context right?
– Ricardo Pontual
Ola Guilherme. Yes, two separate db context. I even separated into two projects given the configuration differences of different providers. To increase decoupling tbm.
– Warley Ferreira Silva