VB.Net/SQL Server Timeout and Connection Pooling Problems

Posted on 2004-11-22
Last Modified: 2008-03-17
I have a VB.Net Console application that has 4 modules.  Each module is used to populate a public dataset.  When I go to populate the last dataset I loop through the previous dataset and do a new sql call for each record in the previous dataset.  Bear with me:).  I hit up to 97 records and run into one of two problems.  The Time Out connection pooling problem or Server time out problem.  I have read through countless threads and have tried everything so I leave it to the experts.  

What I have tried
1. Setting the Connection lifetime, Connection Max Pool, and Time out
2. Closing and setting the connection with in the loop to nothing with in the loop (which doesn't seem to release connections to the pool)
3. Creating a slightly different connection to use in the last module.

I would like to know the best practices for creating data structures like this and how to avoid these connections problem.  Would it be better to use a Stored Proc or create a relation between tables on the side instead??  

Thanks so much in advanced!!
Question by:TechQT
    LVL 4

    Expert Comment

    It would be nice to see some of your code and the error your receiving....

    LVL 8

    Expert Comment


    Author Comment

    Thanks for all your help I will review this article asap!  Here is a little bit of code.

    DSTotalBaselineList= previously filled dataset that contains approx 442 baselineID's

      Dim DR As DataRow = DSTotalBaselineList.Tables(0).Rows(0)
            Dim count As Integer = 0
            Dim strToday As String = Now
            Dim TotalBaselineTable As DataTable
            TotalBaselineTable = DSTotalBaselineList.Tables(0)

            For Each DR In DSTotalBaselineList.Tables(0).Rows
                Dim CnnSKyNet2 As New SqlConnection
                CnnSKyNet2.ConnectionString = (loadconfig("CnnSkyNet2"))
                If Not CnnSKyNet2 Is Nothing AndAlso ((CnnSKyNet2.State And ConnectionState.Open) = ConnectionState.Open) Then
                    CnnSKyNet2.Close() '//Close open database connection
                    Throw New System.Exception("Could not get new sql connection****************************")
                End If
                'CnnSkynet.Open() '//Open Database Connection
                Dim SqlDataAdapter = New SqlDataAdapter("SELECT BaselineID,ActivityID, ActivityNumber from Activities where BaselineID ='" & TotalBaselineTable.Rows(count)("BaselineID") & "'", CnnSKyNet2)

                Catch ex As Exception
                    Dim strexception As String = ex.Message
                    If InStr(strexception, "'") Then
                        strexception = strexception.Replace("'", "''")
                    End If
                    'LOGERROR REFERENCE HERE
                End Try
                count = count + 1
                SqlDataAdapter = Nothing
            'Getting Contents of Dataset XML Check
            Dim xmlDoc As XmlDataDocument = New XmlDataDocument(DSTotalSCOList)
          End Sub
    LVL 9

    Expert Comment

    first off you don't need to open the sql connection for a data adapter. the "Fill" method will automatically open and close the connection for you. secondly looking at your code you instantiate the sqlconnection for every row. is there a reason why you are doing that? i am assume that loadconfig comes back with some sort of dynamic sqlconnection string. also i would opt to use stored procedures instead as it provides a little more scalability and more security for your database.

    Author Comment

    I was not instantiating the connection for every row but at this point I was reaching to see what would fix it.  I will transform the queries into SP but will that solve this connection pooling issue.  Is there a chance that this has to do with the actual SQL server?

    Thanks for your help :-)

    LVL 9

    Expert Comment

    it doesn't seem likely to be the SQL server, are you running an instance of SQL on the server or a cluster? i've had issues for regular ado where a cluster would timeout. but again like i said do not open and close the connection in code. just use the fill method as ado is suppose to handle the pooling.
    LVL 9

    Accepted Solution

    here is an article about connection pooling and timing out that might or might not help you

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
    Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now