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 UpdateNoTrans <T, TK>(this IEnumerable <Table2 <T, TK> > tables, Func <T, string> condition) { if (tables == null) { return(-1); } var updateTabs = tables.Where(a => a.__EditColumns.Count > 0).ToArray(); if (updateTabs.Length == 0) { return(0); } var sqls = new List <KeyValuePair <string, List <DbParameter> > >(); var sb = new StringBuilder(); var paraIndex = 0; var parameters = new List <DbParameter>(); foreach (var table in updateTabs) { if (table.__EditColumns.Count > 0) { sb.Append("\nupdate ").Append(table.TableName).Append(" set "); foreach (var column in table.__EditColumns) { sb.Append("[").Append(column.Key).Append("]=@p").Append(parameters.Count.ToString()).Append(","); parameters.Add(DbBase2 <TK> .CreateDbParameter("@p" + parameters.Count, column.Value)); paraIndex += 2; } sb.Remove(sb.Length - 1, 1); sb.Append(" where ").Append(condition((T)(object)table)); if (paraIndex > 1500) { sqls.Add(new KeyValuePair <string, List <DbParameter> >(sb.ToString(), parameters)); sb = new StringBuilder(); paraIndex = 0; parameters = new List <DbParameter>(); } } } if (sb.Length > 0) { sqls.Add(new KeyValuePair <string, List <DbParameter> >(sb.ToString(), parameters)); } using (var conn = DbBase2 <TK> .GetConnection(false)) { foreach (var sql in sqls) { var cmd = Sql2.CreateDbCommand(sql.Key, conn); cmd.CommandType = CommandType.Text; cmd.Parameters.AddRange(sql.Value.ToArray()); cmd.ExecuteNonQuery(); } } return(updateTabs.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 UpdateSetNoTrans <T, TK>(this Table2 <T, TK> table, string condition, SqlConnection conn, string set = "") { if (table == null) { return(-1); } if (table.__EditColumns.Count == 0) { throw new Exception("更新字段数至少1个以上"); } var sql = new StringBuilder(); var parameters = new List <DbParameter>(); sql.Append("update ").Append(table.TableName).Append(" set "); foreach (var column in table.__EditColumns) { sql.Append("[").Append(column.Key).Append("]=@p").Append(parameters.Count.ToString()).Append(","); parameters.Add(DbBase2 <TK> .CreateDbParameter("@p" + parameters.Count, column.Value)); } if (set.HasValue()) { sql.Append(set); } else { sql.Remove(sql.Length - 1, 1); } sql.Append(" where ").Append(condition); var cmd = Sql2.CreateDbCommand(sql.ToString(), conn); cmd.CommandType = CommandType.Text; cmd.Parameters.AddRange(parameters.ToArray()); var result = cmd.ExecuteNonQuery(); return(result); }
public static int InsertOrUpdate <T, TK>(this IEnumerable <Table2 <T, TK> > tables, Func <T, string> condition) { if (tables == null) { return(-1); } var updateTabs = tables.Where(a => a.__EditColumns.Count > 0).ToArray(); if (updateTabs.Length == 0) { return(0); } var sw = Stopwatch.StartNew(); var tableName = updateTabs[0].TableName; var sqls = new List <KeyValuePair <string, List <DbParameter> > >(); try { var paraIndex = 0; var sb = new StringBuilder(); var parameters = new List <DbParameter>(); foreach (var table in updateTabs) { var where = condition((T)(object)table); sb.Append("if not exists (select 1 from ").Append(tableName).Append(" where ").Append(where).Append(")\n"); sb.Append("insert into ").Append(tableName).Append(" ("); foreach (var column in table.__EditColumns) { sb.Append("[").Append(column.Key).Append("],"); } sb.Remove(sb.Length - 1, 1); sb.Append(")values("); foreach (var column in table.__EditColumns) { sb.Append("@p").Append(paraIndex).Append(","); parameters.Add(DbBase2 <TK> .CreateDbParameter("@p" + paraIndex, column.Value)); paraIndex++; } sb.Remove(sb.Length - 1, 1); sb.Append(")\n"); sb.Append("else update ").Append(tableName).Append(" set "); foreach (var column in table.__EditColumns) { sb.Append("[").Append(column.Key).Append("]=@p").Append(paraIndex).Append(","); parameters.Add(DbBase2 <TK> .CreateDbParameter("@p" + paraIndex, column.Value)); paraIndex++; } sb.Remove(sb.Length - 1, 1); sb.Append(" where ").Append(where).Append("\n"); if (paraIndex > 1500) { sqls.Add(new KeyValuePair <string, List <DbParameter> >(sb.ToString(), parameters)); sb = new StringBuilder(); paraIndex = 0; parameters = new List <DbParameter>(); } } if (sb.Length > 0) { sqls.Add(new KeyValuePair <string, List <DbParameter> >(sb.ToString(), parameters)); } using (var trans = new TransactionScope(TransactionScopeOption.Required, DbBase2.TransactionOption)) { using (var conn = DbBase2 <TK> .GetConnection(false)) { foreach (var sql in sqls) { var cmd = Sql2.CreateDbCommand(sql.Key, conn); cmd.CommandType = CommandType.Text; cmd.Parameters.AddRange(sql.Value.ToArray()); cmd.ExecuteNonQuery(); } trans.Complete(); } } } catch (Exception e) { Log.Error(e.Message + e.StackTrace + sqls.Join("\n")); return(-1); } finally { var useTime = sw.ElapsedMilliseconds; var logStr = "Table.InsertOrUpdate({0}[{1}]) 执行时间 {2} 毫秒".Formats(tableName, updateTabs.Length, useTime.ToString("F0")); if (useTime / 1000 > 5) { Log.Warn(logStr); } Log.Debug(logStr); } return(updateTabs.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); } }