SQLBulkCopy to Insert DataTable Rows at a Time into SQL Server Table using C# VB.Net

DataTable is a memory representation of SQL Server DataBase Table. We can direct Insert all records or rows of a DataTable into Sql Server Database Table at a time using SQL Bulk Copy. SQL Bulk Copy is a most efficient and preffered way to insert bulk rows at a time while the Database server is different from your application server. In this example I will prepare a DataTable first then Insert all Rows at a time to SQL Server Database using Bulk Copy instead of executing hundreds of thousands of insert Statements one by one.

Output:
SQLBULKCOPY_DATATABLE

To do the Example first Create a DataBase Table:

CREATE TABLE [dbo].[TestTable](
	[ID] [int] NULL,
	[Name] [varchar](max) NULL
) ON [PRIMARY]

Namespace to Import:
1. System.Data
2. System.Data.SqlClient
3. System.Configuration

SQLBulkCopy to Insert DataTable Rows into SQL Table using C#:

        private void Form1_Load(object sender, EventArgs e)
        {
            // Create a Sample DataTable to INSERT

            DataTable dt = new DataTable();

            dt.Columns.Add("ID", System.Type.GetType("System.Int64"));
            dt.Columns.Add("Name");

            DataRow oItem = dt.NewRow();
            oItem[0] = "1000";
            oItem[1] = "Shawpnendu";
            dt.Rows.Add(oItem);

            oItem = dt.NewRow();
            oItem[0] = "2000";
            oItem[1] = "Bimalendu";
            dt.Rows.Add(oItem);

            oItem = dt.NewRow();
            oItem[0] = "3000";
            oItem[1] = "Purnendu";
            dt.Rows.Add(oItem);

            oItem = dt.NewRow();
            oItem[0] = "4000";
            oItem[1] = "Roger";
            dt.Rows.Add(oItem);

            string strConnString =@"Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;";
            long xRowsLoaded = 0;
            try
            {
                using (SqlConnection xSqlCon = new SqlConnection(strConnString))
                {
                    SqlBulkCopy xBcp = new SqlBulkCopy(xSqlCon, SqlBulkCopyOptions.Default, null);
                    xBcp.DestinationTableName = "TestTable";
                    xBcp.BatchSize = dt.Rows.Count;
                    xBcp.BulkCopyTimeout = 0;
                    if (xSqlCon.State == ConnectionState.Closed) xSqlCon.Open();
                    xBcp.WriteToServer(dt);
                    xRowsLoaded = dt.Rows.Count;
                    xBcp.Close();
                    xSqlCon.Close();
                }
            }
            catch (Exception Ex)
            {
                // Log Error Here
            }
            
            MessageBox.Show(string.Format("Inserted {0} Rows at a Time!",xRowsLoaded));
        }

SQLBulkCopy to Insert DataTable Rows into SQL Table using VB.Net:

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        ' Create a Sample DataTable to INSERT

        Dim dt As New DataTable()

        dt.Columns.Add("ID", System.Type.[GetType]("System.Int64"))
        dt.Columns.Add("Name")

        Dim oItem As DataRow = dt.NewRow()
        oItem(0) = "1000"
        oItem(1) = "Shawpnendu"
        dt.Rows.Add(oItem)

        oItem = dt.NewRow()
        oItem(0) = "2000"
        oItem(1) = "Bimalendu"
        dt.Rows.Add(oItem)

        oItem = dt.NewRow()
        oItem(0) = "3000"
        oItem(1) = "Purnendu"
        dt.Rows.Add(oItem)

        oItem = dt.NewRow()
        oItem(0) = "4000"
        oItem(1) = "Roger"
        dt.Rows.Add(oItem)

        Dim strConnString As String = "Data Source=.\SQLEXPRESS;Initial Catalog=TESTDB;Trusted_Connection=yes;"
        Dim xRowsLoaded As Long = 0
        Try
            Using xSqlCon As New SqlConnection(strConnString)
                Dim xBcp As New SqlBulkCopy(xSqlCon, SqlBulkCopyOptions.[Default], Nothing)
                xBcp.DestinationTableName = "TestTable"
                xBcp.BatchSize = dt.Rows.Count
                xBcp.BulkCopyTimeout = 0
                If xSqlCon.State = ConnectionState.Closed Then
                    xSqlCon.Open()
                End If
                xBcp.WriteToServer(dt)
                xRowsLoaded = dt.Rows.Count
                xBcp.Close()
                xSqlCon.Close()
            End Using
            ' Log Error Here
        Catch Ex As Exception
        End Try

        MessageBox.Show(String.Format("Inserted {0} Rows at a Time!", xRowsLoaded))

    End Sub

Download Code Example C#        Download Code Example VB.Net

Posted in .Net, C#, Database, SQL Server, VB.Net

Leave a Reply

Your email address will not be published. Required fields are marked *

     

*