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

Step2: Create Type

Step 3: Create Procedure

Step 4: Source Code

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace Bulk
{
class BulkData
{
static bool InsertBulkData(DataTable productTable)
{
try
{
using(var oSqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
{
using(var oSqlCommand = new SqlCommand())
{
oSqlCommand.Connection = oSqlConnection;
oSqlCommand.CommandTimeout = 0;
oSqlCommand.CommandText = "IR.InsertProduct";
oSqlCommand.CommandType = CommandType.StoredProcedure;
oSqlCommand.Parameters.AddWithValue("@ProductTable", productTable);
oSqlConnection.Open();
int x = Convert.ToInt32(oSqlCommand.ExecuteScalar());
oSqlCommand.Dispose();
oSqlConnection.Close();
oSqlConnection.Dispose();
if (x > 0)
return true;
return false;
}
}
} catch (Exception ex)
{
//log the error..
return false;
}
}
static void Main(string[] args)
{
DataTable productTable = new DataTable();
productTable.Columns.Add("ProductName", typeof(string));
productTable.Columns.Add("Price", typeof(int));
var row = productTable.NewRow();
row["ProductName"] = "Pen";
row["Price"] = 10;
productTable.Rows.Add(row);
row = productTable.NewRow();
row["ProductName"] = "NoteBook";
row["Price"] = 100;
productTable.Rows.Add(row);
row = productTable.NewRow();
row["ProductName"] = "Dairy";
row["Price"] = 200;
productTable.Rows.Add(row);
var status = BulkData.InsertBulkData(productTable);
if (status)
Console.WriteLine("Product has been inserted successfully");
else Console.WriteLine("Error occured during Bulk Insert");
}
}
}

Source code contains two method

Main Method is creating DataTable some Product record and calling to InsertBulkData method and displaying status.
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

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