如何获取OracleBulkCopy运行时长实现进度条更新 大Excel上传Oracle场景
问题根源
你现有代码的逻辑是错误的:你写的for循环在bulkCopy.WriteToServer(dr)执行之前就已经跑完了,和Oracle批量插入的实际过程完全无关。OracleBulkCopy原生没有提供实时进度回调的接口,单次调用WriteToServer是阻塞执行的,过程中无法直接获取内部执行进度。
可行实现方案
方案1:分批次批量插入(推荐,进度准确)
把你读取到的Excel全量DataTable按固定行数拆分成分批的小数据块,每插入完一批就上报一次进度,既不会明显损失插入性能,也能拿到准确的进度值。
具体修改逻辑:
- 首先将批量上传逻辑移到
BackgroundWorker1.DoWork事件中,不要写在Form_Load(UI线程)里,避免界面卡死 - 设置每批插入的行数,比如
batchSize = 10000,可根据实际性能调整 - 按批次拆分DataTable,每插入完一批就计算当前进度并上报
修改后代码示例:
Imports System.IO ' 后台执行逻辑放在DoWork事件中 Private Sub BackgroundWorker1_DoWork(sender As Object, e As DoWorkEventArgs) Handles BackgroundWorker1.DoWork Dim _connString As String = "Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.100.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = ORACLE))); User Id=USER; password=PASS;" Dim rutacarpeta As String = "C:\2021\" Dim NombreArchivo As String = "FILE_0.xlsx" Dim tablaBBDD As String = "FILE_TEST" Const batchSize As Integer = 10000 ' 每批插入1万行,可根据实际性能调整 Dim row_file_excel As Integer Using conn As OracleConnection = New OracleConnection(_connString) conn.Open() Dim ot As OracleTransaction = conn.BeginTransaction(IsolationLevel.ReadCommitted) Try Dim xlsxConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=Yes;IMEX=1;';" xlsxConn = String.Format(xlsxConn, Path.Combine(rutacarpeta, NombreArchivo)).Trim() Using excel_con As OleDbConnection = New OleDbConnection(xlsxConn) excel_con.Open() Dim command As OleDbDataAdapter = New OleDbDataAdapter("SELECT * FROM [FILE$]", excel_con) Dim dtset As New DataSet command.Fill(dtset) Dim dr As DataTable = dtset.Tables(0) row_file_excel = dr.Rows.Count Using bulkCopy As OracleBulkCopy = New OracleBulkCopy(conn) bulkCopy.BulkCopyTimeout = 90000 bulkCopy.DestinationTableName = tablaBBDD bulkCopy.BatchSize = batchSize ' 拆分批次插入 For batchIndex = 0 To Math.Ceiling(row_file_excel / batchSize) - 1 Dim startRow = batchIndex * batchSize Dim endRow = Math.Min(startRow + batchSize - 1, row_file_excel - 1) Dim batchRows = dr.AsEnumerable().Skip(startRow).Take(endRow - startRow + 1).CopyToDataTable() bulkCopy.WriteToServer(batchRows) ' 上报进度,百分比为 已插入行数/总行数 *100 Dim progress = CInt((endRow + 1) / row_file_excel * 100) BackgroundWorker1.ReportProgress(progress) Next ot.Commit() bulkCopy.Close() End Using End Using Catch ex As Exception Try ot.Rollback() Catch ex1 As Exception MessageBox.Show(ex1.Message) End Try MessageBox.Show(ex.Message) End Try End Using End Sub ' 进度更新事件,直接更新UI进度条 Private Sub BackgroundWorker1_ProgressChanged(sender As Object, e As ProgressChangedEventArgs) Handles BackgroundWorker1.ProgressChanged ProgressBar1.Value = e.ProgressPercentage End Sub
方案2:预估进度条(无需改插入逻辑,进度为近似值)
如果不想改动全量插入的逻辑,可以用历史耗时做预估:
- 先在测试环境跑3-5次相同量级的Excel上传,拿到平均每秒插入的行数,或者200MB文件的平均总耗时
- 调用
WriteToServer之前启动一个定时器,按照预估的速率匀速更新进度条 WriteToServer执行完成后直接将进度条设置为100%即可
这种方案的缺点是遇到网络波动、数据库负载变化时进度会不准,但实现成本最低。
注意事项
- 分批次插入时要把所有批次都包在同一个事务内,避免中途失败出现部分数据插入成功的脏数据问题
- 批次大小不要设置过小,否则会大幅降低插入性能,建议在5000-20000行的区间内测试最优值
BackgroundWorker的ReportProgress方法触发的事件是运行在UI线程的,可以直接更新进度条控件,不需要额外做跨线程调用处理
内容的提问来源于stack exchange,提问作者Giancarlo
相关产品推荐
相关产品推荐

