' See at the end what you need in SQL Server!

Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Collections.Generic
Imports Microsoft.SqlServer.Server

Module TVPdemo

   ' Connection string. Change to fit to your environment.
   Const connstr As String = _
           "Application Name=TVPdemo;Integrated Security=SSPI;" & _
           "Data Source=.\IKI;Initial Catalog=tempdb"


   ' Procedure to print messages from SQL Server, errors or informational
   ' messages.
   Private Sub PrintSqlMsgs(ByVal msgs As SqlErrorCollection)
      For Each e As SqlError In msgs
         Console.WriteLine ( _
            "Msg {0}, Severity {1}, State: {2}, Procedure {3}, Line no: {4}", _
            e.Number.ToString(), e.Class.ToString(), e.State.ToString(), _
            e.Procedure, e.LineNumber.ToString())
         Console.WriteLine(e.Message)
      Next e
   End Sub

   ' Handler for messages from SQL Server. For this demo, we do not
   ' distinguish between errors and informational messages.
   Private Sub SqlInfoMessage(ByVal sender As Object, _
                              ByVal ea     As SqlInfoMessageEventArgs)
      PrintSqlMsgs(ea.Errors)
   End Sub

   ' Simple generic routine to print the contents of a data set.
   Private Sub PrintDataSet(ds As DataSet)
      Console.WriteLine("============= Dataset =======================")
      If (ds.Tables.Count = 0) Then
         Console.WriteLine("Empty dataset")
      Else
         For Each tbl As DataTable In ds.Tables
            Console.WriteLine("----------------------------------------")
            For Each col As DataColumn In tbl.Columns
               Console.Write(col.ColumnName & vbTab)
            Next
            Console.WriteLine()
            For Each row As DataRow In tbl.Rows
               For Each col As DataColumn In tbl.Columns
                  Console.Write(row(col).ToString() & vbTab)
               Next col
               Console.WriteLine()
            Next row
         Next tbl
      End if
   End Sub

   ' This helper routine sets up our SQL Connection.
   Private Function setup_connection () As SqlConnection
      ' Open the connection.
      Dim cn As New SqlConnection(connstr)

      ' Handle user errors with callbacks, rather than exception.
      AddHandler cn.InfoMessage, AddressOf SqlInfoMessage
      cn.FireInfoMessageEventOnUserErrors = True
      cn.Open()

      Return cn
   End Function

   ' This procedure shows how pass a value to a TVP to a stored procedure
   ' using a List<SqlDataRecord>.
   Private Sub list_example()

      ' Our sample input data.
      Dim products As Integer() = New Integer(3) {9, 12, 27, 37}

      ' Here we use a List<SqlDataRecord>. SqlDataRecord is the namespace
      ' using Microsoft.SqlServer.Server.
      Dim product_list As New List(Of SqlDataRecord)

      ' Create an SqlMetaData object that describes our table type.
      Dim tvp_definition As SqlMetaData() =  _
          New SqlMetaData(0) {new SqlMetaData("n", SqlDbType.Int)}

      ' Loop over the products.
      For Each prodid As Integer In products
         ' Create a new record, using the metadata array above.
         Dim rec As new SqlDataRecord(tvp_definition)
         rec.SetInt32(0, prodid)    ' Set the value.
         product_list.Add(rec)      ' Add it to the list.
      Next prodid

      ' Open a connection and aet up an SqlCommand to call the stored
      ' procedure.
      Using cn As SqlConnection = setup_connection(), _
            cmd As SqlCommand = cn.CreateCommand()

         ' The procedure call itself.
         cmd.CommandType = CommandType.StoredProcedure
         cmd.CommandText = "dbo.get_product_names"

         ' Add the table parameter, for table enum value to use is
         ' Structured. TVPs are input only.
         cmd.Parameters.Add("@prodids", SqlDbType.Structured)
         cmd.Parameters("@prodids").Direction = ParameterDirection.Input

         ' We should specify the table type (although when calling a
         ' a procedure, this is not mandatory).
         cmd.Parameters("@prodids").TypeName = "integer_list_tbltype"

         ' Then we specify the value, this is our List<SqlDataRecord>.
         cmd.Parameters("@prodids").Value = product_list


         ' Time to run the command. To keep the code brief we use a
         ' DataAdapter.Fill, although this may not be the most efficient.
         Using da As New SqlDataAdapter (cmd), _
               ds As New DataSet()
            da.Fill(ds)
            PrintDataSet(ds)
         End Using
      End Using
   End Sub

   ' This procedure shows how to pass a TVP using a DataTable. This time
   ' we don't use a stored procedure, but a plain SQL statement to show
   ' that this is possible.
   Private Sub datatable_example()

      ' Our sample input data.
      Dim custids As String() = _
           New String(3) {"ALFKI", "BONAP", "CACTU", "FRANK"}

      ' Create a DataTable, and define its single column.
      Dim custid_list As new DataTable()
      custid_list.Columns.Add("custid", Type.GetType("System.String"))

      ' Loop over the customer in the table.
      For Each custid As String In custids
         ' Create a new row, save the customer id, and add it to the table.
         Dim dr As DataRow = custid_list.NewRow()
         dr("custid") = custid
         custid_list.Rows.Add(dr)
      Next custid

      ' Open connection and setup the command.
      Using cn As SqlConnection = setup_connection(), _
            cmd As SqlCommand = cn.CreateCommand()

         ' The query to run.
         cmd.CommandText = _
            "SELECT C.CustomerID, C.CompanyName " & _
            "FROM   Northwind.dbo.Customers C " & _
            "WHERE  C.CustomerID IN (SELECT id.custid FROM @custids id)"
         cmd.CommandType = CommandType.Text

         ' Add the table parameter.
         cmd.Parameters.Add("@custids", SqlDbType.Structured)
         cmd.Parameters("@custids").Direction = ParameterDirection.Input

         ' When we use CommandType.Text, we must specify the name of
         ' the table type.
         cmd.Parameters("@custids").TypeName = "custid_list_tbltype"

         ' We pass our data table as the parameter value.
         cmd.Parameters("@custids").Value = custid_list

         ' Time to run the command. To keep the code brief we use a
         ' DataAdapter.Fill, although this may not be the most efficient.
         Using da As new SqlDataAdapter(cmd), _
               ds As new DataSet()
            da.Fill(ds)
            PrintDataSet(ds)
         End Using
      End Using
   End Sub

   ' This example shows how to pass data using a DataReader. The example
   ' is a little cheesy, as it just reads a list of values from an SQL
   ' command. Normally, you would use a DataReader when you have your
   ' input in some other data source: a file, an access database, or even
   ' a TCP port. (In the latter case you would need to implement
   ' IDataReader yourself.)
   Private Sub datareader_example()

      ' Set up a connection using OleDb client we could at least
      ' pretend that it is something else than SQL Server.
      Dim remote_conn As System.Data.OleDb.OleDbConnection = _
          new System.Data.OleDb.OleDbConnection("Provider=SQLOLEDB;" & connstr)
      remote_conn.Open()

      ' Set up the command.
      Dim remote_cmd As System.Data.OleDb.OleDbCommand = _
          remote_conn.CreateCommand()
      remote_cmd.CommandType = CommandType.Text
      remote_cmd.CommandText = _
          "SELECT n FROM (VALUES(9), (12), (27), (37)) as t  (n)"

      ' Set up the reader.
      Dim oledb_reader As System.Data.OleDb.OleDbDataReader = _
          remote_cmd.ExecuteReader()

      ' Now we have the reader, we can run the SQL Server command as
      ' in the other two examples.
      Using cn As SqlConnection = setup_connection(), _
            cmd As SqlCommand = cn.CreateCommand()

         ' The procedure call.
         cmd.CommandType = CommandType.StoredProcedure
         cmd.CommandText = "dbo.get_product_names"

         ' Add the table parameter.
         cmd.Parameters.Add("@prodids", SqlDbType.Structured)
         cmd.Parameters("@prodids").Direction = ParameterDirection.Input
         cmd.Parameters("@prodids").TypeName = "integer_list_tbltype"

         ' Then we specify the value, and this time this is the
         ' OleDB data reader.
         cmd.Parameters("@prodids").Value = oledb_reader

         ' And run the command.
         Using da As new SqlDataAdapter(cmd), _
               ds As new DataSet()
            da.Fill(ds)
            PrintDataSet(ds)
         End Using
      End Using

      ' Cleanup the remote reader.
      oledb_reader.Close()
      oledb_reader.Dispose()
      remote_cmd.Dispose()
      remote_conn.Dispose()
   End Sub


   Public Sub Main()

      ' We use a plain exception handler just to make sure that errors are
      ' printed to command-line window, without getting a message box about
      ' unhandled exception.

      Try
         ' Run the two examples.
         Call list_example()
         Call datatable_example()
         Call datareader_example()
      Catch ex As Exception
         ' For SQL errors, print the full story. (But note that only SQL
         ' errors with severity >= 17 will end up here, since we use
         ' FireInfoMessageEventOnUserErrors.)
         If TypeOf ex Is SqlException Then
            Dim sqlex As SqlException = DirectCast(ex, SqlException)
            PrintSqlMsgs(sqlex.Errors)
         Else
            Console.WriteLine(ex.ToString())
         End If
      End Try
   End Sub
End Module

' To run this demo in SQL Server, first make sure you have the Northwind
' database. Then run this:

' CREATE TYPE integer_list_tbltype AS TABLE (n int NOT NULL PRIMARY KEY)
' CREATE TYPE custid_list_tbltype AS TABLE(custid nchar(5) NOT NULL PRIMARY KEY)
' go
' CREATE PROCEDURE get_product_names @prodids integer_list_tbltype READONLY AS
' SELECT p.ProductID, p.ProductName
' FROM   Northwind..Products p
' WHERE  p.ProductID IN (SELECT n FROM @prodids)