DataList 绑定：“连接未关闭。连接的当前状态为打开。”

Question

我试图将 DataList 控件与 SQL 表中的选定数据绑定：

     private void ShowPossiblePurchases(string CategoryName)
{

    string selectSQL = "SELECT TOP 2 * FROM Menu WHERE CategoryName=@CategoryName ORDER BY NEWID()";
    SqlCommand cmd = new SqlCommand(selectSQL, connection);
    cmd.Parameters.AddWithValue("@CategoryName", CategoryName);
    SqlDataReader reader;

    DataList DataList1 = (DataList)lgnView.FindControl("DataList1");

    try
    {

        connection.Open();
        reader = cmd.ExecuteReader();
        DataList1.DataSource = reader;
        DataList1.DataBind();

        reader.Close();

    }
    catch (Exception ex)
    {
        Label lblError = (Label)lgnView.FindControl("lblError");
        lblError.Text = ex.Message;
    }
    finally
    {
        connection.Close();
    }

当我运行此代码时，我得到“连接未关闭。连接的当前状态为打开”。

我之前版本的方法是这样的：

    private void ShowPossiblePurchases(string CategoryName)

{

string selectSQL = "SELECT TOP 2 * FROM Menu WHERE CategoryName=@CategoryName ORDER BY NEWID()";
SqlCommand cmd = new SqlCommand(selectSQL, connection);
cmd.Parameters.AddWithValue("@CategoryName", CategoryName);
SqlDataReader reader;

DataSet myDataSet = new DataSet();
myDataSet.Tables.Add("Products");


myDataSet.Tables["Products"].Columns.Add("ProductID");
myDataSet.Tables["Products"].Columns.Add("CategoryID");
myDataSet.Tables["Products"].Columns.Add("ProductName");
myDataSet.Tables["Products"].Columns.Add("Price");

DataList DataList1 = (DataList)lgnView.FindControl("DataList1");

try
{
    connection.Open();
    reader = cmd.ExecuteReader();

    while (reader.Read())
    {

        DataRow rowNew = myDataSet.Tables["Products"].NewRow();
        rowNew["ProductID"] = reader["ProductID"];
        rowNew["CategoryID"] = reader["CategoryID"];
        rowNew["ProductName"] = reader["ProductName"];
        rowNew["Price"] = reader["Price"];
        myDataSet.Tables["Products"].Rows.Add(rowNew);
    }

    DataList1.DataSource = myDataSet.Tables["Products"];
    DataList1.DataBind();
}
catch(Exception ex)
{
    Label lblError = (Label)lgnView.FindControl("lblError");
    lblError.Text = ex.Message;
}
finally
{
    connection.Close();
}

}

Answer 1

尝试先关闭连接而不是阅读器

Answer 2

您可以先检查连接是否已关闭：

If ((cmd.Connection.State And System.Data.ConnectionState.Open) _
         <> System.Data.ConnectionState.Open) Then
    cmd.Connection.Open()
End If

C#：

if (((cmd.Connection.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open)) {
    cmd.Connection.Open();
}

Answer 3

你在哪里声明连接？

看起来您是在提供的代码之外声明它并事先在其他地方打开连接。

为了避免尝试打开它两次，您可以进行简单的检查以查看连接是否打开......

if (conn == null || conn.State == ConnectionState.Closed)
                OpenDBConnection();