VB.NET GridView資料insert到MS資料庫

2017-07-28

365
0
2017-09-21

VB.NET

Public Sub DB_Click(sender As Object, e As EventArgs) Handles DB.Click
'算時間
Dim oTimer As New System.Diagnostics.Stopwatch
'連結資料庫字串
Dim sL_ConnectionString As String = ConfigurationManager.ConnectionStrings("XXX").ConnectionString
'開始計時
oTimer.Start()
Dim iRows As Int64 = 0
Dim row As DataRow
Dim sL_Str As String = ""
Dim sL_Step As String = ""
'累積到一萬筆再insert
Dim sI_BatchNo As Integer = 10000
'判斷GridView比數有沒有到一萬筆
If DataGridView1.RowCount < sI_BatchNo Then sI_BatchNo = DataGridView1.RowCount
Try
sL_Step = "Step 1:Bulk Define Begin"

'使用oBulk可大幅縮短資料庫insert時間
Using oBulk As New Data.SqlClient.SqlBulkCopy(sL_ConnectionString, SqlClient.SqlBulkCopyOptions.TableLock) With
{.DestinationTableName = "XXX-table", .BulkCopyTimeout = 0, .BatchSize = sI_BatchNo}
Using oDT As New DataTable
    With oDT.Columns
      '建立參數可以建立很多個
      .Add("F_Type1", GetType(System.String))
      .Add("F_Type2", GetType(System.String))
      .Add("F_Type3", GetType(System.String))
      sL_Step = "Step 2:Datatable Get Type Begin"
    End With
    Dim iBatchsize As Integer = 0
    With DataGridView1
      For x = 0 To .RowCount - 1
row = oDT.NewRow()
sL_Step = "Step 4:DataGridView1 to Row Begin"
'塞資料進參數
row("F_Type1") = .Rows(x).Cells(0).Value.ToString
row("F_Type2") = .Rows(x).Cells(1).Value.ToString
row("F_Type3") = .Rows(x).Cells(2).Value.ToString
sL_Step = "Step 5:DataGridView1 to Row End"
oDT.Rows.Add(row)
iBatchsize += 1
iRows += 1
If iBatchsize = sI_BatchNo Then
   sL_Step = "Step 6:Bulk " & sI_BatchNo & " record Write Begin"
   oBulk.ColumnMappings.Clear()
   oBulk.ColumnMappings.Add("F_Type1", "資料庫欄位1")
   oBulk.ColumnMappings.Add("F_Type2", "資料庫欄位2")
   oBulk.ColumnMappings.Add("F_Type3", "資料庫欄位3")
   oBulk.WriteToServer(oDT)
   oDT.Rows.Clear()
   iBatchsize = 0
   sL_Step = "Step 7:Bulk " & sI_BatchNo & " record Write End"
End If
      Next
sL_Step = "Step 8:Bulk Last record Write Begin"
oBulk.WriteToServer(oDT)
oDT.Rows.Clear()
sL_Step = "Step 9:Bulk Last record Write End"
    End With
End Using
End Using
'計時停止
oTimer.Stop()
Catch ex As Exception
sL_Str = "寫入檔案 (第" & iRows.ToString & "筆) 發生錯誤:" + " " + sL_Step + " )"
MsgBox(sL_Str + ex.Message) : WriteLog(sL_Str + ex.Message)
End Try
MsgBox("成功轉入,共( " & iRows.ToString & " ) 筆資料, 使用了(" & oTimer.Elapsed.TotalSeconds.ToString & ") 秒!!")
Me.DataGridView1.Visible = False
Me.DB.Visible = False
End Sub

回首頁