C# MySQL数据库问题：管理员权限验证后无法更新玩家等级

需求：当指定名称的玩家具备所需管理员权限时，可使用/setlevel命令更新目标玩家等级。目前聊天提示正常，但数据库中的等级值未更新，相关代码如下：

命令实现代码 #

public void CMD_SetLevel(Player player, Player target, int level)
{
    IPlayer pl = new IPlayer();
    using (MySqlConnection connection = new MySqlConnection(connString))
    {
            
        connection.Open();
        MySqlCommand command = connection.CreateCommand();
        command.CommandText = "SELECT * FROM users WHERE name=@name LIMIT 1";
        command.Parameters.AddWithValue("@name", player.Name);
        command.ExecuteNonQuery();

        using (var reader = command.ExecuteReader())
        {
            Main.ServerLog("The server enters the setlevel reader");
            if (reader.HasRows)
            {
                Main.ServerLog("The server enters the rows if");
                reader.Read();
                pl.AdminLevel = Convert.ToInt32(reader["adminlevel"]);
                pl.Level = Convert.ToInt32(reader["level"]);
                pl.Name = Convert.ToString(reader["name"]);
                Main.ServerLog("The server enters the row check from setlevel");
                if (pl.AdminLevel < admLevels[9])
                {
                    Main.ServerLog("The server enters the admin level check from setlevel");
                    player.SendChatMessage("[ERROR] You must be " + admRanks[9] + " !{#FFFFFF}to use this command");
                    return;
                }
                else
                {
                    Main.ServerLog("The server enters the admin level 2 check from setlevel");
                    command.Parameters.Clear();

                    if (target.Equals(player))
                    {
                        Main.ServerLog("The server enters the admin level 3 check from setlevel");
                        command.CommandText = "UPDATE users SET level=@level WHERE name=@name";
                            command.Parameters.AddWithValue("@level", level);
                            //command.Parameters.AddWithValue("@name", pl.Name);
                        //command.ExecuteNonQuery();

                        player.SendChatMessage("You have set your level to " + pl.Level);
                    }
                    else
                    {
                        command.CommandText = "UPDATE users SET level=@level WHERE name=@name";
                            command.Parameters.AddWithValue("@level", pl.Level);
                            command.Parameters.AddWithValue("@name", pl.Name);

                        player.SendChatMessage($"You have set {target.Name}'s level to " + level);
                    }

                }
            }
        }
        connection.Close();
    }
}

IPlayer类代码 #

public class IPlayer : Script
{
    private static string connString = "SERVER=localhost; DATABASE=worldstreetlife; UID=world; PASSWORD=password!";
    public static readonly String DataIdentifier = "PlayerData_Identifier";
    public Player Player { get; set; }
    public int ID;
    public string Name { get; set; }
    public string Password { get; set; }
    public int Level { get; set; }
    public long Cash { get; set; }
    public int Health { get; set; }
    public int Armor { get; set; } 
    public int AdminLevel { get; set; }
    public int VIPLevel { get; set; }
    public Vector3 Position { get; set; }
    public bool IsLoggedIn { get; set; }

    List<int> AdminRanks = new List<int>() { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
    List<int> VIPRanks = new List<int>() { 1, 2, 3, 4 };

    List<string> admRanks = new List<string>() { "!{#FFCC99}Basic Helper", "!{#FFA64D}Helper", "!{#FF8000}Head Helper", "!{#FFFF66}Basic Moderator", "!{#AAFF80}Moderator", "!{#00FF00}Global Moderator", "!{#009900}Head Moderator", "!{#FF8566}Administrator", "!{#FF471A}Head Administrator", "!{#FF0000}Owner" };
    List<string> vipRanks = new List<string>() { "!{#C0C0C0}Silver VIP", "!{#FFD700}Gold VIP", "!{#006600}Advanced VIP", "!{#BFFF00}Premium VIP" };


    public IPlayer()
    {
    }


    public IPlayer(string name, int level, long cash, int adminlevel, int viplevel)
    {
        this.Name = name;
        this.Level = level;
        this.Cash = cash;
        this.AdminLevel = adminlevel;
        this.VIPLevel = viplevel;
    }
}

问题排查与修正 #

核心问题点 #

1. UPDATE语句未执行

   • 目标为自身的分支中，command.ExecuteNonQuery()被注释；目标为其他玩家的分支中，完全未调用该方法。MySQL的修改语句必须通过ExecuteNonQuery()提交到数据库。

2. 参数绑定逻辑错误

   • 修改其他玩家等级时，@level绑定的是当前玩家的pl.Level，而非传入的目标等级level；@name绑定的是当前玩家的pl.Name，而非目标玩家的target.Name，导致修改对象完全错误。

3. 冗余且错误的查询调用

   • 执行SELECT查询时，先调用了command.ExecuteNonQuery()，这是错误的操作，SELECT语句应直接使用ExecuteReader()，该冗余调用会浪费数据库连接资源。

4. 变量作用域问题

   • admLevels和admRanks是IPlayer类的私有成员，但在CMD_SetLevel方法中直接调用，存在访问权限问题，需调整为公共/内部访问级别，或直接使用对应值（如权限10）。

5. 冗余的连接关闭操作

   • using语句会自动释放并关闭数据库连接，无需手动调用connection.Close()。

修正后的代码示例 #

public void CMD_SetLevel(Player player, Player target, int level)
{
    IPlayer pl = new IPlayer();
    // 引用IPlayer类的静态连接字符串
    using (MySqlConnection connection = new MySqlConnection(IPlayer.connString))
    {
        connection.Open();

        // 单独验证管理员权限，避免命令复用导致的连接占用问题
        using (MySqlCommand checkCmd = connection.CreateCommand())
        {
            checkCmd.CommandText = "SELECT adminlevel FROM users WHERE name=@name LIMIT 1";
            checkCmd.Parameters.AddWithValue("@name", player.Name);
            
            using (var reader = checkCmd.ExecuteReader())
            {
                if (!reader.HasRows)
                {
                    player.SendChatMessage("[ERROR] Player not found in database!");
                    return;
                }
                reader.Read();
                pl.AdminLevel = Convert.ToInt32(reader["adminlevel"]);
            }
        }

        // 验证权限，直接使用10对应Owner权限，或调整为pl.AdminRanks[9]并修改成员访问级别
        if (pl.AdminLevel < 10)
        {
            player.SendChatMessage("[ERROR] You must be !{#FF0000}Owner !{#FFFFFF}to use this command");
            return;
        }

        // 执行等级更新操作
        using (MySqlCommand updateCmd = connection.CreateCommand())
        {
            updateCmd.CommandText = "UPDATE users SET level=@level WHERE name=@targetName";
            updateCmd.Parameters.AddWithValue("@level", level);
            updateCmd.Parameters.AddWithValue("@targetName", target.Name);
            
            int affectedRows = updateCmd.ExecuteNonQuery();
            if (affectedRows > 0)
            {
                string message = target.Equals(player) 
                    ? $"You have set your level to {level}" 
                    : $"You have set {target.Name}'s level to {level}";
                player.SendChatMessage(message);
            }
            else
            {
                player.SendChatMessage("[ERROR] Failed to update target player's level!");
            }
        }
    }
}

内容的提问来源于stack exchange，提问作者Alex Mohor