批量插入数据
本文将介绍三种批量插入数据的方法。第一种方法是使用循环语句逐个将数据项插入到数据库中；第二种方法使用的是SqlBulkCopy，使您可以用其他源的数据有效批量加载 SQL Server 表；第三种使用的方法是sql server中的表值参数方法，表值参数是 SQL Server 2008 中的新参数类型。表值参数是使用用户定义的表类型来声明的。使用表值参数，可以不必创建临时表或许多参数，即可向 Transact-SQL 语句或例程（如存储过程或函数）发送多行数据。
代码示例：
此例子为控制台输出程序，有两个类，一个为BulkData类，主要实现了表值参数和sqlbulkcopy是如何插入数据的，一个类为Repository，一个app.config配置文件。所用数据库为sql server 2012。
建库语句：
打开 1 --Create DataBase 2 use master 3 go 4 if exists(select * from master.sys.sysdatabases where name=N'BulkDB') 5 drop database BulkDB 6 create database BulkDB; 7 go 8 9 10 --Create Table11 use BulkDB12 go13 14 if exists(select * from sys.objects where object_id=OBJECT_ID(N'[dbo].[BulkTable]') and type in(N'U'))15 drop table [dbo].BulkTable16 Create table BulkTable(17 Id int primary key,18 UserName nvarchar(32),19 Pwd varchar(16))20 go21 22 23 --Create Table Valued24 use BulkDB25 go26 27 if exists28 (29 select * from sys.types st 30 join sys.schemas ss 31 on st.schema_id=ss.schema_id32 where st.name=N'[BulkType]' and ss.name=N'dbo'33 )34 drop type [dbo].[BulkType]35 go36 37 create type [dbo].[BulkType] as table38 (39 Id int,40 UserName nvarchar(32),41 Pwd varchar(16)42 )43 go44 45 select * from dbo.BulkTable
BulkData.cs
打开 1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Data; 6 using System.Data.SqlClient; 7 using System.Configuration; 8 9 namespace BulkData10 {11 class BulkData12 {13 public static void TableValuedToDB(DataTable dt)14 {15 SqlConnection sqlConn = new SqlConnection(16 ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);17 const string TSqlStatement =18 "insert into BulkTable (Id,UserName,Pwd)" +19 " SELECT nc.Id, nc.UserName,nc.Pwd" +20 " FROM @NewBulkTestTvp AS nc";21 SqlCommand cmd = new SqlCommand(TSqlStatement, sqlConn);22 SqlParameter catParam = cmd.Parameters.AddWithValue("@NewBulkTestTvp", dt);23 24 catParam.SqlDbType = SqlDbType.Structured;25 26 catParam.TypeName = "dbo.BulkType";27 try28 {29 sqlConn.Open();30 if (dt != null && dt.Rows.Count != 0)31 {32 cmd.ExecuteNonQuery();33 }34 }35 catch (Exception ex)36 {37 throw ex;38 }39 finally40 {41 sqlConn.Close();42 }43 }44 45 public static DataTable GetTable()46 {47 DataTable dt = new DataTable();48 49 dt.Columns.AddRange(new DataColumn[]{ new DataColumn("Id",typeof(int)),new DataColumn("UserName",typeof(string)),new DataColumn("Pwd",typeof(string))});50 51 return dt;52 }53 54 public static void BulkToDB(DataTable dt)55 {56 SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString);57 SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);58 bulkCopy.DestinationTableName = "BulkTable";59 bulkCopy.BatchSize = dt.Rows.Count;60 61 try62 {63 sqlConn.Open();64 if (dt != null && dt.Rows.Count != 0)65 bulkCopy.WriteToServer(dt);66 }67 catch (Exception ex)68 {69 throw ex;70 }71 finally72 {73 sqlConn.Close();74 if (bulkCopy != null)75 bulkCopy.Close();76 }77 }78 }79 }
Repository.cs
打开 1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Data; 6 using System.Data.SqlClient; 7 using System.Configuration; 8 using System.Diagnostics; 9 10 namespace BulkData 11 { 12 public class Repository 13 { 14 public static void UseSqlBulkCopyClass() 15 { 16 Stopwatch sw = new Stopwatch(); 17 for (int outLayer = 0; outLayer < 10; outLayer++) 18 { 19 DataTable dt = BulkData.GetTable(); 20 for (int count = outLayer * 100000; count < (outLayer + 1) * 100000; count++) 21 { 22 DataRow r = dt.NewRow(); 23 r[0] = count; 24 r[1] = string.Format("User-{0}", count * outLayer); 25 r[2] = string.Format("Password-{0}", count * outLayer); 26 dt.Rows.Add(r); 27 } 28 sw.Start(); 29 BulkData.BulkToDB(dt); 30 sw.Stop(); 31 Console.WriteLine(string.Format("{1} hundred thousand data elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds, outLayer + 1)); 32 } 33 34 Console.ReadLine(); 35 } 36 37 public static void UseTableValue() 38 { 39 Stopwatch sw = new Stopwatch(); 40 41 for (int outLayer = 0; outLayer < 10; outLayer++) 42 { 43 DataTable dt = BulkData.GetTable(); 44 45 for (int count = outLayer * 100000; count < (outLayer + 1) * 100000; count++) 46 { 47 DataRow dataRow = dt.NewRow(); 48 dataRow[0] = count; 49 dataRow[1] = string.Format("User-{0}", count * outLayer); 50 dataRow[2] = string.Format("Password-{0}", count * outLayer); 51 dt.Rows.Add(dataRow); 52 } 53 54 sw.Start(); 55 BulkData.TableValuedToDB(dt); 56 sw.Stop(); 57 58 Console.WriteLine(string.Format("{1} hundred thousand data elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds, outLayer + 1)); 59 } 60 61 Console.ReadLine(); 62 } 63 64 public static void UserNormalInsert() 65 { 66 Stopwatch sw = new Stopwatch(); 67 68 SqlConnection sqlConn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString); 69 70 SqlCommand sqlComm = new SqlCommand(); 71 sqlComm.CommandText = string.Format("insert into BulkTable(Id,UserName,Pwd)values(@p0,@p1,@p2)"); 72 sqlComm.Parameters.Add("@p0", SqlDbType.Int); 73 sqlComm.Parameters.Add("@p1", SqlDbType.NVarChar); 74 sqlComm.Parameters.Add("@p2", SqlDbType.VarChar); 75 sqlComm.CommandType = CommandType.Text; 76 sqlComm.Connection = sqlConn; 77 sqlConn.Open(); 78 79 try 80 { 81 for (int outLayer = 0; outLayer < 10; outLayer++) 82 { 83 for (int count = outLayer * 100000; count < (outLayer + 1) * 100000; count++) 84 { 85 86 sqlComm.Parameters["@p0"].Value = count; 87 sqlComm.Parameters["@p1"].Value = string.Format("User-{0}", count * outLayer); 88 sqlComm.Parameters["@p2"].Value = string.Format("Password-{0}", count * outLayer); 89 sw.Start(); 90 sqlComm.ExecuteNonQuery(); 91 sw.Stop(); 92 } 93 94 Console.WriteLine(string.Format("{1} hundred thousand data elapsed Time is {0} Milliseconds", sw.ElapsedMilliseconds, outLayer + 1)); 95 } 96 } 97 catch (Exception ex) 98 { 99 throw ex;100 }101 finally102 {103 sqlConn.Close();104 }105 106 Console.ReadLine();107 }108 }109 }
App.config
打开 1 23 4 87
Program.cs
打开 1 using System; 2 using System.Collections.Generic; 3 using System.Linq; 4 using System.Text; 5 using System.Data; 6 using System.Data.SqlClient; 7 using System.Configuration; 8 using System.Diagnostics; 9 10 namespace BulkData11 {12 class Program13 { 14 static void Main(string[] args)15 {16 //Repository.UseSqlBulkCopyClass();17 Repository.UseTableValue();18 //Repository.UserNormalInsert();19 } 20 }21 }
三种方法分别插入100万条数据所用的时间为：
循环语句所用时间：
sqlbulkcopy方法所用时间为：
表值参数所用时间为：
