需求:读取传入的DataSet,将DataSet里的DataTable插入数据库
分析:
1.数据量大,不推荐循环sql insert
2.多表操作,事务
/// <summary>
/// 添加数据
/// </summary>
/// <returns></returns>
public static void AddTaxInfo(DataSet infoSet)
{
using (var conn = new SqlConnection(_connectionString))
{
conn.Open();
SqlTransaction sqlbulkTransaction = conn.BeginTransaction();
try
{
//插入主表
using (var bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, sqlbulkTransaction))
{
var table_Main = infoSet.Tables["Core_TaxMain"];
foreach (DataColumn dc in table_Main.Columns)
{
bulkCopy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
}
bulkCopy.DestinationTableName = "Core_TaxMain";
//复制Table
bulkCopy.WriteToServer(table_Main);
}
//插入系表
using (var bulkCopy_ = new SqlBulkCopy(conn, SqlBulkCopyOptions.CheckConstraints, sqlbulkTransaction))
{
var table_Detail = infoSet.Tables["Core_TaxDetail"];
foreach (DataColumn dc in table_Detail.Columns)
{
bulkCopy_.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
}
bulkCopy_.DestinationTableName = "Core_TaxDetail";
bulkCopy_.WriteToServer(table_Detail);
}
//一个事务
sqlbulkTransaction.Commit();
}
catch (Exception)
{
sqlbulkTransaction.Rollback();
throw;
}
}
}
改进:上面的方法完全能够满足需求,只是创建bulkcopy对象显得重复,于是把数据库连接对象封装了一下。
封装好的类将写在另一篇博客里自己写的数据库工具类,这里直接调用:
public static void AddTaxInfo(DataSet infoSet)
{
var table_Main = infoSet.Tables["Core_TaxMain"];//主表
var table_Detail = infoSet.Tables["Core_TaxDetail"];//系表
using (SqlAccess client = new SqlAccess(_connectionString))//SqlAcess是封装类,类似SqlHelper
{
client.BeginTransaction();
foreach (DataColumn dc in table_Main.Columns)
{
client.BulkCopyColumnMapping.Add(dc.ColumnName, dc.ColumnName);
}
client.SetBulkCopy("a").WriteToServer(table_Main);
foreach (DataColumn dc in table_Detail.Columns)
{
client.BulkCopyColumnMapping.Add(dc.ColumnName, dc.ColumnName);
}
client.SetBulkCopy("b").WriteToServer(table_Detail);
client.Commit();
}