在java中怎么使用sqlbulkcopy_asp.net 使用SqlBulkCopy极速插入数据到 SQL Server

按这个来算，我们那个发水票的时间就会由 10分钟-->20秒，这可太神奇了。

于是乎，下demo，测试，改成自己一般使用的方法测试，NND，还真可以说是极速。

在此贴上我的Demo:SqlBulkCopy.rar

using System;

using System.Diagnostics;

using System.Data;

using System.Data.SqlClient;

using Microsoft.ApplicationBlocks.Data;

namespace ConsoleAppInsertTest

{

class Program

{

static int count = 1000000; //插入的条数

static void Main(string[] args)

{

long sqlBulkCopyInsertRunTime = SqlBulkCopyInsert();

Console.WriteLine(string.Format("使用SqlBulkCopy插入{1}条数据所用的时间是{0}毫秒", sqlBulkCopyInsertRunTime, count));

long commonInsertRunTime = CommonInsert();

Console.WriteLine(string.Format("普通方式插入{1}条数据所用的时间是{0}毫秒", commonInsertRunTime, count));

Console.ReadKey();

}

///

/// 使用普通插入数据

///

///

private static long CommonInsert()

{

Stopwatch stopwatch = new Stopwatch();

stopwatch.Start();

for (int i = 0; i < count; i++)

{

SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnection, CommandType.Text, "insert into passport(PassportKey) values('" + Guid.NewGuid() + "')");

}

stopwatch.Stop();

return stopwatch.ElapsedMilliseconds;

}

///

/// 使用SqlBulkCopy方式插入数据

///

///

private static long SqlBulkCopyInsert()

{

Stopwatch stopwatch = new Stopwatch();

stopwatch.Start();

DataTable dataTable = GetTableSchema();

for (int i = 0; i < count; i++)

{

DataRow dataRow = dataTable.NewRow();

dataRow[2] = Guid.NewGuid();

dataTable.Rows.Add(dataRow);

}

//Console.WriteLine(stopwatch.ElapsedMilliseconds);//初始化数据时间

SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(SqlHelper.SqlConnection);

sqlBulkCopy.DestinationTableName = "Passport";

if (dataTable != null && dataTable.Rows.Count != 0)

{

sqlBulkCopy.WriteToServer(dataTable);

}

sqlBulkCopy.Close();

stopwatch.Stop();

return stopwatch.ElapsedMilliseconds;

}

private static DataTable GetTableSchema()

{

return SqlHelper.ExecuteDataset(SqlHelper.SqlConnection, CommandType.Text, "select * from Passport where 1=2").Tables[0];

}

}

}

转自cnblogs的文章 SQL批量插入数据几种方案的性能详细对比