public static int InsertNoTrans(this IEnumerable <TableBase2> rows, string tableName)
{
if (rows == null)
{
return(-1);
}
var arrRows = rows.ToArray();
if (arrRows.Length == 0)
{
return(0);
}
var dbType = arrRows.First().DbType;
using (var conn = DbBase2.GetSqlConnection(dbType))
{
var cmd = Sql2.CreateDbCommand("SELECT TOP 0 * FROM " + tableName, conn);
var sda = Sql2.CreateDbDataAdapter(cmd);
var dtSource = new DataTable();
sda.Fill(dtSource);
var dt = arrRows.Select(a => a.__EditColumns).ToDataTable(dtSource);
using (var bulkCopy = new SqlBulkCopy(conn))
{
bulkCopy.DestinationTableName = tableName;
bulkCopy.BatchSize = dt.Rows.Count;
bulkCopy.WriteToServer(dt);
return(arrRows.Length);
}
}
}
/// <summary>
/// 删除后重新添加
/// </summary>
public static int DeleteInsertNoTrans <T, TK>(this IEnumerable <Table2 <T, TK> > rows, string deleteCondition)
{
if (rows == null)
{
return(-1);
}
var arrRows = rows.ToArray();
if (arrRows.Length == 0)
{
return(0);
}
var tableName = arrRows[0].TableName;
using (var conn = DbBase2 <TK> .GetSqlConnection(false))
{
var cmd = Sql2.CreateDbCommand("SELECT TOP 0 * FROM " + tableName, conn);
var sda = Sql2.CreateDbDataAdapter(cmd);
var dtSource = new DataTable();
sda.Fill(dtSource);
var dt = arrRows.Select(a => a.__EditColumns).ToDataTable(dtSource);
if (deleteCondition.HasValue())
{
var sql = "DELETE FROM {0} WHERE {1}".Formats(tableName, deleteCondition);
cmd = Sql2.CreateDbCommand(sql, conn);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
using (var bulkCopy = new SqlBulkCopy(conn))
{
bulkCopy.DestinationTableName = tableName;
bulkCopy.BatchSize = dt.Rows.Count;
bulkCopy.WriteToServer(dt);
return(arrRows.Length);
}
}
}
/// <summary>
/// 删除后重新添加
/// </summary>
public static int DeleteInsert <T, TK>(this IEnumerable <Table2 <T, TK> > rows, string deleteCondition)
{
try
{
if (rows == null)
{
return(-1);
}
var arrRows = rows.ToArray();
if (arrRows.Length == 0)
{
return(0);
}
var sw = Stopwatch.StartNew();
var option = new TransactionOptions
{
IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted,
Timeout = new TimeSpan(0, 2, 0)
};
using (var trans = new TransactionScope(TransactionScopeOption.Required, option))
{
using (var conn = DbBase2 <TK> .GetSqlConnection(false))
{
using (var bulkCopy = new SqlBulkCopy(conn))
{
var tableName = arrRows[0].TableName;
var logStr = new StringBuilder();
try
{
var cmd = Sql2.CreateDbCommand("SELECT TOP 0 * FROM " + tableName, conn);
var sda = Sql2.CreateDbDataAdapter(cmd);
var dtSource = new DataTable();
sda.Fill(dtSource);
var dt = arrRows.Select(a => a.__EditColumns).ToDataTable(dtSource);
if (deleteCondition.HasValue())
{
var sql = "DELETE FROM {0} WHERE {1}".Formats(tableName, deleteCondition);
cmd = Sql2.CreateDbCommand(sql, conn);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
logStr.Append(sql).Append(";");
}
bulkCopy.DestinationTableName = tableName;
bulkCopy.BatchSize = dt.Rows.Count;
bulkCopy.WriteToServer(dt);
trans.Complete();
return(arrRows.Length);
}
catch (Exception e)
{
Log.Error(e.Message);
return(-1);
}
finally
{
DbBase2.Close(conn);
bulkCopy.Close();
sw.Stop();
var useTime = sw.ElapsedMilliseconds;
logStr.AppendFormat("{0}.Insert({1}) 执行时间 {2} 毫秒", tableName, arrRows.Length, useTime.ToString("F0"));
if (useTime > 5000)
{
Log.Warn(logStr.ToString());
}
Log.Debug(logStr.ToString());
}
}
}
}
}
catch (Exception e)
{
Log.Error(e.Message + e.StackTrace);
return(-1);
}
}