Pass int array to Table Value parameter for stored procedure - Failure to convert

Question

I have a stored procedure which accepts table value parameter. I would like to pass an int array to it, but right now I am getting this error:

Failed to convert parameter value from a Object[] to a IEnumerable`1.

My code:

int[] orderIds; //int array which contains 0 or more values

List<SqlParameter> queryList = new List<SqlParameter>
     {
        new SqlParameter("@companyId", SqlDbType.Int) {Value = companyId}, 
        new SqlParameter("@marketplaceId", SqlDbType.Int) {Value = marketplaceId}, 
        new SqlParameter("@marketlocalId", SqlDbType.Int) {Value = marketLocal}, 
        new SqlParameter("@storeId", SqlDbType.Int) {Value = storeId}
     };

if (orderIds != null)
{
     queryList.Add(new SqlParameter("@OrderIdsSpecified", SqlDbType.Bit) {Value = 1});
     // converting int array to object array 
     queryList.Add(new SqlParameter("@OrderIds", SqlDbType.Structured) {Value = orderIds.Select(orderId => (object)orderId).ToArray() }); 
}

using (SqlDataReader reader = SqlHelper.ExecuteReader(connString, CommandType.StoredProcedure, "SPName", queryList.ToArray()))
{
     //reader.read()
}

I tried just passing the int array as is, without conversion to object, but that did not work either. I received the same error message, but with int[] instead of object[].

The table value parameter is just a table with one column of ints, and is already defined.

What am I doing wrong here? Any help is appreciated. Thanks!

Answer 1

SQL parameter of type SqlDbType.Structured expects a value of type table. You cannot just pass an array of integer to it. Please read:

• What does SqlDbType.Structured mean?
• How to pass a table-value parameter

Last answer suggests that you can use DataTable, IEnumerable, or DbDataReader. Whether you need Structured or not, depends on your DB schema. In my experience, creating a SQL type called ListOfId is quite common. It's a table with 1 Id column in it. Below code is taken from another answer:

private static DataTable CreateDataTable(IEnumerable<long> ids) {
    DataTable table = new DataTable();
    table.Columns.Add("ID", typeof(long));
    foreach (long id in ids) {
        table.Rows.Add(id);
    }
    return table;
}

Then you should be able to use CreateDataTable(orderIds) as your sql parameter value.

Answer 2

I don't think you need StructuredData for this.

Try this:

queryList.Add(new SqlParameter("@OrderIds", SqlDbType.Int)
{
    Value = orderIds
});