CODEWITHSUNDEEP
c#
Boardy
Boardy

Reputation: 36217

Code repeating itself causing duplicate records in database

this problem is a bit of a difficult one to explain but here it goes. I have a function which adds a record to a MySQL Database online from a local SQLiteDatabase. A function is first called to retrieve the local data and each line is sent to the upload function which adds the record to the online MySQL Database. When these functions are called from a another function A it works fine but when called from a different function. Function B duplicate records are entered into the database.

During debugging to try and resolve the problem I find that when it is duplicating records it is going to cmd.executeNonQuery() then going to the next couple of line but then for no reason will go back up to cmd.executeNonQuery() therefore duplicating the record. The code is below

 private void uploadDatabase(string company, string oldCompany, string companyURL, string loginUsername, string oldUsername, string password, string type, string perform, string direction)
        {
            Boolean recordFound = false;
            recordFound = checkRecordNotExist(company, loginUsername);
            MySQLDBWork dbase = new MySQLDBWork();
            try
            {
                dbase.openConnection();
                if (perform == "insert" && !recordFound)
                {
                    string query = "INSERT INTO `" + username + "` (pas_company, pas_companyURL, pas_username, pas_password, pas_type) "
                        + "VALUES ('" + company + "', '" + companyURL + "', '" + loginUsername + "', '" + password + "', '" + type + "')";
                    Console.WriteLine("Query: " + query);
                    MySqlCommand cmd = new MySqlCommand(query, dbase.conn);
                    cmd.ExecuteNonQuery();
                    recordFound = true;
                    query = "";
                    company = "";
                    loginUsername = "";
                    cmd.Dispose();
                }
                if (perform == "delete")
                {
                    string query = "DELETE FROM `" + username + "` WHERE pas_company='" + company + "' AND pas_username='" + loginUsername + "'";
                    dbase.performQuery(query);
                }
            }
            catch (MySqlException ex)
            {
                Console.WriteLine("Adding Online Error: " + ex.Message);
            }
            catch (Exception ex)
            {
                Console.WriteLine("General Exception: " + ex.Message);
            }
            finally
            {
                dbase.closeConnection();
                //dbase.conn.Dispose();
                company = null;
                loginUsername = null;
            }
        }

The problem is within the if statement perform == "insert" && !recordFound.

I'm not sure if the code above will help to solve the problem but this is the function that is going wrong when called from function b but works fine from function A. Thanks for any help and suggestions you can offer.

Upvotes: 0

Views: 1162

Answers (3)

Chingiz Musayev
Chingiz Musayev

Reputation: 2962

My advice to you:

  • Always use transactions and you won't be able make duplications. You also may make LoginName column unique and properly handle db error.
  • DO NOT concatenate string to build query, please. Use command parameters - simplest way escape SQL injection. Currently you have at least 4 vulnerable parameter. Awesome ;)

Upvotes: 2

shaunmartin
shaunmartin

Reputation: 3919

I would suggest putting a breakpoint on cmd.ExecuteNonQuery(); and inspecting the call stack each time it is hit, paying special attention to the second/duplicate hit. Also pay attention to which thread the breakpoint is being hit on. Doing these things should point you to the problem.

Upvotes: 0

Yochai Timmer
Yochai Timmer

Reputation: 49261

then going to the next couple of line but then for no reason will go back up to cmd.executeNonQuery()

That sounds like a simple multithreading problem. The function is accessed again from a different thread. So what's happening is that it goes through your check exists in both threads before it is inserted in either, and then it is inserted in both.

So, create a lock, and lock the code... something like this:

 private System.Object uploadLock = new System.Object();

 private void uploadDatabase(string company, string oldCompany, string companyURL, string loginUsername, string oldUsername, string password, string type, string perform, string direction)
        {
            lock(uploadLock ) {
            Boolean recordFound = false;
            recordFound = checkRecordNotExist(company, loginUsername);
            MySQLDBWork dbase = new MySQLDBWork();
            try
            {
                dbase.openConnection();
                if (perform == "insert" && !recordFound)
                {
                    string query = "INSERT INTO `" + username + "` (pas_company, pas_companyURL, pas_username, pas_password, pas_type) "
                        + "VALUES ('" + company + "', '" + companyURL + "', '" + loginUsername + "', '" + password + "', '" + type + "')";
                    Console.WriteLine("Query: " + query);
                    MySqlCommand cmd = new MySqlCommand(query, dbase.conn);
                    cmd.ExecuteNonQuery();
                    recordFound = true;
                    query = "";
                    company = "";
                    loginUsername = "";
                    cmd.Dispose();
                }
                if (perform == "delete")
                {
                    string query = "DELETE FROM `" + username + "` WHERE pas_company='" + company + "' AND pas_username='" + loginUsername + "'";
                    dbase.performQuery(query);
                }
            }
            catch (MySqlException ex)
            {
                Console.WriteLine("Adding Online Error: " + ex.Message);
            }
            catch (Exception ex)
            {
                Console.WriteLine("General Exception: " + ex.Message);
            }
            finally
            {
                dbase.closeConnection();
                //dbase.conn.Dispose();
                company = null;
                loginUsername = null;
            }
        }
}

The lock will allow access to the code to only on thread at a time. So no more duplications.

Upvotes: 3

Related Questions