Passing the value to the table variable parameters of stored procedure using VB.NET OR C# code

We can pass the value to the table variable of table type as parameters of stored procedure in sql server by VB.NET code or by C#. For this we have to follow following steps:

1. Creating a demo table type in sql server:

CREATE TYPE DataType AS TABLE(

     ntData INT NOT NULL

)

2. Creating a stored procedure with parameter of type table variable. This procedure only selects the all the records from table variable:

CREATE PROC uspGetData(

     @tblDataType AS DataType READONLY

)

AS

BEGIN

     SELECT * FROM @tblDataType

END

3. Writing VB.Net code to pass the data table to table variable and display the output in console:

Imports System.Data.SqlClient

Module Module1

    Sub Main()

        Dim ConnObj As SqlConnection = Nothing

        Dim CommandObj As SqlCommand

        Dim ObjSqlReader As SqlDataReader = Nothing

        Dim ParamObj As SqlParameter

        Dim ArrayObj As Integer() = {1, 2, 3, 4, 5}

        Dim DataTableObj As New DataTable()

        Try

            'Adding data into data table from array

            DataTableObj.Columns.Add("Value")

            For Each Value As String In ArrayObj

                DataTableObj.Rows.Add(Value)

            Next

            'Creating connection database connection

            ConnObj = New SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Data Source=ServerName;Initial Catalog=DbName")

            ConnObj.Open()

            CommandObj = New SqlCommand("uspGetData", ConnObj)

            CommandObj.CommandType = CommandType.StoredProcedure

            'Adding the paramter for table varaible @tblDataType and passing data table.

            ParamObj = CommandObj.Parameters.AddWithValue("@tblDataType", DataTableObj)

            ParamObj.SqlDbType = SqlDbType.Structured

            ObjSqlReader = CommandObj.ExecuteReader()

            'displaying out of procedure in console

            While (ObjSqlReader.Read)

                Console.WriteLine(ObjSqlReader.GetInt32(0))

            End While

        Catch ex As Exception

            Console.WriteLine(ex.Message)

        Finally

            If ConnObj.State = ConnectionState.Open Then

                ConnObj.Close()

                Console.ReadKey()

            End If

        End Try

    End Sub

End Module

Its equivalent C# code:

using System.Data.SqlClient;

using System.Data;

using System;

static class Module1

{

    public static void Main()

    {

        SqlConnection ConnObj = null;

        SqlCommand CommandObj = default(SqlCommand);

        SqlDataReader ObjSqlReader = null;

        SqlParameter ParamObj = default(SqlParameter);

        int[] ArrayObj = {

                     1,

                     2,

                     3,

                     4,

                     5

              };

        DataTable DataTableObj = new DataTable();

        try

        {

            //Adding data into data table from array

            DataTableObj.Columns.Add("Value");

            foreach (Int32 Value in ArrayObj)

            {

                DataTableObj.Rows.Add(Value);

            }

            //Creating connection database connection

            ConnObj = new SqlConnection("Integrated Security=SSPI;Persist Security Info=False;Data Source=ServerName;Initial Catalog=DbName");

            ConnObj.Open();

            CommandObj = new SqlCommand("uspGetData", ConnObj);

            CommandObj.CommandType = CommandType.StoredProcedure;

            //Adding the paramter for table varaible @tblDataType and passing data table.

            ParamObj = CommandObj.Parameters.AddWithValue("@tblDataType", DataTableObj);

            ParamObj.SqlDbType = SqlDbType.Structured;

            ObjSqlReader = CommandObj.ExecuteReader();

            //displaying out of procedure in console

            while ((ObjSqlReader.Read()))

            {

                Console.WriteLine(ObjSqlReader.GetInt32(0));

            }

        }

        catch (Exception ex)

        {

            Console.WriteLine(ex.Message);

        }

        finally

        {

            if (ConnObj.State == ConnectionState.Open)

            {

                ConnObj.Close();

                Console.ReadKey();

            }

        }

    }

}

Output:

1

2

3

4

5