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