Passing IEnumerable to a SQL Server stored procedure as a user defined table type parameter

Question

I am attempting to pass a list of strings as a parameter into a store procedure that is expecting a UDT of a table with a single column. I am running into errors with SqlCommand object, and I've found posts that say to use a DataTable:

How to pass User Defined Table Type as Stored Procedured parameter in C#

I am wondering if there is an easier way considering that I don't have multiple columns. In the procedure, I am working with the table type as a sub-select against other tables, so it would be nice to keep this functionality.

Is there a better way, or just convert to DataTable?

Thanks!

See [the currently accepted answer](http://stackoverflow.com/a/1253737/173497) to the existing question [c# - Convert IEnumerable to DataTable](http://stackoverflow.com/questions/1253725/convert-ienumerable-to-datatable) for some nice code with which you can convert an `IEnumerable` to a `DataTable`. — Kenny Evitt, May 16 '14 at 18:47

Kenny Evitt · Accepted Answer · 2014-05-16T13:17:36.433

You don't have to use DataTable:

System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or System.Collections.Generic.IEnumerable<SqlDataRecord> ([T:System.Collections.Generic.IEnumerable`1)] objects. You must specify a type name for the table-valued parameter by using the TypeName property of a SqlParameter. The TypeName must match the name of a compatible type previously created on the server. The following code fragment demonstrates how to configure SqlParameter to insert data.

The above is from Table-Valued Parameters (MSDN).

... but using DataTable is probably much easier than the alternatives; here's a simple example showing how to create a DataTable from an IEnumerable<string>:

IEnumerable<string> strings = new List<string>() { "blah", "blah blah", "blah blah blah" };

DataTable table = new DataTable();

table.Columns.Add(
    new DataColumn()
    {
        DataType = Type.GetType("System.String"),
        ColumnName = "String"
    });

DataRow row;

foreach (string aString in strings)
{
    row = table.NewRow();
    row["String"] = aString;
    table.Rows.Add(row);
}

score 2 · Answer 2 · answered May 16 '14 at 20:13

Kenny's advice was what I was looking for. Here's the actual implementation, minus the normal command initialization.

DataTable dt = new DataTable();
dt.Columns.Add(new DataColumn() 
 { DataType = Type.GetType("System.String"), ColumnName = "colname" });

DataRow row;

stringArray.ToList().ForEach(s => 
 { 
   row = dt.NewRow();
   row["colname"] = s;
   dt.Rows.Add(row); 
 });

cmd.Parameters.Add(new SqlParameter("@colname", SqlDbType.Structured)
 {
   Direction = ParameterDirection.Input,
   Value = dt,
 });

Passing IEnumerable to a SQL Server stored procedure as a user defined table type parameter

2 Answers2