Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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?

like image 426
Sonic Lee Avatar asked Jun 23 '09 06:06

Sonic Lee


People also ask

Can we pass table as a parameter to stored procedure?

Table-Valued Parameters aka TVPs are commonly used to pass a table as a parameter into stored procedures or functions. They are helpful in a way, we can use a table as an input to these routines and we can get rid of dealing more complex steps to achieve this process.

How do you execute a user-defined table type?

Firstly, we create a procedure that updates the record. Now we create a table type variable and pass this variable to stored procedure as user-defined table type parameter. After execution of the stored procedure, let us check the data of Employee tables.


2 Answers

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.

like image 76
Jeff Meatball Yang Avatar answered Oct 23 '22 12:10

Jeff Meatball Yang


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

like image 27
Ronald Wildenberg Avatar answered Oct 23 '22 12:10

Ronald Wildenberg