public static string BatchAdd(MeterResponseSequence entity)
{
SqlParameter[] paras =
{
new SqlParameter("@SourceID", SqlDbType.Int),
new SqlParameter("@DeviceCode", SqlDbType.BigInt),
new SqlParameter("@UseID", SqlDbType.Int),
new SqlParameter("@MeterID", SqlDbType.Int),
new SqlParameter("@ArrivalsState", SqlDbType.Int),
new SqlParameter("@ExecState", SqlDbType.Int),
new SqlParameter("@BusinessState", SqlDbType.Int)
};
paras[0].Value = entity.SourceID;
paras[1].Value = entity.DeviceCode;
paras[2].Value = entity.UseID;
paras[3].Value = entity.MeterID;
paras[4].Value = entity.ArrivalsState;
paras[5].Value = entity.ExecState;
paras[6].Value = entity.BusinessState;
var sb = new StringBuilder();
sb.Append("INSERT INTO [MeterResponseSequence]([SourceID],[DeviceCode],[UseID],[MeterID],[ArrivalsState],[ExecState],[BusinessState])");
sb.AppendFormat("values ({0},{1},{2},{3},{4},{5},{6})", paras[0].Value, paras[1].Value, paras[2].Value, paras[3].Value, paras[4].Value, paras[5].Value, paras[6].Value);
return(sb.ToString());
}
static void Main(string[] args)
{
GMNS201Entities db = new GMNS201Entities();
var sw = new Stopwatch();
Console.WriteLine("批量插入：EF一次一条\n");
sw.Start();
for (int i = 0; i < 10000; i++)
{
MeterResponseSequence temp = new MeterResponseSequence();
temp.ArrivalsState = 0;
temp.BusinessState = 0;
temp.DeviceCode = i;
temp.SourceID = i;
temp.MeterID = i;
temp.UseID = i;
temp.ExecState = 0;
db.MeterResponseSequences.Add(temp);
db.SaveChanges();
if (i % 100 == 0)
{
Console.Write(".");
}
}
sw.Stop();
var date = sw.Elapsed;
Console.WriteLine(string.Format("总耗时：{0}", date.TotalSeconds));
Console.WriteLine("--------------------------------------------\n");
Console.WriteLine("\n");
Console.WriteLine("批量插入：EF批量\n");
sw.Restart();
for (int i = 10000; i < 20000; i++)
{
MeterResponseSequence temp = new MeterResponseSequence();
temp.ArrivalsState = 0;
temp.BusinessState = 0;
temp.DeviceCode = i;
temp.SourceID = i;
temp.MeterID = i;
temp.UseID = i;
temp.ExecState = 0;
db.MeterResponseSequences.Add(temp);
if (i % 100 == 0)
{
Console.Write(".");
}
}
db.SaveChanges();
sw.Stop();
date = sw.Elapsed;
Console.WriteLine(string.Format("总耗时：{0}", date.TotalSeconds));
Console.WriteLine("--------------------------------------------\n");
Console.WriteLine("\n");
Console.WriteLine("批量插入：EF批量 AutoDetectChangesEnabled 关闭\n");
// using (var tr=db.Database.)
// {
try
{
db.Configuration.AutoDetectChangesEnabled = false;
sw.Restart();
for (int i = 10000; i < 20000; i++)
{
MeterResponseSequence temp = new MeterResponseSequence();
temp.ArrivalsState = 0;
temp.BusinessState = 0;
temp.DeviceCode = i;
temp.SourceID = i;
temp.MeterID = i;
temp.UseID = i;
temp.ExecState = 0;
db.MeterResponseSequences.Add(temp);
if (i % 100 == 0)
{
Console.Write(".");
}
}
db.SaveChanges();
sw.Stop();
date = sw.Elapsed;
Console.WriteLine(string.Format("总耗时：{0}", date.TotalSeconds));
}
finally
{
db.Configuration.AutoDetectChangesEnabled = true;
}
// }
Console.WriteLine("--------------------------------------------\n");
Console.WriteLine("\n");
Console.WriteLine("批量插入：拼Sql批量插入\n");
sw.Restart();
var sql = new StringBuilder();
for (int i = 20000; i < 30000; i++)
{
MeterResponseSequence temp = new MeterResponseSequence();
temp.ArrivalsState = 0;
temp.BusinessState = 0;
temp.DeviceCode = i;
temp.SourceID = i;
temp.MeterID = i;
temp.UseID = i;
temp.ExecState = 0;
//生成SQL
sql.Append(BatchAdd(temp));
if (i % 100 == 0)
{
Console.Write(".");
}
}
//一次性执行SQL
((IObjectContextAdapter)db).ObjectContext.CommandTimeout = 0;//永不超时
db.Database.ExecuteSqlCommand(sql.ToString());
sw.Stop();
date = sw.Elapsed;
Console.WriteLine(string.Format("总耗时：{0}", date.TotalSeconds));
Console.ReadLine();
}
