C#登录功能抛出InvalidCastException：无法将DBNull转换为其他类型

开发C#登录功能时，点击登录按钮触发用户验证方法，抛出异常：InvalidCastException: object cannot be cast from dbnull to other types.。已确认数据库表的name和password字段为VARCHAR类型，C#中存入的是string类型，但无论输入正确或错误的账号密码都会触发该异常，怀疑问题出在while (reader.Read())代码块中。

验证用户方法 #

public User validateUser(string name, string pass)
{
    User user = null;

    String query = "SELECT * FROM users WHERE name = @name AND password = @password;";
    try
    {
        con = dbConnect.getConnection();

        if (con != null)
        {
            con.Open();
            using (MySqlCommand cmd = new MySqlCommand(query, con))
            {
                cmd.Parameters.Add(new MySqlParameter("@name", name));
                cmd.Parameters.Add(new MySqlParameter("@password", pass));

                using (MySqlDataReader reader = cmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {

                            string nam = Convert.IsDBNull(reader["name"]) ? "" : reader["name"].ToString();
                            string password = Convert.IsDBNull(reader["password"]) ? "" : reader["password"].ToString();

                            user = new User(nam, password);

                            //   user = new User(reader["name"].ToString(), reader["password"].ToString());
                        }
                    }
                }
            }
        }
    }
    catch (MySqlException error1)
    {
        MessageBox.Show(" 123 " + error1.Message);
    }
    catch (InvalidCastException error2)
    { 
        MessageBox.Show(" 789 " + error2.Message);
    }
    return user;
}

按钮点击方法 #

private void button1_Click(object sender, EventArgs e)
{
    string name = txtUser.Text;
    string pass = txtPass.Text;

    if (name == "" || pass == "")
    {
        MessageBox.Show("Rellena los campos.");
    }
    else
    {
        try
        {
            user = userDao.validateUser(name, pass);
        }
        catch (InvalidCastException ex)
        {
            MessageBox.Show("Error: " + ex.Message);
        }
        if (user == null)
        {
            txtMsg.Text = "ERROR";
        }
        else
        {
            txtMsg.Text = "CORRECTO";
        }
    }
}

问题根源 #

你虽然对name和password字段做了DBNull判断，但查询语句用了SELECT *，会返回表中所有字段。如果表中存在其他允许为NULL的非字符串类型字段（比如int、datetime等），当这些字段值为NULL时，MySqlDataReader读取时会尝试将DBNull隐式转换为对应.NET类型，直接触发InvalidCastException。

修复步骤 #

1. 替换SELECT *为指定字段
   只查询需要的name和password字段，避免读取其他可能引发问题的字段：

   String query = "SELECT name, password FROM users WHERE name = @name AND password = @password;";
   

2. 使用更安全的字段读取方式
   用reader.IsDBNull()结合GetOrdinal()判断字段是否为NULL，再用强类型方法读取，避免隐式转换错误：

   string nam = reader.IsDBNull(reader.GetOrdinal("name")) ? "" : reader.GetString(reader.GetOrdinal("name"));
   string password = reader.IsDBNull(reader.GetOrdinal("password")) ? "" : reader.GetString(reader.GetOrdinal("password"));
   

3. 确保数据库连接自动释放
   给数据库连接加上using语句，确保连接使用后自动释放，避免资源泄漏：

   using (var con = dbConnect.getConnection())
   {
       if (con != null)
       {
           con.Open();
           // 后续命令执行逻辑
       }
   }
   

4. 优化循环逻辑
   账号密码匹配通常只会返回一条数据，用if (reader.Read())代替while循环更合理。

修复后的完整验证方法 #

public User validateUser(string name, string pass)
{
    User user = null;
    String query = "SELECT name, password FROM users WHERE name = @name AND password = @password;";
    try
    {
        using (var con = dbConnect.getConnection())
        {
            if (con != null)
            {
                con.Open();
                using (MySqlCommand cmd = new MySqlCommand(query, con))
                {
                    cmd.Parameters.Add(new MySqlParameter("@name", name));
                    cmd.Parameters.Add(new MySqlParameter("@password", pass));

                    using (MySqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            string nam = reader.IsDBNull(reader.GetOrdinal("name")) ? "" : reader.GetString(reader.GetOrdinal("name"));
                            string password = reader.IsDBNull(reader.GetOrdinal("password")) ? "" : reader.GetString(reader.GetOrdinal("password"));
                            user = new User(nam, password);
                        }
                    }
                }
            }
        }
    }
    catch (MySqlException error1)
    {
        MessageBox.Show("数据库错误: " + error1.Message);
    }
    catch (InvalidCastException error2)
    { 
        MessageBox.Show("类型转换错误: " + error2.Message);
    }
    return user;
}

内容的提问来源于stack exchange，提问作者Andrei Catalin Marin