VB.Net/SQL Server Timeout and Connection Pooling Problems

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 ADO.net side instead??  

Thanks so much in advanced!!
N-
TechQTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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



Moiz
0
TechQTAuthor Commented:
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)
        Console.WriteLine(TotalBaselineTable.Rows.Count)

        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)
            Try
                SqlDataAdapter.Fill(DSTotalSCOList)

            Catch ex As Exception
                Dim strexception As String = ex.Message
                If InStr(strexception, "'") Then
                    strexception = strexception.Replace("'", "''")
                End If
                Console.Write(strexception)
                'LOGERROR REFERENCE HERE
            Finally
                CnnSKyNet2.Close()
            End Try
            count = count + 1
         
            CnnSKyNet2.Close()
            CnnSKyNet2.Dispose()
            SqlDataAdapter = Nothing
            System.GC.WaitForPendingFinalizers()
        Next
        'Getting Contents of Dataset XML Check
        Dim xmlDoc As XmlDataDocument = New XmlDataDocument(DSTotalSCOList)
        DSTotalSCOList.WriteXml("C:\TotalSCOList.xml")
      End Sub
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

caball88Commented:
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.
0
TechQTAuthor Commented:
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 :-)

0
caball88Commented:
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.
0
caball88Commented:
here is an article about connection pooling and timing out that might or might not help you

http://weblogs.asp.net/angelsb/archive/2004/08/25/220333.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.

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.