Insert Multiple Rows At A Time From C# To SQL

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] [intNULL,  
  3.     [ItemCode] [varchar](150) NULL,  
  4.     [Name] [varchar](150) NULL,  
  5.     [Price] [intNULL    
  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);  
  11.   
  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

 

Next Recommended Reading Bulk Insert In SQL Server From C#