SqlDataReader - 结果集缓存？
当您使用 SqlDataReader 时，返回集是否完全由 ExecuteReader 步骤确定，或者您可以通过在读取时写入源表来影响所获得的结果吗？ 这是一个非常粗略的伪代码的示例。
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() ;
}
}
我在其他环境中看到过许多由于写入您正在读取的表而导致的严重错误。 这里，记录 k 从列表顶部（Adams）跳到底部（Ziegler）。 我假设（哈！）SqlDataReader 是免疫的。 真的？ 错误的？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
这取决于您的事务隔离级别或其他锁定提示，但 iirc 默认情况下从 sql server 中的表读取会锁定这些记录，因此您发布的代码要么死锁（sc2 最终将超时），要么更新将进入事务日志，并且不会写入任何内容，直到您读者已读完。 我不记得是哪一个了。
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.
如果您想假设读取的数据不会因这些更新而改变，您是否可以将数据读取到临时对象容器中，然后在完成所有读取后，然后进行更新？ 这会让这个问题变得毫无意义。
当然，从“这到底是如何运作的”的角度来看，我确实发现这个问题很有趣。
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.
我看到的一个问题是，当阅读器打开时，它拥有数据库连接，当阅读器打开时，其他任何东西都不能使用它。 因此，唯一可能的方法是使用不同的数据库连接，但这仍然取决于事务级别
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
如果您想在迭代查询结果时进行更新，您可以将其全部读入数据集。
我知道您没有问过这个问题，而且我也知道这是伪代码，但请务必将您的 sc、sdr 和 sc2 变量包装在 using () 语句中，以确保它们得到正确处理。
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.