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

Question

Assuming that I call the method below with the 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
        finally
        {
            conn.Close();
        }//finally

        return isConnected;
    }

I have successfully open the connection in my 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 is how can I determine the connection status in ValidateUserCode() method? How can I close it afterwards?

Note: I explicitly declare the string variables in UserAccountDefine(); so you don't have to worry about that.

I already tried declaring a new OleDbConnection conn inside the ValidateUserCode() to but the conn.State always returning "Closed".

UPDATE

I have a system with 2-layer security feature. 1st is in application and 2nd is on database. If a user logs in to the application, the username and password is also used to log him/her in to the database. Now, the scenario is when a user forgot his/her password, we can't determine the fullname, email and contact (which are maintained in the database) of the user. I just know his usercode. To determine the contact details, I have to open an active connection using a DUMMY_ACCOUNT.

Note that I never maintain the password inside the database.

Answer 1

First of all, you call Close() in your finally block, which means that at any point in your second method, the connection would be closed. Moreover, even if you don't Close() it,since conn is a local variable in Connect(), when you're back in ValidateUserCode(), the connection is already up for garbage collection, and when it's Dispose()d, it also closes automatically.

I sugges you either make it a member, pass it as an out parameter, return it by the Connect() method (and return null for failure, or something, if you don't like exceptions)..or redesign the 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
    finally
    {
        //you don't want to close it here
        //conn.Close();
    }//finally

    return null;
}

Answer 2

I'm not sure I follow the question quite right. My answer is based on the premise that you want to open/retrieve a connection, take an action, and close/release the connection afterward.

The code you include does not do that well. Typical DAO code resembles this pseudocode, in my case taken from some boilerplate code I use.

public DataSet FetchDataSet(string sql, IDictionary paramHash) {
    var cnn = AcquireConnection();
    var rtnDS = new DataSet();
    try
    {
        var cmd = cnn.CreateCommand();
        cmd.CommandText = sql;

        SetParameters(cmd, paramHash);
        IDbDataAdapter ida = new DataAdapter { SelectCommand = cmd };
        LogSql(sql, paramHash, "FetchDataSet");
        ida.Fill(rtnDS);
    }
    catch (Exception ex)
    {
        DebugWriteLn("Failed to get a value from the db.", ex);
        throw;
    }
    finally
    {
        ReleaseConnection(cnn);
    }
    return rtnDS;
}

Note that the code above is strictly about communicating with the database. There is no assessment of whether the data is right or wrong. You might have a DAO that is a subclass of the one that contains the above code, and it might do this:

public MyItemType FindSomeValue(long Id)
{
    const string sql = @"SELECT something from somewhere where id=:id";
    var myParams = new Dictionary<string, long> { { "id", Id } };
    var ds = FetchDataSet(sql, myParams);

    return (from DataRow row in ds.Tables[0].Rows
            select new Item
            {
                Id = Convert.ToInt64(row["ID"], CultureInfo.InvariantCulture),
                Name = row["NAME"].ToString()
            }).FirstOrDefault();
}

In fact, the above is pseudocode from a DAO implementation that I've used for years. It makes data access relatively painless. Note that there is some real code behind those methods like SetParameters (30 - 80 lines or so), and I have a bunch of other protected methods like FetchScalar, ExecuteSQL, etc.

Answer 3

I am not sure how this information helps you.

I had similar problem while using OLEDB connection for Excel Reading. I didn't knew the answer. So, just I added a global variable for OleDbConnection initialized to null.

In my method, I used to check that null, if not close it and again open it.

        if (con != null)
        {
            con.Close();
            con.Dispose();
        }

        try
        {
            con = new OleDbConnection(connectionString);
        }
        catch (Exception ex)
        {
            MessageBox.Show("oledbConnection = " + ex.Message);
        }

        try
        {
            con.Open();
        }
        catch (Exception ex)
        {
            MessageBox.Show("connection open = " + ex.Message + "\n");
        }

I could able to continue after this. You can try, if it works for you its good!