0

I am doing some tests locally, because i have had some serious problems using Entity Framework on .NET (a lot of blocks, concurrency problems, etc), so i decided to start from the bottom and analyze the Ado.NET connections.

I am debugging my code and at the same time, i am checking the open connections with MySql Workbench.

The code i am debugging is the next one:

using (var con = new MySqlConnection(_connectionString))
        {
            con.Open();

            //this extension method is because i am using the Dapper Library
            var products = con.Query<Product>("Select productId, name from products");

            con.Close();
        }

So, the step by step with the connection state is:

con.Open();

After execute the open() method, the connection is open (Command:Sleep)

var products = con.Query<Product>("Select productId, name from products");

After execute the query, the connection is still open (logically). And finally i execute:

con.Close();

After that, the connection is still open in command:sleep. How can avoid that? Because it keeps the connection open until i kill the IIS Instance... It is not very good for concurrency... because if there is a lot of users retrieving data against the database, i don't want a lot of connections open if they are not executing anything against the database.

How can improve that?

Amit Joshi
  • 15,448
  • 21
  • 77
  • 141
ascherman
  • 1,762
  • 2
  • 20
  • 41
  • 1
    MySQL connector has that behavior for better performance. These connections are sleeping in case you need them again. It is very expensive to remove and create new connections for each request. – Barto May 07 '15 at 22:26
  • 1
    @ArielScherman keeping a connection open helps a lot to reuse a connection without the need to recreate another one for every user. Your code is fine because it releases the NET MySqlConnection object but ADO.NET Connection Pooling keeps the unmanaged connection resource open and ready to serve another user without creating another object with another unmanaged resource. The important thing is the using statement (you could also remove the call to Close because the end using brace closes it) – Steve May 07 '15 at 22:31

0 Answers0