思路一:对于大批量数据采用ADO.Net或者linq to sql进行一条条执行会极大影响效率,而采用SQL Bulkcopy将整个对象集导入可以极大的缩短时间。代码执行分为如下三步。
1.使用代码对数据集进行预处理,比如修改数据或者过滤数据
2.将数据集转换为DataTable(自适应算法)
public static DataTable ToDataTable<T>(IEnumerable<T> collection)
{
var props = typeof(T).GetProperties();
var dt = new DataTable();
dt.Columns.AddRange(props.Select(p => new DataColumn(p.Name, p.PropertyType)).ToArray());
if (collection.Count() > 0)
{
for (int i = 0; i < collection.Count(); i++)
{
ArrayList tempList = new ArrayList();
foreach (PropertyInfo pi in props)
{
object obj = pi.GetValue(collection.ElementAt(i), null);
tempList.Add(obj);
}
object[] array = tempList.ToArray();
dt.LoadDataRow(array, true);
}
}
return dt;
}
3.使用SQL Bulkcopy将DataTable导入数据库
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
/// <summary>
/// 批量导入
/// </summary>
/// <param name="connectionString">链接字符串</param>
/// <param name="TableName">数据库名</param>
/// <param name="dt">数据表</param>
private void SqlBulkCopyByDatatable(string connectionString, string TableName, DataTable dt)
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
using (SqlBulkCopy sqlbulkcopy =
new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))
{
try
{
sqlbulkcopy.DestinationTableName = TableName;
for (int i = 0; i < dt.Columns.Count; i++)
{
sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);
}
sqlbulkcopy.WriteToServer(dt);
}
catch (System.Exception ex)
{
throw ex;
}
}
}
}
如果数据结构不同的话则需要额外更改映射
/// <summary>
/// 不同表之间复制数据
/// </summary>
private static void PerformBulkCopyDifferentSchema()
{
string connectionString = @"连接字符串";
DataTable sourceData = new DataTable();
// 源
using (SqlConnection sourceConnection = new SqlConnection(connectionString))
{
SqlCommand myCommand = new SqlCommand("SELECT TOP 5 * FROM Products_Archive", sourceConnection);
sourceConnection.Open();
SqlDataReader reader = myCommand.ExecuteReader();
// 目的
using (SqlConnection destinationConnection = new SqlConnection(connectionString))
{
// 打开连接
destinationConnection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))
{
bulkCopy.ColumnMappings.Add("ProductID", "ProductID");
bulkCopy.ColumnMappings.Add("ProductName", "Name");
bulkCopy.ColumnMappings.Add("QuantityPerUnit", "Quantity");
bulkCopy.DestinationTableName = "Products_TopSelling";
bulkCopy.WriteToServer(reader);
}
}
reader.Close();
}
}
思路二:使用UpdateCommand参数将数据写入
DateTime begin = DateTime.Now;
try
{
using (SqlConnection conn = new SqlConnection(connString))
{
conn.Open();
SqlDataAdapter sd = new SqlDataAdapter();
sd.SelectCommand = new SqlCommand("select top 200 * from Mans", conn);
DataSet dataset = new DataSet();
sd.Fill(dataset);
Random r = new Random(1000);
sd.UpdateCommand = new SqlCommand("SQL 语句, conn);
sd.UpdateCommand.Parameters.Add("@参数", SqlDbType.VarChar, 200, "参数");
sd.UpdateCommand.UpdatedRowSource = UpdateRowSource.None;
sd.UpdateBatchSize = 0;
for (int count = 0; count < dt.Rows.Count - 1; count++)
{
for (int i = 0; i < 200; i++, count++)
{
if (count > dt.Rows.Count - 1) break;
dataset.Tables[0].Rows[i].BeginEdit();
dataset.Tables[0].Rows[i]["参数"] = dt.Rows[count][0];//源数据表对应的位置
dataset.Tables[0].Rows[i].EndEdit();//终止发生在该行的编辑。
}
//Update:为指定 System.Data.DataTable 中每个已插入、已更新或已删除的行调用相应的 INSERT、UPDATE 或 DELETE 语句。
sd.Update(dataset.Tables[0]);
}
dataset.Tables[0].Clear();
sd.Dispose();
dataset.Dispose();
conn.Close();
}
}
catch (Exception ex) { }
TimeSpan ts = DateTime.Now - begin;//更新一万条数据2秒左右
外层一定要套一个空的catch语句,否则如果出现数据库中不存在的数据就会触发并发性错误终止程序,加上空catch则不会影响执行,同时需要在构造datatable的时候做一个存在性检查,这里使用select语句
foreach (DataRow item in X.Rows)
{
string Find = "IDNo='{0}'";
Find = string.Format(Find, item.ItemArray[2].ToString().Trim());
if (basement.Select(Find).Length > 0)
{
string AreaZone = System.IO.Path.GetFileNameWithoutExtension(Path);
string Locations = item.ItemArray[0].ToString().Trim();
DataRow dr = dt.NewRow();
dr["MLocation"] = Locations;
dr["AreaZone"] = DataX.GetArea(Locations);
dr["AreaName"] = DataX.GetHome(Locations);
dr["IDNo"] = item.ItemArray[2].ToString().Trim();
dt.Rows.Add(dr);
}
Count++;
settext(Pbar, Count, lblValue, X.Rows.Count);
}