C# & SQL Server : fetch data and then delete returns error

I have the following script to fetch me some data from a database and then remove it:

我有以下脚本从数据库中获取一些数据，然后将其删除：

public void checkDB()
{
    string query = "SELECT * FROM dbt";

    SqlCommand sqlCommand = new SqlCommand(query, conn);

    SqlDataReader reader;
    int id = -1;

    using (reader = sqlCommand.ExecuteReader())
    {
            if (reader.Read())
            {
                String data= reader["sdata"].ToString();
                Order o = new Order(reader["sdata"].ToString());
                o.prepareForScript();
                id = reader.GetSqlInt32(1).Value;
            }

            reader.Close();
    }

    if (id != -1)
    {
        string removeQuery = "DELETE FROM data WHERE ID=" + id;

        SqlCommand removeCMD = new SqlCommand(removeQuery, conn);

        removeCMD.ExecuteNonQuery();
    }
}

This code results in an exception

此代码导致异常

unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.dll

System.Data.dll中发生未处理的“System.InvalidOperationException”类型异常

with the aditional information that a reader is already associated with this connection. However as you can see the reader is both closed and inside a using loop meaning that it should definitly be closed. Anybody know how to fix this?

阅读器已与此连接关联的附加信息。然而，正如您所看到的那样，阅读器既关闭又在使用循环内部，这意味着它应该绝对关闭。有谁知道如何解决这个问题？

1 个解决方案

#1

You need to dispose first SqlCommand as below :

您需要首先处理SqlCommand，如下所示：

using (SqlCommand sqlCommand = new SqlCommand(query, conn))
{
        SqlDataReader reader;
        int id = -1;

        using (reader = sqlCommand.ExecuteReader())
        {
            if (reader.Read())
            {


                String data= reader["sdata"].ToString();
                Order o = new Order(reader["sdata"].ToString());
                o.prepareForScript();
                id = reader.GetSqlInt32(1).Value;
            }
            reader.Close();
        }
}

要么

enable MultipleActiveResultSets. This way SQL Server allows several open data readers on a single connection.

启用MultipleActiveResultSets。这样，SQL Server允许在单个连接上使用多个开放数据读取器。

But i would suggest not to use your SQL statements together to avoid Sql Injection.

但我建议不要一起使用SQL语句来避免Sql注入。

#1