Re: Database Connection Problem. Please Help

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Sam wrote:
Hi all,

I have a process which first pulls one time all application IDs from a database and stores them in a table(this process works fine everytime). I then loop through the table, one at a time, and use application id to pull details info and process it. For example, if I have 500 records in my table, then I would have to open database 500 times. Also between processing each record, my process sleep 3 seconds.

The problem is that after processing about 100 records, using SqlDataReader, my database connection will starts failing. When I traced the error in my try catch clause, the exception message just say connection fails to open database. I also found that all the subsequent connection also failed to open unless I stop my window service and restart it.

Would some one please give me a hand? I've been trying to find a way to pull all details for all records at once, instead of one at a time but I have not been able to do that.

Thanks in advance for everyone's suggestions

Regards,

Sam


Here is part of my process that pull details of an application base on applicationID
===============================
Dim appReader As SqlDataReader

appReader = GetDataReader("user_ApplicationDetails", _
"@appID", appID)

Do While appReader.Read ' Read applicat

'process data
Loop

If Not appReader Is Nothing Then appReader.Close()

================================
Here is my function that returns sqldataReader

Public Function GetDataReader(ByVal sStoredProc As String, ByVal sVarName As String, ByVal iInt As Integer) As SqlDataReader

Dim aCommand As New SqlCommand

Try

aConnection = OpenConnection(aConnection) ' OpenConnection returns sqlconnection

aCommand.CommandText = sStoredProc
aCommand.CommandType = CommandType.StoredProcedure
aCommand.Connection = aConnection

aCommand.Parameters.Add(sVarName, iInt)

Return(aCommand.ExecuteReader(CommandBehavior.CloseConnection))
Catch e As Exception
Throw New Exception(e.Message, e)
Finally
If aConnection.State.Open = True Then
aConnection.Close()
aCommand.Dispose()
End If
End Try

End Function




Don't open the connection 500 times. Open it once and keep it open, then just use that same connection over and over oand over. Also, you can use the same command object over and over too.
.

Relevant Pages

  • Re: Database Connection Problem. Please Help
    ... connection again. ... pull details info and process it. ... then I would have to open database 500 times. ... SqlDataReader, my database connection will starts failing. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: what is the best way to connect to a sql database?
    ... The most common way to connect to a database is to use the "SqlConnection", ... "SqlCommand" and "SqlDataReader" classes. ... to keep the connection to the database open the whole time. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Database Connection Problem. Please Help
    ... database and stores them in a table. ... The problem is that after processing about 100 records, using SqlDataReader, ... my database connection will starts failing. ... I've been trying to find a way to pull ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Database Connection Problem. Please Help
    ... method which takes a data reader and maintain a connection myself ... database and stores them in a table. ... SqlDataReader, my database connection will starts failing. ... Here is part of my process that pull details of an application base on ...
    (microsoft.public.dotnet.languages.vb)
  • Re: SqlDataReader problem... [Code]
    ... The SqlDataReader is a low level provider that works by retrieving one row ... at a time on a active connection to the database. ... SqlDataReader to work with really large resultsets, ... the associated Connection object is closed ...
    (microsoft.public.dotnet.languages.csharp)