Connection Weirdness
- From: "David Wimbush" <david_wimbush@xxxxxxxxxxx>
- Date: 15 May 2006 01:51:08 -0700
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
.
- Follow-Ups:
- Re: Connection Weirdness
- From: Miha Markic [MVP C#]
- Re: Connection Weirdness
- Prev by Date: From QueryDef to plain SQL text
- Next by Date: Crystal Report HTML problem
- Previous by thread: From QueryDef to plain SQL text
- Next by thread: Re: Connection Weirdness
- Index(es):
Relevant Pages
|
