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

[IR] is my schema name

CREATE table [IR].[PRODUCT]

(

PRODUCTID INT IDENTITY(1,1) PRIMARY KEY,

PRODUCTNAME VARCHAR(50)NOT NULL,

PRICE INT NOT NULL,

CREATED_DATE DATETIME DEFAULT GETDATE()

);

Step2: Create Type

CREATE TYPE [IR].[ProductType] AS TABLE(

[ProductName] [varchar](50)Not null,

[Price] int not null

)

Step 3: Create Procedure

CREATE PROCEDURE [IR].[InsertProduct]

@ProductTable [IR].[ProductType] READONLY

AS

BEGIN

SET NOCOUNT ON;

------INSETING DATA IN TABLE

INSERT INTO [IR].[PRODUCT](PRODUCTNAME,PRICE)

SELECT * FROM @ProductTable

SELECT @@rowcount;

END

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

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

<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.