Insert Multiple Rows At A Time From C# To SQL

We can insert multiple records from c# to sql in a single time. Ultimately saves number of database request.

We can insert multiple records from C# to SQL in a single instance. This ultimately saves a number of database requests. Please follow the below steps to achieve this.

Step 1

Create a user-defined table type in SQL.

Example

CREATE TYPE [dbo].[ShopProduct] AS TABLE(
    [ItemNumber] [int] NULL,
    [ItemCode] [varchar](150) NULL,
    [Name] [varchar](150) NULL,
    [Price] [int] NULL
)

Step 2

Create a stored procedure to accept the above created table type as input. This can be invoked from C#

create procedure [dbo].[usp_InsertProducts](@tableproducts ShopProduct readonly)
as
begin
   insert into ShopProducts select [ItemCode],[Name],[Price] from @tableproducts
end

Step 3

Invoke the stored procedure created in step 2 from C# code. 

DataTable dt = new DataTable();
//Add columns
dt.Columns.Add(new DataColumn("ItemNumber", typeof(string)));
dt.Columns.Add(new DataColumn("ItemCode", typeof(string)));
dt.Columns.Add(new DataColumn("Name", typeof(string)));
dt.Columns.Add(new DataColumn("Price", typeof(int)));
//Add rows
dt.Rows.Add("1000", "Code1", "Phone1", 20000);
dt.Rows.Add("1001", "Code2", "Phone2", 30000);
dt.Rows.Add("1002", "Code3", "Phone3", 50000);

//sqlcon as SqlConnection
SqlCommand sqlcom = new SqlCommand("usp_InsertProducts", sqlcon);
sqlcom.CommandType = CommandType.StoredProcedure;
sqlcom.Parameters.AddWithValue("@tableproducts", dt);
sqlcom.Parameters.Add(prmReturn);
sqlcon.Open();
sqlcom.ExecuteNonQuery();

Screenshot of ShopProducts before code execution.

 

Screenshot of ShopProducts after execution.