Insert(Bulk) DataTable Data in Database

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  
  2.   
  3. CREATE table [IR].[PRODUCT]  
  4.   
  5. (  
  6.   
  7. PRODUCTID INT IDENTITY(1,1) PRIMARY KEY,  
  8.   
  9. PRODUCTNAME VARCHAR(50)NOT NULL,  
  10.   
  11. PRICE INT NOT NULL,  
  12.   
  13. CREATED_DATE DATETIME DEFAULT GETDATE()  
  14.   
  15. );  
Step2: Create Type
  1. CREATE TYPE [IR].[ProductType] AS TABLE(  
  2.   
  3. [ProductName] [varchar](50)Not null,  
  4.   
  5. [Price] int not null  
  6.   
  7. )  
Step 3: Create Procedure
  1. CREATE PROCEDURE [IR].[InsertProduct]  
  2.   
  3. @ProductTable [IR].[ProductType] READONLY  
  4.   
  5. AS  
  6.   
  7. BEGIN  
  8.   
  9. SET NOCOUNT ON;  
  10.   
  11. ------INSETING DATA IN TABLE  
  12.   
  13. INSERT INTO [IR].[PRODUCT](PRODUCTNAME,PRICE)  
  14.   
  15. SELECT * FROM @ProductTable  
  16.   
  17. SELECT @@rowcount;  
  18.   
  19. END  
Step 4: Source Code
  1. using System;  
  2.   
  3. using System.Configuration;  
  4.   
  5. using System.Data;  
  6.   
  7. using System.Data.SqlClient;  
  8.   
  9. namespace Bulk  
  10.   
  11. {  
  12.   
  13.     class BulkData  
  14.   
  15.     {  
  16.   
  17.         static bool InsertBulkData(DataTable productTable)  
  18.   
  19.         {  
  20.   
  21.             try  
  22.   
  23.             {  
  24.   
  25.                 using(var oSqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))  
  26.   
  27.                 {  
  28.   
  29.                     using(var oSqlCommand = new SqlCommand())  
  30.   
  31.                     {  
  32.   
  33.                         oSqlCommand.Connection = oSqlConnection;  
  34.   
  35.                         oSqlCommand.CommandTimeout = 0;  
  36.   
  37.                         oSqlCommand.CommandText = "IR.InsertProduct";  
  38.   
  39.                         oSqlCommand.CommandType = CommandType.StoredProcedure;  
  40.   
  41.                         oSqlCommand.Parameters.AddWithValue("@ProductTable", productTable);  
  42.   
  43.                         oSqlConnection.Open();  
  44.   
  45.                         int x = Convert.ToInt32(oSqlCommand.ExecuteScalar());  
  46.   
  47.                         oSqlCommand.Dispose();  
  48.   
  49.                         oSqlConnection.Close();  
  50.   
  51.                         oSqlConnection.Dispose();  
  52.   
  53.                         if (x > 0)  
  54.   
  55.                         return true;  
  56.   
  57.                         return false;  
  58.   
  59.                     }  
  60.   
  61.                 }  
  62.   
  63.             } catch (Exception ex)  
  64.   
  65.             {  
  66.   
  67.                 //log the error..  
  68.   
  69.                 return false;  
  70.   
  71.             }  
  72.   
  73.         }  
  74.   
  75.         static void Main(string[] args)  
  76.   
  77.         {  
  78.   
  79.             DataTable productTable = new DataTable();  
  80.   
  81.             productTable.Columns.Add("ProductName"typeof(string));  
  82.   
  83.             productTable.Columns.Add("Price"typeof(int));  
  84.   
  85.             var row = productTable.NewRow();  
  86.   
  87.             row["ProductName"] = "Pen";  
  88.   
  89.             row["Price"] = 10;  
  90.   
  91.             productTable.Rows.Add(row);  
  92.   
  93.             row = productTable.NewRow();  
  94.   
  95.             row["ProductName"] = "NoteBook";  
  96.   
  97.             row["Price"] = 100;  
  98.   
  99.             productTable.Rows.Add(row);  
  100.   
  101.             row = productTable.NewRow();  
  102.   
  103.             row["ProductName"] = "Dairy";  
  104.   
  105.             row["Price"] = 200;  
  106.   
  107.             productTable.Rows.Add(row);  
  108.   
  109.             var status = BulkData.InsertBulkData(productTable);  
  110.   
  111.             if (status)  
  112.   
  113.             Console.WriteLine("Product has been inserted successfully");  
  114.   
  115.             else Console.WriteLine("Error occured during Bulk Insert");  
  116.   
  117.         }  
  118.   
  119.     }  
  120.   
  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>  
  2.   
  3. <add name="ConnectionString" connectionString="Data Source=;Initial Catalog=;Persist Security Info=True;uid=;pwd="/>  
  4.   
  5. </connectionStrings>  
  6.   
  7. Data Source=Add your Database Server Name  
  8.   
  9. Initial Catalog=Database Name  
  10.   
  11. Uid=Database Server User Name  
  12.   
  13. Password: Database Server password Name.  
X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now