Base solution for your next web application
Open Closed

How to connect to an Oracle Database to query data? #7983


User avatar
0
oaalvarado created

Hi,

I'm using ASP.NET Zero Core MVC JQuery version is 7.0.0 with SQL Server Database. I need to access data from an Oracle Database, but can't get it to work.

I added the Oracle.EntityFrameworkCore nuget package to the EntityFrameworkCore and Web.Mvc projects. I created a connectionstring for the Oracle Database in the appsettings.json file in Web.Mvc project. Created another DbContext, DbContextconfigurer and DbContextFactory in the EntityFrameworkCore project for the Oracle Database. Added code in the PreInitialize subrutine in the EntityFrameworkCoreModule to configure the Oracle Database.

I tried to create a custom repository to query the Oracle Database, but I can't get it to connect to the database. I can't find any documentation on what to do next. Can you please provide some guidance?


5 Answer(s)
  • User Avatar
    0
    maliming created
    Support Team

    You can refer to this example, using multiple dbcontext in zero.

    The migration of the two databases may be different. Please generate and perform the migration separately.

    https://github.com/aspnetboilerplate/aspnetboilerplate-samples/tree/master/MultipleDbContextEfCoreDemo

  • User Avatar
    0
    oaalvarado created

    Hi maliming,

    Thanks. the second database is only to get data from, so no migration for this database is needed. I will just query this Oracle Database. I followed the example and I was missing the MyConnectionStringResolver.cs class. But now, when I try to connect to the Oracle Database, I get the error:

    ArgumentException: IsolationLevel must be ReadCommitted or Serializable Parameter name: isolationLevel Oracle.ManagedDataAccess.Client.OracleConnection.BeginTransaction(IsolationLevel isolationLevel)

    I'm using a Custom Repository to query the Oracle Database, I followed this https://aspnetboilerplate.com/Pages/Documents/Articles/Using-Stored-Procedures,-User-Defined-Functions-and-Views/index.html to add the Custom Repository to query a View in the Oracle Database, but when I try to connect to the database using this function:

    private void EnsureConnectionOpen() { var connection = Context.Database.GetDbConnection();

    if (connection.State != ConnectionState.Open)
    {
        connection.Open();
    }
    

    }

    I get the error in the Context.Database.GetDbConnection() call.

  • User Avatar
    0
    oaalvarado created

    Hi maliming,

    I figured out that I need to configure the Isolationlevel in the Preinitialize function of EntityFrameworkCoreModule:

    Configuration.UnitOfWork.IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted;

    And now I can connect to the Oracle Database.

    Question: Is this something that can be configure only for the Oracle DBContext or is a global setting?

    Is there a performance penalty on using IsolationLevel.ReadCommitted instead of IsolationLeve.ReadUncommitted?

  • User Avatar
    0
    maliming created
    Support Team

    The database uses locks based on the isolation level. Concurrency execution transactions may have performance impact.

  • User Avatar
    0
    oaalvarado created

    Thanks maliming.