c# - 根据MySQL数据库数据显示消息？ C#

Question

我想从 MySQL 数据库中获取值，这需要根据值显示消息。但这并没有发生，它总是显示 int privilege 为 0。如果我没有分配该默认值，代码将显示错误。

如何解决这个问题并根据 int privilege 值显示消息？

private void button_login_Click(object sender, RoutedEventArgs e)
{

    string username = usernameInput.Text;
    string password = passwordInput.Password;
    int privilege = 0;

    try
    {                
        //This is command class which will handle the query and connection object.
        string Query = "SELECT`tbl_user_login`.`u_id`,`tbl_user_login`.`u_username`,
                 `tbl_user_login`.`u_password`,`tbl_user_login`.`u_privilege` 
                 FROM `bcasdb`.`tbl_user_login`WHERE `tbl_user_login`.`u_username` = '"
                 + username + "' AND `tbl_user_login`.`u_password` ='" + password 
                 + "' AND `tbl_user_login`.`u_privilege` = @privi;";
        MySqlConnection conn = 
                     new MySqlConnection(BCASApp.DataModel.DB_CON.connection);
        MySqlCommand cmd = new MySqlCommand(Query, conn);
        cmd.Parameters.AddWithValue("@privi", privilege);
        MySqlDataReader MyReader;
        conn.Open();
        MyReader = cmd.ExecuteReader();
          // Here our query will be executed and data saved into the database.

        if (MyReader.HasRows && this.Frame != null)
        {
            while (MyReader.Read())
            {

                if (privilege == 1)
                {
                    DisplayMsgBox("click ok to open the admin page ", "OK");
                }
                if (privilege == 2)
                {
                    DisplayMsgBox("click ok to open the staff page ", "OK");
                }
                else
                {
                    DisplayMsgBox("privilege 0", "ok");
                }   
            }
        }                
        else
        {
            DisplayMsgBox("sucess else", "ok");
        }

        conn.Close();
    }
    catch (Exception )
    {
        DisplayMsgBox("sucess catch", "ok");
    }
}

Answer 1

看起来您正在尝试做的是检查 u_privilege 的值来自 tbl_user_login 的专栏表，而不是根据 privilege 创建 where 条件.您需要删除此 where 条件

AND `tbl_user_login`.`u_privilege` = @privi

同时移除参数赋值

cmd.Parameters.AddWithValue("@privi", privilege);

可以得到tbl_user_login.u_privilege的值通过使用 MySqlDataReader.GetInt32 while (MyReader.Read()) 中的语法 block

MyReader.GetInt32(3)

请注意3使用是因为 MyReader.GetInt32需要一个从零开始的索引参数和 tbl_user_login.u_privilege是查询中的第四列。该值应分配给 privilege变量如下

privilege = MyReader.GetInt32(3)

附带说明一下，您应该将查询参数化以避免 SQL injection .这是实现上述更改后的完整代码

int privilege = 0;

try
{                
    //This is command class which will handle the query and connection object.
    string Query = "SELECT`tbl_user_login`.`u_id`,`tbl_user_login`.`u_username`,
             `tbl_user_login`.`u_password`,`tbl_user_login`.`u_privilege` 
             FROM `bcasdb`.`tbl_user_login`WHERE `tbl_user_login`.`u_username` = 
             @username AND `tbl_user_login`.`u_password` = @password;";
    MySqlConnection conn = 
                 new MySqlConnection(BCASApp.DataModel.DB_CON.connection);
    MySqlCommand cmd = new MySqlCommand(Query, conn);
    cmd.Parameters.AddWithValue("@username", username);
    cmd.Parameters.AddWithValue("@password", password);
    MySqlDataReader MyReader;
    conn.Open();
    MyReader = cmd.ExecuteReader();
      // Here our query will be executed and data saved into the database.

    if (MyReader.HasRows && this.Frame != null)
    {
        while (MyReader.Read())
        {
            privilege = MyReader.GetInt32(3)

            if (privilege == 1)
            {
                DisplayMsgBox("click ok to open the admin page ", "OK");
            }
            if (privilege == 2)
            {
                DisplayMsgBox("click ok to open the staff page ", "OK");
            }
            else
            {
                DisplayMsgBox("privilege 0", "ok");
            }   
        }
    }                
    else
    {
        DisplayMsgBox("sucess else", "ok");
    }

    conn.Close();
}
catch (Exception )
{
    DisplayMsgBox("sucess catch", "ok");
}