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