Reading access database, background process still running on close

Question

So I am using c# windows form with visual studio to query an access database.

When I run with debugger and stop the application from within visual studio there is no problem, however when I run WITHOUT debugger, query the database and then close using X, the process which appears under "Apps" in Task manager becomes a background process. I can have multiple instances of this process if I run the application numerous times.

I would appreciate any information on this, Thanks!

Here is the code I am using.

    private void BtnSendQuery_Click(object sender, EventArgs e)
    {
        ReadDatabase();
    }
    private void Form1_FormClosing(object sender, FormClosingEventArgs e)
    {
        var x = MessageBox.Show("Are you sure you want to exit? ", "Exit", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
        if (x == DialogResult.No)
        {
            e.Cancel = true;
        }
        else
        {
            e.Cancel = false;
        }
    }
    private void ReadDatabase()
    {

        string CONNECTION_STR = @"Provider=Microsoft.ACE.OLEDB.12.0; 
                                  Data Source = C:\\Users\\***\\Documents\\db_folder\\access_db.accdb; 
                                  Persist Security Info = False";

        string query = ""; // query string
        OleDbConnection DB_CONNECTION = null;
        

        try
        {
            DB_CONNECTION = new OleDbConnection(CONNECTION_STR);
            DB_CONNECTION.Open();
            query = TbInputQuery.Text;
            var command = new OleDbCommand(query, DB_CONNECTION);
            var str = new StringBuilder();
            using (OleDbDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    str.AppendLine(reader["ID"].ToString());
                }
                TbOutputTable.Text = str.ToString();
            }
            DB_CONNECTION.Close();

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            if (DB_CONNECTION != null)
            {
                DB_CONNECTION.Close();
            }
        }
    }
}

Answer 1

As general rule, both your connection and cmdSQL or reader should be in a using block. While your reader is in a using block, the ALL important connection object is not.

In fact, once beyond the using block for connection? You could get away not even having using blocks for the command and reader object.

And even if a trapped error, or UN-trapped error occurs? The using block WILL ALWAYS clean up the connection.

So, for command and reader - not end of world for using block.

But, for connection? yes, always do that.

Project->settings - I would use the connection builder for the connection string - not put in code.

eg this one:

Then use advanced, and make sure you choose ACE (for accdb) or JET (for mdb) So this:

So, with above setting, then we have ONE spot in the system - never typing connecting string by hand or having to place in the code (makes change of connection very hard).

Also, don't use "any cpu" force the project to x86 for using Access x32 (or if using x64, then force project to that).

So, code say like this:

private void ReadDatabase()
{
    string CONNECTION_STR = Properties.Settings.Default.AccessDB;
    string query = ""; // query string
    try
    {
        using (OleDbConnection DB_CONNECTION = new OleDbConnection(CONNECTION_STR))
        {
            using (OleDbCommand command = new OleDbCommand(query, DB_CONNECTION))
            {
                DB_CONNECTION.Open();
                var str = new StringBuilder();
                using (OleDbDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        str.AppendLine(reader["ID"].ToString());
                    }
                    TbOutputTable.Text = str.ToString();
                }

            }
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
    finally
    {
    }
}

Note in above - don't really care about the catch block - as long as the using block for the connection is built - it gets cleaned up no matter what - and even if no try/catch, or if in fact you have one!! And if a error trigger - still again, note how we do NOT have to clean up, or close the connection.