c# mysqlreader multithread fail

Question

I'm writing a Database class using c# that interacts with a multithread sdk. I should use only one connection(No need to say!) However, I always get errors about connection and datareaders. Anly help appreciated.

Here is the structure of my code

static class Db
{
  static MySqlConnection conn = new MySqlConnection(connectionString);

  private static void Connect()
  {
    if (conn.State == ConnectionState.Closed)
                    conn.Open();
  }

   private static void DisConnect()
   {
     if (conn.State == ConnectionState.Open)
     conn.Close();
    }  


    static int Insert()
    {
        Connect();
        MySqlCommand cmd = new MySqlCommand(sql, conn);
        cmd.ExecuteNonQuery();
        return Convert.ToInt32(cmd.LastInsertedId);
        DisConnect();
    }

     public static DataTable select(string sql) //Especially fails here
     {
         Connect();
         MySqlCommand cmd = new MySqlCommand(sql, conn);
         using (MySqlDataReader read = cmd.ExecuteReader())
         {
             if (read.HasRows){ Some Code... }
         }
         DisConnect();
     }
  }

1. Probable I/O race condition detected while copying memory, 2. There is an already open connection. 3. Connection must be valid and open. — fatih
– fatih, Commented Jan 21, 2015 at 7:53
Why do you use everything static? Give an instance per thread? — Sriram Sakthivel
– Sriram Sakthivel, Commented Jan 21, 2015 at 7:56
Current code with static connection, which gets accessed by multiple threads is a recipe for failure, multiple threads try using it in the command object and fails due to AV issue. You should preferably use a connection pool, which user defined size, and then have multiple threads wait for connection to be free and use, but pool with just one thread, you can better lock and work with it, it would serialize access of all threads — Mrinal Kamboj
– Mrinal Kamboj, Commented Jan 21, 2015 at 8:10

user2930590 · Accepted Answer · 2015-01-21 10:17:05Z

1

Note that MySqlConnection instance is not guaranteed to be thread safe. You should avoid using the same MySqlConnection in several threads at the same time. It is recommended to open a new connection per thread and to close it when the work is done. Actually, connections will not be created/disposed every time with the Pooling=true; connection string option - connections will be stored in the connection pool. This boosts performance greatly.

I also suggest to use the 'using' clause on creating a connection so that it will be automatically disposed/closed and returned to the connection pool.

answered Jan 21, 2015 at 10:17

user2930590

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

c# mysqlreader multithread fail

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related