.net connection pooling

I don't get what is the syntax difference between regular connection and connection poo开发者_StackOverflow中文版l.

When I'm using the using key such as:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    command.ExecuteNonQuery();
}

Is this the way to perform a connection pool?

---

You can read about connection pooling here.

Basically, as long as the connection string is the same (including case), connections will be taken from the same connection pool.

---

You do not control the connection pool with connections but with the connection string. Most ADO providers use pooling per default.

The using statement is used to call the Dispose method of the object (in this case the connection class). By doing so, the connection is either returned to the pool or being disconnected depending of the connection string configuration.

You should also be aware of that connections are not returned to the pool directly if distributed transactions are being used (TransactionScope in .Net 4). The connections are returned when the transaction have been completed/rolled back.

If you are not using using, you should make sure that you call Connection.Close() as soon as possible. Especially if your application is under some form of load.

---

The management of connection pool is abstracted away from you using SqlConnection like in the above. By default in ADO.NET connection pool is turn on and you can further control that such as turning it off or controlling the pool size in the connection string e.g.

Turn off

Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Pooling=false;

or controlling min and max

Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Min Pool Size=5; Max Pool Size=20;

More details explanation and way to verify the pool http://www.codeproject.com/KB/dotnet/ADONET_ConnectionPooling.aspx

---

as far as i know,

connection pooling is managed by ado.net client, since making connections to db is costly operation. ado.net makes pool of connections and whenever you need a connection it tries to give it from pool. even if you say to client code to close connection, ado.net hold that connection for later use. you dont manage the pooling

connection pool is told in web.config file of application. when you use using statements , you tell that object should be disposed end of using.

---

The SQL connection defaul is connection pooling. (Max Pool Size=100)

You can config you connection pool from connection string.

You can find more informamtion about connection string from here.