VB.net 大量插入数据库的方法

Imports System.Data.SqlClient

Public Class Form1
    Public sql As SqlConnection


    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        sql = New SqlConnection("Data Source=.;Initial Catalog=gushi;Integrated Security=True")
        sql.Open()
    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim sw As Stopwatch = New Stopwatch()
        sw.Start()
        Dim dt As New DataTable
        dt.Columns.Add(New DataColumn("id", GetType(String)))
        dt.Columns.Add(New DataColumn("name", GetType(String)))
        dt.Columns.Add(New DataColumn("value", GetType(Double)))
        dt.Columns.Add(New DataColumn("time", GetType(Date)))
        For i = 0 To 1000000
            dt.Rows.Add("", "11", 1, Now())
        Next






        Debug.Print("生成数据耗费" & sw.ElapsedMilliseconds & "毫秒")
        sw.Restart()
        Dim bulkCopy As SqlBulkCopy = New SqlBulkCopy(sql)
        bulkCopy.DestinationTableName = "data"
        bulkCopy.BatchSize = dt.Rows.Count
        bulkCopy.WriteToServer(dt)
        Debug.Print("数据传输耗费" & sw.ElapsedMilliseconds & "毫秒")

        sw.Stop()
    End Sub
End Class

 

CREATE TABLE [dbo].[data] (
    [Id]    INT           IDENTITY (1, 1) NOT NULL,
    [name]  NVARCHAR (50) NOT NULL,
    [value] FLOAT (53)    NOT NULL,
    [time]  DATETIME      NOT NULL, 
    CONSTRAINT [PK_data] PRIMARY KEY ([Id])
);