参数化查询需要‘@firstname nvarchar，@lastname nvarchar，@phone nvarchar’参数@email，但未提供

Question

有没有上师可以帮我解决这个问题呢？我一直在C#中抛出这个异常，但我看到的一切似乎都是正确的。以下是代码

  public bool Insert(loginBLL u)
    {
        bool isSuccess = false;
        SqlConnection conn = new SqlConnection(myconnstrng);

        try
        {
            String sql = "INSERT INTO customers (firstname, lastname, phone, email, address, city, state, zipcode, password, username) VALUES (@firstname, @lastname, @phone, @email, @address, @city, @state, @zipcode, @password, @username)";
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.AddWithValue("@firstname", u.firstname);
            cmd.Parameters.AddWithValue("@lastname", u.lastname);
            cmd.Parameters.AddWithValue("@phone", u.phone);
            cmd.Parameters.AddWithValue("@email", u.email);
            cmd.Parameters.AddWithValue("@address", u.address);
            cmd.Parameters.AddWithValue("@city", u.city);
            cmd.Parameters.AddWithValue("@state", u.state);
            cmd.Parameters.AddWithValue("@zipcode", u.zipcode);
            cmd.Parameters.AddWithValue("@password", u.password);
            cmd.Parameters.AddWithValue("@username", u.username);

         
            conn.Open();

            int rows = cmd.ExecuteNonQuery();

Answer 1

我怀疑您的问题是由于为空参数传递(object)null而不是DBNull.Value造成的。

现在，我通过编写一个扩展方法来绑定来自FormattableString的参数，从而避免了所有的参数样板。

        public static SqlCommand GetCommandInterpolated(this SqlConnection conn, FormattableString sql)
        {
            var cmd = conn.CreateCommand();
            var replacements = new string[sql.ArgumentCount];
            var args = sql.GetArguments();

            for (int i = 0; i < sql.ArgumentCount; i++)
            {
                var p = cmd.CreateParameter();
                cmd.Parameters.Add(p);
                p.ParameterName = replacements[i] = $"@p_{i}";
                p.Value = args[i] ?? DBNull.Value;
            }
            cmd.CommandText = string.Format(sql.Format, replacements);
            return cmd;
        }


        var cmd = conn.GetCommandInterpolated(
            $@"INSERT INTO customers (firstname, lastname, phone, email, address, city, state, zipcode, password, username)
            VALUES ({u.firstname}, {u.lastname}, {u.phone}, {u.email}, {u.address}, {u.city}, {u.state}, {u.zipcode}, {u.password}, {u.username}"
        );