In SQL Server 2008, we can define a table type and use it as a stored procedures' parameter.
But how can I use it in C# invocation of this stored procedure? In other words, how to create a table or list and pass it into a stored procedure in C# code with this new feature of SQL Server 2008?
You need to see this example on CodeProject.
SqlParameter param = cmd.Parameters.AddWithValue("@FileDetails", dt);
where dt is a DataTable, and the @fileDetails parameter is a table type in SQL:
create type FileDetailsType as table
(
FileName varchar(50),
CreatedDate varchar(50),
Size decimal(18,0)
)
Edit: This MSDN Developer's Guide article also would help.
The easiest way is by passing a DataTable as the parameter. Check out some examples here.
In my case, I need to specify the data type explicitly
SqlParameter param = sqlCommand.Parameters.AddWithValue("@something", dtSomething); // dtSomething is a DataTable
param.SqlDbType = SqlDbType.Structured;
param.TypeName = "dbo.type_something"; // The name of the User-Defined Table Type
From Table-Valued Parameters, linked to in Jeff Meatball Yang's answer:
System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or System.Collections.Generic.IEnumerable ([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.
