c# - 存储过程不允许空参数，即使参数表示接受空值？

Question

我看到很多关于stack overflow的问题，没有一个触及我自己的问题

procedure or function expects parameter which was not supplied

我创建了这个 SQL Server 存储过程:

CREATE proc [dbo].[spAddCustomer]
    @cuName varchar(50),
    @cuAddress varchar(50),
    @cuMobile varchar(50),
    @cuImage image,
    @cityId int,
    @exist int output
AS
BEGIN
    IF NOT EXISTS(SELECT Cu_Mobile FROM tblCustomers WHERE Cu_Mobile = @cuMobile)
    BEGIN
        INSERT INTO tblCustomers (Cu_Name, Cu_Address, Cu_Mobile, Cu_Image, City_ID)
        VALUES (@cuName, @cuAddress, @cuMobile, @cuImage, @cityId)

        SET @exist = 1
    END
    ELSE
        SET @exist = 0
END

在我的数据访问层中，我有一个负责非查询命令的方法:

public static int ExecuteNonQuery(string query, CommandType type, params SqlParameter[] arr)
{
    int outParam;

    SqlCommand cmd = new SqlCommand(query, cn);
    cmd.Parameters.AddRange(arr);
    cmd.CommandType = type;

    int i = cmd.ExecuteNonQuery();

    foreach (SqlParameter param in arr)
    {
        if (param.Direction == ParameterDirection.Output)
        {
            outParam = (int)cmd.Parameters[param.ToString()].Value;
            return outParam;
        }
    }

    return i;
}

负责创建参数的方法:

public static SqlParameter CreateParameter(string name, SqlDbType type, object value, ParameterDirection pd = ParameterDirection.Input)
{
    SqlParameter pr = new SqlParameter();
    pr.ParameterName = name;
    pr.Direction = pd;
    pr.SqlDbType = type;
    pr.SqlValue = value;

    return pr;
}

而这个方法在业务层，传递来自表现层的参数

public static int spAddCustomer(string cusName, string cusAddress, string cusMobile, byte[] arrImg, int cityId)
{
    DataAccessLayer.Open();

    int i = DataAccessLayer.ExecuteNonQuery("spAddCustomer", CommandType.StoredProcedure,
        DataAccessLayer.CreateParameter("@cuName", SqlDbType.VarChar, cusName),
        DataAccessLayer.CreateParameter("@cuAddress", SqlDbType.VarChar, cusAddress),
        DataAccessLayer.CreateParameter("@cuMobile", SqlDbType.VarChar, cusMobile),
        DataAccessLayer.CreateParameter("@cuImage", SqlDbType.Image, arrImg),
        DataAccessLayer.CreateParameter("@cityId", SqlDbType.Int, cityId),
        DataAccessLayer.CreateParameter("@exist", SqlDbType.Int, null, ParameterDirection.Output));

    DataAccessLayer.Close();

    return i;
}

当用户点击添加时，一条新记录被插入到表(tblCustomers)

private void btnAU_Click(object sender, EventArgs e)
{
    byte[] imgArr;

    if (PbCustomer.Image == null)
           imgArr = null;
    else
    {
        MemoryStream ms = new MemoryStream();
        PbCustomer.Image.Save(ms, PbCustomer.Image.RawFormat);
        imgArr = ms.ToArray();
    }

    int cityId = int.Parse(cmbCities.SelectedValue.ToString());

    try
    {
        int exist = CustomerClass.spAddCustomer(txtName.Text, txtAddress.Text, txtMobile.Text, imgArr, cityId);

        if (exist == 1)
        {
            MessageBox.Show("A new customer has been saved");
            txtAddress.Text = txtMobile.Text = txtName.Text = "";
            PbCustomer.Image = null;
        }
        else
            MessageBox.Show("A customer with the same mobile number\nalready exists, add new number!");
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}

但是当我点击 Add 按钮(传递空图像)时，我得到这个错误:

procedure or function 'spAddCustomer' expects parameter '@cuImage' which was not supplied

尽管表 tblCustomers 接受空值

Answer 1

我刚刚发现我可以为存储过程中的参数设置默认值:

ALTER proc [dbo].[spAddCustomer]
@cuName varchar(50)=null,
@cuAddress varchar(50)=null,
@cuMobile varchar(50)= null,
@cuImage image= null,
@cityId int= null,
@exist int output

这解决了我的问题!这对于 PictureBox 中的空图像特别有用，因为我有一个检查空字符串的辅助方法。