Connection Weirdness

[David Wimbush]

I have an application that follows this pattern every time it needs to
interact with SQL Server:
1. Open a connection with a fixed connection string
2. Execute query
3. Close connection
I'm expecting connection pooling to give me the same connection each
time.

Two odd things are happening:

1. The app generally has about 10 connections open, even though I
always open a connection just in time, read any results out and close
it again straight away. Why so many? Each class fetches its own data
from the database but collections are populated in one go using a
DataReader (which I do explicitly close straight away). There is a fair
amount of database interaction going on but I would expect one
connection to handle it. Is there a delay between returning a
connection to the pool and its being available for re-use?

2. Our SQL server has several databases and sometimes I call a stored
procedure in one database, sometimes in another. I always use the exact
same connection string and set the SqlCommand's CommandText using the
format <database-name>.dbo.<stored-procedure-name>. But, while nearly
all of the 10-odd connections are to the database specified in the
connection string, 1 is always to a different database (the one last
stored procedure it called is in). I set up the SqlCommand, set its
CommandText, open the connection and put it in the SqlCommand's
Connection property. Can it be that ADO sees the two databases are
different and changes my connection?

This is starting to get on my nerves a bit. Does anyone have any ideas,
please?

Tech details:
SQL Server 2000 Standard Edition SP4.
App is a Windows Service written in VB.Net 2003. Works on a timer about
every 20 seconds.
Connection string is stored in app.config:
"server=X;database=Y;Integrated Security=SSPI;application name=Z"

Here's a sample of my data access code:

My collection class:

Public Sub Populate()
Dim oPrm As SqlClient.SqlParameter
Dim oCmd As New SqlClient.SqlCommand
Dim oReader As SqlClient.SqlDataReader

Try
With oCmd
.CommandText = "MyDB.dbo.spFaxesGet"
.CommandType = CommandType.StoredProcedure
End With
oReader = cDBLayer.getPopulatedReaderFromCommand(oCmd)
LoadData(oReader)
Catch ex As Exception
Throw
Finally
oPrm = Nothing
oCmd = Nothing
End Try
End Sub

Public Sub LoadData(ByRef Reader As SqlDataReader)
Try
Do While Reader.Read
'Add new collection item and populate its properties
from the Reader's row
Loop
Catch ex As Exception
Throw
Finally
cDBLayer.CloseDataReader(Reader)
End Try
End Sub

cDBLayer (utility class):

Public Overloads Shared Function
getPopulatedReaderFromCommand(ByRef Command As SqlCommand) As
SqlDataReader
Dim oReader As SqlClient.SqlDataReader

Try
Command.Connection = getConnection()
oReader =
Command.ExecuteReader(CommandBehavior.CloseConnection)
Catch sqlex As SqlException
Throw New SQLCmdException(sqlex, Command)
Catch ex As Exception
Throw
End Try
Return oReader
End Function

Public Shared Function getConnection() As SqlConnection
Try
Dim oConn As SqlClient.SqlConnection = New
SqlClient.SqlConnection(AppSettings(ConnectionStringKey))
oConn.Open()
Return oConn
Catch oE As Exception
Throw New Exception("Connection with database could not be
established", oE)
End Try

End Function

Public Shared Sub closeConnection(ByRef Conn As SqlConnection)
Try
If Not Conn Is Nothing Then
If Conn.State <> ConnectionState.Closed Then
Conn.Close()
Conn.Dispose()
End If
End If
Catch e As Exception
Throw New Exception("Connection with database could not be
broken", e)
End Try
End Sub

 

[Miha Markic [MVP C#]]

Hi David,

Regarding 1.
1. Are you sure you are closing connections?
2. Is your app multithreaded?
3. Do you exlictly set connection pool size?

 

[David Wimbush]

Hi Miha,

Thanks for taking an interest.

1. Yes. I've stepped through it and it definitely calls the
DataReader's Close.
2. Yes and no. My business object code does no threading but this app
is running under a service. Apparently, each time the timer fires my
code off it is in a separate thread from the previous timer event. But
interestingly the connection pool seems to be at the Service app level
as some of these connections were logged in an hour ago and yet
executed their last batch very recently.
3. No. The connection string is as I quoted apart from I changed the
names for readability. (And security!)

 

[Miha Markic [MVP C#]]

Hi Miha,

Thanks for taking an interest.

1. Yes. I've stepped through it and it definitely calls the
DataReader's Close.
2. Yes and no. My business object code does no threading but this app
is running under a service. Apparently, each time the timer fires my
code off it is in a separate thread from the previous timer event.

Yep, you are using System.Threading.Timer, right.

But

interestingly the connection pool seems to be at the Service app level
as some of these connections were logged in an hour ago and yet
executed their last batch very recently.

From docs:
If MinPoolSize is either not specified in the connection string or is
specified as zero, the connections in the pool will be closed after a period
of inactivity. However, if the specified MinPoolSize is greater than zero,
the connection pool is not destroyed until the AppDomain is unloaded and the
process ends. Maintenance of inactive or empty pools involves minimal system
overhead.

Perhaps you are seeing connections due to that period of time. Are your
thread running concurently. That would explain why more connections are
being created. I assume you connections (since they are using integrated
security) are opened using same credentials.
Or that number derives from the fact that your Timer uses 10 threads from
ThreadPool or something in that direction.
Try setting MaxPoolSize to smaller size and see what happens (you'll get an
exception probably).

3. No. The connection string is as I quoted apart from I changed the
names for readability. (And security!)

No, no you have to show us the real one (just kidding).