Database Connection Problem. Please Help

S

Sam

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
 
C

Chris

Sam said:
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
Click to expand...


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.
 
C

Cor Ligthert [MVP]

Sam,

I find it strange that it does 100 rows. The function is called and closes
and even disposes the connection immidiatly. How can it than after that read
100 rows?

Cor
 
S

Sam

Cor,

Sorry for the confusion, I have a windows service that first sleep for about
5 minutes then download all records that it needs to be processed into a
table. Then for each of these records, I have to process it (one by one) by
calling one of our class objects which then open database and pull out the
details of that record. What code fragment I posted is from the object class
that pulls out record details and does the process.

Chris who replied to this post suggested that I should maintain the
connection until all records are processed instead of open then closed for
each record. The problem I'm facing is that the object that does the
processing opens the database, create datareader and then close the
connection again. Unless I create my own data reader and connection and pass
this data reader from my windows service to this object, I can't really
maintain the same connection.

Regards,

Sam
 
S

Sam

Chris,

Thanks for your great suggestions. The problem I'm facing is that the
process that pulls details of each record is a class object which uses
datareader to pull data out from our database. I guess if I want to maintain
a connection till all records have been processed, I will need to create a
method which takes a data reader and maintain a connection myself
 
C

Cor Ligthert [MVP]

Sam,

What I find strange that it is a kind of inbetween function, it does
something but not complete, would it not better to understand if your method
would return a complete datarow (or whatever represtenting that) instead of
a datareader?

Cor
 
Top