Read Oracle data connection using Entity Framework in C#

Question

I have Oracle database 11g and I want to use Entity Framework to read data from it. In first step I am trying to establish database connection and get status if it connected. However I am getting following error;

An exception of type 'System.ArgumentException' occurred in Oracle.ManagedDataAccess.dll but was not handled in user code

my connection string is as following;

 <connectionStrings>
    <add name="EBS_UCAS_DbConnection" providerName="Oracle.ManagedDataAccess.Client" connectionString="Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 193.00.100.00)(PORT = 1500)))(CONNECT_DATA =(SERVICE_NAME = myservice)));Persist Security Info=True;User ID=myuser;Password=mypassword;Unicode=True"/>
</connectionStrings>

this is where I am trying to test code, where I am getting error

 public void TestOracleConnection()
    {
        using (var dbContext = new UCAS_dbContext())
        {
            var data = dbContext.SyncCodes.Select(x => x.SyncDesc).ToList();

            var xx = "dd";
        }
    }

Base DBContext class

public class BaseContext<TContext> : DbContext where TContext : DbContext
{
    static BaseContext()
    {
        Database.SetInitializer<TContext>(null);
    }

    protected BaseContext()
    : base("name = EBS_UCAS_DbConnection")
    { }
}

DbContext class

 public class UCAS_dbContext :BaseContext<UCAS_dbContext>
{
    public DbSet<SyncCodesEntity> SyncCodes { get; set; }
}

Model class

 [Table("SYNC_CODES")]
public class SyncCodesEntity
{
    [Key]
    public int ID { get; set; }

    public string SyncDesc { get; set; }
}

In other project where TNS is working

 public string database = "(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 193.00.100.00)(PORT = 1500)))(CONNECT_DATA =(SERVICE_NAME = myserver)))";

   public void setConnectString(string username, string password, string database)
    {

        if (this.disposed)
        {
            throw new ObjectDisposedException("clarusOracleObj");
        }
        connectString = "Data Source=" + database + ";Persist Security Info=True;User ID=" + username + ";Password=" + password;
        try
        {
            dbConn = new OracleConnection(connectString);
            dbConn.Open();
            dbConn.Close();
        }
        catch (Exception ex)
        {
            //reset the connectString and throw the exception again.
            connectString = "";
            throw ex;
        }
    }

Connection string copy from data property in visual studio and I use .NET Framework Data Provider for Oracle

 Data Source=sittingbull.blackpool.ac.uk:1500/myservice;Persist Security Info=True;User ID=myuser;Password=***********;Unicode=True

First question is what have you got installed re the oracle client on the machine your using — Simon Price
– Simon Price, Commented Nov 11, 2016 at 8:28
are you referencing your TNSNames file correctly and whats in your ODBC \ Datasources manager — Simon Price
– Simon Price, Commented Nov 11, 2016 at 8:32
also, this might be of some use to you oracle.com/webfolder/technetwork/tutorials/obe/db/dotnet/NuGet/… — Simon Price
– Simon Price, Commented Nov 11, 2016 at 8:32

K.Z · Accepted Answer · 2016-11-11 11:21:51Z

after long battle found the answer of my issue.

Because I am using in WPF application so in App.Config file define the data Source alias and TNS detail to oracle database that you are trying to read ;

App.Config

<oracle.manageddataaccess.client>
 <version number="*">
  <dataSources>
    <dataSource alias="DefaultDataSource" descriptor="(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 190.00.100.00)(PORT = 1500)))(CONNECT_DATA =(SERVICE_NAME = myserver)))"/>
  </dataSources>
 </version>
</oracle.manageddataaccess.client>

and point the data source alias in connection string with username and password

<connectionStrings>
  <add name="OracleDbContext" providerName="Oracle.ManagedDataAccess.Client" connectionString="Data Source=DefaultDataSource;User ID=myusername;Password=mypass;"/>
</connectionStrings>

so now you can use OracleDbContext with DbContext class to reference this database;

In doing this implementation I have come across another error

Solving System.Data.Entity.Core.EntityCommandExecutionException

System.Data.Entity.Core.EntityCommandExecutionException' with InnerException: ORA-00942 table or view does not exist

this error happened to due to wrong schema pick by default, I believe it pick dbo where in my case schema is ucas, so I need to define schema in onModelCreating override method

 public class UCAS_dbContext :BaseContext<UCAS_dbContext>
{

    public DbSet<SyncCodesEntity> SyncCodes { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("UCAS");
    }
}

and finally my test Connection class

Test Connection

 public void TestOracleConnection()
    {
        using (var dbContext = new UCAS_dbContext())
        {
            var query = (from b in dbContext.SyncCodes
                         select b).ToList();

        }
    }

Wernfried Domscheit · Accepted Answer · 2016-11-11 09:32:30Z

One problem could be the spaces in your TNS. Enclose it by double-quotes, i.e. try this:

<connectionStrings>
    <add 
       name="EBS_UCAS_DbConnection" 
       providerName="Oracle.ManagedDataAccess.Client" 
       connectionString='"Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 193.00.100.00)(PORT = 1500)))(CONNECT_DATA =(SERVICE_NAME = myservice)))";Persist Security Info=True;User ID=myuser;Password=mypassword;Unicode=True'/>
</connectionStrings>

or

<connectionStrings>
    <add 
       name="EBS_UCAS_DbConnection" 
       providerName="Oracle.ManagedDataAccess.Client" 
       connectionString="&quot;Data Source=(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 193.00.100.00)(PORT = 1500)))(CONNECT_DATA =(SERVICE_NAME = myservice)))&quot;;Persist Security Info=True;User ID=myuser;Password=mypassword;Unicode=True"/>
</connectionStrings>

Collectives™ on Stack Overflow

Read Oracle data connection using Entity Framework in C#

Base DBContext class

DbContext class

Model class

In other project where TNS is working

2 Answers 2

App.Config

Solving System.Data.Entity.Core.EntityCommandExecutionException

Test Connection

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Base DBContext class

DbContext class

Model class

In other project where TNS is working

2 Answers 2

App.Config

Solving System.Data.Entity.Core.EntityCommandExecutionException

Test Connection

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related