sqlserver 下三种批量插入数据的方法
本文将介绍三种批量插入数据的方法。第一种方法是使用循环语句逐个将数据项插入到数据库中；第二种方法使用的是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;
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", sw.ElapsedMilliseconds, 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,UserName,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", sw.ElapsedMilliseconds, outLayer + 1));
}
}
catch (Exception ex)
{
throw ex;
}
finally
{
sqlConn.Close();
}
Console.ReadLine();
}
}
}
App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="ConnStr"
connectionString="data source=.;Integrated Security=SSPI;Initial Catalog=BulkDB"
providerName="System.Data.SqlClient" />
</connectionStrings>
</configuration>
Program.cs
using System;
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();
}
}
}
三种方法分别插入100万条数据所用的时间为：
循环语句所用时间：
