大量数据快速插入到DB

许多时候需要上传一个excel文件到服务器端，然后将excel中大量数据导入到DB，

如果写正常的插入sql语句速度比较慢，用以下方法比较快。

''' <summary>
    ''' 插入临时表
    ''' </summary>
    ''' <param name="tbInfo"></param>
    ''' <param name="userId"></param>
    ''' <remarks></remarks>
    Private Sub BatchInsertInfo(ByVal tbInfo As DataTable, ByVal userId As String)
        Dim strConn As String

        Dim tableName As String = "EmployeeMaster_Import_Tmp"
        Dim dr As DataRow
        Dim queryString As String
        Dim dt As DateTime = Now
        Dim connection As New SqlConnection

        Try
            strConn = "数据库连接字符串"
            queryString = "select * from EmployeeMaster_Import_Tmp where UserID = '' "

            'Using connection As New OracleConnection(strConn)
            connection = New SqlConnection(strConn)

            Dim adapter As New SqlDataAdapter()
            adapter.SelectCommand = New SqlCommand(queryString, connection)
            Dim builder As SqlCommandBuilder = New SqlCommandBuilder(adapter)

            connection.Open()

            Dim dataSet As DataSet = New DataSet
            adapter.Fill(dataSet, tableName)

            ' Code to modify data in DataSet here
            Dim table As DataTable = dataSet.Tables(tableName)

            For i As Integer = 0 To tbInfo.Rows.Count - 1
                dr = tbInfo.Rows(i)

                table.Rows.Add(table.NewRow)
                table.Rows(table.Rows.Count - 1).Item(0) = userId
                table.Rows(table.Rows.Count - 1).Item(1) = dr(0)

                。。。

                table.Rows(table.Rows.Count - 1).Item(17) = userId
                table.Rows(table.Rows.Count - 1).Item(18) = dt
                table.Rows(table.Rows.Count - 1).Item(19) = userId
                table.Rows(table.Rows.Count - 1).Item(20) = dt
                table.Rows(table.Rows.Count - 1).Item(21) = ""
                table.Rows(table.Rows.Count - 1).Item(22) = ""
            Next

            builder.GetUpdateCommand()

            ' Without the SqlCommandBuilder this line would fail.
            adapter.Update(dataSet, tableName)

            'End Using
        Catch ex As Exception
            '异常抛出
            Throw ex
        Finally
            '如何连接打开中，则关闭
            If (connection.State = ConnectionState.Open) Then
                '关闭数据库连接
                connection.Close()
            End If
        End Try

    End Sub