Aspdotnet-Suresh

aspdotnet-suresh offers C#.net articles and tutorials,csharp dot net,asp.net articles and tutorials,VB.NET Articles,Gridview articles,code examples of asp.net 2.0 /3.5,AJAX,SQL Server Articles,examples of .net technologies

Asp.net- Pass a Table Valued Parameter to Stored Procedure in C# VB.NET Example

Sep 23, 2012
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


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

If you observe above output whenever we click on button it is showing 4 records inserted into table because from our code behind we are passing 4 values through table.

If you enjoyed this post, please support the blog below. It's FREE!

Get the latest Asp.net, C#.net, VB.NET, jQuery, Plugins & Code Snippets for FREE by subscribing to our Facebook, Twitter, RSS feed, or by email.

subscribe by rss Subscribe by RSS subscribe by email Subscribe by Email

14 comments :

kranthi said...

how could the if and else results will be the same.

Suresh Dasari said...

@Karanthi...
if there is no record inserted means then we will get result "0 rows inserted into table" that's why i used same statement for both if and else. Anyway i changed both conditions. Please check it once.

Anonymous said...

excelevt

Anonymous said...

l

Anonymous said...

create table emp(empno int,Ename varchar(50),Job varchar(50),salary varchar(30))
select * from emp1
insert emp1 values(568,'bdystg','dasd',10111)
insert emp1 values(235,'fgfd','rakash',12345)
insert emp1 values(897,'fgfdgy','kshd',4564)
insert emp(empno,Ename) values (789,'srujan')
select * into emp1 from emp
update emp set Job='sujith' where empno=789

update emp set salary='50000' where empno=789

select * from emp where Ename='sujith' or empno=345 or Job = 'kshd'
select * from emp order by salary desc
select Job from emp where empno=345 union select Job from emp where empno=123

create view emp2 as select * from emp where empno=345
select * from emp2

select emp.*,emp1.empno,emp1.Ename,emp1.Job,emp1.salary from emp full outer join emp1 on emp.empno=emp1.empno
select * from emp union select * from emp1




Stored procedure



CREATE PROCEDURE sujith1
(@Id int,@Name varchar(50),@Job varchar(50),@salary varchar(50))
AS
BEGIN

SET NOCOUNT ON;

insert into emp(empno,Ename,Job,salary)
values(@Id,@Name,@Job,@salary)

END
GO


Anonymous said...

con.Open();
SqlCommand cmd3 = new SqlCommand("select *from Department", con);
SqlDataReader dr3 = cmd3.ExecuteReader();
DataTable dt = new DataTable();
dt.Load(dr3);
for (int i = dt.Rows.Count - 1;i>=0; i--)
{
CheckBox ck = (CheckBox)GridView1.Rows[i].Cells[0].FindControl("chk");
if (ck.Checked == true)
{


SqlCommand cmd = new SqlCommand("delete from Department where DeptName='" + GridView1.Rows[i].Cells[2].Text.ToString() + "'", con);
int k = cmd.ExecuteNonQuery();
SqlCommand cmd1 = new SqlCommand("select *from Department", con);
SqlDataReader dr = cmd1.ExecuteReader();
DataTable dt1 = new DataTable();
dt1.Load(dr);
GridView1.DataSource = dt;
GridView1.DataBind();
con.Close();




}

Anonymous said...

con.Open();
int i;
string path = "D://Exam.csv";
StringBuilder csv1 = new StringBuilder();
if (!File.Exists(path))
File.Delete(path);
SqlCommand c = new SqlCommand("select * from tblStates", con);
SqlDataReader s = c.ExecuteReader();
i = s.FieldCount;
while (s.Read())
{
for (int j = 0; j < i; j++)
{
csv1.Append(s[j].ToString().Trim());
csv1.Append(",");
}
csv1.Remove(csv1.Length - 1, 1);
csv1.AppendLine();
}
s.Close();
con.Close();
File.WriteAllText(path, csv1.ToString());
TextBox1.Text = csv1.ToString();

Anonymous said...

string s = "insert into tblEmployees values('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "'," + DropDownList1.SelectedItem.Text + "," + DropDownList2.SelectedItem.Text + "," + DropDownList3.SelectedItem.Text + ")";

Anonymous said...

con.Open();
string s1="", s2="", s3="";
SqlCommand cmd = new SqlCommand("select DeptID from Department where DeptName='"+DropDownList1.Text+"'", con);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
s1=dr[0].ToString();
}
dr.Close();
SqlCommand cmd1 = new SqlCommand("select CtryID from Countries where CtryName='"+DropDownList2.Text+"'", con);
SqlDataReader dr1 = cmd1.ExecuteReader();
while (dr1.Read())
{
s2=dr1[0].ToString();
}
dr1.Close();
SqlCommand cmd2 = new SqlCommand("select StID from States where StName='"+DropDownList3.Text+"'", con);
SqlDataReader dr2 = cmd2.ExecuteReader();
while (dr2.Read())
{
s3=dr2[0].ToString();
}
dr2.Close();



string s = "insert into Employees values('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "','" +Convert.ToDateTime(TextBox4.Text.ToString()) + "','" + s1 + "'," + TextBox5.Text + "," + s2 + "," + s3 + ",'"+true+"')";

SqlCommand cmg = new SqlCommand(s, con);
int k = cmg.ExecuteNonQuery();
con.Close();

Anonymous said...

con.Open();
SqlCommand cmd = new SqlCommand("select DeptName from Department", con);
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
DropDownList1.Items.Add(dr[0].ToString());
}
dr.Close();
SqlCommand cmd1 = new SqlCommand("select CtryName from Countries", con);
SqlDataReader dr1 = cmd1.ExecuteReader();
while (dr1.Read())
{
DropDownList2.Items.Add(dr1[0].ToString());
}
dr1.Close();
SqlCommand cmd2 = new SqlCommand("select StName from States", con);
SqlDataReader dr2 = cmd2.ExecuteReader();
while (dr2.Read())
{
DropDownList3.Items.Add(dr2[0].ToString());
}
dr2.Close();

con.Close();

Anonymous said...

gud

Anonymous said...

Sir , I want to know coding for search.Means every site has textbox for search about that particular site,so i can not find out that how to develope it in c#.net .Please help me sir ,,,,,its urgent

thanks a lot .......

Anonymous said...

hey suresh can u tell pr puslish the article about ASP.Net URL Rewriting

Anonymous said...

Hi Mr.Suresh Give Example of One StoreProcedure With Multiple Querys(e.g Insert,Update) And How Implement it to in Asp.Net(C#) Coding..Pls Help...

Give your Valuable Comments

Note: Only a member of this blog may post a comment.

© 2015 Aspdotnet-Suresh.com. All Rights Reserved.
The content is copyrighted to Suresh Dasari and may not be reproduced on other websites without permission from the owner.