9

I am having C# multi threading application and using MySQL with single connection to whole application. But when two or more thread try to access database at the same time, then i get below error :

There is already an open DataReader associated with this Connection which must be closed first.

My Connection code is below 

public static _connectionSetup = new MySqlConnection("Server=server ; Database=database;User ID=user;Password=pass;Pooling=true;");

and when i need to use connection i am using below code :- 

using (MySqlConnection connection =_connectionSetup )
{
    using (MySqlCommand command = new MySqlCommand("proc", connection))
    {
        ....
    }
}

I tried used pooling=true and i have create two separated connection as well for two different thread, but still i am getting above error.
Am I missing something?

How can I implement connection pool so that all thread will use separate connection and won't cause any issue?

VMAtm
  • 27,943
  • 17
  • 79
  • 125
Varun Jain
  • 495
  • 3
  • 9
  • 22
  • 10
    Don't make it static, create the connection in the `using` and you're all done. http://stackoverflow.com/questions/9705637/executereader-requires-an-open-and-available-connection-the-connections-curren/9707060#9707060 – Tim Schmelter Sep 28 '14 at 20:46
  • don't you think create connection all the time in using will cause cost, because getting connection again and again is very costly operation. – Varun Jain Sep 29 '14 at 19:28
  • No. Please read my answer in the link above, it'll explain it better than i could do here in the comment. – Tim Schmelter Sep 29 '14 at 19:33
  • Thanks For the link it was very helpful, i am new to C#, as per the link when we close connection it will back to ADO.NET connection pool instead of closing connection from server. i am not sure if i write `new MySqlConnection("Server=ser....` is it ADO.NET? (Am i using ADO.net in above code ? i don't know ) and if i make `_connectionSetup` instance variable then will connect again to database server or get connection from pool for second or further when i open connection using instance variable . – Varun Jain Sep 29 '14 at 20:14
  • It's all ADO.NET. But it depends if you use connection-pooling. And yes, by default pooling is enabled for MySql (for other dbms like Sql-Sever as well). It doesn't hurt to add `Pooling=True;` in the connection-string. But again, [that's the default behaviour](http://www.connectionstrings.com/mysql-connector-net-mysqlconnection/connection-pooling/). To cut a long story short, **pooling is enabled if you don't specify `pooling=false` in the connection string**. – Tim Schmelter Sep 29 '14 at 20:26
  • so in my application if i use `public MySqlConnection _connectionSetup = = new MySqlConnection("Server=" ` and `_connectionSetup.Open();` at number of place and in background worker thread, then it wont go to database server and try to connect again and again right? it will connect only once util application stop, please correct if i am wrong. – Varun Jain Sep 29 '14 at 20:34
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/62140/discussion-between-tim-schmelter-and-varun-jain). – Tim Schmelter Sep 29 '14 at 20:35
  • Hi Tim, Is below correct way to use static helper :- ` public static MySqlConnection getConnection() { MySqlConnection connection = new MySqlConnection(connectionString); connection.Open(); return connection; } using (MySqlConnection connection = ConnectionUtil.getConnection()) { ... }` – Varun Jain Oct 02 '14 at 11:33
  • Yes, that should be fine, even though i would name it `GetOpenConnection` ;) – Tim Schmelter Oct 02 '14 at 11:38
  • Thanks Tim for such a great help :) please write your answer – Varun Jain Oct 02 '14 at 12:31

1 Answers1

23

Pooling is turned on by default, so you don't need that connection string parameter.

Don't share MySqlConnection instances. That's it.

Pooling is not something you implement in your code, it's done for you by ADO.NET.

Max Toro
  • 28,282
  • 11
  • 76
  • 114
  • 4
    i am not using `ADO.NET` used own code to get the data and save the data,How can i make sure each background worker thread will work with separate connection and not give error mention above – Varun Jain Sep 29 '14 at 19:31