需要关闭与Command关联的SqlDataReader才能成功登录

本文关键字：SqlDataReader 成功 登录 关联 Command | 更新日期: 2023-09-27 17:50:53

每次尝试使用正确的凭据登录时，都会出现以下错误消息:

已经有一个打开的数据读取器与这个命令相关联，必须先关闭它。

请有人剖析这段代码，这样我就可以最终进入下一阶段，谢谢大家!

// Login Function for Manual Login
public void ent()
{
    try
    {
        SqlConnection con = new SqlConnection(cc.connectDB());
        con.Open();
        SqlCommand cmd = new SqlCommand("delete from log", con);
        cmd.ExecuteNonQuery();
        SqlCommand cmd1 = new SqlCommand("select * from Login where username='" + username.Text + "' and password='" + password.Text + "'", con);
        cmd1.ExecuteNonQuery();
        SqlDataReader c = cmd1.ExecuteReader();
        if (c.Read() == true)
        {
            SqlCommand cmd2 = new SqlCommand("select typeid from Login where username='" + username.Text + "' and password='" + password.Text + "'", con);
            Int32 count = (Int32)cmd2.ExecuteScalar();
            if (count == 1)
            {
                SqlCommand cmd3 = new SqlCommand("insert into log values ('" + 1 + "')", con);
                cmd3.ExecuteNonQuery();
            }
            else
                if (count == 2)
                {
                    SqlCommand cmd3 = new SqlCommand("insert into log values ('" + 2 + "')", con);
                    cmd3.ExecuteNonQuery();
                }
            Menu shw = new Menu();
            shw.Show();
            this.Hide();
        }
        else
        {
            MessageBox.Show("Login failed");
        }
    }
    catch (Exception ee)
    {
        MessageBox.Show(ee.Message);
    }
}

我正在连接到一个完全启动和运行的SQL Express服务器，但我似乎无法找到一种方法，我能够关闭阅读器而不会造成不必要的错误。

需要关闭与Command关联的SqlDataReader才能成功登录

在同一个表(dbo.LOGIN)上执行了两次select而没有在第二次执行前关闭reader

代替后面的

行
 SqlCommand cmd2 = new SqlCommand("select typeid from Login where username='" + username.Text + "' and password='" + password.Text + "'", con);
 Int32 count = (Int32)cmd2.ExecuteScalar();

可以将typeid读取为

INT32 count = Convert.ToInt32(c["typeid"].ToString());

还建议处理连接，命令和阅读器对象，否则您迟早会遇到一些错误

例如

using(SqlConnection con = new SqlConnection(cc.connectDB())
  {
    con.Open();
    using(SqlCommand cmd = new SqlCommand("delete from log", con))
      {
            cmd.ExecuteNonQuery();
      }
    using(SqlCommand cmd1 = new SqlCommand("select * from Login where username='" + username.Text + "' and password='" + password.Text + "'", con))
      {
         //You don't need this
         //cmd1.ExecuteNonQuery();
         using(SqlDataReader c = cmd1.ExecuteReader())
              {
                //You don't need these lines - this is probably the line of error
                //SqlCommand cmd2 = new SqlCommand("select typeid from Login where username='" + username.Text + "' and password='" + password.Text + "'", con);
                //Int32 count = (Int32)cmd2.ExecuteScalar();
                INT32 count = Convert.ToInt32(c["typeid"].ToString());
                //other sruffs
              }
       }
  }

问题可能起源于这里，即在调用cmd1.ExecuteReader()之前不应该调用无关的cmd1.ExecuteNonQuery();:

cmd1.ExecuteNonQuery(); <-- This is redundant, and possibly harmful
SqlDataReader c = cmd1.ExecuteReader(); <-- Just do this.

你只需要呼叫cmd1.ExecuteReader

然而，在你的代码中有很多未关闭/未处理的资源。您应该处理连接、命令和读取器，例如使用using:

using (var cmd = new SqlCommand("delete from log", con))
{
   cmd.ExecuteNonQuery();
}

编辑

在哪里?像这样。您还需要对查询进行参数化。请注意，您可以堆叠您的使用，以防止大量嵌套:

  var someMessageToShowToUser = "";
  try
  {
     using (var con = new SqlConnection(cc.connectDB()))
     using (var cmd = new SqlCommand("delete from log", con))
     using (var cmd1 = new SqlCommand(
              "select * from Login where username=@UserName and password=@Password", con))
     {
        con.Open();
        cmd.ExecuteNonQuery();
        cmd1.Parameters.AddWithValue("@UserName", username.Text);
        cmd1.Parameters.AddWithValue("@Password", password.Text);
        // cmd1.ExecuteNonQuery(); -> Delete this line.
        using (var c = cmd1.ExecuteReader())
        {
           if (c.Read() == true)
           {
              using (var cmd2 = new SqlCommand("Parameterize me too")
              {
                 var count = (Int32) cmd2.ExecuteScalar();
                 if (count == 1)
                 {
                    using (var cmd3 = new SqlCommand("insert into log values (@AnotherParam)", con))
                    {
                       cmd3.ExecuteNonQuery();
                    }
                 }
                 else if (count == 2)
                 {
                    using (var cmd3 = new SqlCommand("insert into log values (@AnotherParam)", con))
                    {
                       cmd3.ExecuteNonQuery();
                    }
                 }
                 Menu shw = new Menu();
                 shw.Show();
                 this.Hide();
              }
           }
           else
           {
              someMessageToShowToUser = "Login failed";
           }
        }
     }
 }
 catch (Exception ee)
 {
    someMessageToShowToUser = ee.Message;
 }
 MessageBox.Show(someMessageToShowToUser);

我想另一点是它不是一个好主意，显示MessageBoxes(等待用户点击按钮)，而你已经打开连接到数据库。理想情况下，您希望将数据访问代码与表示代码分开(也可能进一步分开)。

相关文章：