|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Database Connection Problem. Please HelpI 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 Sam wrote:
Show quoteHide quote > Hi all, Don't open the connection 500 times. Open it once and keep it open, > > 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 > > then just use that same connection over and over oand over. Also, you can use the same command object over and over too. 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 Show quoteHide quote "Chris" <no@spam.com> wrote in message news:OXfDimCqGHA.4996@TK2MSFTNGP05.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 >> 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. 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 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 Show quoteHide quote "Sam" <ask***@yahoo.com> wrote in message news:u1mixn7pGHA.3584@TK2MSFTNGP03.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_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 > 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 Show quoteHide quote "Sam" <ask***@yahoo.com> schreef in bericht news:O8zdjhdqGHA.4760@TK2MSFTNGP05.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" <ask***@yahoo.com> wrote in message > news:u1mixn7pGHA.3584@TK2MSFTNGP03.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_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 >> > > 
Other interesting topics
Resume next in VB.NET ?
C.NET VS VB6 Using A Bound Dataset to insert new ddl selectedindexchanged not firing on first item - I'm using a dataset to populate the ddl Seeking Design Advice What's wrong with the combobox in grid object reference not set to an instance of an object error Why won't DateTimePicker work? Re: Strict type for Hashtable? Writign a routine to automatically backup under XP |
|||||||||||||||||||||||
