我必须打开两个连接来执行两个不同的查询

本文关键字：两个 执行 查询 连接 | 更新日期: 2023-09-27 18:11:27

目前我正在对两个不同的表执行两个查询，并得到这个异常，

连接未关闭。连接的当前状态为打开。

这就是我要做的，

    protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        int userID = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["userID"].ToString());
        string deleteStatement = "Delete from Table1 where userID=@userID";
        string deleteStatement2 = "Delete from Table2 where userID=@userID";
        using (SqlConnection connection = new SqlConnection(CS()))
        using (SqlCommand cmd = new SqlCommand(deleteStatement, connection))
        {
            connection.Open();
            cmd.Parameters.Add(new SqlParameter("@userID", userID));
            cmd.ExecuteNonQuery();
            using (SqlCommand cmd2 = new SqlCommand(deleteStatement2, connection))
            {
                connection.Open();
                cmd2.Parameters.Add(new SqlParameter("@userID", userID));
                int result2 = cmd2.ExecuteNonQuery();
                if (result2 == 1)
                {
                    BindData();
                }
            }
        }
    }

我这样做是因为表2有userID作为外键，必须在删除user之前删除

我必须打开两个连接来执行两个不同的查询

您呼叫Open()两次。可以删除第二个呼叫Open() 

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
    int userID = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["userID"].ToString());
    string deleteStatement = "Delete from Table1 where userID=@userID";
    string deleteStatement2 = "Delete from Table2 where userID=@userID";
    using (SqlConnection connection = new SqlConnection(CS()))
    using (SqlCommand cmd = new SqlCommand(deleteStatement, connection))
    {
        connection.Open();
        cmd.Parameters.Add(new SqlParameter("@userID", userID));
        cmd.ExecuteNonQuery();
        using (SqlCommand cmd2 = new SqlCommand(deleteStatement2, connection))
        {
            // connection.Open(); // remove this line
            cmd2.Parameters.Add(new SqlParameter("@userID", userID));
            int result2 = cmd2.ExecuteNonQuery();
            if (result2 == 1)
            {
                BindData();
            }
        }
    }
}

第二个connection.Open()是不需要的，因为它将从第一个语句打开。

为安全起见，您可以使用

if (connection.State == ConnectionState.Closed)
    connection.Open();

第二个connection.Open();不需要在那里。第一个就足够了;如错误信息所示，它已打开。

一个连接可以执行多个查询，只需要调用一个Open。

 protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
  {
    int userID = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["userID"].ToString());
    string deleteStatement = "Delete from Table1 where userID=@userID";
    string deleteStatement2 = "Delete from Table2 where userID=@userID";
    using (SqlConnection connection = new SqlConnection(CS()))
    {
    connection.Open();
    using (SqlCommand cmd = new SqlCommand(deleteStatement, connection))
    {
        cmd.Parameters.Add(new SqlParameter("@userID", userID));
        cmd.ExecuteNonQuery();
    }
      using (SqlCommand cmd2 = new SqlCommand(deleteStatement2, connection))
        {
            cmd2.Parameters.Add(new SqlParameter("@userID", userID));
            int result2 = cmd2.ExecuteNonQuery();
            if (result2 == 1)
            {
                BindData();
            }
        }
     }
}

我认为这将工作:

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
    {
        int userID = Convert.ToInt32(GridView1.DataKeys[e.RowIndex].Values["userID"].ToString());
        string deleteStatement = "Delete from Table1 where userID=@userID";
        string deleteStatement2 = "Delete from Table2 where userID=@userID";
        using (SqlConnection connection = new SqlConnection(CS()))
        using (SqlCommand cmd = new SqlCommand())
        {
            cmd.Connection = connection;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = deleteStatement;
            cmd.Parameters.Add(new SqlParameter("@userID", userID));
            cmd.ExecuteNonQuery();

            cmd.CommandText = deleteStatement2;
            int result = cmd.ExecuteNonQuery();
            if (result == 1) BindData();
        }
    }
相关文章：