I'm trying to place every needed DB-information/configuration for running an ASP.NET-application inside the web.config. Nothing should stay in machine config and also not in tnsnames.ora.
But in my real world application this did not work. So I breaked the problem down into a really small ASP.NET-app, which just has a default.aspx with just a TextBox to display some debugging-information. The Page_Load-handler just does this:
protected void Page_Load(object sender, EventArgs e){
//Enumerate datasource with closed DB
GetDataSources("BEFORE OPEN CONNECTION");
//Open connection and fetch something
String ConnectionString = ConfigurationManager.ConnectionStrings["My_ConnectionString"].ConnectionString;
try {
using (OracleConnection con = new OracleConnection(ConnectionString)) {
using (OracleCommand cmd = new OracleCommand("SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') NOW FROM DUAL", con)) {
con.Open();
String actTime = (String)cmd.ExecuteScalar();
this.TextBox1.Text += String.Format("Actual time from DB: {0}", actTime)+System.Environment.NewLine;
}
}
}
catch (Exception ex){
this.TextBox1.Text += String.Format("Error in SELECT: Message : {0} Stacktrace: {1}", ex.Message, ex.StackTrace) + System.Environment.NewLine;
}
//close connection and enumerate datasources again
//connection closes implicit
GetDataSources("AFTER OPENED CONNECTION");
}
void GetDataSources(String prefix) {
try {
//GetDataSource
string ProviderName = "Oracle.ManagedDataAccess.Client";
DbProviderFactory factory = DbProviderFactories.GetFactory(ProviderName);
if (factory.CanCreateDataSourceEnumerator) {
this.TextBox1.Text += String.Format("Start GetDataSource with prefix {0}", prefix)+System.Environment.NewLine;
DbDataSourceEnumerator dsenum = factory.CreateDataSourceEnumerator();
DataTable dt = dsenum.GetDataSources();
if (dt.Rows.Count > 0){
this.TextBox1.Text += String.Format("Count of datasources: {0}", dt.Rows.Count)+System.Environment.NewLine;
this.TextBox1.Text += String.Format("Enumerating datasources:")+System.Environment.NewLine;
int i = 0;
foreach (DataRow drs in dt.Rows) {
this.TextBox1.Text += String.Format("DataSoure #{0}:", i)+System.Environment.NewLine;
foreach (DataColumn dc in dt.Columns)
this.TextBox1.Text += String.Format("Column {0} has value: {1}", dc.ColumnName, drs[dc])+ System.Environment.NewLine;
i++;
}
}
else
this.TextBox1.Text += String.Format("No datasources in GetDataSources!")+ System.Environment.NewLine;
}
else
this.TextBox1.Text += String.Format("Provider does not support DataSoureceEnumerator (CanCreateDataSourceEnumerator is false)") + System.Environment.NewLine;
}
catch (Exception ex) {
this.TextBox1.Text += String.Format("Error in GetDataSources: Message : {0} Stacktrace: {1}", ex.Message, ex.StackTrace) +System.Environment.NewLine; }
}
The connectionString in the web.config is named My_ConnectionString and beside the USER ID and PASSWORD it only contains DATA SOURCE=MY_TEST_DS.
The managed driver part inside the config is:
<oracle.manageddataaccess.client>
<version number="*">
<dataSources>
<dataSource alias="MY_TEST_DS" descriptor="(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora-dbsrv)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBMV)))"/>
</dataSources>
<settings>
<!--<setting name="tns_admin" value="C:\traces\" />-->
<setting name="TraceFileLocation" value="C:\traces\"/>
<setting name="TraceLevel" value="7"/>
<setting name="TraceOption" value="0"/>
</settings>
</version>
</oracle.manageddataaccess.client>
If I use this (tns_admin commented out), I receive (in the TextBox):
Start GetDataSource with prefix BEFORE OPEN CONNECTION
No datasources in GetDataSources!
Error in SELECT: Message : ORA-12154: TNS: Angegebener Connect Identifier konnte nicht aufgelöst werden Stacktrace: at OracleInternal.ConnectionPool.PoolManager`3.Get(ConnectionString csWithDiffOrNewPwd, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)
at OracleInternal.ConnectionPool.OraclePoolManager.Get(ConnectionString csWithNewPassword, Boolean bGetForApp, String affinityInstanceName, Boolean bForceMatch)
at OracleInternal.ConnectionPool.OracleConnectionDispenser`3.Get(ConnectionString cs, PM conPM, ConnectionString pmCS, SecureString securedPassword, SecureString securedProxyPassword)
at Oracle.ManagedDataAccess.Client.OracleConnection.Open()
at failover_light._default.Page_Load(Object sender, EventArgs e) in c:\POLICE\TLBS_FAILOVERTEST\failover_light\default.aspx.cs:line 30
Start GetDataSource with prefix AFTER OPENED CONNECTION
No datasources in GetDataSources!
And in the trace-file there is (just the important part where it comes to DataSources; I took away the timestamps ):
(CFG) (ENV) Application Directory : C:\FAILOVERTEST\failover_light
(CFG) (VER) Oracle Data Provider for .NET, Managed Driver Version : 4.121.2.0
(CFG) (VER) Oracle Data Provider for .NET, Managed Driver Informational Version : 4.121.2.20160405 ODAC RELEASE 4
(CFG) (.NET) TraceOption : 0
(CFG) (.NET) TraceFileLocation : C:\traces\
(CFG) (.NET) TraceLevel : 7
(CFG) (.NET) Resolved Trace File Location: C:\traces\
(CFG) (TNSNAMES) MY_TEST_DS : (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora-dbsrv)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBMV)))
(CFG) (SQLNET) FilePath : (null)
(CFG) (TNSNAMES) FilePath : (null)
(PUB) (ENT) OracleClientFactory.CreateDataSourceEnumerator()
(PUB) (EXT) OracleClientFactory.CreateDataSourceEnumerator()
(PUB) (ENT) OracleDataSourceEnumerator.GetDataSources()
(PRI) (ENT) (CP) OracleConnectionDispenser`3..cctor()
(PRI) (EXT) (CP) OracleConnectionDispenser`3..cctor()
(PUB) (EXT) OracleDataSourceEnumerator.GetDataSources()
(PUB) (ENT) OracleConnection.ctor()
(PRI) (ENT) (CP) ConnectionString.GetCS()
(PRI) (ENT) (CP) ConnectionString.ctor()
(PRI) (ENT) (CP) ConnectionString.Parse()
(PRI) (ENT) (CP) ConnectionString.SetProperty()
(PRI) (EXT) (CP) ConnectionString.SetProperty()
(PRI) (ENT) (CP) ConnectionString.SetProperty()
(PRI) (EXT) (CP) ConnectionString.SetProperty()
(PRI) (ENT) (CP) ConnectionString.SetProperty()
(PRI) (EXT) (CP) ConnectionString.SetProperty()
(PRI) (EXT) (CP) ConnectionString.Parse()
(PRI) (EXT) (CP) ConnectionString.ctor()
(PRI) (EXT) (CP) ConnectionString.GetCS()
So it seems that it reads in the dataSource MY_TEST_DS, but the DataSource-Enumerator does not find it (you'll see the difference in trace-snippet when it works).
So now I only uncomment the tns_names-setting in the web.config (line 7 abobe in the web.config-snippet) and comment the dataSource-line (line 4) and place a tnsnames.ora-file under c:\traces (this I don't do in production :-) just want to have it very basic here...) and the only content of the tnsnames.ora is:
MY_TEST_DS= (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora-dbsrv)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBMV)))
which is the exact same like the dataSource in the web.config before (in fact I really copied the descriptor-attribute's content from the web.config to the tnsnames.ora for being sure....
Now I get the wished result i nmy TextBox:
Start GetDataSource with prefix BEFORE OPEN CONNECTION
Count of datasources: 1
Enumerating datasources:
DataSoure #0:
Column InstanceName has value: MY_TEST_DS
Column ServerName has value: ora-dbsrv
Column ServiceName has value: DBMV
Column Protocol has value: tcp
Column Port has value: 1521
Actual time from DB: 05-09-2016 11:44:22
Start GetDataSource with prefix AFTER OPENED CONNECTION
Count of datasources: 1
Enumerating datasources:
DataSoure #0:
Column InstanceName has value: MY_TEST_DS
Column ServerName has value: ora-dbsrv
Column ServiceName has value: DBMV
Column Protocol has value: tcp
Column Port has value: 1521
and in the tracefile there is now:
(CFG) (ENV) Application Directory : C:\POLICE\TLBS_FAILOVERTEST\failover_light
(CFG) (VER) Oracle Data Provider for .NET, Managed Driver Version : 4.121.2.0
(CFG) (VER) Oracle Data Provider for .NET, Managed Driver Informational Version : 4.121.2.20160405 ODAC RELEASE 4
(CFG) (.NET) TraceOption : 0
(CFG) (.NET) TraceFileLocation : C:\traces\
(CFG) (.NET) tns_admin : C:\traces\
(CFG) (.NET) TraceLevel : 7
(CFG) (.NET) Resolved Trace File Location: C:\traces\
(CFG) (TNSNAMES) MY_TEST_DS : (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora-dbsrv)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBMV)))
(CFG) (TNSNAMES) MY_TEST_DS : (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora-dbsrv)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBMV)))
(CFG) (SQLNET) FilePath : (null)
(CFG) (TNSNAMES) FilePath : C:\traces\tnsnames.ora
(PUB) (ENT) OracleClientFactory.CreateDataSourceEnumerator()
(PUB) (EXT) OracleClientFactory.CreateDataSourceEnumerator()
(PUB) (ENT) OracleDataSourceEnumerator.GetDataSources()
(PRI) (ENT) (CP) OracleConnectionDispenser`3..cctor()
(PRI) (EXT) (CP) OracleConnectionDispenser`3..cctor()
(CFG) (TNSNAMES) MY_TEST_DS : (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora-dbsrv)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBMV)))
(PUB) (EXT) OracleDataSourceEnumerator.GetDataSources()
(PUB) (ENT) OracleConnection.ctor()
(PRI) (ENT) (CP) ConnectionString.GetCS()
(PRI) (ENT) (CP) ConnectionString.ctor()
So it seems that here now the call to GetDataSources receives the previously read TNSNAMES.
and so everything works fine.
Additional information:
- There is no sqlnet.ora in the c:\traces directory (where the tnsnames.ora is).
- The Oracle Managed Driver has been added to the project over newestr NuGet-Version 12.1.24160419
- in comparable project with same simplicity but as a command-line application, the definition of a dataSource without tnsnames.ora works!!
- for these tests I used IISExpress, but it behaves the same for me under IIS
- .NET-framework is 4.6
Can somebody see what I'm doing wrong?