用.NET SqlBulkCopy类执行批量复制

Imports System.Data

Imports System.Data.SqlClient

Imports System.IO

Module Module1

Sub Main()

Dim cString As String

Dim comma As Char

comma = ","
cString = "Data Source=(local);User ID=tester;Password=tester;Initial Catalog=Northwind;"

Dim conn As New SqlConnection
conn = New SqlConnection(cString)
conn.Open()

Dim comm As SqlCommand
comm = New SqlCommand("SELECT EmployeeID, FirstName, LastName, HomePhone FROM Employees;", conn)

Dim reader As SqlDataReader

reader = comm.ExecuteReader()

Dim conn2 As SqlConnection

conn2 = New SqlConnection(cString)

conn2.Open()

Dim bcp As SqlBulkCopy
bcp = New SqlBulkCopy(conn2)
bcp.DestinationTableName = "dbo.Employees_bcp"

Try

Dim dt As DataTable

Dim dr As DataRow

Dim dc As DataColumn
dt = New DataTable()
bcp.DestinationTableName = "dbo.Employees_bcp"

dc = New DataColumn()
dc.ColumnName = "Last"
dt.Columns.Add(dc)

dc = New DataColumn()
dc.ColumnName = "First"
dt.Columns.Add(dc)

dc = New DataColumn()
dc.ColumnName = "HomePhone"
dt.Columns.Add(dc)

dc = New DataColumn()
dc.ColumnName = "Title"
dt.Columns.Add(dc)

Dim sr As StreamReader
sr = New StreamReader("c:\emp.txt")

Dim input As String

input = sr.ReadLine()

While Not (input Is Nothing)

Dim s As String()

s = input.Split(comma)
dr = dt.NewRow()
dr("Last") = s(1)
dr("First") = s(2)
dr("HomePhone") = s(3)
dr("Title") = s(4)
dt.Rows.Add(dr)

input = sr.ReadLine()

End While
sr.Close()
bcp.ColumnMappings.Add("Last", "LastName")
bcp.ColumnMappings.Add("First", "FirstName")
bcp.ColumnMappings.Add("Title", "Title")
bcp.ColumnMappings.Add("HomePhone", "HomePhone")
bcp.WriteToServer(dt)

Catch ex As Exception
Console.WriteLine(ex.Message)

Finally
reader.Close()

End Try

End Sub

End Module