.Net批量插入数据
1. 一般我们普通数据插入是这样的:
现在我们写一个控制台程序用常规办法添加10000条数据。
//以下是批量插入数据的办法
//连接字符串 string str = "Server=.;Database=doudou;Uid=sa;Pwd=1994min"; string doudou = "doudou"; int age = 1; DateTime startTime = DateTime.Now; //创建一个本地的数据集合 DataTable dt = new DataTable(); //创建列 dt.Columns.Add("name"); dt.Columns.Add("age"); for (int i = 1; i <= 10000; i++) { DataRow row = dt.NewRow();//创建 DataRow对象 //为每列赋值 row["name"] = doudou; row["age"] = age; dt.Rows.Add(row);//插入到本地datatable中 }
这种方式执行完大概需要21秒
2.使用SqlBulkCopy 类操作数据的批量插入(同样是添加10000条数据):
//连接字符串 string str = "Server=.;Database=doudou;Uid=sa;Pwd=1994min"; string doudou = "doudou"; int age = 1; DateTime startTime = DateTime.Now; //创建一个本地的数据集合 DataTable dt = new DataTable(); //创建列 dt.Columns.Add("name"); dt.Columns.Add("age"); for (int i = 1; i <= 10000; i++) { DataRow row = dt.NewRow();//创建 DataRow对象 //为每列赋值 row["name"] = doudou; row["age"] = age; dt.Rows.Add(row);//插入到本地datatable中 } //批量插入语法 using(SqlBulkCopy sbc = new SqlBulkCopy(str)) { //设置要插入的表名 sbc.DestinationTableName = "BatchWrite"; //与服务器数据库列名映射, sbc.ColumnMappings.Add("name","name"); sbc.ColumnMappings.Add("age", "age"); //执行 sbc.WriteToServer(dt); }; //获取执行的时间段 TimeSpan ts = DateTime.Now - startTime; Console.WriteLine(ts.Seconds); Console.ReadKey();
结果输出只用了不到1秒的时间
所以在批量添加的时候使用SqlBulkCopy类来操作效率还是比较高的,如果数据量特别大的时候也要考虑内存的问题,为了方便使用就封装了一下
public static bool ExcuteNonQuery(DataTable dt) { using (SqlConnection connection = new SqlConnection(connStr))//创建数据库连接 { using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connection)) { connection.Open(); //sqlbulkcopy.BulkCopyTimeout = 100; //超时之前操作完成所允许的秒数 sqlbulkcopy.BatchSize = dt.Rows.Count; //每一批次中的行数 sqlbulkcopy.DestinationTableName = dt.TableName; //服务器上目标表的名称 for (int i = 0; i < dt.Columns.Count; i++) { sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName); //映射定义数据源中的列和目标表中的列之间的关系 } sqlbulkcopy.WriteToServer(dt); // 将DataTable数据上传到数据表中 } } return true; }
调用方法:
public void AddData(){ //创建一个本地的数据集合 DataTable dt = new DataTable(); //创建列 dt.Columns.Add("name"); dt.Columns.Add("age"); for(int i;i<1000;i++){ DataRow row = dt.NewRow();//创建 DataRow对象 row["name"] = "张三"; row["age"] = 25; dt.Rows.Add(row); } dt.TableName = "UserInfo"; //要添加的表明 SqlHelper.ExcuteNonQuery(dt);//调用批量插入方法 }
特别注意 sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
插入的时候列的顺序可以不一致,但名称和数据类型最好要保存一致。不一致时候,也能进行正确的转换,除了比如DataTime数据类型,不能插入一个无效的string时间字符串。
在插入数据的时候如果数据类型是Guid类型的时候。一定要创建列的时候一定要指定数据类型: dt.Columns.Add("UserID", typeof(System.Data.SqlTypes.SqlGuid));,
不然会报“来自数据源的 String 类型的给定值不能转换为指定目标列的类型 uniqueidentifier。”这个错。