|
许多时候需要上传一个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
|
