How to avoid re-occurring Err "ORA-12154: TNS:could not resolve service name" (not a common oracle connection issue)
I made a web application which uses oracle database. Below is the minimum code which explains how I am accessing the data.
At a time the application working fine and I can access and do everything with database. Some other time database server is down for scheduled maintenance. If I try to run the application at this point of time it will raise err "ORA-12154: TNS:could not resolve service name"
(I'm catching this exception and displaying a custom error message to users). After some hours the database server is up an running.
Now if I run the application it should behave normal and give database access. But it dose not, and continue to give the same ORA-12154" error.
In such case I have to reset IIS to make the application work normal. So the question is how to avoid re-occurring Err "ORA-12154".
private string get_name()
{
string name = "no name";
string conn_str = "Provider=MSDAORA.1;Persist Security Info=True;Data Source=test;User ID=test;Password=test";
OleDbConnection db_conn = new OleDbConnection(conn_str);
try
{
db_conn.Open(); //Err hits at this line
string str_sql = "select NAME from users where EMPNO = '" + TextBox1.Text + "'";
OleDbCommand db_cmd = new OleDbCommand(str_sql, db_conn);
OleDbDataReader db_reader = db_cmd.ExecuteReader();
while (db_reader.Read())
{
name = db_reader[0].ToString();
}
db_reader.Close();
db_conn.Close();
return name;
}
catch
{
db_conn.Dispose();
return name;
}
} // end
For your info. I am a novice in both asp.net and programming field.
As per MSDN Note "To deploy high-performance applications, you need to use connection pooling. When you use the .NET Framework Data Provider for OLE DB, you do not need to enable connection pooling because the provider manages this automatically."
The connection will establish if "db_conn.Open();" is successfull. (The Err hits at this line) If its not successful that means there is no connection and I dont need to close or dispose it?