Insert Bulk Data through procedure

Introduction: Through Ado.net we can insert entire data table data in database follow below steps.

Step 1: Create Table

  1. [IR] is my schema name
  3. CREATE table [IR].[PRODUCT]
  5. (
  7. PRODUCTID INT IDENTITY(1,1) PRIMARY KEY,
  9. PRODUCTNAME VARCHAR(50)NOT NULL,
  11. PRICE INT NOT NULL,
  13. CREATED_DATE DATETIME DEFAULT GETDATE()
  15. );
Step2: Create Type
  1. CREATE TYPE [IR].[ProductType] AS TABLE(
  3. [ProductName] [varchar](50)Not null,
  5. [Price] int not null
  7. )
Step 3: Create Procedure
  1. CREATE PROCEDURE [IR].[InsertProduct]
  3. @ProductTable [IR].[ProductType] READONLY
  5. AS
  7. BEGIN
  9. SET NOCOUNT ON;
  11. ------INSETING DATA IN TABLE
  13. INSERT INTO [IR].[PRODUCT](PRODUCTNAME,PRICE)
  15. SELECT * FROM @ProductTable
  17. SELECT @@rowcount;
  19. END
Step 4: Source Code
  1. using System;
  3. using System.Configuration;
  5. using System.Data;
  7. using System.Data.SqlClient;
  9. namespace Bulk
  11. {
  13. class BulkData
  15. {
  17. static bool InsertBulkData(DataTable productTable)
  19. {
  21. try
  23. {
  25. using(var oSqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
  27. {
  29. using(var oSqlCommand = new SqlCommand())
  31. {
  33. oSqlCommand.Connection = oSqlConnection;
  35. oSqlCommand.CommandTimeout = 0;
  37. oSqlCommand.CommandText = "IR.InsertProduct";
  39. oSqlCommand.CommandType = CommandType.StoredProcedure;
  41. oSqlCommand.Parameters.AddWithValue("@ProductTable", productTable);
  43. oSqlConnection.Open();
  45. int x = Convert.ToInt32(oSqlCommand.ExecuteScalar());
  47. oSqlCommand.Dispose();
  49. oSqlConnection.Close();
  51. oSqlConnection.Dispose();
  53. if (x > 0)
  55. return true;
  57. return false;
  59. }
  61. }
  63. } catch (Exception ex)
  65. {
  67. //log the error..
  69. return false;
  71. }
  73. }
  75. static void Main(string[] args)
  77. {
  79. DataTable productTable = new DataTable();
  81. productTable.Columns.Add("ProductName", typeof(string));
  83. productTable.Columns.Add("Price", typeof(int));
  85. var row = productTable.NewRow();
  87. row["ProductName"] = "Pen";
  89. row["Price"] = 10;
  91. productTable.Rows.Add(row);
  93. row = productTable.NewRow();
  95. row["ProductName"] = "NoteBook";
  97. row["Price"] = 100;
  99. productTable.Rows.Add(row);
  101. row = productTable.NewRow();
  103. row["ProductName"] = "Dairy";
  105. row["Price"] = 200;
  107. productTable.Rows.Add(row);
  109. var status = BulkData.InsertBulkData(productTable);
  111. if (status)
  113. Console.WriteLine("Product has been inserted successfully");
  115. else Console.WriteLine("Error occured during Bulk Insert");
  117. }
  119. }
  121. }
Source code contains two method
  1. Main Method is creating DataTable some Product record and calling to InsertBulkData method and displaying status.
  2. InsertBulkData Method is parameterized method which is taking one DataTable parameter and it will insert entire DataTable record in database if records is inserted then will get rows count if row count is greater than one it will return true else false.

Step 5: Add ConnectionString in AppConfig

  1. <connectionStrings>
  3. <add name="ConnectionString" connectionString="Data Source=;Initial Catalog=;Persist Security Info=True;uid=;pwd="/>
  5. </connectionStrings>
  7. Data Source=Add your Database Server Name
  9. Initial Catalog=Database Name
  11. Uid=Database Server User Name
  13. Password: Database Server password Name.