Feb 5, 2013

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

No comments:

Post a Comment