SqlDataReader的和的SqlCommand [英] SqlDataReader and SqlCommand
问题描述
我有以下的code。
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
connection.Open();
SqlCommand select = new SqlCommand("SELECT RTRIM(LTRIM(PART_NO)) AS PART_NO, record FROM [RMAData].[dbo].[IMPORTING_ORDER_EDI] WHERE sessionID = '" + Session.SessionID + "'", connection);
SqlDataReader reader = select.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
if (!currentPart.IsActive)
{
// this part is not active, set the active flag in sql to 0
SqlCommand update = new SqlCommand("UPDATE [RMAData].[dbo].[IMPORTING_ORDER_EDI] SET valid = 0, active = 0 WHERE record = " + reader["record"].ToString() + ";", connection);
update.ExecuteNonQuery();
}
else
{
///blah
}
}
reader.Close();
}
}
但是这会导致以下异常...
but this causes the following exception...
的 System.InvalidOperationException:有已经是一个公开的DataReader
使用此命令,必须先关闭相关联。的
我需要阅读返回的每一行,在做一些数据验证和进行更新,如果有必要,再继续下一个记录。我怎样才能做到这一点,如果我不能使用的SqlCommand
同时通过 reader.Read循环()
?
I need to read each row returned, do some validation on the data and make an update if necessary, then continue to the next record. How can I achieve this if I can't use a SqlCommand
while looping through reader.Read()
?
推荐答案
另外一个办法是不加MultipleActiveResultSets = TRUE - 有这样做的一个小的性能损失 - 所以是这样的:
An alternative is not add MultipleActiveResultSets=True - there is a small performance penalty for doing so - and so something like this:
using (SqlConnection connection = new ...))
{
connection.Open();
SqlCommand select = new SqlCommand(...);
SqlDataReader reader = select.ExecuteReader();
var toInactivate = new List<string>();
if (reader.HasRows)
{
while (reader.Read())
{
if (!currentPart.IsActive)
{
toInactivate.Add(reader["record"].ToString());
}
else
{
///blah
}
}
reader.Close();
}
SqlCommand update = new SqlCommand("UPDATE ... SET valid = 0, active = 0 " +
"WHERE record IN(" + string.Join(",", toInactivate) + ");", connection);
update.ExecuteNonQuery();
}
这在一个SQL语句更新所有所需的记录的优势。
which has the advantage of updating all the required records in a single SQL statement.
当然,整个事情就这么多整洁使用EF和LINQ。
And of course the whole thing would be so much neater using EF and Linq.
这篇关于SqlDataReader的和的SqlCommand的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!