Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VB.Net/SQL Server Timeout and Connection Pooling Problems

Posted on 2004-11-22
10
Medium Priority
?
605 Views
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 ADO.net side instead??  

Thanks so much in advanced!!
N-
0
Comment
Question by:TechQT
7 Comments
 
LVL 4

Expert Comment

by:Moizsaif123
ID: 12649503
It would be nice to see some of your code and the error your receiving....



Moiz
0
 

Author Comment

by:TechQT
ID: 12655870
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 9

Expert Comment

by:caball88
ID: 12666980
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
 

Author Comment

by:TechQT
ID: 12667268
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
 
LVL 9

Expert Comment

by:caball88
ID: 12667873
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
 
LVL 9

Accepted Solution

by:
caball88 earned 1000 total points
ID: 12667949
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Screencast - Getting to Know the Pipeline
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…
Suggested Courses
Course of the Month10 days, 13 hours left to enroll

571 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