In Focus

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

  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