Insert Bulk Row at a Time in SQL Table from C# Code

1. Initially create Type in SQL Server
 
----Create type-----
create type _table as table
(
E_Name varchar(50),
Salary varchar(200)
)

2. After that create Procedure in sql:=>here I have to use _table type as table-valued parameter in Procedure.

The table-valued parameter "@tablevariable
" must be declared with the READONLY option.
 
------Create procedure with the table-valued parameter---
 
create proc uspInsertBulkRow
 
@tablevariable _table readonly
as
begin
insert into mytable(Name,salary) select E_Name,Salary from @tablevariable
end

3. Create 
this method that contains multiple rows
 
private DataTable _datatableData(){
DataTable _dt = new DataTable();
_dt.Columns.Add("id");
_dt.Columns.Add("Name");
_dt.Columns.Add("Salary");
_dt.Rows.Add("1","Anu", "12000");
_dt.Rows.Add("3","Anamika", "15000");
_dt.Rows.Add("4", "Ram", "17000");
 _dt.Rows.Add("5","Aman", "4000");
_dt.Rows.Add("6","Tarun", "18000");
_dt.Rows.Add("7","Akash", "5000");
return _dt;}
 
Then write this code on the Save button click
 
Here I have to pass the datatable as a parameter value in the stored procedure and This procedure inserts the multiple row in the SQL table.
 
using (SqlCommand cmd = new SqlCommand("uspInsertBulkRow", con))
{
      _datatableData() Is a  method and its return datatable- this method contain multiple row----------
      cmd.Parameters.AddWithValue("@tablevariable", _datatableData());
      cmd.CommandType = CommandType.StoredProcedure;
      con.Open();
      cmd.ExecuteNonQuery();
      con.Close();
}

Output is: Its a simple example. you can use this concept as per requirement
f.PNG