ADO.NET: Connection Pooling
This sample illustrates how to construct a pool of connections to a datasource. You will want to do this to deploy high-performance applications. In this example the pool is established in the connection string and managed automatically by the SqlConnection.
VB ConnectionPooling.aspx
[Run Sample] |
[View Source]
In this example we specify the pooling characteristics in the connection string when the SqlConnection is constructed, as shown in the following code sample. Please note: Pooling is implicit, you automatically get it unless you disable it. Therefore, "true" is the default for the pooling keyword (pooling=true).
Dim connString as String
' Specification in the connection string:
' Please note: Pooling is implicit, you automatically get it unless you disable it.
' Therefore, "true" is the default for the pooling keyword (pooling=true).
' Connection Reset: False
' Connection Lifetime: 5
' Enlist: true
' Min Pool Size: 1
' Max Pool Size: 50
connString = "server=(local)\VSdotNET;Trusted_Connection=yes;database=northwind;" & _
"connection reset=false;" & _
"connection lifetime=5;" & _
"enlist=true;" & _
"min pool size=1;" & _
"max pool size=50"
Dim myConnection1 as SqlConnection = new SqlConnection(connString)
Dim myConnection2 as SqlConnection = new SqlConnection(connString)
Dim myConnection3 as SqlConnection = new SqlConnection(connString)
Now we have code to use several of the connections in our pool. First, open two connections and return both of the connections to the pool. Then, open three connections from the pool and return all three connections to the pool.
' Open two connections. One is from the pool (see min pool size), the other is created.
Console.WriteLine ("Open two connections.")
myConnection1.Open()
myConnection2.Open()
' Now there are two connections in the pool that matches the connection string.
' Return the both connections to the pool.
Console.WriteLine ("Return both of the connections to the pool.")
myConnection1.Close()
myConnection2.Close()
' Get a connection out of the pool.
Console.WriteLine ("Open a connection from the pool.")
myConnection1.Open()
' Get a second connection out of the pool.
Console.WriteLine ("Open a second connection from the pool.")
myConnection2.Open()
' Open a third connection.
Console.WriteLine ("Open a third connection.")
myConnection3.Open()
' Return the all connections to the pool.
Console.WriteLine ("Return all three connections to the pool.")
myConnection1.Close()
myConnection2.Close()
myConnection3.Close()
The model for pooled connections is the similar to non-pooled connections. However, it is especially important to call Close when you finish with a pooled connection to release it back into the pool.