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";
}
}
public class BaseContext<TContext> : DbContext where TContext : DbContext
{
static BaseContext()
{
Database.SetInitializer<TContext>(null);
}
protected BaseContext()
: base("name = EBS_UCAS_DbConnection")
{ }
}
public class UCAS_dbContext :BaseContext<UCAS_dbContext>
{
public DbSet<SyncCodesEntity> SyncCodes { get; set; }
}
[Table("SYNC_CODES")]
public class SyncCodesEntity
{
[Key]
public int ID { get; set; }
public string SyncDesc { get; set; }
}
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
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 ;
<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
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
public void TestOracleConnection()
{
using (var dbContext = new UCAS_dbContext())
{
var query = (from b in dbContext.SyncCodes
select b).ToList();
}
}