CODEWITHSUNDEEP
c#asp.netsqlado.net
NIck
NIck

Reputation: 563

Pass table valued parameter using ADO.NET

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

Upvotes: 54

Views: 39579

Answers (5)

fcm
fcm

Reputation: 1331

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

Upvotes: 0

Naresh Goradara
Naresh Goradara

Reputation: 1896

  1. Create type in SQL Server:

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

  2. Create Procedure: 

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

BEGIN
    SELECT * FROM @myData
END

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

  4. Create SQL Parameter:

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

Upvotes: 81

Uthaiah
Uthaiah

Reputation: 1303

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

Upvotes: -6

Matthew M.
Matthew M.

Reputation: 1107

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

Upvotes: 20

Ryan Prechel
Ryan Prechel

Reputation: 6762

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

Upvotes: 0

Related Questions