本文将介绍三种批量插入数据的方法。第一种方法是使用循环语句逐个将数据项插入到数据库中；第二种方法使用的是sqlBulkCopy，使您可以用其他源的数据有效批量加载 sql Server 表；第三种使用的方法是sql server中的表值参数方法，表值参数是 sql Server 2008 中的新参数类型。表值参数是使用用户定义的表类型来声明的。使用表值参数，可以不必创建临时表或许多参数，即可向 Transact-sql 语句或例程（如存储过程或函数）发送多行数据。
代码示例：
此例子为控制台输出程序，有两个类，一个为BulkData类，主要实现了表值参数和sqlbulkcopy是如何插入数据的，一个类为Repository，一个app.config配置文件。所用数据库为sql server 2012。
建库语句：
--Create DataBase
use master
go
if exists(select * from master.sys.sysdatabases where name=N'BulkDB')
drop database BulkDB
create database BulkDB;
go
--Create Table
use BulkDB
go
if exists(select * from sys.objects where object_id=OBJECT_ID(N'[dbo].[BulkTable]') and type in(N'U'))
drop table [dbo].BulkTable
Create table BulkTable(
Id int primary key,
UserName nvarchar(32),
Pwd varchar(16))
go
--Create Table Valued
use BulkDB
go
if exists
(
select * from sys.types st
join sys.schemas ss
on st.schema_id=ss.schema_id
where st.name=N'[BulkType]' and ss.name=N'dbo'
)
drop type [dbo].[BulkType]
go
create type [dbo].[BulkType] as table
(
Id int,
UserName nvarchar(32),
Pwd varchar(16)
)
go
select * from dbo.BulkTable
BulkData.cs
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.sqlClient;
using System.Configuration;
namespace BulkData
{
class BulkData
{
public static void TableValuedToDB(DataTable dt)
{
sqlConnection sqlConn = new sqlConnection(
ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
const string TsqlStatement =
"insert into BulkTable (Id,UserName,Pwd)" +
" SELECT nc.Id,nc.UserName,nc.Pwd" +
" FROM @NewBulkTestTvp AS nc";
sqlCommand cmd = new sqlCommand(TsqlStatement,sqlConn);
sqlParameter catParam = cmd.Parameters.AddWithValue("@NewBulkTestTvp",dt);
catParam.sqlDbType = sqlDbType.Structured; catParam.TypeName = "dbo.BulkType";
try
{
sqlConn.Open();
if (dt != null && dt.Rows.Count != 0)
{
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Close();
}
}
public static DataTable GetTable()
{
DataTable dt = new DataTable();
dt.Columns.AddRange(new DataColumn[]{new DataColumn("Id",typeof(int)),new DataColumn("UserName",typeof(string)),new DataColumn("Pwd",typeof(string))});
return dt;
}
public static void BulkToDB(DataTable dt)
{
sqlConnection sqlConn = new sqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
sqlBulkCopy bulkCopy = new sqlBulkCopy(sqlConn);
bulkCopy.DestinationTableName = "BulkTable";
bulkCopy.BatchSize = dt.Rows.Count;
try
{
sqlConn.Open();
if (dt != null && dt.Rows.Count != 0)
bulkCopy.WriteToServer(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Close();
if (bulkCopy != null)
bulkCopy.Close();
}
}
}
}
Repository.cs
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.sqlClient;
using System.Configuration;
using System.Diagnostics;
namespace BulkData
{
public class Repository
{
public static void UsesqlBulkCopyClass()
{
Stopwatch sw = new Stopwatch();
for (int outLayer = 0; outLayer < 10; outLayer++)
{
DataTable dt = BulkData.GetTable();
for (int count = outLayer * 100000; count < (outLayer + 1) * 100000; count++)
{
DataRow r = dt.NewRow();
r[0] = count;
r[1] = string.Format("User-{0}",count * outLayer);
r[2] = string.Format("Password-{0}",count * outLayer);
dt.Rows.Add(r);
}
sw.Start();
BulkData.BulkToDB(dt);
sw.Stop();
Console.WriteLine(string.Format("{1} hundred thousand data elapsed Time is {0} Milliseconds",sw.ElapsedMilliseconds,outLayer + 1));
}
Console.ReadLine();
}
public static void UseTableValue()
{
Stopwatch sw = new Stopwatch();
for (int outLayer = 0; outLayer < 10; outLayer++)
{
DataTable dt = BulkData.GetTable();
for (int count = outLayer * 100000; count < (outLayer + 1) * 100000; count++)
{
DataRow dataRow = dt.NewRow();
dataRow[0] = count;
dataRow[1] = string.Format("User-{0}",count * outLayer);
dataRow[2] = string.Format("Password-{0}",count * outLayer);
dt.Rows.Add(dataRow);
}
sw.Start();
BulkData.TableValuedToDB(dt);
sw.Stop();
Console.WriteLine(string.Format("{1} hundred thousand data elapsed Time is {0} Milliseconds",outLayer + 1));
}
Console.ReadLine();
}
public static void UserNormalInsert()
{
Stopwatch sw = new Stopwatch();
sqlConnection sqlConn = new sqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);
sqlCommand sqlComm = new sqlCommand();
sqlComm.CommandText = string.Format("insert into BulkTable(Id,Pwd)values(@p0,@p1,@p2)");
sqlComm.Parameters.Add("@p0",sqlDbType.Int);
sqlComm.Parameters.Add("@p1",sqlDbType.NVarChar);
sqlComm.Parameters.Add("@p2",sqlDbType.VarChar);
sqlComm.CommandType = CommandType.Text;
sqlComm.Connection = sqlConn;
sqlConn.Open();
try
{
for (int outLayer = 0; outLayer < 10; outLayer++)
{
for (int count = outLayer * 100000; count < (outLayer + 1) * 100000; count++)
{
sqlComm.Parameters["@p0"].Value = count;
sqlComm.Parameters["@p1"].Value = string.Format("User-{0}",count * outLayer);
sqlComm.Parameters["@p2"].Value = string.Format("Password-{0}",count * outLayer);
sw.Start();
sqlComm.ExecuteNonQuery();
sw.Stop();
}
Console.WriteLine(string.Format("{1} hundred thousand data elapsed Time is {0} Milliseconds",outLayer + 1));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Close();
}
Console.ReadLine();
}
}
}
App.config
providerName="System.Data.SqlClient" />
Program.cs
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.sqlClient;
using System.Configuration;
using System.Diagnostics;
namespace BulkData
{
class Program
{
static void Main(string[] args)
{
//Repository.UsesqlBulkCopyClass();
Repository.UseTableValue();
//Repository.UserNormalInsert();
}
}
}
循环语句所用时间：
表值参数所用时间为：
我不会告诉你有一种sql语法可以这么写:
