InvalidOperationException on SqlCommand.ExecuteScalar()

Question

我正在运行一个执行查询的方法，然后在关闭连接之前尝试执行另一个查询。第一个查询执行得很好，但是第二个查询会导致：

InvalidOperationException上的SqlCommand.ExecuteScalar()方法。

我之前已经完美地运行了，但是有些事情已经改变了，我似乎无法追踪。在执行查询之前，我正在测试连接是关闭的还是空的，但它不是空的还是关闭的。所以这件事让我很困惑。有人知道怎么回事吗？

正在执行的查询非常简单：

SELECT COUNT(*) 
FROM browsers;

我在中对数据库进行了测试，它运行得很好。

    internal static String[,] executeSelect(SqlConnection conn, Query query, Editor reference)
    {
        if (conn == null)
        {
            throw new System.ArgumentNullException("The SqlConnection parameter to method CSED.SQLServerdb.executeSelect(...) is null");
        }
        if (query == null)
        {
            throw new System.ArgumentNullException("The Query parameter to method CSED.SQLSererdb.executeSelect(...) is null");
        }

        String[,] data = null;
        SqlCommand cmd = null;
        SqlDataReader rdr = null;
        SqlCommand cmd2 = null;
        SqlCommand cmd3 = null;

        try
        {
            Debug.WriteLine("SQLServerdb.executeSelect - query: " + query.ToString());
            cmd = new SqlCommand(query.ToString(), conn);
            rdr = cmd.ExecuteReader();
            Field[] flds = query.Fields;
            int columns = flds.Length;
            //Debug.WriteLine("SQLServerdb.executeSelect -columns: " + columns);
            int recordCount = 0;
            List<TableRow> rows = new List<TableRow>();
            TableRow tr = null;
            while (rdr.Read())
            {
                tr = new TableRow();
                recordCount++;
                for (int i = 0; i < columns; i++)
                {
                    tr.Add(DRExtension.GetStringOrNull(rdr, i));
                }
                rows.Add(tr);
            }


            data = convert2DArray(rows, columns);

            //If reference to the class Editor is null then this Database class isn't being used
            //in conjunction with the Editor class.  Therefore, we don't need to calculate the number
            //of records returned from the query.
            if (reference != null && reference.IsUsingSSP)
            {
                bool flag = false;
                int foundrows = 0;
                //GET THE NUMBER OF RECORDS RETURNED BASED ON THE ORIGINAL QUERY.
                String queryStr = "";
                if (query.HaveWhereConditions)
                {
                    queryStr = "SELECT COUNT(*) FROM " + query.GetParentTable() + query.prepareWhere(query.WhereConditions);
                }
                else
                {
                    queryStr = "SELECT COUNT(*) FROM " + query.GetParentTable();
                    flag = true;
                }
                Debug.WriteLine("queryStr: " + queryStr);
                if(conn.State == ConnectionState.Closed)
                    Debug.WriteLine("conn is closed");
                if(conn == null)
                    Debug.WriteLine("conn is NULL");
                cmd2 = new SqlCommand(queryStr, conn);
                object result = cmd2.ExecuteScalar();  //This is where I get the error
                if (result != null)
                {
                    foundrows = Convert.ToInt32(result);
                    query.IFilteredTotal = foundrows;
                }

                //Debug.WriteLine("SQLServerdb.executeSelect - foundrows: " + foundrows);

                //GET THE TOTAL NUMBER OF RECORDS IN THE TABLE.
                if (flag == false)
                {
                    queryStr = "SELECT COUNT(*) FROM " + query.GetParentTable();
                    //Debug.WriteLine("SQLServerdb.executeSelect - queryStr: " + queryStr);
                    cmd3 = new SqlCommand(queryStr, conn);
                    result = cmd3.ExecuteScalar();
                    if (result != null)
                    {
                        int r = Convert.ToInt32(result);
                        // Debug.WriteLine("SQLServerdb.executeSelect - Number of Records in the Table: " + r);
                        query.ITotal = r;
                    }

                }
                else
                {
                    query.ITotal = foundrows;
                }
            }           

        }
        catch (SqlException sqle)
        {
            String extra = "SQL Problem: " + sqle.Message + Constants.NEWLINE;
            extra += "Vendor Error: " + sqle.ErrorCode + Constants.NEWLINE;
            log.Error(extra + sqle.StackTrace);
            Debug.WriteLine(extra + sqle.StackTrace);
        }
        catch (Exception e)
        {
            log.Error("SQLServerdb.executeSelect - query: " + query.ToString());
            log.Error(e.StackTrace);
            Debug.WriteLine(e.StackTrace);
        }
        finally
        {
            // Always make sure result sets and statements are closed,
            // and the connection is returned to the pool
            if(cmd != null)
            {
                cmd.Dispose();
                cmd = null;
            }
            if (cmd2 != null)
            {
                cmd2.Dispose();
                cmd2 = null;
            }
            if (cmd3 != null)
            {
                cmd3.Dispose();
                cmd3 = null;
            }
            if (rdr != null)
            {
                rdr.Close();
                rdr.Dispose();
                rdr = null;
            }
            if (conn != null)
            {
                conn.Close();
                conn.Dispose();
                conn = null;
            }
        }

        return data;
    }//end executeSelect me

Answer 1

执行COUNT命令时，前面的SqlDataReader仍处于打开状态。这会导致错误，因为如MSDN所述

在使用SqlDataReader时，关联的SqlConnection正忙于为SqlDataReader服务，除了关闭SqlConnection之外，其他任何操作都不能在SqlConnection上执行。在调用SqlDataReader的Close方法之前，情况就是如此。例如，在调用Close之后才能检索输出参数。

因此，如果这是导致错误的原因，则应该在同一连接上发出另一个命令之前添加一个rdr.Close。(或者仅仅添加MultipleActiveResultSets

有一点是很重要的:创建要执行的字符串的体系结构是有缺陷的。对于每个与数据库相关的代码来说，SQL注入都是一种危险的可能性。您应该需要在您的交易工具中添加参数化查询。