Draxuss Draxuss - 3 months ago 14
SQL Question

What's the best way to multithread?

I've got a little problem with my application.
I have a database editor that hangs up sometimes when I try to update the database file. Not every time, but pretty often, and every time it happens right before any changes are made to the database. I figured it's because of not using multithreading. I've only started learning programming recently though, and I'm kind of lost even after reading through a few multithreading explanations. Could someone explain to me how should I implement it in my specific example?


private void adjustStatsButton_Click(object sender, EventArgs e)
{
ReadWrite.AdjustStats(winnerInput.Text, loserInput.Text);
winnerInput.Text = "";
loserInput.Text = "";
Refresh(leaderboardBox);
}



public class ReadWrite
{
public static void AdjustStats(string winner, string loser)
{
SQLiteConnection dbConnection = new SQLiteConnection("Data Source = Leaderboards.sqlite; Version = 3");

string sql = "SELECT * FROM leaderboard WHERE name='" + winner + "'";
SQLiteCommand command = new SQLiteCommand(sql, dbConnection);
dbConnection.Open();
SQLiteDataReader reader = command.ExecuteReader();


double wrating = Convert.ToDouble(reader["rating"]);
int wmatches = Convert.ToInt32(reader["matches"]);
int wwins = Convert.ToInt32(reader["wins"]);

sql = "SELECT * FROM leaderboard WHERE name='" + loser + "'";
command = new SQLiteCommand(sql, dbConnection);
reader = command.ExecuteReader();

double lrating = Convert.ToDouble(reader["rating"]);
int lmatches = Convert.ToInt32(reader["matches"]);
int lwins = Convert.ToInt32(reader["wins"]);
int llosses = Convert.ToInt32(reader["losses"]);

double RC = (1 - ((wrating - lrating) / 200)) * 8;
if (RC < 0) RC *= -1;
if (RC < 4) RC = 4;
else if (RC > 12) RC = 12;

wmatches++;
wwins++;
lmatches++;
llosses++;
wrating += RC;
if (wrating < 0) wrating = 0;
lrating -= RC;
if (lrating < 0) lrating = 0;
double wwinrate = Convert.ToDouble(wwins) / wmatches;
double lwinrate = Convert.ToDouble(lwins) / lmatches;

sql = "UPDATE leaderboard SET rating=" + wrating + ", matches=" + wmatches + ", wins=" + wwins + ", winrate=" + wwinrate + " WHERE name='" + winner + "'";
command = new SQLiteCommand(sql, dbConnection);
command.ExecuteNonQuery();

sql = "UPDATE leaderboard SET rating=" + lrating + ", matches=" + lmatches + ", losses=" + llosses + ", winrate=" + lwinrate + " WHERE name='" + loser + "'";
command = new SQLiteCommand(sql, dbConnection);
command.ExecuteNonQuery();
dbConnection.Close();
}
}

Answer

The problem is that you are running the query in the UI thread. Here's an example for using the BackgroundWorker, heavily inspired from this example, and using Arguments. This way it will be running in a separate thread and it will not lock the GUI.

// Class for passing arguments
public class BqArguments
{
    public string Winner {get;set}
    public string Loser {get;set}
}

And your implementation:

BackgroundWorker _bw; // You need to initialize this somewhere.

private void adjustStatsButton_Click(object sender, EventArgs e)
{
    // Maybe this should be initialized in ctor. But for this example we do it here...
    _bw = new BackgroundWorker();
    var arguments = new BqArguments
    {
       Winner = winnerInput.Text,
       Loser = loserInput.Text
    }
    _bw.DoWork += bw_DoWork;
    _bw.RunWorkerCompleted += bw_RunWorkerCompleted;
    _bw.RunWorkerAsync(arguments);    
}

private void bw_DoWork (object sender, DoWorkEventArgs e)
{
    // Run your query in the background.
    var arguments = e.Argument as BqArguments;
    ReadWrite.AdjustStats(arguments.Winner, arguments.Loser);
}

private void bw_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
{
    // All done. Let's update the GUI.
    winnerInput.Text = "";
    loserInput.Text = "";
    Refresh(leaderboardBox);
}