大数据量插入

：.net用了好久，却一直没有注意System.Data.SqlClient下还有一个SqlBulkCopy类，使用这个类批量插入数据，共花了23.954秒，是这四个算法最好的一个，比算法2快1倍。

Code
1 Private Sub Button1_Click()Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
2        Dim t As New System.Data.DataTable
3        Dim c As System.Data.DataColumn
4        Dim sw As New System.Diagnostics.Stopwatch
5        sw.Start()
6        Dim conn As New System.Data.SqlClient.SqlConnection("Data Source=JXBW-YESHUNQUAN\SQLEXPRESS;Initial Catalog=test;Integrated Security=True")
7        conn.Open()
8        c = New System.Data.DataColumn("id")
9        t.Columns.Add(c)
10        c = New System.Data.DataColumn("name")
11        t.Columns.Add(c)
12        c = New System.Data.DataColumn("sex")
13        t.Columns.Add(c)
14        c = New System.Data.DataColumn("remark")
15        t.Columns.Add(c)
16        Dim bc As New System.Data.SqlClient.SqlBulkCopy(conn)
17        bc.DestinationTableName = "tbl1"
18        Dim i As Long
19        For i = 1 To 100000
20            t.Rows.Add(i, "gsdgfsdfg" & i, "男", "adfasdfasdddsssssssssssssssss我爱中国")
21            If i Mod 100 = 0 Then
22                bc.WriteToServer(t, DataRowState.Added)
23                t.Rows.Clear()
24            End If
25        Next i
26        conn.Close()
27        sw.Stop()
28        Debug.Print(sw.ElapsedMilliseconds / 1000 & "秒")
29    End Sub

文章来自: 转
引用通告: 查看所有引用 | 我要引用此文章
Tags: SqlBulkCopySqlBulkCopy
相关日志: