Pass table valued parameter using ADO.NET

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

Contents hide

Answers:

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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);

Method 2

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";

Method 3

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

Method 4

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)

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

Method 5

You can prefix with Exec

using( SqlConnection con = new SqlConnection( "Server=.;database=employee;user=sa;password=12345" ) )
    {
        SqlCommand cmd = new SqlCommand( " exec ('drop table '<a href="https://getridbug.com/cdn-cgi/l/email-protection" class="__cf_email__" data-cfemail="e2c9a2968380">[email protected]</a>)" , con );
        cmd.Parameters.AddWithValue( "@tab" ,"Employee" );
        con.Open( );
        cmd.ExecuteNonQuery( );
    }

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating