[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

.Net Connection Pooling

Posted on 2005-05-05
5
Medium Priority
?
285 Views
Last Modified: 2010-04-23
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?
0
Comment
Question by:ABaruh
  • 3
5 Comments
 
LVL 7

Author Comment

by:ABaruh
ID: 13937195
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
 
LVL 3

Accepted Solution

by:
manlimnag earned 1400 total points
ID: 13937483
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
 
LVL 7

Author Comment

by:ABaruh
ID: 13937528
manlimnag, can you show an example?
0
 
LVL 7

Author Comment

by:ABaruh
ID: 13937642
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
 
LVL 4

Assisted Solution

by:gdexter
gdexter earned 600 total points
ID: 13937678
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month19 days, 4 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question