Another developer asked how to import CSV files using INSERT statements. Instead of simply saying “Go get a copy of the book” (again), I decided to provide the example from the book’s DVD.
SqlBulkCopy leverages the considerable work the SQL Server team has done to make importing data fast (really fast). Consider that none of the data access interfaces are designed to do bulk imports—except DBLib. That is, until ADO.NET 2.5 when the SqlBulkCopy API was added to the .NET SqlClient namespace the only way to do bulk operations was to use the BCP utility, SSIS or a TSQL bulk operation.
Using INSERT statements can be fairly easy to setup but really slow down the operation. It’s like delivering coal with a Toyota 1/4 ton pickup. This is great until you have to move 800 tons of coal to the local power plant.
Here’s the code extracted from “Hitchhiker’s Guide to Visual Studio and SQL Server (7th Edition)”
'Copyright (c) 2005-2009 Beta V Corporation. All rights reserved.
' For demonstration purposes only. No warranty of any kind expressed or implied.
' This example illustrates one way to use the SqlBulkCopy class.
' It creates a new target table on the target server by executing a SQL script,
' opens a delimited text file using the ODBC Text driver,
' opens a connection to the target SQL Server database and
' uses SqlBulkCopy to transfer the data.
Imports System.Data.Sqlclient
Imports System.Data.Odbc
Public Class Form1
Dim WithEvents sqlBCbc As SqlBulkCopy
Dim dr As Odbc.OdbcDataReader
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Try
' Address target server
Dim strProjData As String = My.Application.Info.DirectoryPath
Using cnOut As New SqlConnection("data source=demoserver;integrated security=sspi;database=biblio")
cnOut.Open()
' Create target table to accept Stock data
If cbPrepareTargetTable.Checked = True Then ExecuteSQLFromScript(cnOut, strProjData & "\StockData.sql")
' Define ODBC Connection object using text driver Connection string
' Note that the driver name must match EXACTLY, including spaces but it is not case sensitive.
Using cnIn As New OdbcConnection( _
"Driver={Microsoft Text Driver (*.txt; *.csv)};" _
& "Dbq=" & strProjData)
cnIn.Open()
' Create DataReader from Text File
Dim cmd As New OdbcCommand("SELECT * FROM stockdata.TXT", cnIn)
dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
' Create new SqlBulkCopy instance to export data from ODBC DataReader
sqlBCbc = New SqlBulkCopy(cnOut.ConnectionString, SqlBulkCopyOptions.TableLock)
With sqlBCbc
.BatchSize = 100 ' Put 100 rows at a time on the wire (Should be 10,000 or more)
.DestinationTableName = "StockData"
.NotifyAfter = 50 ' Fire SqlRowsCopied event every 50 rows (Should be 10 batches or more)
.BulkCopyTimeout = 90 ' Wait 90 seconds to complete (Should be normal run X 1.5)
.WriteToServer(dr) ' Start synchronous operation
End With
Dim cmd2 As New SqlCommand("SELECT Count(*) FROM StockData", cnOut)
Dim strRows As String
strRows = cmd2.ExecuteScalar().ToString
lblRowsInTable.Text = strRows
MsgBox("Operation completed normally. Note that the values might not match because the SqlRowsCopied event is not fired for the last partial batch", _
MsgBoxStyle.Information, "SqlBulkCopy Complete")
End Using ' CnIn
End Using ' CnOut
Catch exIO As InvalidOperationException
Debug.Print(exIO.Message)
MsgBox(exIO.ToString)
Catch exODBC As Odbc.OdbcException
Debug.Print(exODBC.Message)
MsgBox("ODBC failed to read source text file." & vbCrLf & exODBC.Message)
Catch ex As Exception
MsgBox(ex.ToString)
End Try
End Sub
Private Sub bc_SqlRowsCopied(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlRowsCopiedEventArgs) Handles sqlBCbc.SqlRowsCopied
' This event fires after NotifyAfter rows have been copied
' It does not fire after the last partial batch
lblRowsCopied.Text = e.RowsCopied.ToString
Me.Refresh()
End Sub
End Class
