Database Connection Problem. Please Help

[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_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

[Chris]

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_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

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.

[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

[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













"Sam" <as****@yahoo.c omwrote in message
news:u1******** ******@TK2MSFTN GP03.phx.gbl...

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

[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

"Chris" <[e-mail] te in message
news:OX******** ******@TK2MSFTN GP05.phx.gbl...

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
SqlDataReade r, 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
applicationI D
============== =============== ==
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) '
OpenConnecti on returns sqlconnection

aCommand.Comman dText = sStoredProc
aCommand.Comman dType = CommandType.Sto redProcedure
aCommand.Connec tion = aConnection

aCommand.Parame ters.Add(sVarNa me, iInt)
Return(aComman d.ExecuteReader (CommandBehavio r.CloseConnecti on))
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

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.

[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

"Sam" <as****@yahoo.c omschreef in bericht
news:O8******** ******@TK2MSFTN GP05.phx.gbl...

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













"Sam" <as****@yahoo.c omwrote in message
news:u1******** ******@TK2MSFTN GP03.phx.gbl...

>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
SqlDataReade r, 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
applicationI D
============== =============== ==
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(aComman d.ExecuteReader (CommandBehavio r.CloseConnecti on))
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