SQL Bulk Copy Demo (Console App.)

'------------------------------------------------------------------------------------------
'           Notice of My Copyright and Intellectual Property Rights
'
' Any intellectual property contained within the program by Joseph L. Bolen remains the
' intellectual property of the Joseph L. Bolen. This means that no person may distribute,
' publish or provide such intellectual property to any other person or entity for any
' reason, commercial or otherwise, without the express written permission of Joseph L. Bolen.
'
'                 Copyright © 2015. All rights reserved.
'        All trademarks remain the property of their respective owners.
'-------------------------------------------------------------------------------------------
' Program Name:   SQL Bulk Copy Demo (Console App.)
'
' Author:         Joseph L. Bolen
' Date Created:   June 2015
'
' Description:    Quick insert of records from a Comma Separated Variable (CSV) file into
'                 a "previously defined table" in a SQL type server. Demo is using the input file
'                 called "MyTable.csv" in the MyDocument folder. The output is to a database
'                 table name "Contacts".
'
'                 NOTE: To use, change the hardcoding of the scrFile and targetTable variables.
'                 Also, if the csv file has or has not a Header columns row, then change the
'                 HasColumnHeadings variable. In addition, modify in the App.config file for
'                 the correct ConnectionString.
'
'                 Documentation is at:
'                   App's Visual Basic .NET code is at http://pastebin.com/u/jaybeeoh
'                   Video tutorial at YouTube: http://www.youtube.com/user/bolenpresents
'-------------------------------------------------------------------------------------------

Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Imports System.Configuration

'   1) You must add System.Configuration as a reference to the project.
'   2) You must modify the App.Config file to have a
'      named connection string:

'               <connectionStrings>
'               <clear />
'               <add name="AppConnStr"
'                   providerName="System.Data.SQLClient"
'                   connectionString="Server=.\SQLEXPRESS; Database=SimpleDB; Integrated Security=True;" />
'               </connectionStrings>

Module BulkCopyLoad

    Sub Main()

        Dim inputSrc As String = My.Computer.FileSystem.SpecialDirectories.MyDocuments & "\MyTable.csv"
        Dim targetTable As String = "Contacts"
        Dim HasColumnHeadings As Boolean = True

        Dim dt As New DataTable
        Dim line As String = Nothing

        Using sr As StreamReader = File.OpenText(inputSrc)
            ' Prime the pump read.
            line = sr.ReadLine()

            Do While line IsNot Nothing
                Dim data() As String = line.Split(","c)
                If data.Length > 0 Then

                    If HasColumnHeadings Then
                        For Each item In data
                            dt.Columns.Add(New DataColumn(item.ToString))
                        Next item
                        HasColumnHeadings = False
                    Else
                        Dim row As DataRow = dt.NewRow()
                        row.ItemArray = data
                        dt.Rows.Add(row)
                    End If
                End If
                line = sr.ReadLine()
            Loop
        End Using

        Dim connStr As String = ConfigurationManager.ConnectionStrings("AppConnStr").ConnectionString
        Using con As New SqlConnection(connStr)
            con.Open()
            'Using copy As New SqlBulkCopy(connStr)
            Using copy As New SqlBulkCopy(connStr, SqlBulkCopyOptions.TableLock) ' Performance improvement.
                Try
                    'copy.ColumnMappings.Add([inputSrc's Header or Ordinal Position], [TargetTable's Column Header or Ordinal Position])
                    'copy.ColumnMappings.Add("ContactId", "ContactId")
                    'copy.ColumnMappings.Add(0, 0)
                    'copy.ColumnMappings.Add(1, 1)
                    'copy.ColumnMappings.Add(2, 2)
                    'copy.ColumnMappings.Add(3, 3)
                    copy.DestinationTableName = targetTable
                    copy.WriteToServer(dt)
                    Console.WriteLine()
                    Console.WriteLine("SqlBulkCopy completed. " & dt.Rows.Count.ToString & " records were processed.")
                Catch ex As Exception
                    Console.WriteLine()
                    Console.WriteLine("Error occured durring processing.")
                    Console.WriteLine("Message: " & ex.Message)
                End Try
            End Using
        End Using

        Console.WriteLine()
        Console.WriteLine("Press any key to exit.")
        Console.ReadKey()
    End Sub

End Module