The age old question. If you ever saw one of my TechEd sessions or WebCasts, you know that I am a big fan of using the DataReader, especially when you are doing Web DataBinding. I am all about the firehose, forward only cursor. Today I actually replaced a DataReader with a DataSet, so I think that I need to tell the world the story. J
So my app that runs each Sunday morning to get data over HTTP and regex out stuff was bogging down. What happens is that I have a DataReader on the client that grabs the URLs, RegEx patterns, etc from a table for the main application “loop” to process the URLs and save the data to the database. There are about 30,000 records stuffed into the datareader and the stored procedure that powers it has to do a join to the table I am adding data into on each iteration of the loop to make sure that if the process stops and restarts, I don’t reprocess any duplicate URLs.
So all of a sudden (this code has been in production for 15 months, and on .NET 1.1 for 4 months) I started to get timeouts when I read data from the DataReader. Randomly this would happen, maybe once ever few weeks. I never really tracked it down. So this week I would run the process and every 10th record would cause a timeout. It was a timeout when I tried to read data from the next row in the DataReader, on the 10 row. I start and stop and this happened a zillion times. I spent a few minutes playing with some settings, etc, but more of the same. Oddly enough, setting the command behavior of the command that filled the reader to SequentialAccess did not even let me read data from the first record, it returned an error saying that it can only look at data starting at the 10th row.
I have not discovered the problem here, but it must have something to do with the buffer, I must have been stuffing way too much data in there. So I said DUH, let’s use a DataSet. Well I never looped through a DataTable before, so here is how it goes:
Private void DataTableLoop (DataTable dt) {
foreach (DataRow dr in dt.Rows) {
foreach (DataColumn dc in dt.Columns) {
Response.Write (dr[dc] +”
}