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

  1. CREATE TYPE [dbo].[ShopProduct] AS TABLE(
  2. [ItemNumber] [int] NULL,
  3. [ItemCode] [varchar](150) NULL,
  4. [Name] [varchar](150) NULL,
  5. [Price] [int] NULL
  6. )

Step 2

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

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

Step 3

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

  1. DataTable dt = new DataTable();
  2. //Add columns
  3. dt.Columns.Add(new DataColumn("ItemNumber", typeof(string)));
  4. dt.Columns.Add(new DataColumn("ItemCode", typeof(string)));
  5. dt.Columns.Add(new DataColumn("Name", typeof(string)));
  6. dt.Columns.Add(new DataColumn("Price", typeof(int)));
  7. //Add rows
  8. dt.Rows.Add("1000", "Code1", "Phone1", 20000);
  9. dt.Rows.Add("1001", "Code2", "Phone2", 30000);
  10. dt.Rows.Add("1002", "Code3", "Phone3", 50000);
  12. //sqlcon as SqlConnection
  13. SqlCommand sqlcom = new SqlCommand("usp_InsertProducts", sqlcon);
  14. sqlcom.CommandType = CommandType.StoredProcedure;
  15. sqlcom.Parameters.AddWithValue("@tableproducts", dt);
  16. sqlcom.Parameters.Add(prmReturn);
  17. sqlcon.Open();
  18. sqlcom.ExecuteNonQuery();

Screenshot of ShopProducts before code execution.

Screenshot of ShopProducts before code execution

Screenshot of ShopProducts after execution.

Screenshot of ShopProducts before after execution