几种批量插入数据方式的比较
1 直接循环insert :这样效率是最慢的 经过测试测试程序 插入10w条数据需要 5分钟多
2 拼接sql 语句 再一次执行 :这样效率有明显提升 只需要40多s
3 使用 sqlbullcopy :先把要插入的数据 组装成 datatable 再copy 到数据库 效率最佳 只要5s
以下 测试 代码
主程序:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
class Program { static void Main(string[] args) { Console.WriteLine("start"); Fun1(); Console.WriteLine("end"); Console.WriteLine("SHEEP ..."); Thread.Sleep(3000); Fun2(); Console.WriteLine("end"); Console.WriteLine("SHEEP ..."); Fun3(); Console.ReadKey(); } /// <summary> /// recyle insert /// </summary> static void Fun1() { Console.WriteLine("recyle start:"+ DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); for (int i = 0; i < 100000; i++) { string commandText = "insert into GoodsTB(goodsid,goodsname,goodsprice,goodinventory) values('{0}','{1}','{2}','{3}')"; commandText = string.Format(commandText, Guid.NewGuid().ToString(), "shop recyle " +i, new Random().Next(20, 200), new Random().Next(20, 200)); SQLHelper.ExcuteSqlNoReturn(commandText); } Console.WriteLine("recyle end:" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); } static void Fun2() { Console.WriteLine("stringBuilder start:" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); StringBuilder sb = new StringBuilder(); for (int i = 0; i < 100000; i++) { string commandText = "insert into GoodsTB(goodsid,goodsname,goodsprice,goodinventory) values('{0}','{1}','{2}','{3}')"; commandText = string.Format(commandText, Guid.NewGuid().ToString(), "shop stringBuilder " + i, new Random().Next(20, 200), new Random().Next(20, 200)); sb.Append(commandText); sb.Append("; "); } SQLHelper.ExcuteSqlNoReturn(sb.ToString()); Console.WriteLine("stringBuilder end:" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); } static void Fun3() { Console.WriteLine("MutiInsertData start:" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); DataTable dt = new DataTable(); dt.Columns.Add(new DataColumn("GoodsId",typeof(string))); dt.Columns.Add(new DataColumn("GoodsName", typeof(string))); dt.Columns.Add(new DataColumn("GoodsPrice",typeof(Decimal))); dt.Columns.Add(new DataColumn("GoodsInventory", typeof(Int16))); for (int i = 0; i < 100000; i++) { dt.Rows.Add(Guid.NewGuid().ToString(), "shop MutiInsertData " + i, new Random().Next(20, 200), new Random().Next(20, 200)); } SQLHelper.MutiInsertData(dt); Console.WriteLine("MutiInsertData end:" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); } }
辅助数据库操作类:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public class SQLHelper { public static string connectionstring = "Data Source=blueker;Initial Catalog=EasyShop;User ID=sa;Password=killers8Y;"; /// <summary> /// return -1 :error else affected rows /// </summary> /// <param name="commandText"></param> /// <returns></returns> public static int ExcuteSqlNoReturn(string commandText) { int rows = 0; SqlConnection sql = new SqlConnection(connectionstring); SqlCommand cmd = new SqlCommand(); try { sql.Open(); cmd.Connection = sql; cmd.CommandText = commandText; cmd.CommandType = CommandType.Text; rows = cmd.ExecuteNonQuery(); } catch (Exception e) { rows = -1; Console.WriteLine(e.Message + e.StackTrace); } finally { sql.Close(); sql.Dispose(); cmd.Dispose(); } return rows; } public static void MutiInsertData(DataTable DT) { SqlConnection conn = new SqlConnection(connectionstring); try { conn.Open(); using (SqlBulkCopy sbc = new SqlBulkCopy(conn)) { sbc.BatchSize = DT.Rows.Count; sbc.BulkCopyTimeout = 100; sbc.DestinationTableName = "goodstb"; sbc.WriteToServer(DT); } } catch (Exception e) { Console.WriteLine(e.ToString()); } finally { conn.Close(); conn.Dispose(); } } } }