SQL Server connection pooling

  • Thread starter Thread starter fniles
  • Start date Start date
F

fniles

I am using VB.NET 2003 and SQL 2005.
To use connection pooling and avoid the error "There is already an open
DataReader associated with this Connection which must be closed first." , I
understand that I want to release/close connections in a timely fashion.
What I do is I declare the SqlClient.SqlConnection variable as a local
variable inside my subroutine (instead of a global variable). Right before I
Fill the dataset I open the connection, and right after I fill the dataset I
close the connection, like shown below. Do I do it correctly ? Thank you.

sub MySub
Dim adoCon As SqlClient.SqlConnection
Dim cmdSQL As SqlClient.SqlCommand
Dim dsSQL As DataSet
Dim daSQL As SqlClient.SqlDataAdapter

adoCon = New SqlClient.SqlConnection
With adoCon
.ConnectionString = DB_Path
.Open() '--> open connection
End With
cmdSQL = New SqlClient.SqlCommand
With cmdSQL
.Connection = adoCon
.CommandText = sSQL
End With
daSQL = New SqlClient.SqlDataAdapter
dsSQL = New DataSet
daSQL.SelectCommand = m_cmdSQL
daSQL.Fill(m_dsSQL) '--> fill dataset
If Not adoCon Is Nothing Then
adoCon.Close() '--> close connection
adoCon = Nothing
End If
 
Thank you.
Does it mean that if I set MultipleActiveResultSets to true, I can have more
than 1 DataReader open in the connection ?

Regarding using a local SQLConnection variable (instead of using a global
one) and set it to new, open and close the connection everytime I fill a
dataset, is it correct ?
Or, do I need to use a global SQLConnection variable
1. new and open it on form_load (and do not close this one until
form_Closed)
2. and in each subroutine use this global SQLConnection variable but new,
open and close the connection in this subroutine ?
 
Also, when I set my connection string to the following
Data Source=mySQL2005;Initial Catalog=myDB;User
ID=myUserID;Password=myPass;MultipleActiveResultSets=True
I got the error "Keyword not supported: 'multipleactiveresultsets'.

Dim adoCon As SqlClient.SqlConnection
With adoCon
.ConnectionString = DB_Path -->>error here
.Open()
End With
 
Regarding MultipleActiveResultSets, that's right, set it to True and your
may be able to have more than a DataReader open at the same time, just
remember to close them anyway so the system behaves nicely with resources
like memory usage, processor, network and so on.

Regarding the issue of using a local or global connection, it will depend on
your application, but if the application is moderate or intensive in its use
of database, then I will recomend to use a global connection.

You may want to take a look at the Database Application Block from the
Microsoft Enterprise Library 3.0 (it's free -$0.00- !!!). It makes things a
little bit easier to access the database and you don't have to control the
connection state yourself (it handles the connection state, a connection
pool, and so on):

http://msdn2.microsoft.com/en-us/library/aa480453.aspx

Hope this helps,

Carlos Camargo
 
Thank you.
If I use a global variable, does it mean that
1. I set it to new and open it on main form_load (and do not close this one
until form_Closed)
2. and in each subroutine use this global SQLConnection variable but set it
to new, open and close this connection in this subroutine ?
or,
simply in each subroutine use this global SQLConnection variable but set it
to new, open and close this connection in this subroutine, and do not need
to open it in the main form_load ?
 
fniles,

MARS is a .Net 2 feature. You would need to use VS 2005 to have access to it.

I will also mention that using MARS is more involved than just turning it on
in the connection string. MARS has all sorts of ramifications that you need
to understand really well before using it.

Kerry Moorman
 
Open the connection in your application's entry point, for example in the
Load event for the main form, and store the resulting connection object in a
global variable.

Close it when that same form gets closed.

In individual procedures where you go for some info from the database or
update data there, just send the querys and get the results, don't open the
connection and close it again, because doing that (open and close
connections all the time) will make your application somehow slow because
opening the connection involves a relatively-heavy process for both, the
server computer and the client computer. Once a connection gets stablished
things will go faster.

Best regards,

Carlos Camargo
 
I am using VB.NET 2003, so MultipleActiveResultSets is not available (it's
available in 2005 according to another user here).
In the individual procedures if I do not open and close the connection, I
will get the error "There is already an open DataReader associated with this
Connection which must be closed first."
You mentioned that if I open and close the connection the application will
be slow, but I thought with connection pooling this won't be slow, am I
wrong ?
 
Thanks Kerry,
For awhile there I thought that there was no rational people lurking on
this list.
I completely agree. Use MARS at your own risk. It is fraught with issues and
unintended side-effects. While it might mask the issue of an unclosed
operation on a connection, it simply delays the inevitable.
The ultimate solution to the problem is understanding how the
instantiation and tear-down of Connections works. You simply must close
connections when they are in scope--you can't depend on the finalizer or
garbage collector to do so.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
Back
Top