c# - 在 C# 中使用参数化查询时抛出异常

Question

我使用参数化查询将数据保存到 sql server。执行时会抛出以下错误。

The parameterized query '(@CustomerID int,@SerialNo nvarchar(2),@ModelNo nvarchar(2),@Sal' expects the parameter '@CreatedBy', which was not supplied.

这是我的代码。

   public static bool SaveSales(int custid, string model, string serial, DateTime salesdate, decimal price, string mainservice, string comments, DateTime createddate, string createdby, DateTime modifieddate, string modifiedby)
    {
        bool saved = false;

        try
        {
            using (SqlConnection conn = new SqlConnection(DBManager.DBConnection))
            {
                conn.Open();
                using (var command = conn.CreateCommand())
                {
                    command.CommandText = "INSERT INTO tbl_Sales VALUES(@CustomerID, @SerialNo, @ModelNo, @SalesDate, @Price, @MaintanenceService, @Comments, @CreatedDate, @CreatedBy, @ModifiedDate, @ModifiedBy)";
                    command.CommandType = CommandType.Text;

                    command.Parameters.AddWithValue("@CustomerID", custid);
                    command.Parameters.AddWithValue("@SerialNo", serial);
                    command.Parameters.AddWithValue("@ModelNo", model);
                    command.Parameters.AddWithValue("@SalesDate", salesdate);
                    command.Parameters.AddWithValue("@Price", price);
                    command.Parameters.AddWithValue("@MaintanenceService", mainservice);
                    command.Parameters.AddWithValue("@Comments", comments);
                    command.Parameters.AddWithValue("@CreatedDate", createddate);
                    command.Parameters.AddWithValue("@CreatedBy", createdby);
                    command.Parameters.AddWithValue("@ModifiedDate", modifieddate);
                    command.Parameters.AddWithValue("@ModifiedBy", modifiedby);

                    command.ExecuteNonQuery();
                }
                conn.Close();
            }

            saved = true;
        }
        catch (SqlException ex)
        {
            MessageBox.Show(ex.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Error);
            return saved;
        }

        return saved;
    }

我的查询有什么问题？我使用参数化查询是为了正确保存销售日期。任何帮助将不胜感激。

Answer 1

我怀疑 createdby 是 null，这意味着它不会被发送 - 它需要是 DBNull。

尝试:

command.Parameters.AddWithValue("@CreatedBy", createdby ?? (object)DBNull.Value);

或者:尝试使用像 Dapper 这样的工具，它可以:a:总体上更容易，b:做对；例如:

using (SqlConnection conn = new SqlConnection(DBManager.DBConnection))
{
    conn.Execute("INSERT INTO tbl_Sales VALUES(@CustomerID, @SerialNo, @ModelNo, @SalesDate, @Price, @MaintanenceService, @Comments, @CreatedDate, @CreatedBy, @ModifiedDate, @ModifiedBy)",
      new {
        CustomerID = custid, SerialNo = serial,
        ModelNo = model, SalesDate = salesdate,
        Price = price, MaintanenceService = mainservice,
        Comments = comments, CreatedDate = createddate,
        CreatedBy = createdby, ModifiedDate = modifieddate,
        ModifiedBy = modifiedby });
}

或者更简单:

using (SqlConnection conn = new SqlConnection(DBManager.DBConnection))
{
    conn.Execute("INSERT INTO tbl_Sales VALUES(@custid, @serial, @model, @salesdate, @price, @mainservice, @comments, @createddate, @createdby, @modifieddate, @modifiedby )",
      new {
        custid, serial, model, salesdate,
        price, mainservice, comments, createddate,
        createdby, modifieddate, modifiedby });
}