2
\$\begingroup\$

I'm writing C# applications that store their data in MS Access databases, (I'd rather use a different type of database but currently have no other option). The applications are in use by multiple people at a time and so when there are multiple connection attempts (or someone mistakenly opens a table), some of them fail to connect.

In order to get around this I've written a couple of methods that reattempt a connection/disconnection using Thread.Sleep(n) (I realise the disconnection reattempts probably aren't necessary but have left it in just in case).

Is there a better way of accomplishing this? I was reading this thread on 'Why is thread sleep so harmful' and I thought maybe in the case it would be alright. I'd rather have the application look like its frozen for a few seconds than pop up an error message for the end user, which would cause confusion and my inbox to be inundated by users requesting support. Am I just looking at this completely wrong?

Also as a side note, I've never had my code reviewed before and have no one to go to for mentoring or the like as I'm the only person in the team, so any other advice, tips or critiques would be appreciated.

Here are the open and close methods in question:

    public void OpenDatabaseConnection(string conString)
    {
        connection = new OleDbConnection(conString);
        try
        {
            bool success = false;
            int attempts = 0;
            while (success == false && attempts < 1000)
            {
                try
                {
                    connection.Open();
                    success = true;
                }
                catch
                {
                    attempts++;
                    Thread.Sleep(60);
                }
            }
        }
        catch (Exception e)
        {
            string msgbox = "Error while trying to open a database, details below: /n" + e;
            MessageBox.Show(msgbox);
            ErrorHandling.ErrorDetailsByEmail(e, msgbox);
        }
    }

    public void CloseDatabaseConnection()
    {
        try
        {
            bool success = false;
            int attempts = 0;
            while (success == false && attempts < 1000)
            {
                try
                {
                    connection.Close();
                    success = true;
                }
                catch
                {
                    attempts++;
                    Thread.Sleep(60);
                }
            }
        }
        catch (Exception e)
        {
            string msgbox = "Error while trying to close a database, details below: /n" + e;
            MessageBox.Show(msgbox);
            ErrorHandling.ErrorDetailsByEmail(e, msgbox);
        }
    }

An example of how the methods above are being used:

    public void EmailCurrentItem_UpdateItem(string conString, string SQL, string notes)
    {
        try
        {
            using (OleDbCommand command = new OleDbCommand())
            {
                try
                {
                    OpenDatabaseConnection(conString);

                    command.Connection = connection;
                    command.CommandType = CommandType.Text;
                    command.CommandText = SQL;

                    command.Parameters.AddWithValue("@Notes", notes);

                    command.ExecuteNonQuery();
                    CloseDatabaseConnection();
                }
                catch (OleDbException odbcEx)
                {
                    string msgbox = "Error while trying to perform select query on database: " + odbcEx;
                    ErrorHandling.ErrorDetailsByEmail(odbcEx, msgbox);
                    MessageBox.Show(msgbox);
                }
                catch (Exception ex)
                {
                    string msgbox = "CLR error while trying to perform select query on database: " + ex;
                    ErrorHandling.ErrorDetailsByEmail(ex, msgbox);
                    MessageBox.Show(msgbox);
                }
            }
        }
        catch (Exception ex)
        {
            string msgbox = "Error while trying to perform an insert query to a database: " + ex;
            MessageBox.Show(msgbox);
            ErrorHandling.ErrorDetailsByEmail(ex, msgbox);
        }
    }
\$\endgroup\$
  • \$\begingroup\$ You might get a better answer to your primary question from Programming.SE, as one way to address this would be to add an intermediate service to talk to the database. That would be off-topic here, but I think it would be on-topic there. Obviously you wouldn't include the code in a question there. \$\endgroup\$ – mdfst13 Aug 30 '16 at 23:45
  • \$\begingroup\$ Thanks for the tip! I've cross posted here \$\endgroup\$ – Rob Aug 31 '16 at 0:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.