CODEWITHSUNDEEP
c#sql-serversqldatareader
SeaDrive
SeaDrive

Reputation: 4292

SqlDataReader - result set cached?

When you use a SqlDataReader, is the return set completely determined by the ExecuteReader step, or can you influence what you get by writing to the source table(s) while reading? Here is an example in very rough pseudo code.

sc = new SqlCommand("select * from people order by last, first",db) ;
sdr = sc.ExecuteReader() ;

while (sdr.read())
{
    l = (string) sdr["last"] ;
    k = (string) sdr["key"] ;
    if (l.Equals("Adams")) 
    {
       sc2 = new SqlCommand("update people set last = @nm where key = @key") ;
       sc2.Parameters.Add(new SqlParameter("@nm", "Ziegler"));
       sc2.Parameters.Add(new SqlParameter("@key", k));
       sc2.ExecuteNonQuery() ;
    }
}

I've seen a lot of bad errors in other environments caused by writing to the table you are reading. Here record k gets bumped from the top of the list (Adams) to the bottom (Ziegler). I've assumed (ha!) that SqlDataReader is immune. True? False?

Upvotes: 2

Views: 2908

Answers (4)

n8wrl
n8wrl

Reputation: 19765

If you want to do updates while you're iterating on the query results you could read it all into a DataSet.

I know you didn't ask about this, and I also know this is pseudo-code, but be sure to wrap your sc, sdr, and sc2 variables in using () statements to ensure they're disposed properly.

Upvotes: 0

Matt
Matt

Reputation: 41832

If you want to assume that the read data is not altered by those updates, could you read the data into a temporary object container, and then after all the reading is done, then do your updates? It would make the issue moot.

Of course, I did find the question interesting from a "how does this really work" standpoint.

Upvotes: 0

Brettski
Brettski

Reputation: 20101

One issue I see is that when the reader is open it owns the database connection, nothing else can use it while the reader is open. So the only way possible to do this is using a different database connection, and still it would depend on transaction level

Upvotes: 1

Joel Coehoorn
Joel Coehoorn

Reputation: 416083

It depends on your transaction isolation level or other locking hints, but iirc by default reading from a table in sql server locks those records, and therefore the code you posted will either deadlock (sc2 will eventually timeout) or the updates will go into the the transaction log and none will be written until your reader is finished. I don't remember which off the top of my head.

Upvotes: 4

Related Questions