赞
踩
1.普通的Insert插入方法
public static void Insert(IEnumerablepersons) { using (var con = new SqlConnection("Server=.;Database=DemoDataBase;User ID=sa;Password=8888;")) { con.Open(); foreach (var person in persons) { using (var com = new SqlCommand( "INSERT INTO dbo.Person(Id,Name,Age,CreateTime,Sex)VALUES(@Id,@Name,@Age,@CreateTime,@Sex)", con)) { com.Parameters.AddRange(new[] { new SqlParameter("@Id", SqlDbType.BigInt) {Value = person.Id}, new SqlParameter("@Name", SqlDbType.VarChar, 64) {Value = person.Name}, new SqlParameter("@Age", SqlDbType.Int) {Value = person.Age}, new SqlParameter("@CreateTime", SqlDbType.DateTime) {Value = person.CreateTime ?? (object) DBNull.Value}, new SqlParameter("@Sex", SqlDbType.Int) {Value = (int)person.Sex}, }); com.ExecuteNonQuery(); } } } }
2.拼接BatchInsert插入语句
public static void BatchInsert(Person[] persons) { using (var con = new SqlConnection("Server=.;Database=DemoDataBase;User ID=sa;Password=8888;")) { con.Open(); var pageCount = (persons.Length - 1) / 1000 + 1; for (int i = 0; i < pageCount; i++) { var personList = persons.Skip(i * 1000).Take(1000).ToArray(); var values = personList.Select(p => $"({p.Id},'{p.Name}',{p.Age},{(p.CreateTime.HasValue ? $"'{p.CreateTime:yyyy-MM-dd HH:mm:ss}'" : "NULL")},{(int) p.Sex})"); var insertSql = $"INSERT INTO dbo.Person(Id,Name,Age,CreateTime,Sex)VALUES{string.Join(",", values)}"; using (var com = new SqlCommand(insertSql, con)) { com.ExecuteNonQuery(); } } } }
3.SqlBulkCopy插入方案
public static void BulkCopy(IEnumerablepersons) { using (var con = new SqlConnection("Server=.;Database=DemoDataBase;User ID=sa;Password=8888;")) { con.Open(); var table = new DataTable(); table.Columns.AddRange(new [] { new DataColumn("Id", typeof(long)), new DataColumn("Name", typeof(string)), new DataColumn("Age", typeof(int)), new DataColumn("CreateTime", typeof(DateTime)), new DataColumn("Sex", typeof(int)), }); foreach (var p in persons) { table.Rows.Add(new object[] {p.Id, p.Name, p.Age, p.CreateTime, (int) p.Sex}); } using (var copy = new SqlBulkCopy(con)) { copy.DestinationTableName = "Person"; copy.WriteToServer(table); } } }
4.三种方案速度对比
三者插入效率对比,Insert明显比SqlBulkCopy要慢太多,大概20~40倍性能差距,下面我们将SqlBulkCopy封装一下,让批量插入更加方便。
1.初始化数据源类型(此处我使用DataTable类型),一般是使用表中的列名作为数据源的列名
DataTable CopyData= new DataTable("TestTable");
CopyData.Columns.Add("id", typeof(Int));
CopyData.Columns.Add("Name", typeof(string));
CopyData.Columns.Add("Time", typeof(DateTime));
CopyData.Columns.Add("Content", typeof(string));
2.给数据填充数据
foreach (var dr in dt.Tables[0].Rows)
{
DataRow row = importDataTable.NewRow();
row["id"] = Convert.ToInt(id);
row["Name"] = RealName;//操作人
row["Time"] = DateTime.Now;//操作时间
row["Content"] = String.Join("=", dr.ItemArray);//内容
CopyData.Rows.Add(row);
}
3.打开数据库连接插入数据
string connectionStr = “数据库连接字符串”; SqlConnection connection = new SqlConnection(connectionStr ); using (connection) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(connectionStr, SqlBulkCopyOptions.UseInternalTransaction)) { try { sqlBulkCopy.DestinationTableName = “TestTable”; sqlBulkCopy.BatchSize = CopyData.Rows.Count; for (int i = 0; i < CopyData.Columns.Count; i++) { sqlBulkCopy.ColumnMappings.Add(CopyData.Columns[i].ColumnName, CopyData.Columns[i].ColumnName); } sqlBulkCopy.WriteToServer(CopyData); return "成功"; } catch (Exception ex) { return "失败," + ex; } } }
这样就批量插入完毕了,执行起来速度很快。
sqlBulkCopy.DestinationTableName------->指定数据库表名
sqlBulkCopy.BatchSize--------->每一批次的行数
sqlBulkCopy.ColumnMappings.Add------->指定数据库与数据源之间的对应关系
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。