三种批量插入数据的方法
三种批量插入数据的方法
批量插入数据
本文将介绍三种批量插入数据的方法。第一种方法是使用循环语句逐个将数据项插入到数据库中；第二种方法使用的是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 Table 11 use BulkDB 12 go 13 14 if exists( select * from sys.objects where object_id=OBJECT_ID(N ' [dbo].[BulkTable] ' ) and type in (N ' U ' )) 15 drop table [dbo].BulkTable 16 Create table BulkTable( 17 Id int primary key, 18 UserName nvarchar( 32 ), 19 Pwd varchar( 16 )) 20 go 21 22 23 -- Create Table Valued 24 use BulkDB 25 go 26 27 if exists 28 ( 29 select * from sys.types st 30 join sys.schemas ss 31 on st.schema_id= ss.schema_id 32 where st.name=N ' [BulkType] ' and ss.name=N ' dbo ' 33 ) 34 drop type [dbo].[BulkType] 35 go 36 37 create type [dbo].[BulkType] as table 38 ( 39 Id int , 40 UserName nvarchar( 32 ), 41 Pwd varchar( 16 ) 42 ) 43 go 44 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 BulkData
10 {
11 class BulkData
12 {
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 try
28 {
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 finally
40 {
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 try
62 {
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 finally
72 {
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 finally
102 {
103 sqlConn.Close();
104 }
105
106 Console.ReadLine();
107 }
108 }
109 }
App.config
打开
1 <?xml version= " 1.0 " encoding= " utf-8 " ?> 2 <configuration> 3 <connectionStrings> 4 <add name= " ConnStr " 5 connectionString= " data source=.;Integrated Security=SSPI;Initial Catalog=BulkDB " 6 providerName= " System.Data.SqlClient " /> 7 </connectionStrings> 8 </configuration>
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 BulkData
11 {
12 class Program
13 {
14 static void Main( string [] args)
15 {
16 // Repository.UseSqlBulkCopyClass();
17 Repository.UseTableValue();
18 // Repository.UserNormalInsert();
19 }
20 }
21 }
三种方法分别插入100万条数据所用的时间为：
循环语句所用时间：
sqlbulkcopy方法所用时间为：
表值参数所用时间为：
分类: C#学习
标签: C#学习
作者： Leo_wl
出处： http://www.cnblogs.com/Leo_wl/
本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。
版权信息声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://haodehen.cn/did46317