Table Value Parameter Type
CREATE TYPE [dbo].[empname_tablevalue] AS TABLE(
[name] [varchar](max) NULL,
[salary] [int] NULL
)
Store Procedure
Create proc [dbo].[sptablevalues]( @f varchar(20), @tblemp empname_tablevalue readonly)
as
begin
if @f='L'
begin
select * from EmpName
end
if @f='I'
begin
insert into EmpName (Name,Salare) select name,salary from @tblemp;
select 'Successfully'
end
end
C#
protected void btnsave_Click(object sender, EventArgs e)
{
DataTable dt = tblempnameColumns();
DataRow dr = dt.NewRow();
dr["Name"] = txtFullname.Text;
dr["Salary"] = txtsalary.Text;
dt.Rows.Add(dr);
BLClass _b = new BLClass();
Info _I = new Info();
_I._p_TableValue = dt;
_b.InsertUser_TableValue(_I);
}
private DataTable tblempnameColumns()
{
DataTable dt = new DataTable();
dt.Columns.Add("Name", typeof(string));
dt.Columns.Add("Salary", typeof(int));
return dt;
}
BLClass
public class BLClass
{
public BLClass()
{
//
// TODO: Add constructor logic here
//
}
public string InsertUser_TableValue(Info _I)
{
SqlConnection con = new SqlConnection(ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand("sptablevalues", con);
cmd.CommandType = CommandType.StoredProcedure;
try
{
cmd.Parameters.AddWithValue("@tblemp", _I._p_TableValue);
cmd.Parameters.AddWithValue("@f", "I");
cmd.ExecuteNonQuery();
con.Close();
string i = "0";
return i;
}
catch (Exception ex)
{
throw ex;
}
finally
{
cmd.Dispose();
con.Close();
con.Dispose();
}
}
}
Info Class
public class Info
{
private DataTable _TableValue;
public DataTable _p_TableValue
{
get
{ return _TableValue; }
set { _TableValue = value; }
}
public Info()
{
}
}
