.Net Connection Pooling

I have a windows service that runs on about 25 client machines.  The service reads local text files and when it comes across records its configured to look for, it performs a SQL insert.  There are several functions and subroutines in the service that do other SQL updates and selects.

My problem is that on my SQL Server, I am looking in the Current Activity --> Processes and see about 500 processes under the SQL account set up for these windows services.  I thought that by default I was using connection pooling, maybe I am but why would I have 500 or so processes under this account?

When I look at the "Login Time" in the SQL Current Activity --> Processes form all of the Login Times for these processes in question are from within the last hour, nothing is left over and seeming abandoned.

This is how I make my connections in the Windows Service:

I have a Module in which I declare my global variables.  I use this variable for my connection string:

Public sConnStr As String = "Provider=SQLOLEDB;data source=192.168.1.1;initial catalog=MyDatabase;user id=MyUser;Password=MyPassword"

Whenever I have to make a SQL connection, I do so like this:

Public Sub Declare_Client_Version(ByVal iEventID As Integer, ByVal sVersion As String, ByVal dtVersionDate As DateTime)
        Dim sSQL As String
        sSQL = "UPDATE t_LogReader_Config SET " & _
                    "Client_Version = '" & sVersion & "'" & _
                    ", Client_Version_Date = '" & dtVersionDate & "' " & _
                    "WHERE EventID = " & iEventID
        Dim oDC As New OleDbConnection(sConnStr)
        oDC.Open()
        Dim cmd As New OleDbCommand(sSQL, oDC)
        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception

        End Try
        oDC.Close()
    End Sub

I cannot tell if connection pooling is working normally or if the 500 connections will hose up my SQL Server.  Any advice?
LVL 7
ABaruhAsked:
Who is Participating?
 
manlimnagCommented:
Yes certainly SqlClient will be better choice. Alter the code slightly open the connection inside try block add finally clause and inside that close the connection and instead of creating new connection always preserve the same instance. Create new instance at initialization event.



 
0
 
ABaruhAuthor Commented:
Also, can anyone recommend using the System.Data.SqlClient over System.Data.OleDb?  I've heard that the SqlClient method bypasses the OLE DB layer and is better for performance but is different in terms of how I go about working with connection pools.
0
 
ABaruhAuthor Commented:
manlimnag, can you show an example?
0
 
ABaruhAuthor Commented:
Am I correct in that this is what you suggest:

In my module:

Public oDC As System.Data.SqlClient.SqlConnection

Then in my code:

Public Sub Declare_Client_Version(ByVal iEventID As Integer, ByVal sVersion As String, ByVal dtVersionDate As DateTime)
        Dim sSQL As String
        sSQL = "UPDATE t_LogReader_Config SET " & _
                    "Client_Version = '" & sVersion & "'" & _
                    ", Client_Version_Date = '" & dtVersionDate & "' " & _
                    "WHERE EventID = " & iEventID
        oDC = New SqlConnection(sConnStr)
        Try
            oDC.Open()
            Dim cmd As New OleDbCommand(sSQL, oDC)
            cmd.ExecuteNonQuery()
        Catch ex As Exception

        Finally
            oDC.Close()
        End Try
    End Sub
0
 
gdexterCommented:
manlimnag

I agree with the Try .. Finally construct.
However why would you preserve the instance of the connection.
The idea behind pooling is that a call to "new SQLConnection()"
does not actually create a new connection unless the pool needs to create one... behind the scenes the pool is just handing the application an existing connection.

I would suggest setting the "Max Pool Size" and "Connection Lifetime" attributes on the connection string to an explicit value and then taking a look at the pool to see if the values have an effect on the connections  ABaruh  is seeing.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.