BulkCopy :
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
kim - --- -
http://www.pin5i.com/showtopic-21934.html
[b]批量删除:[/b]
1、使用 Entity FrameWork 删除数据,着实是一件比较头疼的数据,若是少量数据,可以使用以下方法删除
using (DB.Entity.StudentDBEntities context = new DB.Entity.StudentDBEntities())
{
foreach (var item in context.Students.Where(row => row.Isleft == true))
{
context.DeleteObject(item);
}
context.SaveChanges();
}
2、但是若是要删除的数据有个三五万,10万20万那该如何,若采用上述方法,保守估计也得10分钟吧,不信,你可以写个程序,打开SQL Server Profiler试一下,这时,若你不想另外配置连接字符串,可以借用EF的连接字符串,使用ADO.Net 完成批量删除功能
.net Framework 3.5
public static void DeleteObject(SqlBtmsModel context, string deleteString)
{
var bindingFlags = BindingFlags.Instance | BindingFlags.NonPublic;
var factoryProperty = typeof(EntityConnection).GetProperty("StoreProviderFactory", bindingFlags);
var factory = factoryProperty.GetValue(context.Connection, null) as DbProviderFactory;
var connStr = (context.Connection as EntityConnection).StoreConnection.ConnectionString;
using (var conn = factory.CreateConnection())
{
conn.ConnectionString = connStr;
conn.Open();
var cmd = factory.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = deleteString;
cmd.ExecuteNonQuery();
}
}
Ø .Net FrameWork 4.0
当然,若是你使用的是 .Net FrameWork 4.0,那你可以使用以下 方式。
using (DB.Entity.StudentDBEntities context = new DB.Entity.StudentDBEntities())
{
context.ExecuteStoreCommand("delete from Students where StudentId = @studentId", new SqlParameter("@studentId", 5));
context.ExecuteStoreCommand("insert into students (StudentName)values (@p1)", new SqlParameter("@p1", "test"));
}注:3.5不支持此方式。
可能有人,想使用EntityCommand 进行批量删除,但是EntityCommand 仅支持 EntitySql ,问题就在这里了,EntitySql 目前不支持 Insert 、Delete 、Update操作,静等微软的下一步动作吧。
[b]C#中海量数据的批量插入和更新:[/b]
DateTime begin = DateTime.Now;
string connectionString = "";
using(SqlConnection conn = new SqlConnection(connectionString))...{
conn.Open();
SqlDataAdapter sd = new SqlDataAdapter();
sd.SelectCommand = new SqlCommand("select top 200 devid,data_time,data_value from CurrentTest", conn);
DataSet dataset = new DataSet();
sd.Fill(dataset);
Random r = new Random(1000);
sd.UpdateCommand = new SqlCommand("update CurrentTest "
+ " set data_time = @data_time,data_value = @data_value where devid = @devid", conn);
sd.UpdateCommand.Parameters.Add("@data_time", SqlDbType.Char, 19, "data_time");
sd.UpdateCommand.Parameters.Add("@data_value", SqlDbType.Int, 4, "data_value");
sd.UpdateCommand.Parameters.Add("@devid", SqlDbType.Char, 20, "devid");
sd.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
sd.UpdateBatchSize = 0;
for (int count = 0; count < 100000;)
...{
for (int i = 0; i < 200; i++,count++)
...{
dataset.Tables[0].Rows[i].BeginEdit();
dataset.Tables[0].Rows[i]["data_time"] = "2222-22-22 22:22:22";
dataset.Tables[0].Rows[i]["data_value"] = 100;
dataset.Tables[0].Rows[i]["devid"] = "DEVID"+count;
dataset.Tables[0].Rows[i].EndEdit();
}
sd.Update(dataset.Tables[0]);
}
dataset.Tables[0].Clear();
sd.Dispose();
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx
kim - --- -
http://www.pin5i.com/showtopic-21934.html
[b]批量删除:[/b]
1、使用 Entity FrameWork 删除数据,着实是一件比较头疼的数据,若是少量数据,可以使用以下方法删除
using (DB.Entity.StudentDBEntities context = new DB.Entity.StudentDBEntities())
{
foreach (var item in context.Students.Where(row => row.Isleft == true))
{
context.DeleteObject(item);
}
context.SaveChanges();
}
2、但是若是要删除的数据有个三五万,10万20万那该如何,若采用上述方法,保守估计也得10分钟吧,不信,你可以写个程序,打开SQL Server Profiler试一下,这时,若你不想另外配置连接字符串,可以借用EF的连接字符串,使用ADO.Net 完成批量删除功能
.net Framework 3.5
public static void DeleteObject(SqlBtmsModel context, string deleteString)
{
var bindingFlags = BindingFlags.Instance | BindingFlags.NonPublic;
var factoryProperty = typeof(EntityConnection).GetProperty("StoreProviderFactory", bindingFlags);
var factory = factoryProperty.GetValue(context.Connection, null) as DbProviderFactory;
var connStr = (context.Connection as EntityConnection).StoreConnection.ConnectionString;
using (var conn = factory.CreateConnection())
{
conn.ConnectionString = connStr;
conn.Open();
var cmd = factory.CreateCommand();
cmd.Connection = conn;
cmd.CommandText = deleteString;
cmd.ExecuteNonQuery();
}
}
Ø .Net FrameWork 4.0
当然,若是你使用的是 .Net FrameWork 4.0,那你可以使用以下 方式。
using (DB.Entity.StudentDBEntities context = new DB.Entity.StudentDBEntities())
{
context.ExecuteStoreCommand("delete from Students where StudentId = @studentId", new SqlParameter("@studentId", 5));
context.ExecuteStoreCommand("insert into students (StudentName)values (@p1)", new SqlParameter("@p1", "test"));
}注:3.5不支持此方式。
可能有人,想使用EntityCommand 进行批量删除,但是EntityCommand 仅支持 EntitySql ,问题就在这里了,EntitySql 目前不支持 Insert 、Delete 、Update操作,静等微软的下一步动作吧。
[b]C#中海量数据的批量插入和更新:[/b]
DateTime begin = DateTime.Now;
string connectionString = "";
using(SqlConnection conn = new SqlConnection(connectionString))...{
conn.Open();
SqlDataAdapter sd = new SqlDataAdapter();
sd.SelectCommand = new SqlCommand("select top 200 devid,data_time,data_value from CurrentTest", conn);
DataSet dataset = new DataSet();
sd.Fill(dataset);
Random r = new Random(1000);
sd.UpdateCommand = new SqlCommand("update CurrentTest "
+ " set data_time = @data_time,data_value = @data_value where devid = @devid", conn);
sd.UpdateCommand.Parameters.Add("@data_time", SqlDbType.Char, 19, "data_time");
sd.UpdateCommand.Parameters.Add("@data_value", SqlDbType.Int, 4, "data_value");
sd.UpdateCommand.Parameters.Add("@devid", SqlDbType.Char, 20, "devid");
sd.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
sd.UpdateBatchSize = 0;
for (int count = 0; count < 100000;)
...{
for (int i = 0; i < 200; i++,count++)
...{
dataset.Tables[0].Rows[i].BeginEdit();
dataset.Tables[0].Rows[i]["data_time"] = "2222-22-22 22:22:22";
dataset.Tables[0].Rows[i]["data_value"] = 100;
dataset.Tables[0].Rows[i]["devid"] = "DEVID"+count;
dataset.Tables[0].Rows[i].EndEdit();
}
sd.Update(dataset.Tables[0]);
}
dataset.Tables[0].Clear();
sd.Dispose();