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_Applicatio nDetails", _
"@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(B yVal 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.Comman dText = sStoredProc
aCommand.Comman dType = CommandType.Sto redProcedure
aCommand.Connec tion = aConnection
aCommand.Parame ters.Add(sVarNa me, iInt)
Return(aCommand .ExecuteReader( CommandBehavior .CloseConnectio n))
Catch e As Exception
Throw New Exception(e.Mes sage, e)
Finally
If aConnection.Sta te.Open = True Then
aConnection.Clo se()
aCommand.Dispos e()
End If
End Try
End Function
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_Applicatio nDetails", _
"@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(B yVal 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.Comman dText = sStoredProc
aCommand.Comman dType = CommandType.Sto redProcedure
aCommand.Connec tion = aConnection
aCommand.Parame ters.Add(sVarNa me, iInt)
Return(aCommand .ExecuteReader( CommandBehavior .CloseConnectio n))
Catch e As Exception
Throw New Exception(e.Mes sage, e)
Finally
If aConnection.Sta te.Open = True Then
aConnection.Clo se()
aCommand.Dispos e()
End If
End Try
End Function
Comment