public void Insert <T>(DataTable table, CRUDOption option) where T : class
{
using (var sqlCopy = new SqlBulkCopy(_configuration.GetConnectionString("DefaultConnection")))
{
sqlCopy.DestinationTableName = option.TableName;
sqlCopy.BatchSize = option.BatchSize;
sqlCopy.BulkCopyTimeout = option.BulkCopyTimeout;
//sqlCopy.ColumnMappings = new SqlBulkCopyColumnMappingCollection();
//sqlCopy.WriteToServer(table);
sqlCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
sqlCopy.NotifyAfter = option.NotifyAfter;
// Set up the column mappings by name.
sqlCopy.AddValueToValueMappings(option.ColumnMapping);
try
{
// Write from the source to the destination.
sqlCopy.WriteToServer(table);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{
table.Clear();
}
}
}
public void Insert(DataTable table, BatchOperationOption option)
{
if (option == null)
{
throw new Exception("参数option不能为空");
}
if (string.IsNullOrEmpty(option.ConnectionString))
{
throw new Exception("参数option的属性ConnectionString必须是一个数据库连接字符串");
}
if (string.IsNullOrEmpty(option.TableName))
{
throw new Exception("参数option的属性TableName必须是一个数据库表名称");
}
if (option.ColumnMapping == null && option.ColumnMapping.Count == 0)
{
throw new Exception("参数option的属性ColumnMapping必须是一个从excel到数据库字段名的字典");
}
using (var sqlCopy = new SqlBulkCopy(option.ConnectionString))
{
sqlCopy.DestinationTableName = option.TableName;
if (option.BatchSize > 0)
{
sqlCopy.BatchSize = option.BatchSize;
}
if (option.BulkCopyTimeout > 0)
{
sqlCopy.BulkCopyTimeout = option.BulkCopyTimeout;
}
sqlCopy.SqlRowsCopied += new SqlRowsCopiedEventHandler(OnSqlRowsCopied);
sqlCopy.NotifyAfter = option.NotifyAfter;
// Set up the column mappings by name.
sqlCopy.AddValueToValueMappings(option.ColumnMapping);
if (option.ChiefId > 0)
{
table.Columns.Add("ChiefId", typeof(string), option.ChiefId.ToString());
sqlCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping("ChiefId", "ChiefId"));
}
try
{
// Write from the source to the destination.
sqlCopy.WriteToServer(table);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
throw ex;
}
finally
{
table.Clear();
}
}
}