using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Diagnostics;
using TH.Com.General;
using TH.Common; public partial class EFUpdate : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{ }
protected void Button1_Click(object sender, EventArgs e)
{
var sp = new Stopwatch();
sp.Start(); var isSqlBulkCopy = true;
var list = new List<Article>();
using (var da = new THCommon())
{
Random rand = new Random(); for (var i = ; i < ; i++)
{
var art = new Article();
art.ID = Guid.NewGuid();
art.Area = Convert.ToDecimal(rand.Next() / 100.0);
art.CreateBy = "sdm";
art.CreateTime = DateTime.Now.AddMinutes(i);
art.Height = rand.Next();
art.Name = "Name" + i;
art.Note = "This is Note" + i;
art.Number = i;
art.Price = Convert.ToDecimal(rand.Next() / 100.0);
art.Weight = Convert.ToDecimal(rand.Next() / 100.0); list.Add(art); }
sp.Stop();
Debug.Print("Articles.Add = " + sp.ElapsedMilliseconds);
sp.Restart(); if (isSqlBulkCopy)
{
SaveList(da, list);
}
else
{
da.Articles.AddRange(list);
da.SaveChanges();
} sp.Stop();
Debug.Print("SaveChanges = " + sp.ElapsedMilliseconds);
sp.Restart();
}
} private void SaveList(DbContext db, List<Article> list)
{
//保存上传记录
var tableToInsert = Tools.ListToDataTable(list);
string cnstr = db.Database.Connection.ConnectionString;
using (var sqlBC = new SqlBulkCopy(cnstr))
{
sqlBC.DestinationTableName = "Art.Article";
sqlBC.BatchSize = ;
sqlBC.BulkCopyTimeout = ;
sqlBC.WriteToServer(tableToInsert);
}
} //更新测试
protected void Button2_Click(object sender, EventArgs e)
{
Debug.Print("测试数据更新方法速度比较");
Debug.Print("1.ef直接更新"); //EFUpdate1(10000);
//EFUpdate2(10000);
SqlDataAdapterUpdate();
} private void EFUpdate1(int count)
{
var sp = new Stopwatch();
sp.Start();
using (var da = new THCommon())
{
var list = da.Articles.Take().ToList(); sp.Stop();
Debug.Print("ToList = " + sp.ElapsedMilliseconds);
sp.Restart(); list.ForEach(t => t.Area = t.Height * t.Price);
sp.Stop();
Debug.Print("ForEach = " + sp.ElapsedMilliseconds);
sp.Restart(); da.SaveChanges(); sp.Stop();
Debug.Print("SaveChanges = " + sp.ElapsedMilliseconds);
sp.Restart();
}
} private void EFUpdate2(int count)
{
var sp = new Stopwatch();
sp.Start();
using (var da = new THCommon())
{
var list = da.Articles.Take().ToList(); var sql = "update art.article set Area = Height * Price where createtime<'2017-01-07 10:13:54.870' "; da.Database.ExecuteSqlCommand(sql); sp.Stop();
Debug.Print("ExecuteSqlCommand = " + sp.ElapsedMilliseconds);
sp.Restart();
}
} private void SqlDataAdapterUpdate()
{
var sp = new Stopwatch();
sp.Start();
using (var da = new THCommon())
{
var conn = (SqlConnection)da.Database.Connection; //设置select查询命令，SqlCommandBuilder要求至少有select命令
var selectCMD = new SqlCommand("select * from art.article where createtime<'2017-01-07 10:13:54.870'", conn);
var dt = new DataTable();
var sda = new SqlDataAdapter(selectCMD); //上面的语句中使用select 0，不是为了查询出数据，而是要查询出表结构以向DataTable中填充表结构
sda.Fill(dt); foreach (DataRow row in dt.Rows)
{
row["Area"] = Convert.ToDecimal(row["Height"]) * Convert.ToDecimal(row["Price"]);
}
//SqlCommandBuilder scb = new SqlCommandBuilder(sda); var dtc = dt.GetChanges();
SqlCommandBuilder scb = new SqlCommandBuilder(sda); sp.Stop();
Debug.Print("SqlCommandBuilder = " + sp.ElapsedMilliseconds);
sp.Restart(); if (dtc != null)
{
//执行更新
sda.Update(dtc);
} ////使DataTable保存更新
//dt.AcceptChanges(); sp.Stop();
Debug.Print("sda.Update = " + sp.ElapsedMilliseconds);
sp.Restart();
} } protected void Button3_Click(object sender, EventArgs e)
{
var sp = new Stopwatch();
sp.Start(); var list = new List<Article>(); var isDeleteSql = true; using (var da = new THCommon())
{
if (isDeleteSql)
{
var start = Convert.ToDateTime("2017-01-16");
var end = Convert.ToDateTime("2017-01-18"); var toDeleteListIDS =
da.Articles.Where(
t =>
t.CreateTime > start &&
t.CreateTime < end).Select(t => t.ID).ToList();
var ids = string.Join("','", toDeleteListIDS.ToArray());
var sql = string.Format("delete art.article where id in ('{0}') ", ids); da.Database.ExecuteSqlCommand(sql); sp.Stop();
Debug.Print("ExecuteSqlCommand delete = " + sp.ElapsedMilliseconds);
sp.Restart();
}
else
{
var dels = da.Articles.Take(); da.Articles.RemoveRange(dels);
da.SaveChanges();
sp.Stop();
Debug.Print("RemoveRange = " + sp.ElapsedMilliseconds);
sp.Restart();
} }
} /// <summary>
/// 先删除，再插入，在事务中执行
/// </summary>
private void DeleteAndInsert()
{
var rand = new Random();
using (var da = new THCommon())
{
using (System.Data.Entity.DbContextTransaction dbTran = da.Database.BeginTransaction())
{
try
{
var start = Convert.ToDateTime("2017-01-20");
var end = Convert.ToDateTime("2017-01-21"); var list = da.Articles.Where(t => t.CreateTime > start && t.CreateTime < end).ToList();
var ids = string.Join("','", list.Select(t => t.ID).ToArray()); //删除
var sql = string.Format("delete art.article where id in ('{0}') ", ids);
da.Database.ExecuteSqlCommand(sql); list.ForEach(delegate(Article art)
{
art.Height = ;
art.Number = ;
art.Weight = ;
art.Area = rand.Next();
}); SaveList(da, list); //commit transaction
dbTran.Commit();
}
catch (Exception ex)
{
//Rollback transaction if exception occurs
dbTran.Rollback();
} } }
}
protected void Button4_Click(object sender, EventArgs e)
{
DeleteAndInsert();
}
protected void Button5_Click(object sender, EventArgs e)
{
//1.删除临时表
//2.选择记录，并修改，并批插入表临时表
//3.update from var sp = new Stopwatch();
sp.Start(); using (var da = new THCommon())
{
var list = da.Articles.Take(100000).ToList(); sp.Stop();
Debug.Print("ToList = " + sp.ElapsedMilliseconds);
sp.Restart(); list.ForEach(delegate(Article art)
{
art.Area = 1;
art.Height = 2;
art.Number = 3;
art.Price = 4;
art.Weight = 5;
}); sp.Stop();
Debug.Print("ForEach = " + sp.ElapsedMilliseconds);
sp.Restart(); var sql = "truncate table art.articletmp";
da.Database.ExecuteSqlCommand(sql); sp.Stop();
Debug.Print("ExecuteSqlCommand truncate = " + sp.ElapsedMilliseconds);
sp.Restart(); //保存上传记录
var tableToInsert = Tools.ListToDataTable(list);
string cnstr = da.Database.Connection.ConnectionString;
using (var sqlBC = new SqlBulkCopy(cnstr))
{
sqlBC.DestinationTableName = "Art.ArticleTmp";
sqlBC.BatchSize = 10000;
sqlBC.BulkCopyTimeout = 300;
sqlBC.WriteToServer(tableToInsert);
} sp.Stop();
Debug.Print(" SqlBulkCopy = " + sp.ElapsedMilliseconds);
sp.Restart(); sql = "update art.article set area = t0.area,height=t0.height,number = t0.number,price = t0.price,weight = t0.weight from art.articletmp t0 where art.article.id = t0.id";
da.Database.ExecuteSqlCommand(sql);
sp.Stop();
Debug.Print("ExecuteSqlCommand update = " + sp.ElapsedMilliseconds);
sp.Restart(); }
}
}