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

Question

In SQL Server 2008, we can define a table type and use it as Stored Procedures' parameters. But How can I use it in C# invocation of this SP? In other words, how to create a table or list and pass it into Stored Procedure in C# code with this new feature of SQL Server 2008?

Answer 1

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.

Answer 2

The easiest way is by passing a DataTable as the parameter. Check out some examples here.