Pass table valued parameter using ADO.NET

Question

How to pass table valued parameter to stored procedure using ADO.NET?

See also https://stackoverflow.com/q/5595353/8479 – Rory Jun 09 '22 at 20:13 — Rory, Jun 09 '22 at 20:13

score 81 · Accepted Answer · edited Apr 28 '17 at 20:33

81

Create type in SQL Server:

CREATE TYPE [dbo].[MyDataType] As Table
(
    ID INT,
    Name NVARCHAR(50)
)

Create Procedure:

CREATE PROCEDURE [dbo].[MyProcedure]
(
    @myData As [dbo].[MyDataType] Readonly
)
AS

BEGIN
    SELECT * FROM @myData
END

Create DataTable in C#:

DataTable myDataTable = new DataTable("MyDataType");
myDataTable.Columns.Add("Name", typeof(string));
myDataTable.Columns.Add("Id", typeof(Int32));
myDataTable.Rows.Add("XYZ", 1);
myDataTable.Rows.Add("ABC", 2);

Create SQL Parameter:

SqlParameter parameter = new SqlParameter();
parameter.ParameterName = "@myData";
parameter.SqlDbType = System.Data.SqlDbType.Structured;
parameter.Value = myDataTable;
command.Parameters.Add(parameter);

edited Apr 28 '17 at 20:33

KyleMit

30,350
66
462
664

answered May 02 '12 at 07:49

Naresh Goradara

1,896
2
30
41

This is applicable only in Sql Server 2008 – Ashish Gupta Dec 27 '13 at 04:25
I meant this is available only Sql server 2008 and above and so It would work in 2012 too. – Ashish Gupta Dec 27 '13 at 15:33
7

**Note**, I believe that the columns from the DataTable you create in Step 3 must match column order you define in your Table Type in Step 1. You are just passing in a grid of data and then SQL Server reads it in positionally. In fact, the actual column name you give it in .NET is just a string for your own benefit - it can be different as SQL won't use it. – KyleMit Apr 28 '17 at 20:52
Can a `DataTable` created in C# be used in a Entity Framework LINQ query? – Luke T O'Brien Jun 27 '17 at 14:55
@KyleMit You are correct - the column order must match. xlink to related SO question: https://stackoverflow.com/questions/40628276/fastmember-column-order-preservation – callisto Oct 02 '18 at 19:44
1

What do you do if you want to use a TVP in a single SQL query and not inside a stored-procedure? Do you need the `TYPE` declaration at all in that case? – Dai Feb 05 '19 at 01:19
As per @MatthewM's answer you need to set `parameter.TypeName` for this to work https://stackoverflow.com/a/22948571/8479 – Rory Jun 09 '22 at 20:13

score 20 · Answer 2 · answered Apr 08 '14 at 21:30

20

I tried this and received the exception:

The table type parameter '@MyDataType' must have a valid type name.

I had to set the "TypeName" property of the SqlParameter:

parameter.TypeName = "MyDataType";

answered Apr 08 '14 at 21:30

Matthew M.

932
10
17

Adding the db schema as part of the table name did it for me : DataTable myDataTable = new DataTable("dbo.MyDataType"); – noontz Jul 31 '17 at 14:10

score 0 · Answer 3 · edited May 23 '17 at 12:26

0

This question is a duplicate of How to pass table value parameters to stored procedure from .net code. Please see that question for an example illustrating the use of either a DataTable or an IEnumerable<SqlDataRecord>.

edited May 23 '17 at 12:26

Community

1
1

answered May 28 '12 at 05:16

Ryan Prechel

6,592
5
23
21

score 0 · Answer 4 · answered Oct 23 '19 at 06:36

For multilinguals, a little late to the show:

a) elsewhere on tsql

--- create a vector data type
CREATE TYPE [dbo].[ItemList] AS TABLE([Item] [varchar](255) NULL)

b)

Dim Invoices As New DataTable("dbo.ItemList") 'table name is irrelevant
Invoices.Columns.Add("Invoice", GetType(String))
...
        With .SqlCommand.Parameters
            .Clear()
            .Add(New Data.SqlClient.SqlParameter() With {
                        .SqlDbType = Data.SqlDbType.Structured,
                        .Direction = Data.ParameterDirection.Input,
                        .ParameterName = "@Invoices",
                        .TypeName = "dbo.ItemList",
                        .Value = Invoices})
        End With
...
   ' using  store procedure
   .CommandText = "SELECT * FROM dbo.rpt(@invoices) "
   ' or direct reference is a select
   .CommandText = "SELECT * FROM dbo.invoicedata" +
        "where ((select count(*) from @invoices) = 0 or "+
             "InvoiceNumber in (select distinct * from @Invoices))

score -6 · Answer 5 · answered Apr 30 '14 at 12:46

You can prefix with Exec

using( SqlConnection con = new SqlConnection( "Server=.;database=employee;user=sa;password=12345" ) )
    {
        SqlCommand cmd = new SqlCommand( " exec ('drop table '+@tab)" , con );
        cmd.Parameters.AddWithValue( "@tab" ,"Employee" );
        con.Open( );
        cmd.ExecuteNonQuery( );
    }

Pass table valued parameter using ADO.NET

5 Answers5

Linked

Related