Introduction:
In this article I will explain how to pass a table valued parameter to stored procedure in asp.net using C#, VB.NET.
Description:
In previous post I explained Pass table as parameter to stored procedure in SQL Server. Now I will explain how to pass a table valued parameter to stored procedure in asp.net using C#, VB.NET.
Before implement this concept first we need to create table type parameter and stored procedure to accept table type as parameter in database for that check this article
Pass table as parameter to stored procedure in SQL Server 2008
Once stored procedure created with table type parameter in database write the following code in your aspx page
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>pass table as parameter to stored procedure in asp.net using c#,vb.net</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="btnSubmit" Text="Submit" runat="server" onclick="btnSubmit_Click" /><br />
<asp:Label ID="lblDetails" runat="server" />
</div>
</form>
</body>
</html>
Now add the following namespaces in code behind
C# Code
using System;
using System.Data;
using System.Data.SqlClient;
After add namespaces write the following code in code behind
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnSubmit_Click(object sender, EventArgs e)
{
// New Table with data
DataTable dt=new DataTable();
dt = new DataTable("EmpDetails");
dt.Columns.Add("EmployeeId", typeof(int));
dt.Columns.Add("EmployeeName", typeof(string));
dt.Columns.Add("EmpRole", typeof(string));
dt.Rows.Add(2,"Mahesh","Developer");
dt.Rows.Add(3,"Prasanthi","Consultant");
dt.Rows.Add(4,"Madhav","Analyst");
dt.Rows.Add(5,"Nagaraju","Developer");
// Send datatable as parameter to stored procedure
using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("prc_InsertEmpDetails", con);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter tblvaluetype= cmd.Parameters.AddWithValue("@TVP", dt); //Passing table value parameter
tblvaluetype.SqlDbType = SqlDbType.Structured; // This one is used to tell ADO.NET we are passing Table value Parameter
int result = cmd.ExecuteNonQuery();
if (result >= 1)
{
lblDetails.Text = result+" Rows Inserted into table ";
}
else
{
lblDetails.Text = "No Rows Inserted into table ";
}
con.Close();
}
}
VB.NET Code
Imports System.Data
Imports System.Data.SqlClient
Partial Class VBCode
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
End Sub
Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As EventArgs)
' New Table with data
Dim dt As New DataTable()
dt = New DataTable("EmpDetails")
dt.Columns.Add("EmployeeId", GetType(Integer))
dt.Columns.Add("EmployeeName", GetType(String))
dt.Columns.Add("EmpRole", GetType(String))
dt.Rows.Add(2, "Mahesh", "Developer")
dt.Rows.Add(3, "Prasanthi", "Consultant")
dt.Rows.Add(4, "Madhav", "Analyst")
dt.Rows.Add(5, "Nagaraju", "Developer")
' Send datatable as parameter to stored procedure
Using con As New SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB")
con.Open()
Dim cmd As New SqlCommand("prc_InsertEmpDetails", con)
cmd.CommandType = CommandType.StoredProcedure
Dim tblvaluetype As SqlParameter = cmd.Parameters.AddWithValue("@TVP", dt)
'Passing table value parameter
tblvaluetype.SqlDbType = SqlDbType.Structured
' This one is used to tell ADO.NET we are passing Table value Parameter
Dim result As Integer = cmd.ExecuteNonQuery()
If result >= 1 Then
lblDetails.Text = result+" Rows Inserted into table "
Else
lblDetails.Text = "No Rows Inserted into table "
End If
con.Close()
End Using
End Sub
End Class
Demo
