SQL Server parameters array bol --> dal

Question

The error is cannot convert object to sql parameter

I'm working with a bol->dal, building an array of parameters to pass to the dal:

bol:

SqlParameter[] sqlParams = new SqlParameter[]         
{   
    new SqlParameter("@p1", SqlDbType.VarChar, 30).Value = "val1",
    new SqlParameter("@p2", SqlDbType.VarChar, 30).Value = "val2"
};

dal:

    public static int ExecuteNonQuery(string sql, SqlParameter[] @params)
    {
        SqlConnection cnn = new SqlConnection(connectionstring);
        SqlCommand cmd = new SqlCommand(sql, cnn);
        for (int i = 0; i <= @params.Length - 1; i++)
        {
            cmd.Parameters.Add(@params[i]);
        }

        foreach (IDataParameter param in cmd.Parameters)
        {
            if (param.Value == null) param.Value = DBNull.Value;
        } 

        cnn.Open();
        int retval = cmd.ExecuteNonQuery();
        cnn.Close();
        return retval;
    }

I need help improving this, thanks--

Answer 1

You need to pass in the actual SqlParameter. Notice the object initializer of the Value:

var sqlParams = new SqlParameter[]
        {
            new SqlParameter("@p1", SqlDbType.VarChar, 30) {Value = "val1"},
            new SqlParameter("@p2", SqlDbType.VarChar, 30) {Value = "val2"},
        };