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 = 0; i < 1000000; i++)
{
var art = new Article();
art.ID = Guid.NewGuid();
art.Area = Convert.ToDecimal(rand.Next(10000) / 100.0);
art.CreateBy = "sdm";
art.CreateTime = DateTime.Now.AddMinutes(i);
art.Height = rand.Next(100);
art.Name = "Name" + i;
art.Note = "This is Note" + i;
art.Number = i;
art.Price = Convert.ToDecimal(rand.Next(100000) / 100.0);
art.Weight = Convert.ToDecimal(rand.Next(100000) / 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 = 3000;
sqlBC.BulkCopyTimeout = 300;
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(10000).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(10000).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(10000);
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 = 10;
art.Number = 10;
art.Weight = 50;
art.Area = rand.Next(10);
});
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();
}
}
}