最近因为要做数据性能优化,原有的数据通过foreach循环插入数据库,由于每循环一次就要和数据库交互一次,性能非常差,用时很长,在网上查阅了一些资料找到了SqlBulkCopy这个类,在原有的数据基础上进行改造,性能非常高。在此将自己的一些使用心得和大家分享:
private void ForeachTa(DataTable dtF,string id)
{
Stopwatch watch = new Stopwatch();
watch.Start();
DataTable dt = new DataTable();//准备存放数据的临时表
//构造表的列
dt.Columns.Add("ID");
dt.Columns.Add("MaterialID");
dt.Columns.Add("BillID");
dt.Columns.Add("code");
dt.Columns.Add("Name");
dt.Columns.Add("Model");
dt.Columns.Add("MeasureUnit");
dt.Columns.Add("Price");
dt.Columns.Add("Provider");
dt.Columns.Add("LastPrice");
foreach (DataRow row in dtF.Rows)
{
if (row["MaterialID"] == DBNull.Value)
{
if (!string.IsNullOrEmpty(row["Price"].ToString()))
{
DataRow rows = dt.NewRow();
rows["ID"] = System.Guid.NewGuid().ToString();
rows["BillID"] = id;
rows["Name"] = row["Name"];
rows["Model"] = row["Model"];
rows["MeasureUnit"] = row["MeasureUnit"];
rows["Price"] = decimal.Parse(row["Price"].ToString());
rows["Provider"] = row["Provider"];
dt.Rows.Add(rows);
}
else
{
DataRow rows = dt.NewRow();
rows["ID"] = System.Guid.NewGuid().ToString();
rows["BillID"] = id;
rows["Name"] = row["Name"];
rows["Model"] = row["Model"];
rows["MeasureUnit"] = row["MeasureUnit"];
rows["Provider"] = row["Provider"];
dt.Rows.Add(rows);
}
}
else
{
if (!string.IsNullOrEmpty(row["Price"].ToString()))
{
DataRow rows = dt.NewRow();
rows["ID"] = System.Guid.NewGuid().ToString();
rows["MaterialID"] = row["MaterialID"];
rows["BillID"] = id;
rows["Code"] = row["Code"];
rows["Name"] = row["Name"];
rows["Model"] = row["Model"];
rows["MeasureUnit"] = row["MeasureUnit"];
rows["Price"] = decimal.Parse(row["Price"].ToString());
rows["LastPrice"] = decimal.Parse(row["Price"].ToString());
rows["Provider"] = row["Provider"];
dt.Rows.Add(rows);
}
else
{
DataRow rows = dt.NewRow();
rows["ID"] = System.Guid.NewGuid().ToString();
rows["MaterialID"] = row["MaterialID"];
rows["BillID"] = id;
rows["Code"] = row["Code"];
rows["Name"] = row["Name"];
rows["Model"] = row["Model"];
rows["MeasureUnit"] = row["MeasureUnit"];
rows["Provider"] = row["Provider"];
dt.Rows.Add(rows);
}
}
}
//重点代码处理方式
string connStr = ConfigurationManager.ConnectionStrings["SqlConnectionString"].ConnectionString;//连接字符串
using (SqlBulkCopy sqlBulkcopy = new SqlBulkCopy(connStr))
{
sqlBulkcopy.DestinationTableName = "表名";//定义需要存入数据库中的相应的表的名称
sqlBulkcopy.ColumnMappings.Add("ID", "ID");
sqlBulkcopy.ColumnMappings.Add("MaterialID", "MaterialID");
sqlBulkcopy.ColumnMappings.Add("BillID", "BillID");
sqlBulkcopy.ColumnMappings.Add("Code", "Code");
sqlBulkcopy.ColumnMappings.Add("Name", "Name");
sqlBulkcopy.ColumnMappings.Add("Model", "Model");
sqlBulkcopy.ColumnMappings.Add("MeasureUnit", "MeasureUnit");
sqlBulkcopy.ColumnMappings.Add("Price", "Price");
sqlBulkcopy.ColumnMappings.Add("LastPrice", "LastPrice");
sqlBulkcopy.ColumnMappings.Add("Provider", "Provider");
sqlBulkcopy.WriteToServer(dt);
}
watch.Stop();
}