mysql connector in a multi-threaded environment

Question

I have a c# server that is connecting to a mysql server for data. This c# server is a backend server for a game, that has an open thread for every player currently logged in. How do I go about doing the mysql connection, an open connection for each thread? A single connection using locking for all threads?

I read somewhere that there is a 'threadpool' for this. Is that true? If so, is this the proper way to use it:

using(var conn = new MySqlConnection(DatabaseHelper.getConnectionString()))
using (var cmd = conn.CreateCommand())
{
    conn.Open();
    cmd.CommandText = "SELECT username FROM characters WHERE id=1";
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            user = reader.GetString("username");
        }
    }
}

score 4 · Accepted Answer · edited May 23 '17 at 12:00

I think you are confusing two different resources here; you may indeed want an open thread (effectively a process) for each player logged on, but this doesn't mean you should have a connection to the database open for each player constantly, as things like database connections, file handles etc are not managed resources and should be freed up as soon as you are finished with them.

There's a good introduction and explanation of C# threading here.

You should only open a connection to the database when you need it. This means your data access classes could be instantiated many times (this is the simplest design) each with their own connection. If you use connection pooling, which I feel you actually might have been asking about, then you'll benefit further. Moving towards a static database connection design (where many people share the same dao classes) can be much more problematic as you may have to synchronize certain resources, ensure certain variables can only be accessed in sequence via locking or similar, and more.

You can read more about this here for example. Microsoft also talk about the benefits of connection pooling here.

The code you have above is a good starting point for connecting to the database, by including the using statement you are closing and disposing of the connection as soon as you are finished with it; you might also consider the following improvements:

using(var conn = new MySqlConnection(DatabaseHelper.getConnectionString())) 
{
  using (var cmd = conn.CreateCommand()) 
  { 
      conn.Open(); 
      cmd.CommandType = CommandType.Text;
      //Parameterize your queries!
      cmd.CommandText = "SELECT username FROM characters WHERE id=?"; //Use ? as placeholder for parameters, and add parameters in the order they appear in your query.
      cmd.Parameters.Add(new MySqlParameter("@userid", MySqlDbType.Int, userid));

      using(IDataReader reader = cmd.ExecuteReader())
      { 
          //You should only expect one record. You might want to test for more than 1 record.
          if (reader.Read()) 
          { 
              user = reader.GetString(reader.GetOrdinal("username")); //Think also about null value checking.
          } 
      } 
  }
}

You might have a DAO class, or a method on a user class to do this. For example, if it was a method on user, you might do something like:

User myUser = new User(7);
myUser.Load();

Inside of Load, one of the methods you might call is PopulateFromDB() which would contain the code above, and would load all of the properties of this user. You might also have a DAO class that does the same thing:

UserLoader loader = new UserLoader();
string userName = loader.GetUserName(int userId);

which would return the username using the code in the example above. I'd prefer this method to be on a class like User as it's logically connected to it. However, you then run the risk of mixing the DAO logic with the user business logic, which is a topic all on it's own.

Rather than write a lot of this data access logic, you might consider looking at some form of framework like an ORM or similar - this question has already been answered for MySql on SO. This could also save you a lot of time and effort, and will allow you to concentrate on design.

So, at least in the shortrun, your example above should be what I am using? — will, Dec 10 '11 at 17:26
@will Yes, the two most important aspects are the USING statement which means the unmanged resource gets freed up as soon as you are finished with it, and PARAMETERIZATION of sql queries, which will add a layer of protection to your app from sql injection. I'll add a bit at the bottom of the answer with an example of how you might use it in practice. — dash, Dec 10 '11 at 17:37
Parameterization of queries also has benefits for many database systems as they can reuse query execution plans for parameterized queries, too. — dash, Dec 10 '11 at 17:49
Oh, right after the parameters add in your example, should there still be a "using (var reader = cmd.ExecuteReader())" between the parameter add and the '{' ? — will, Dec 10 '11 at 18:23
When I make the paramertization change, I get "unknown column _userid in where clause" — will, Dec 10 '11 at 18:29
@will Updated - sorry, I did indeed miss the using, and I've updated the syntax of the MySql query for parameterization. Good spot! — dash, Dec 10 '11 at 18:46

mysql connector in a multi-threaded environment

1 Answers1