c# - 服务将空值发布到 MySQL 数据库

Question

我正在尝试将 JSON 对象发布到我的服务，反序列化它并将其保存到数据库。它有效 - 有点。问题是 JSON 的某些字段被保存到数据库中，而其他字段则为空。

例如，发布此 JSON 时:

{
"FirstName": "Michael",
"LastName": "Ledley",
"BirthPlace": "Austria",
"Gender": "M",
"OIB": "12348879991",
"CurrentPlace": "New Guinea",
"Department": "D_21570"
}

...在数据库中，仅 CurrentPlace、Gender 和 Department 正确存储，而所有其他值 (FirstName 、姓氏、出生地、...) 为 NULL。它们的类型都是 VARCHAR(45)，与正确存储的 CurrentPlace 相同。

执行保存的代码如下所示:

[RoutePrefix("api/employee")]
public class EmployeeApiController : ApiController
{

    readonly EmployeePersistence persistence;

    public EmployeeApiController()
    {
        persistence = new EmployeePersistence();
    }

    [HttpPost]
    [Route("")]
    public void Post([FromBody] Employee employee)
    {
        // saving id for the debugging purposes
        long id = persistence.SaveEmployee(employee);
    }


    public long SaveEmployee(Employee employee)
    {
        string sqlString =
            "INSERT INTO Employee (FirstName, LastName, BirthPlace, CurrentPlace, Gender, Department, OIB) " +
            "VALUES (@FirstName, @LastName, @BirthPlace, @CurrentPlace, @Gender, @Department, @OIB)";

        MySqlCommand cmd = new MySqlCommand(sqlString, conn);

        cmd.Parameters.AddWithValue("@FirstName", employee.FirstName);
        cmd.Parameters.AddWithValue("@LastName", employee.LastName);
        cmd.Parameters.AddWithValue("@BirthPlace", employee.BirthPlace);
        cmd.Parameters.AddWithValue("@CurrentPlace", employee.CurrentPlace);
        cmd.Parameters.AddWithValue("@Gender", employee.Gender == EmployeeGender.M ? 1 : 0);
        cmd.Parameters.AddWithValue("@Department", employee.Department.GetStringValue());
        cmd.Parameters.AddWithValue("@OIB", employee.OIB);

        ExecuteSqlCommand(cmd);
        return cmd.LastInsertedId;
    }

    void ExecuteSqlCommand(MySqlCommand cmd)
    {
        try
        {
            // execute the SQL command
            cmd.ExecuteNonQuery();
        }
        catch (MySqlException e)
        {
            // log the error
            throw new Exception(
                String.Format("Error executing the command '{0}'. The error is '{1}'.",
                              cmd, e.Message));
        }
    }

为什么保存到数据库时有些值是 NULL，有些值不是 NULL？

Answer 1

您可能会发现更完整地填充 Parameter 对象很有帮助。

而不是...

 MySqlCommand cmd = new MySqlCommand(sqlString, conn);  
 cmd.Parameters.AddWithValue("@FirstName", employee.FirstName);
  ...

试试这个。

 MySqlCommand cmd = new MySqlCommand(sqlString, conn);  
 cmd.Parameters.Add("@CategoryName", SqlDbType.VarChar, 45).Value = employee.FirstName;
  ...

重点是显式声明参数的数据类型和长度。