/// <summary>
/// 使用事务和SqlBulkCopy批量导入数据
/// By Serein 2013-12-31
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="dt">數據集</param>
public string SqlBulkCopyData(string tableName, DataTable dt)
{
string msg = string.Empty;
SetConnection();
Cmd.Connection.Open();
SqlTransaction sqlbulkTransaction = Cmd.Connection.BeginTransaction();
SqlBulkCopy copy = new SqlBulkCopy(Cmd.Connection, SqlBulkCopyOptions.CheckConstraints, sqlbulkTransaction);
copy.DestinationTableName = tableName;
foreach (DataColumn dc in dt.Columns)
{
copy.ColumnMappings.Add(dc.ColumnName, dc.ColumnName);
}
try
{
copy.WriteToServer(dt);
sqlbulkTransaction.Commit();
msg = "S0000";
}
catch (Exception ex)
{
sqlbulkTransaction.Rollback();
//Console.WriteLine(ex.ToString());
msg = ex.Message;
//throw ex;
}
finally
{
copy.Close();
Cmd.Connection.Close();
}
return msg;
}
SqlCommand Cmd = new SqlCommand();
void SetConnection()
{
if (Cmd.Connection == null)
{
switch (DataBase)
{
case "MBS":
Cmd.Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MBSConnectionString"].ConnectionString);
break;
}
}
}