SQLBulkCopy Example in SQL Server and VB.Net

BULK INSERT <TABLE_NAME> FROM 'D:\AAA.TXT'

WITH (FIELDTERMINATOR = ',', 
      ROWTERMINATOR = '\N')

Imports System.Data.SqlClient.SqlBulkCopy

Private Sub Button_Insert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button_Insert.Click

  con.Open()

        Dim sql_bulkcopy As New SqlClient.SqlBulkCopy(con)

        sql_bulkcopy.DestinationTableName = "abc"

        'sql_bulkcopy.BulkCopyTimeout = 30

        Try

            sql_bulkcopy.WriteToServer(fill_datatable())

        Catch ex As Exception

            MsgBox(ex.Message)

        End Try

        sql_bulkcopy.Close()

    End Sub

Function fill_datatable() As DataTable

        Dim dt As New DataTable

        dt.Clear()

        ' Header columns

        For Each column As DataGridViewColumn In DataGridView1.Columns

            Dim dc As New DataColumn(column.Name.ToString())

            dt.Columns.Add(dc)

        Next

        ' Data cells

        For i As Integer = 0 To DataGridView1.Rows.Count - 2

            Dim row As DataGridViewRow = DataGridView1.Rows(i)

            Dim dr As DataRow = dt.NewRow()

            For j As Integer = 0 To DataGridView1.Columns.Count - 1

                dr(j) = If((row.Cells(j).Value Is Nothing), " ", row.Cells(j).Value.ToString())

            Next

            dt.Rows.Add(dr)

        Next

        ' Related to the bug arround min size when using ExcelLibrary for export

        For i As Integer = DataGridView1.Rows.Count To DataGridView1.Rows.Count - 1

            Dim dr As DataRow = dt.NewRow()

            For j As Integer = 0 To dt.Columns.Count - 1

                dr(j) = " "

            Next

            dt.Rows.Add(dr)

        Next

         Return dt

     End Function

Private Sub Button_Insert_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button_Clear.Click

       

        Try

            Dim x As Integer

            i = DataGridView1.Rows.Count

            While (x <= i - 2)

                sql = "Insert into abc values('" & DataGridView1.Item(0, x).Value & "','" & DataGridView1.Item(1, x).Value & "','" & DataGridView1.Item(2, x).Value & "')"

                cmd = New SqlCommand(sql, con)

                icount = cmd.ExecuteNonQuery

                x = x + 1

            End While

            MsgBox("Insert Successful!")

            DataGridView1.Rows.Clear()

        Catch es As Exception

            MsgBox(es.Message)

        End Try

    End Sub