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 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?

score 24 · Accepted Answer · answered Jun 23 '09 at 06:13

24

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.

answered Jun 23 '09 at 06:13

Jeff Meatball Yang

37,839
27
91
125

1

Is this option more efficient to insert multiple records? compared to calling an SP within a foreach and iterating many times? – Alonso Contreras Feb 16 '21 at 12:54

score 2 · Answer 2 · answered Jun 23 '09 at 06:14

2

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

answered Jun 23 '09 at 06:14

Ronald Wildenberg

31,634
14
90
133

score 1 · Answer 3 · answered Mar 19 '19 at 18:56

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

score 0 · Answer 4 · edited May 23 '17 at 12:03

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.

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

4 Answers4

Linked