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 Email
|
|||
14 comments :
how could the if and else results will be the same.
@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.
excelevt
l
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
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();
}
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();
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 + ")";
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();
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();
gud
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 .......
hey suresh can u tell pr puslish the article about ASP.Net URL Rewriting
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...
Note: Only a member of this blog may post a comment.