How to determine an existing oracle database connection in C#? -

assuming call method below right credentials:

private bool connect(string username, string password)     {         string connstring = "provider = msdaora; data source = isdqa; user id = {0}; password = {1};";         oledbconnection conn = new oledbconnection();         string strcon = string.format(connstring, username, password);         conn.connectionstring = strcon;         bool isconnected = false;          try         {             conn.open();              if (conn.state.tostring() == "open")                 isconnected = true;         }//try         catch (exception ex)         {             lblerr.text = "connection error";         }//catch                 {             conn.close();         }//finally          return isconnected;     } 

i have open connection in method below:

private bool validateusercode(string usercode) {     useraccountdefine def = new useraccountdefine();     useraccountservice srvc = new useraccountservice();     useraccountobj obj = new useraccountobj();      bool returnval = false;     bool isvalid = connect(def.db_dummy_usercode, def.db_dummy_password);     if (isvalid)     {         obj.sqlquery = string.format(def.sql_login, usercode.tolower(), datetime.now.tostring("mm/dd/yyy"));         datatable dt = srvc.execute(obj, crud.readall);         if (dt.rows.count == 1)         {             returnval = true;         }     }     return returnval; } 

the question how can determine connection status in validateusercode() method? how can close afterwards?

note: explicitly declare string variables in useraccountdefine(); don't have worry that.

i tried declaring new oledbconnection conn inside validateusercode() conn.state returning "closed".

update

i have system 2-layer security feature. 1st in application , 2nd on database. if user logs in application, username , password used log him/her in database. now, scenario when user forgot his/her password, can't determine fullname, email , contact (which maintained in database) of user. know usercode. determine contact details, have open active connection using dummy_account.

note never maintain password inside database.

first of all, call close() in finally block, means @ point in second method, connection closed. moreover, if don't close() it,since conn local variable in connect(), when you're in validateusercode(), connection garbage collection, , when it's dispose()d, closes automatically.

i sugges either make member, pass out parameter, return connect() method (and return null failure, or something, if don't exceptions)..or redesign code.

private oledbconnection connect(string username, string password) {     string connstring = "provider = msdaora; data source = isdqa; user id = {0}; password = {1};";     oledbconnection conn = new oledbconnection();     string strcon = string.format(connstring, username, password);     conn.connectionstring = strcon;      try     {         conn.open();          if (conn.state.tostring() == "open")             return conn;     }//try     catch (exception ex)     {         lblerr.text = "connection error";     }//catch         {         //you don't want close here         //conn.close();     }//finally      return null; }