How can I add members to an array of SqlParameter?

Question

I've got this code:

private SqlParameter[] GetSPParams()
{
    SqlParameter[] spParams = null;
    int loopNum = 1;
    while (ParamExists(loopNum))
    {
        //spParams.
        new SqlParameter()
        {
            ParameterName = GetParamName(loopNum),
            SqlDbType = GetSqlDbType(loopNum),
            Value = GetParamValue(loopNum)
        };
        loopNum++;
    }
    return spParams;
}

...which, as you can see, returns null always.

The "spParams." is commented out because I found no "Add" or such among the Intellisense possibilities - or anything that looked right.

I need a SqlParameter array, because that is what ends up getting passed to my method that runs the Stored Proc:

private void RunStoredProc()
{
    SqlParameter[] spparams = GetSPParams();
    DataTable dtSPResults = SPRunnerSQL.ExecuteSQLReturnDataTable(comboBoxCPSStoredProcs.Text,
        CommandType.StoredProcedure,
        spparams
    );
    rowsReturned = dtSPResults.Rows.Count;
    labelRowsReturned.Text = String.Format("Rows returned: {0}", rowsReturned);
    if (rowsReturned > 0)
    {
        dataGridView.DataSource = dtSPResults;
    }
}

. . .

public static DataTable ExecuteSQLReturnDataTable(string sql, CommandType cmdType, params SqlParameter[] parameters)

So how can I add SqlParameter values to the SqlParameter array?

I think you need to read about Table Value Parameters. https://msdn.microsoft.com/en-us/library/bb675163(v=vs.110).aspx — Valentin, May 27 '16 at 19:14

Felipe Oriani · Accepted Answer · 2016-05-27T19:12:02.910

2

You could use a List<SqlParameter> instead a array, then you should be able to add new elements. Then, just return an array calling the method ToArray() to convert the collection into an array.

private SqlParameter[] GetSPParams()
{
    var spParams = new List<SqlParameter>();
    int loopNum = 1;
    while (ParamExists(loopNum))
    {
        spParams.Add(new SqlParameter()
        {
            ParameterName = GetParamName(loopNum),
            SqlDbType = GetSqlDbType(loopNum),
            Value = GetParamValue(loopNum)
        });
    }
    return spParams.ToArray();
}

Now, if you need to add nem members, you could derive from SqlParameter but as the documentation, this class is sealed.

edited May 27 '16 at 19:12

answered May 27 '16 at 19:06

Felipe Oriani

37,948
19
131
194

1

https://msdn.microsoft.com/ru-ru/library/system.data.sqlclient.sqlparameter(v=vs.110).aspx. The class is sealed. – Valentin May 27 '16 at 19:09
1

Sorry guys, I didn't know that `parameters` are sealed. Thank you! – Felipe Oriani May 27 '16 at 19:12
1

you can't derive from sealed class, but you are still suggesting to derive. – Valentin May 27 '16 at 19:13

How can I add members to an array of SqlParameter?

1 Answers1