C# MySQL Connection Pooling

Question

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?

Don't make it static, create the connection in the using and you're all done. stackoverflow.com/questions/9705637/… — Tim Schmelter
– Tim Schmelter, Commented Sep 28, 2014 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
– Varun Jain, Commented Sep 29, 2014 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
– Tim Schmelter, Commented Sep 29, 2014 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
– Varun Jain, Commented Sep 29, 2014 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. To cut a long story short, pooling is enabled if you don't specify pooling=false in the connection string. — Tim Schmelter
– Tim Schmelter, Commented Sep 29, 2014 at 20:26

Max Toro · Accepted Answer · 2014-09-28 20:47:20Z

24

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.

answered Sep 28, 2014 at 20:47

Max Toro

28.7k11 gold badges81 silver badges116 bronze badges

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

1 Comment

Varun Jain Over a year ago

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

Collectives™ on Stack Overflow

C# MySQL Connection Pooling

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related