Everything runs find under normal conditions. I have a query that takes 5 seconds (I've tried to cut these down as much as possible) to retrieve and return the results to the user. I'm going to assume there are impatient users out there who will hit refresh
or select another link to go to a different page during this load. That's when all hell breaks loose. Until I run an iisreset or code recompile, it seems that *all* connections to the database fail, not just the ones for the current user. A page refresh or
trying to go to a different page still results in connection errors. The current solution is not acceptable, especially since this is running in a fairly extensive SharePoint environment.
I've added "MultipleActiveResultSets=True" to the querystring, but the results have been the same. I've received the same results when trying to close the connection before the initial open, as well. The function above is running within a non-static object,
but all calls to this are occurring through a static DAL.
I've been using the datareader over datasets for a while due to their speed, but this might force me to go back to datasets.
lastly I don't think this is the answer to your issue - but "commandbehavior.closeconnection" automatically closes the connection when you close the datareader. I am assuming this
to be pseudocode:
if reader != null
{ reader.close }
connection.close()
Sadly, modifications to the connection string related to connection pooling don't seem to make a difference.
All of my calls using the data reader close the reader and connection within the finally block of the try/catch. I've also added the commandbehavior noted above during initialization of the object, to no avail.
The problem seems to be that the code is just never called when interrupted by the user trying to use another instance of the datareader. In the interim I've tried to improve my SQL some and implement paging, but there's still a small gap where this issue
can occur.
chuck4jmu
Member
2 Points
2 Posts
SqlDataReader related issue
Mar 02, 2012 02:50 PM|LINK
My application is running fine under normal conditions and my SQL connections (SQL 2008) with the datareader are using the following format:
try { connection.open() ... reader = command.executereader (commandbehavior.closeconnection). datatable.load(reader) } finally{ if reader != null { reader.close } connection.close() }Everything runs find under normal conditions. I have a query that takes 5 seconds (I've tried to cut these down as much as possible) to retrieve and return the results to the user. I'm going to assume there are impatient users out there who will hit refresh or select another link to go to a different page during this load. That's when all hell breaks loose. Until I run an iisreset or code recompile, it seems that *all* connections to the database fail, not just the ones for the current user. A page refresh or trying to go to a different page still results in connection errors. The current solution is not acceptable, especially since this is running in a fairly extensive SharePoint environment.
I've added "MultipleActiveResultSets=True" to the querystring, but the results have been the same. I've received the same results when trying to close the connection before the initial open, as well. The function above is running within a non-static object, but all calls to this are occurring through a static DAL.
I've been using the datareader over datasets for a while due to their speed, but this might force me to go back to datasets.
Any thoughts would be appreciated.
Echo88
Participant
1474 Points
370 Posts
Re: SqlDataReader related issue
Mar 03, 2012 06:47 PM|LINK
You might want to look into connection pooling - http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx there is a setting
lastly I don't think this is the answer to your issue - but "commandbehavior.closeconnection" automatically closes the connection when you close the datareader. I am assuming this to be pseudocode:
if reader != null { reader.close } connection.close()chuck4jmu
Member
2 Points
2 Posts
Re: SqlDataReader related issue
Mar 12, 2012 11:40 AM|LINK
Sadly, modifications to the connection string related to connection pooling don't seem to make a difference.
All of my calls using the data reader close the reader and connection within the finally block of the try/catch. I've also added the commandbehavior noted above during initialization of the object, to no avail.
The problem seems to be that the code is just never called when interrupted by the user trying to use another instance of the datareader. In the interim I've tried to improve my SQL some and implement paging, but there's still a small gap where this issue can occur.