c# - 输入值时如何停止错误“列用户名不能为空”

Question

我正在尝试创建管理员可以在其中创建新用户帐户的 ac# 管理系统。我已经用 MySQL 查询编写了应用程序端的代码，但正在尝试将其转换为存储过程。当我输入一个新的用户帐户时出现错误Column 'username' cannot be null。即使用户名文本框中有文本，也会发生这种情况。我的代码在 SQL 应用程序端工作得很好，但是在让它与存储过程一起工作时遇到了麻烦。

我相信下面的代码也存在检查用户名是否存在的问题，因为它说用户名是唯一的，即使它不是。这可能会导致后面的问题。

    if (table.Rows.Count > 0)  //This is returning false everytime I call this method
            {
                MessageBox.Show("Exists");
                return true;
            }
            else
            {
                MessageBox.Show("Unique");
                return false;
            } 

这是我的代码目前的样子（对不起代码量）：

更新：我对此进行了简化并添加了评论

 public bool Confirm()
    {
        string user = usernameField.Text;
        string password = passwordField.Text;
        string email = emailField.Text;
        bool admin = adminCheckBox.Checked;
        if (Create(user, password, email, admin)) //Calls the Create Method with the parameters of the textboxes
        {
         //Does something if true
            return true;
        }
        else
        {
         //Does something if false
            return false;
        }
    }
    public bool Create(string user, string pass, string email, bool admin)
    {
        MySqlCommand cmd = new MySqlCommand("AdminCreatePlayerAccount", Conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@user", user);
        cmd.Parameters.AddWithValue("@password", pass);
        cmd.Parameters.AddWithValue("@email", email);
        cmd.Parameters.AddWithValue("@admin", admin);
        Conn.Open();
        try
        {
            if (!checkInputFields()) //If the checkInputFields method returns false
            {
                if (!checkUsername(user)) //if the checkUsername method returns false with the parameter of the user value (usernameField.Text)
                {
                    try
                    {
                        if (cmd.ExecuteNonQuery() == 1)
                        {
                            //If true creates user
                            return true;
                        }
                        else
                        {
                            Conn.Close(); //Close connection if false
                            return false;
                        }
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                        Conn.Close();
                        return false;
                    }
                }
                else
                {
                    Conn.Close(); //Close connection if false
                    return false;
                }
            }
            else
            {
                Conn.Close();//Close connection if false
                return false;
            }

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
            Conn.Close();
            return false;
        }

    }
    public bool checkUsername(string user) //This method is to check if the database contains a row with the inputted username
    {
        using (MySqlCommand cmd = new MySqlCommand("AdminCheckUsername", Conn))
        {
            DataTable table = new DataTable();
            MySqlDataAdapter adapter = new MySqlDataAdapter();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@uname", user);
            adapter.SelectCommand = cmd;
            adapter.Fill(table);
            if (table.Rows.Count > 0)  //This is returning false everytime I call this method
            {
                //Returns true if the username exists in the database
                return true;
            }
            else
            {
                //Returns false if the username doesn't exist in the database
                return false;
            }
        }
    }

    public bool checkInputFields() //Checks the length of the input fields
    {
        if (usernameField.Text.ToLower().Trim().Equals("username") ||
            passwordField.Text.ToLower().Trim().Equals("password") ||
            emailField.Text.ToLower().Trim().Equals("email"))
        {
            MessageBox.Show("Please Enter All Details");
            return true;
        }
        else
        {
            return false;
        }
    }

这是我创建的存储过程：

drop procedure if exists AdminCreatePlayerAccount; 
delimiter //
create procedure AdminCreatePlayerAccount(IN `user` varchar(100), `password` varchar(100), `email` varchar(100), `admin` tinyint)
    BEGIN
        INSERT INTO tbl_user (username, user_password, user_email, user_loginStatus, user_loginAttempts, user_accountStatus, user_isAdmin) 
            VALUES (@user,@password,@email, 0, 0, 0,@admin);
        COMMIT;
    END//
delimiter ;                                                                                                                                      


drop procedure if exists AdminCheckUsername; 
delimiter //
create procedure AdminCheckUsername(IN `uname` varchar(100))
    BEGIN
        SELECT * 
            From tbl_user 
                WHERE `username` = @uname;
        COMMIT;
    END//
delimiter ;  

当我不使用存储过程时，这就是原始代码的样子：

这完全按照它应该的方式工作，但我需要对其进行转换，以便 SQL 查询位于服务器端，而不是应用程序端。

public void Confirm()
{
    //Add a new User
    Connect database = new Connect();
    MySqlCommand cmd = new MySqlCommand("INSERT INTO tbl_user (username, user_email, user_password, user_loginAttempts, user_accountStatus, user_isAdmin) VALUES (@uname,@email,@pwd, 0, 0,@admin)", database.getConnection());
    cmd.Parameters.Add("@uname", MySqlDbType.VarChar).Value = usernameField.Text;
    cmd.Parameters.Add("@pwd", MySqlDbType.VarChar).Value = passwordField.Text;
    cmd.Parameters.Add("@email", MySqlDbType.VarChar).Value = emailField.Text;
    cmd.Parameters.AddWithValue("@admin", adminCheckBox.Checked);
    //Open Connection
    database.openConnection();

    //Calls checkInputFields to see if the user has inputted data into all fields
    if (!checkInputFields())
    {
        //Calls checkUsername to see if the username already exists in the system
        if (checkUsername())
        {
            //If the username already exists, it opens a message box that displays "This Username Already Exists, Please Enter a new one try loggin in"
            MessageBox.Show("This Username Already Exists, Please Enter a new one", "", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
        }
        else
        {
            //Execute The Query that creates a new account
            if (cmd.ExecuteNonQuery() == 1)
            {
                OpenSettings();
            }
            else
            {
                MessageBox.Show("Error");
            }
        }
    }
    else
    {
        MessageBox.Show("Please Enter all details", "", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
    }

    //Close Connnection
    database.closeConnection();
}//Main method
public Boolean checkUsername()
{
    Connect database = new Connect();
    String username = usernameField.Text;

    DataTable table = new DataTable();
    MySqlDataAdapter adapter = new MySqlDataAdapter();
    MySqlCommand command = new MySqlCommand("SELECT * From tbl_user WHERE `username` = @uname", database.getConnection());
    command.Parameters.Add("uname", MySqlDbType.VarChar).Value = username;
    adapter.SelectCommand = command;
    adapter.Fill(table);

    /*Check to see if username already exists*/
    if (table.Rows.Count > 0)
    {
        /*Login Success*/
        return true;
    }
    else
    {
        /*Login Error*/
        return false;
    }
}
public Boolean checkInputFields()
{
    String username = usernameField.Text;
    String password = passwordField.Text;
    String email = passwordField.Text;

    if (username.ToLower().Trim().Equals("username") || password.ToLower().Trim().Equals("password") || email.ToLower().Trim().Equals("email"))
    {
        return true;
    }
    else
    {
        return false;
    }
}

public void OpenSettings()
{
    MessageBox.Show("Account Has Been Created Successfully", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
    this.Hide();
    Settings settings = new Settings();
    settings.ShowDialog();
}
}

任何解决此问题的帮助将不胜感激，或者帮助将内联 SQL 查询转换为存储过程。

标签： c#mysqlstored-procedures