C# SqlDataReader 和 SqlCommand
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15005974/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SqlDataReader and SqlCommand
提问by Stuart
I have the following 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: There is already an open DataReader associated with this Command which must be closed first.
System.InvalidOperationException: 已经有一个与此命令关联的打开的 DataReader，必须先关闭它。
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 SqlCommandwhile looping through reader.Read()?
我需要读取返回的每一行，对数据进行一些验证并在必要时进行更新，然后继续下一条记录。如果我不能使用SqlCommandwhile 循环，我该如何实现reader.Read()？
采纳答案by Phil
An alternative is not add MultipleActiveResultSets=True - there is a small performance penalty for doing so - and so something like this:
另一种选择是不添加 MultipleActiveResultSets=True - 这样做会有一个小的性能损失 - 所以像这样：
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();
}
which has the advantage of updating all the required records in a single SQL statement.
这具有在单个 SQL 语句中更新所有必需记录的优点。
And of course the whole thing would be so much neater using EF and Linq.
当然，使用 EF 和 Linq 整个事情会更加整洁。
回答by Grant Thomas
Could be as simple as amending your connection string:
可能就像修改连接字符串一样简单：
add MultipleActiveResultSets=True to connection string
将 MultipleActiveResultSets=True 添加到连接字符串
回答by ????
You need to either create multiple instances of you connection.
As only one command can be excuted against a connection in general
or
do as suggested by @grantThomas
Or you can use multiple connection as follows
您需要创建多个连接实例。
因为通常只能针对一个连接执行一个命令，
或者
按照@grantThomas 的建议执行
或者您可以使用多个连接，如下所示
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
using (SqlConnection connection1 = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
SqlCommand update = new SqlCommand("UPDATE [RMAData].[dbo].[IMPORTING_ORDER_EDI] SET valid = 0, active = 0 WHERE record = " + reader["record"].ToString() + ";", connection1);
update.ExecuteNonQuery();
}
}
else
{
///blah
}
}
reader.Close();
}
}
