許多時候需要上傳一個excel文件到服務(wù)器端，然后將excel中大量數(shù)據(jù)導(dǎo)入到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 = "數(shù)據(jù)庫連接字符串"
            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
            '如何連接打開中，則關(guān)閉
            If (connection.State = ConnectionState.Open) Then
                '關(guān)閉數(shù)據(jù)庫連接
                connection.Close()
            End If
        End Try

    End Sub