public static MigrationBase Run(IDbConnectionFactory dbFactory, Type nextRun, Action <MigrationBase> migrateAction) { var sb = StringBuilderCache.Allocate(); var holdFilter = OrmLiteConfig.BeforeExecFilter; OrmLiteConfig.BeforeExecFilter = dbCmd => sb.AppendLine(dbCmd.GetDebugString()); var namedConnection = nextRun.FirstAttribute <NamedConnectionAttribute>()?.Name; IDbConnection? useDb = null; IDbTransaction?trans = null; var instance = nextRun.CreateInstance <MigrationBase>(); try { useDb = namedConnection == null ? dbFactory.OpenDbConnection() : dbFactory.OpenDbConnection(namedConnection); instance.DbFactory = dbFactory; instance.Db = useDb; trans = useDb.OpenTransaction(); instance.AfterOpen(); instance.Transaction = trans; instance.StartedAt = DateTime.UtcNow; // Run Migration migrateAction(instance); instance.CompletedDate = DateTime.UtcNow; instance.Log = sb.ToString(); instance.BeforeCommit(); trans.Commit(); trans.Dispose(); trans = null; } catch (Exception e) { instance.CompletedDate = DateTime.UtcNow; instance.Error = e; instance.Log = sb.ToString(); instance.BeforeRollback(); trans?.Rollback(); trans?.Dispose(); } finally { instance.Db = null; instance.Transaction = null; OrmLiteConfig.BeforeExecFilter = holdFilter; useDb?.Dispose(); StringBuilderCache.Free(sb); } return(instance); }
public ResponseResult ReverseProcess(WfAppRunner runner) { IWorkflowService wfService = new WorkflowService(); IDbConnection conn = SessionFactory.CreateConnection(); IDbTransaction trans = null; try { trans = conn.BeginTransaction(); var result = wfService.ReverseProcess(conn, runner, trans); trans.Commit(); if (result.Status == WfExecutedStatus.Success) { return(ResponseResult.Success()); } else { return(ResponseResult.Error(result.Message)); } } catch (WorkflowException w) { trans.Rollback(); return(ResponseResult.Error(w.Message)); } finally { trans.Dispose(); if (conn.State == ConnectionState.Open) { conn.Close(); } } }
/// <summary> /// 使用ioc中注册的数据库 /// </summary> /// <param name="callback"></param> /// <param name="iso"></param> public static void PrepareConnection(Func <IDbConnection, IDbTransaction, bool> callback, IsolationLevel?iso = null) { PrepareConnection(con => { IDbTransaction t = null; if (iso == null) { t = con.BeginTransaction(); } else { t = con.BeginTransaction(iso.Value); } try { if (callback.Invoke(con, t)) { t.Commit(); } else { t.Rollback(); } } catch (Exception e) { t.Rollback(); throw e; } finally { t.Dispose(); } }); }
public void Dispose() { trans.Dispose(); }
/// <summary> /// Save the display preferences associated with an item in the repo /// </summary> /// <param name="displayPreferences">The display preferences.</param> /// <param name="userId">The user id.</param> /// <param name="client">The client.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns>Task.</returns> /// <exception cref="System.ArgumentNullException">item</exception> public async Task SaveDisplayPreferences(DisplayPreferences displayPreferences, Guid userId, string client, CancellationToken cancellationToken) { if (displayPreferences == null) { throw new ArgumentNullException("displayPreferences"); } if (displayPreferences.Id == Guid.Empty) { throw new ArgumentNullException("displayPreferences.Id"); } cancellationToken.ThrowIfCancellationRequested(); var serialized = _jsonSerializer.SerializeToBytes(displayPreferences); await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false); IDbTransaction transaction = null; try { transaction = _connection.BeginTransaction(); using (var cmd = _connection.CreateCommand()) { cmd.CommandText = "replace into userdisplaypreferences (id, userid, client, data) values (@1, @2, @3, @4)"; cmd.Parameters.Add(cmd, "@1", DbType.Guid).Value = displayPreferences.Id; cmd.Parameters.Add(cmd, "@2", DbType.Guid).Value = userId; cmd.Parameters.Add(cmd, "@3", DbType.String).Value = client; cmd.Parameters.Add(cmd, "@4", DbType.Binary).Value = serialized; cmd.Transaction = transaction; cmd.ExecuteNonQuery(); } transaction.Commit(); } catch (OperationCanceledException) { if (transaction != null) { transaction.Rollback(); } throw; } catch (Exception e) { _logger.ErrorException("Failed to save display preferences:", e); if (transaction != null) { transaction.Rollback(); } throw; } finally { if (transaction != null) { transaction.Dispose(); } _writeLock.Release(); } }
public virtual bool Import(DataTable Data, ODAParameter[] Prms) { int ImportCount = 0; string Sqlcols = ""; string Sqlprms = ""; DataTable ImportData = Data.Copy(); for (int i = 0; i < Prms.Length; i++) { if (ImportData.Columns.Contains(Prms[i].ColumnName)) { Sqlcols += "," + Prms[i].ColumnName; Sqlprms += "," + this.ParamsMark + Prms[i].ParamsName; } else { ImportData.Columns.Add(new DataColumn(Prms[i].ColumnName)); } ImportData.Columns[Prms[i].ColumnName].SetOrdinal(i); } string sql = new StringBuilder() .Append("INSERT INTO ") .Append(Data.TableName) .Append(" ( ") .Append(Sqlcols.TrimStart(',')) .Append(") VALUES (") .Append(Sqlprms.TrimStart(',')) .Append(")").ToString(); IDbTransaction tmpTran = null; IDbConnection conn = null; if (this.Transaction != null) { conn = this.Transaction.Connection; } else { conn = this.GetConnection(); tmpTran = conn.BeginTransaction(); } try { for (int i = 0; i < ImportData.Rows.Count; i++) { for (int j = 0; j < Prms.Length; j++) { Prms[j].ParamsValue = ImportData.Rows[i][j]; Prms[j].Direction = ParameterDirection.Input; } var tmpCmd = conn.CreateCommand(); tmpCmd.CommandTimeout = 60000; tmpCmd.CommandType = CommandType.Text; SetCmdParameters(ref tmpCmd, sql, Prms); if (this.Transaction == null) { tmpCmd.Transaction = tmpTran; } else { tmpCmd.Transaction = this.Transaction; } ImportCount += tmpCmd.ExecuteNonQuery(); tmpCmd.Dispose(); } if (tmpTran != null) { tmpTran.Commit(); tmpTran.Dispose(); } return(ImportCount > 0); } catch { if (tmpTran != null) { tmpTran.Rollback(); tmpTran.Dispose(); } throw; } finally { if (conn != null && this.Transaction == null) { conn.Close(); conn.Dispose(); conn = null; } } }
/// <summary> /// Inserts multiple data in the database in an asynchronous way. /// </summary> /// <typeparam name="TEntity">The type of the object (whether a data entity or a dynamic).</typeparam> /// <param name="connection">The connection object to be used.</param> /// <param name="tableName">The name of the target table to be used.</param> /// <param name="entities">The list of data entity or dynamic objects to be inserted.</param> /// <param name="batchSize">The batch size of the insertion.</param> /// <param name="fields">The mapping list of <see cref="Field"/> objects to be used.</param> /// <param name="commandTimeout">The command timeout in seconds to be used.</param> /// <param name="transaction">The transaction to be used.</param> /// <param name="trace">The trace object to be used.</param> /// <param name="statementBuilder">The statement builder object to be used.</param> /// <param name="skipIdentityCheck">True to skip the identity check.</param> /// <returns>The number of inserted rows.</returns> internal static async Task <int> InsertAllAsyncInternalBase <TEntity>(this IDbConnection connection, string tableName, IEnumerable <TEntity> entities, int batchSize = Constant.DefaultBatchOperationSize, IEnumerable <Field> fields = null, int?commandTimeout = null, IDbTransaction transaction = null, ITrace trace = null, IStatementBuilder statementBuilder = null, bool skipIdentityCheck = false) where TEntity : class { // Guard the parameters var count = GuardInsertAll(entities); // Validate the batch size batchSize = Math.Min(batchSize, count); // Get the function var callback = new Func <int, InsertAllExecutionContext <TEntity> >((int batchSizeValue) => { // Variables needed var identity = (Field)null; var dbFields = DbFieldCache.Get(connection, tableName); var inputFields = (IEnumerable <DbField>)null; var outputFields = (IEnumerable <DbField>)null; var identityDbField = dbFields?.FirstOrDefault(f => f.IsIdentity); // Set the identity value if (skipIdentityCheck == false) { identity = IdentityCache.Get <TEntity>()?.AsField(); if (identity == null && identityDbField != null) { identity = FieldCache.Get <TEntity>().FirstOrDefault(field => field.UnquotedName.ToLower() == identityDbField.UnquotedName.ToLower()); } } // Filter the actual properties for input fields inputFields = dbFields? .Where(dbField => dbField.IsIdentity == false) .Where(dbField => fields.FirstOrDefault(field => field.UnquotedName.ToLower() == dbField.UnquotedName.ToLower()) != null) .AsList(); // Set the output fields if (batchSizeValue > 1) { outputFields = identityDbField?.AsEnumerable(); } // Variables for the context var multipleEntitiesFunc = (Action <DbCommand, IList <TEntity> >)null; var identitySettersFunc = (List <Action <TEntity, DbCommand> >)null; var singleEntityFunc = (Action <DbCommand, TEntity>)null; var identitySetterFunc = (Action <TEntity, object>)null; // Get if we have not skipped it if (skipIdentityCheck == false && identity != null) { if (batchSizeValue <= 1) { identitySetterFunc = FunctionCache.GetDataEntityPropertyValueSetterFunction <TEntity>(identity); } else { identitySettersFunc = new List <Action <TEntity, DbCommand> >(); for (var index = 0; index < batchSizeValue; index++) { identitySettersFunc.Add(FunctionCache.GetDataEntityPropertySetterFromDbCommandParameterFunction <TEntity>(identity, identity.UnquotedName, index)); } } } // Identity which objects to set if (batchSizeValue <= 1) { singleEntityFunc = FunctionCache.GetDataEntityDbCommandParameterSetterFunction <TEntity>( string.Concat(typeof(TEntity).FullName, ".", tableName, ".InsertAll"), inputFields?.AsList(), null); } else { multipleEntitiesFunc = FunctionCache.GetDataEntitiesDbCommandParameterSetterFunction <TEntity>( string.Concat(typeof(TEntity).FullName, ".", tableName, ".InsertAll"), inputFields?.AsList(), outputFields, batchSizeValue); } // Identify the requests var insertAllRequest = (InsertAllRequest)null; var insertRequest = (InsertRequest)null; // Create a different kind of requests if (typeof(TEntity) == typeof(object)) { if (batchSizeValue > 1) { insertAllRequest = new InsertAllRequest(tableName, connection, fields, batchSizeValue, statementBuilder); } else { insertRequest = new InsertRequest(tableName, connection, fields, statementBuilder); } } else { if (batchSizeValue > 1) { insertAllRequest = new InsertAllRequest(typeof(TEntity), connection, fields, batchSizeValue, statementBuilder); } else { insertRequest = new InsertRequest(typeof(TEntity), connection, fields, statementBuilder); } } // Return the value return(new InsertAllExecutionContext <TEntity> { CommandText = batchSizeValue > 1 ? CommandTextCache.GetInsertAllText(insertAllRequest) : CommandTextCache.GetInsertText(insertRequest), InputFields = inputFields, OutputFields = outputFields, BatchSize = batchSizeValue, SingleDataEntityParametersSetterFunc = singleEntityFunc, MultipleDataEntitiesParametersSetterFunc = multipleEntitiesFunc, IdentityPropertySetterFunc = identitySetterFunc, IdentityPropertySettersFunc = identitySettersFunc }); }); // Get the context var context = (InsertAllExecutionContext <TEntity>)null; // Identify the number of entities (performance), get an execution context from cache context = batchSize == 1 ? InsertAllExecutionContextCache <TEntity> .Get(tableName, fields, 1, callback) : InsertAllExecutionContextCache <TEntity> .Get(tableName, fields, batchSize, callback); // Before Execution if (trace != null) { var cancellableTraceLog = new CancellableTraceLog(context.CommandText, entities, null); trace.BeforeInsertAll(cancellableTraceLog); if (cancellableTraceLog.IsCancelled) { if (cancellableTraceLog.IsThrowException) { throw new CancelledExecutionException(context.CommandText); } return(0); } context.CommandText = (cancellableTraceLog.Statement ?? context.CommandText); entities = (IEnumerable <TEntity>)(cancellableTraceLog.Parameter ?? entities); } // Before Execution Time var beforeExecutionTime = DateTime.UtcNow; // Execution variables var result = 0; // Make sure to create transaction if there is no passed one var hasTransaction = (transaction != null); try { // Ensure the connection is open await connection.EnsureOpenAsync(); if (hasTransaction == false) { // Create a transaction transaction = connection.BeginTransaction(); } // Create the command using (var command = (DbCommand)connection.CreateCommand(context.CommandText, CommandType.Text, commandTimeout, transaction)) { // Directly execute if the entities is only 1 (performance) if (context.BatchSize == 1) { foreach (var entity in entities) { // Set the values context.SingleDataEntityParametersSetterFunc(command, entity); // Actual Execution var returnValue = ObjectConverter.DbNullToNull(await command.ExecuteScalarAsync()); // Set the return value if (returnValue != null) { context.IdentityPropertySetterFunc?.Invoke(entity, returnValue); } // Iterate the result result++; } } else { foreach (var batchEntities in entities.Split(batchSize)) { var batchItems = batchEntities.AsList(); // Break if there is no more records if (batchItems.Count <= 0) { break; } // Check if the batch size has changed (probably the last batch on the enumerables) if (batchItems.Count != batchSize) { // Get a new execution context from cache context = InsertAllExecutionContextCache <TEntity> .Get(tableName, fields, batchItems.Count, callback); // Set the command properties command.CommandText = context.CommandText; // Prepare the command command.Prepare(); } // Set the values context.MultipleDataEntitiesParametersSetterFunc(command, batchItems); // Actual Execution result += await command.ExecuteNonQueryAsync(); // Set the identities if (context.IdentityPropertySettersFunc != null && command.Parameters.Count > 0) { for (var index = 0; index < batchItems.Count; index++) { var func = context.IdentityPropertySettersFunc.ElementAt(index); func(batchItems[index], command); } } } } } if (hasTransaction == false) { // Commit the transaction transaction.Commit(); } } catch { if (hasTransaction == false) { // Rollback for any exception transaction.Rollback(); } throw; } finally { if (hasTransaction == false) { // Rollback and dispose the transaction transaction.Dispose(); } } // After Execution if (trace != null) { trace.AfterInsertAll(new TraceLog(context.CommandText, entities, result, DateTime.UtcNow.Subtract(beforeExecutionTime))); } // Return the result return(result); }
/// <summary> /// Deletes the user. /// </summary> /// <param name="user">The user.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns>Task.</returns> /// <exception cref="System.ArgumentNullException">user</exception> public async Task DeleteUser(User user, CancellationToken cancellationToken) { if (user == null) { throw new ArgumentNullException("user"); } if (cancellationToken == null) { throw new ArgumentNullException("cancellationToken"); } cancellationToken.ThrowIfCancellationRequested(); await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false); IDbTransaction transaction = null; try { transaction = _connection.BeginTransaction(); using (var cmd = _connection.CreateCommand()) { cmd.CommandText = "delete from users where guid=@guid"; cmd.Parameters.Add(cmd, "@guid", DbType.Guid).Value = user.Id; cmd.Transaction = transaction; cmd.ExecuteNonQuery(); } transaction.Commit(); } catch (OperationCanceledException) { if (transaction != null) { transaction.Rollback(); } throw; } catch (Exception e) { _logger.ErrorException("Failed to delete user:", e); if (transaction != null) { transaction.Rollback(); } throw; } finally { if (transaction != null) { transaction.Dispose(); } _writeLock.Release(); } }
public void CancelUpdate(IDbTransaction trans) { trans.Rollback(); trans.Dispose(); m_conn.Close(); m_conn = null; }
public void Dispose() { _dbTransaction.Dispose(); _dbConnection.Dispose(); }
/// <summary> /// The execute. /// </summary> /// <param name="dbfunctionType"> /// The dbfunction type. /// </param> /// <param name="operationName"> /// The operation name. /// </param> /// <param name="parameters"> /// The parameters. /// </param> /// <param name="result"> /// The result. /// </param> /// <param name="transaction"></param> /// <returns> /// The execute. /// </returns> public virtual bool Execute( DbFunctionType dbfunctionType, [NotNull] string operationName, [NotNull] IEnumerable<KeyValuePair<string, object>> parameters, [CanBeNull] out object result, IDbTransaction transaction = null) { if (this.IsSupportedOperation(operationName)) { this._sqlMessages.Clear(); bool createdTransaction = transaction == null; try { if (transaction == null) { transaction = this.DbAccess.BeginTransaction(); } if (transaction.Connection is SqlConnection) { var sqlConnection = transaction.Connection as SqlConnection; sqlConnection.FireInfoMessageEventOnUserErrors = true; sqlConnection.InfoMessage += new SqlInfoMessageEventHandler(this.sqlConnection_InfoMessage); var operationSuccessful = this.RunOperation(sqlConnection, transaction, dbfunctionType, operationName, parameters, out result); if (createdTransaction && operationSuccessful) { transaction.Commit(); } return operationSuccessful; } } finally { if (createdTransaction && transaction != null) { transaction.Dispose(); } } } result = null; return false; }
public void BeginTransaction(IsolationLevel isolationLevel) { if (IsOpen) { throw new InvalidOperationException("Transaction already open."); } //Open connection try { connection.Open(); transaction = this.connection.BeginTransaction(isolationLevel); transactionOpen = true; } catch (Exception) { // in the event of an error, close the connection and destroy the transaction object. if (connection != null) { connection.Close(); } if (transaction != null) { transaction.Dispose(); } transactionOpen = false; throw; } }
public void CloseConnection(IDbTransaction tran) { if (tran.Connection != null && tran.Connection.State != ConnectionState.Closed) { try { tran.Connection.Close(); tran.Connection.Dispose(); } finally { tran.Dispose(); } } }
public void EndUpdate(IDbTransaction trans) { trans.Commit(); trans.Dispose(); m_conn.Close(); m_conn = null; }
internal static async Task <int> SaveAllAsync <T>(this IDbCommand dbCmd, IEnumerable <T> objs, CancellationToken token) { var saveRows = objs.ToList(); var firstRow = saveRows.FirstOrDefault(); if (Equals(firstRow, default(T))) { return(0); } var modelDef = typeof(T).GetModelDefinition(); var firstRowId = modelDef.GetPrimaryKey(firstRow); var defaultIdValue = firstRowId != null?firstRowId.GetType().GetDefaultValue() : null; var idMap = defaultIdValue != null ? saveRows.Where(x => !defaultIdValue.Equals(modelDef.GetPrimaryKey(x))).ToSafeDictionary(x => modelDef.GetPrimaryKey(x)) : saveRows.Where(x => modelDef.GetPrimaryKey(x) != null).ToSafeDictionary(x => modelDef.GetPrimaryKey(x)); var existingRowsMap = (await dbCmd.SelectByIdsAsync <T>(idMap.Keys, token)).ToDictionary(x => modelDef.GetPrimaryKey(x)); var rowsAdded = 0; IDbTransaction dbTrans = null; if (dbCmd.Transaction == null) { dbCmd.Transaction = dbTrans = dbCmd.Connection.BeginTransaction(); } var dialectProvider = dbCmd.GetDialectProvider(); try { foreach (var row in saveRows) { var id = modelDef.GetPrimaryKey(row); if (id != defaultIdValue && existingRowsMap.ContainsKey(id)) { if (OrmLiteConfig.UpdateFilter != null) { OrmLiteConfig.UpdateFilter(dbCmd, row); } await dbCmd.UpdateAsync(row, token); } else { if (modelDef.HasAutoIncrementId) { var newId = await dbCmd.InsertAsync(row, selectIdentity : true, token : token); var safeId = dialectProvider.ConvertDbValue(newId, modelDef.PrimaryKey.FieldType); modelDef.PrimaryKey.SetValueFn(row, safeId); id = newId; } else { if (OrmLiteConfig.InsertFilter != null) { OrmLiteConfig.InsertFilter(dbCmd, row); } await dbCmd.InsertAsync(token, row); } rowsAdded++; } if (modelDef.RowVersion != null) { modelDef.RowVersion.SetValueFn(row, await dbCmd.GetRowVersionAsync(modelDef, id, token)); } } if (dbTrans != null) { dbTrans.Commit(); } } finally { if (dbTrans != null) { dbTrans.Dispose(); } } return(rowsAdded); }
private async Task InsertOrUpdate(SyncJobItem jobItem, IDbCommand cmd) { if (jobItem == null) { throw new ArgumentNullException("jobItem"); } CheckDisposed(); await WriteLock.WaitAsync().ConfigureAwait(false); IDbTransaction transaction = null; try { transaction = _connection.BeginTransaction(); var index = 0; cmd.GetParameter(index++).Value = new Guid(jobItem.Id); cmd.GetParameter(index++).Value = jobItem.ItemId; cmd.GetParameter(index++).Value = jobItem.ItemName; cmd.GetParameter(index++).Value = jobItem.MediaSourceId; cmd.GetParameter(index++).Value = jobItem.JobId; cmd.GetParameter(index++).Value = jobItem.TemporaryPath; cmd.GetParameter(index++).Value = jobItem.OutputPath; cmd.GetParameter(index++).Value = jobItem.Status.ToString(); cmd.GetParameter(index++).Value = jobItem.TargetId; cmd.GetParameter(index++).Value = jobItem.DateCreated; cmd.GetParameter(index++).Value = jobItem.Progress; cmd.GetParameter(index++).Value = _json.SerializeToString(jobItem.AdditionalFiles); cmd.GetParameter(index++).Value = jobItem.MediaSource == null ? null : _json.SerializeToString(jobItem.MediaSource); cmd.GetParameter(index++).Value = jobItem.IsMarkedForRemoval; cmd.GetParameter(index++).Value = jobItem.JobItemIndex; cmd.Transaction = transaction; cmd.ExecuteNonQuery(); transaction.Commit(); } catch (OperationCanceledException) { if (transaction != null) { transaction.Rollback(); } throw; } catch (Exception e) { Logger.ErrorException("Failed to save record:", e); if (transaction != null) { transaction.Rollback(); } throw; } finally { if (transaction != null) { transaction.Dispose(); } WriteLock.Release(); } }
/// <summary> /// Закрыть транзакцию /// </summary> /// <param name="transaction"></param> public static void СommitTransaction(IDbTransaction transaction) { if (transaction != null) { var v_storedConn = transaction.Connection; transaction.Commit(); transaction.Dispose(); if (v_storedConn != null) { v_storedConn.Close(); v_storedConn.Dispose(); } } }
/// <summary> /// Disposes transaction and closes sql connection /// </summary> public void Dispose() { sqlTransaction.Dispose(); sqlConnection.Close(); }
/// <summary> /// Откатить транзакцию /// </summary> /// <param name="trans"></param> public static void RollbackTransaction(IDbTransaction trans) { if (trans != null) { var v_storedConn = trans.Connection; try { trans.Rollback(); } catch (InvalidOperationException) { } trans.Dispose(); if (v_storedConn != null) { v_storedConn.Close(); v_storedConn.Dispose(); } } }
/// <summary> /// 事务回滚 /// </summary> public void Rollback() { _transaction.Rollback(); _transaction.Dispose(); }
/// <summary> /// Creates the queue and history records for the given schedule. /// </summary> /// <param name="scheduleId">The ID of the schedule records are being created for.</param> /// <param name="scheduleDate">The schedule date records are being created for.</param> /// <param name="queued">The queued records to create.</param> /// <param name="history">The history records to create.</param> /// <param name="transaction">The transaction to use, if applicable.</param> /// <returns>The number of records created.</returns> public int CreateQueuedAndHistoryForSchedule(long scheduleId, DateTime scheduleDate, IEnumerable<QueueRecord> queued, IEnumerable<HistoryRecord> history, IDbTransaction transaction) { int created = 0; bool commitRollback = false; if (transaction == null) { commitRollback = true; transaction = this.BeginTransaction(); } try { const string InsertQueuedSql = @"INSERT INTO [BlueCollarQueue]([ApplicationName],[ScheduleId],[QueueName],[JobName],[JobType],[Data],[QueuedOn],[TryNumber]) VALUES(@ApplicationName,@ScheduleId,@QueueName,@JobName,@JobType,@Data,@QueuedOn,@TryNumber);"; const string InsertHistorySql = @"INSERT INTO [BlueCollarHistory]([ApplicationName],[WorkerId],[ScheduleId],[QueueName],[JobName],[JobType],[Data],[QueuedOn],[TryNumber],[StartedOn],[Status],[Exception],[FinishedOn]) VALUES (@ApplicationName,@WorkerId,@ScheduleId,@QueueName,@JobName,@JobType,@Data,@QueuedOn,@TryNumber,@StartedOn,@StatusString,@Exception,@FinishedOn);"; if (queued != null && queued.Count() > 0) { created += this.connection.Execute(InsertQueuedSql, queued, transaction, null, null); } if (history != null && history.Count() > 0) { created += this.connection.Execute(InsertHistorySql, history, transaction, null, null); } if (commitRollback) { transaction.Commit(); } } catch { if (commitRollback) { transaction.Rollback(); } throw; } finally { if (commitRollback) { transaction.Dispose(); } } return created; }
/// <summary> /// Save a user in the repo /// </summary> /// <param name="user">The user.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns>Task.</returns> /// <exception cref="System.ArgumentNullException">user</exception> public async Task SaveUser(User user, CancellationToken cancellationToken) { if (user == null) { throw new ArgumentNullException("user"); } if (cancellationToken == null) { throw new ArgumentNullException("cancellationToken"); } cancellationToken.ThrowIfCancellationRequested(); var serialized = _jsonSerializer.SerializeToBytes(user); cancellationToken.ThrowIfCancellationRequested(); await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false); IDbTransaction transaction = null; try { transaction = _connection.BeginTransaction(); using (var cmd = _connection.CreateCommand()) { cmd.CommandText = "replace into users (guid, data) values (@1, @2)"; cmd.Parameters.Add(cmd, "@1", DbType.Guid).Value = user.Id; cmd.Parameters.Add(cmd, "@2", DbType.Binary).Value = serialized; cmd.Transaction = transaction; cmd.ExecuteNonQuery(); } transaction.Commit(); } catch (OperationCanceledException) { if (transaction != null) { transaction.Rollback(); } throw; } catch (Exception e) { _logger.ErrorException("Failed to save user:", e); if (transaction != null) { transaction.Rollback(); } throw; } finally { if (transaction != null) { transaction.Dispose(); } _writeLock.Release(); } }
/// <summary> /// 子类实现此方法释放指定的事务。 /// </summary> /// <returns></returns> protected virtual void DisposeTransaction(IDbTransaction tran) { tran.Dispose(); }
/// <summary> /// 事务资源回收 /// </summary> public void Dispose() { dbTransaction.Dispose(); dbConnection.Close(); dbConnection.Dispose(); }
public int Save(IdpeVersion version, IDal dal = null, IDbConnection connection = null, IDbTransaction transaction = null) { ValidateVersion(version); int versionId = 0; bool localTransaction = false; if (dal == null) { dal = new DataAccessLayer(EyediaCoreConfigurationSection.CurrentConfig.Database.DatabaseType).Instance; connection = dal.CreateConnection(_ConnectionString); connection.Open(); transaction = dal.CreateTransaction(connection); localTransaction = true; } IDbCommand command = dal.CreateCommand(); command.Connection = connection; command.Transaction = transaction; try { command.CommandText = "INSERT INTO [IdpeVersion]([Version], [Type], [ReferenceId], [Data], [CreatedBy], [CreatedTS], [Source]) "; command.CommandText += " VALUES (@Version, @Type, @ReferenceId, @Data, @CreatedBy, @CreatedTS, @Source)"; command.AddParameterWithValue("Version", version.Version); command.AddParameterWithValue("Type", version.Type); command.AddParameterWithValue("ReferenceId", version.ReferenceId); command.AddParameterWithValue("Data", version.Data.ToArray()); command.AddParameterWithValue("CreatedBy", Information.LoggedInUser != null ? Information.LoggedInUser.UserName : "******"); command.AddParameterWithValue("CreatedTS", version.CreatedTS); command.AddParameterWithValue("Source", GetSource(version.Source)); command.ExecuteNonQuery(); //Deb: I know dirty coding, need to be changed. OUTPUT INSERTED.Id not working @SQL CE command.Parameters.Clear(); command.CommandText = "SELECT max(Id) from [IdpeVersion]"; versionId = (Int32)command.ExecuteScalar(); if (localTransaction) { transaction.Commit(); } } catch (Exception ex) { if (localTransaction) { transaction.Rollback(); } Trace.TraceError(ex.ToString()); throw new Exception(ex.Message, ex); } finally { if (localTransaction) { if (connection.State == ConnectionState.Open) { connection.Close(); } connection.Dispose(); transaction.Dispose(); } } return(versionId); }
public async Task Update(ActivityLogEntry entry) { if (entry == null) { throw new ArgumentNullException("entry"); } using (var connection = await CreateConnection().ConfigureAwait(false)) { using (var saveActivityCommand = connection.CreateCommand()) { saveActivityCommand.CommandText = "replace into ActivityLogEntries (Id, Name, Overview, ShortOverview, Type, ItemId, UserId, DateCreated, LogSeverity) values (@Id, @Name, @Overview, @ShortOverview, @Type, @ItemId, @UserId, @DateCreated, @LogSeverity)"; saveActivityCommand.Parameters.Add(saveActivityCommand, "@Id"); saveActivityCommand.Parameters.Add(saveActivityCommand, "@Name"); saveActivityCommand.Parameters.Add(saveActivityCommand, "@Overview"); saveActivityCommand.Parameters.Add(saveActivityCommand, "@ShortOverview"); saveActivityCommand.Parameters.Add(saveActivityCommand, "@Type"); saveActivityCommand.Parameters.Add(saveActivityCommand, "@ItemId"); saveActivityCommand.Parameters.Add(saveActivityCommand, "@UserId"); saveActivityCommand.Parameters.Add(saveActivityCommand, "@DateCreated"); saveActivityCommand.Parameters.Add(saveActivityCommand, "@LogSeverity"); IDbTransaction transaction = null; try { transaction = connection.BeginTransaction(); var index = 0; saveActivityCommand.GetParameter(index++).Value = new Guid(entry.Id); saveActivityCommand.GetParameter(index++).Value = entry.Name; saveActivityCommand.GetParameter(index++).Value = entry.Overview; saveActivityCommand.GetParameter(index++).Value = entry.ShortOverview; saveActivityCommand.GetParameter(index++).Value = entry.Type; saveActivityCommand.GetParameter(index++).Value = entry.ItemId; saveActivityCommand.GetParameter(index++).Value = entry.UserId; saveActivityCommand.GetParameter(index++).Value = entry.Date; saveActivityCommand.GetParameter(index++).Value = entry.Severity.ToString(); saveActivityCommand.Transaction = transaction; saveActivityCommand.ExecuteNonQuery(); transaction.Commit(); } catch (OperationCanceledException) { if (transaction != null) { transaction.Rollback(); } throw; } catch (Exception e) { Logger.ErrorException("Failed to save record:", e); if (transaction != null) { transaction.Rollback(); } throw; } finally { if (transaction != null) { transaction.Dispose(); } } } } }
public void Commit() { _tran.Commit(); _tran.Dispose(); _tran = null; }
/// <summary> /// Replaces the notification. /// </summary> /// <param name="notification">The notification.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns>Task.</returns> private async Task ReplaceNotification(Notification notification, CancellationToken cancellationToken) { if (string.IsNullOrEmpty(notification.Id)) { notification.Id = Guid.NewGuid().ToString("N"); } if (string.IsNullOrEmpty(notification.UserId)) { throw new ArgumentException("The notification must have a user id"); } cancellationToken.ThrowIfCancellationRequested(); await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false); IDbTransaction transaction = null; try { transaction = _connection.BeginTransaction(); _replaceNotificationCommand.GetParameter(0).Value = new Guid(notification.Id); _replaceNotificationCommand.GetParameter(1).Value = new Guid(notification.UserId); _replaceNotificationCommand.GetParameter(2).Value = notification.Date.ToUniversalTime(); _replaceNotificationCommand.GetParameter(3).Value = notification.Name; _replaceNotificationCommand.GetParameter(4).Value = notification.Description; _replaceNotificationCommand.GetParameter(5).Value = notification.Url; _replaceNotificationCommand.GetParameter(6).Value = notification.Level.ToString(); _replaceNotificationCommand.GetParameter(7).Value = notification.IsRead; _replaceNotificationCommand.GetParameter(8).Value = string.Empty; _replaceNotificationCommand.GetParameter(9).Value = string.Empty; _replaceNotificationCommand.Transaction = transaction; _replaceNotificationCommand.ExecuteNonQuery(); transaction.Commit(); } catch (OperationCanceledException) { if (transaction != null) { transaction.Rollback(); } throw; } catch (Exception e) { _logger.ErrorException("Failed to save notification:", e); if (transaction != null) { transaction.Rollback(); } throw; } finally { if (transaction != null) { transaction.Dispose(); } _writeLock.Release(); } }
/// <summary> /// Rolbacks actual transaction and sets wrapped transaction for related connection to null.</summary> public void Dispose() { actualTransaction.Dispose(); DetachConnection(); }
public void AbortTransaction() { _transaction.Dispose(); }
/// <summary> /// Saves the items. /// </summary> /// <param name="items">The items.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns>Task.</returns> /// <exception cref="System.ArgumentNullException"> /// items /// or /// cancellationToken /// </exception> public async Task SaveItems(IEnumerable <BaseItem> items, CancellationToken cancellationToken) { if (items == null) { throw new ArgumentNullException("items"); } cancellationToken.ThrowIfCancellationRequested(); CheckDisposed(); await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false); IDbTransaction transaction = null; try { transaction = _connection.BeginTransaction(); foreach (var item in items) { cancellationToken.ThrowIfCancellationRequested(); _saveItemCommand.GetParameter(0).Value = item.Id; _saveItemCommand.GetParameter(1).Value = item.GetType().FullName; _saveItemCommand.GetParameter(2).Value = _jsonSerializer.SerializeToBytes(item); _saveItemCommand.Transaction = transaction; _saveItemCommand.ExecuteNonQuery(); } transaction.Commit(); } catch (OperationCanceledException) { if (transaction != null) { transaction.Rollback(); } throw; } catch (Exception e) { _logger.ErrorException("Failed to save items:", e); if (transaction != null) { transaction.Rollback(); } throw; } finally { if (transaction != null) { transaction.Dispose(); } _writeLock.Release(); } }
private async Task InsertOrUpdate(SyncJob job, IDbCommand cmd) { if (job == null) { throw new ArgumentNullException("job"); } CheckDisposed(); await WriteLock.WaitAsync().ConfigureAwait(false); IDbTransaction transaction = null; try { transaction = _connection.BeginTransaction(); var index = 0; cmd.GetParameter(index++).Value = new Guid(job.Id); cmd.GetParameter(index++).Value = job.TargetId; cmd.GetParameter(index++).Value = job.Name; cmd.GetParameter(index++).Value = job.Profile; cmd.GetParameter(index++).Value = job.Quality; cmd.GetParameter(index++).Value = job.Bitrate; cmd.GetParameter(index++).Value = job.Status.ToString(); cmd.GetParameter(index++).Value = job.Progress; cmd.GetParameter(index++).Value = job.UserId; cmd.GetParameter(index++).Value = string.Join(",", job.RequestedItemIds.ToArray()); cmd.GetParameter(index++).Value = job.Category; cmd.GetParameter(index++).Value = job.ParentId; cmd.GetParameter(index++).Value = job.UnwatchedOnly; cmd.GetParameter(index++).Value = job.ItemLimit; cmd.GetParameter(index++).Value = job.SyncNewContent; cmd.GetParameter(index++).Value = job.DateCreated; cmd.GetParameter(index++).Value = job.DateLastModified; cmd.GetParameter(index++).Value = job.ItemCount; cmd.Transaction = transaction; cmd.ExecuteNonQuery(); transaction.Commit(); } catch (OperationCanceledException) { if (transaction != null) { transaction.Rollback(); } throw; } catch (Exception e) { Logger.ErrorException("Failed to save record:", e); if (transaction != null) { transaction.Rollback(); } throw; } finally { if (transaction != null) { transaction.Dispose(); } WriteLock.Release(); } }
public async Task SaveChildren(Guid parentId, IEnumerable <Guid> children, CancellationToken cancellationToken) { if (parentId == Guid.Empty) { throw new ArgumentNullException("parentId"); } if (children == null) { throw new ArgumentNullException("children"); } CheckDisposed(); await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false); IDbTransaction transaction = null; try { transaction = _connection.BeginTransaction(); // First delete _deleteChildrenCommand.GetParameter(0).Value = parentId; _deleteChildrenCommand.Transaction = transaction; _deleteChildrenCommand.ExecuteNonQuery(); foreach (var id in children) { cancellationToken.ThrowIfCancellationRequested(); _saveChildrenCommand.GetParameter(0).Value = parentId; _saveChildrenCommand.GetParameter(1).Value = id; _saveChildrenCommand.Transaction = transaction; _saveChildrenCommand.ExecuteNonQuery(); } transaction.Commit(); } catch (OperationCanceledException) { if (transaction != null) { transaction.Rollback(); } throw; } catch (Exception e) { _logger.ErrorException("Failed to save children:", e); if (transaction != null) { transaction.Rollback(); } throw; } finally { if (transaction != null) { transaction.Dispose(); } _writeLock.Release(); } }
/// <summary> /// Insert multiple rows in the table in an asynchronous way. /// </summary> /// <typeparam name="TEntity">The type of the object (whether a data entity or a dynamic).</typeparam> /// <param name="connection">The connection object to be used.</param> /// <param name="tableName">The name of the target table to be used.</param> /// <param name="entities">The list of data entity or dynamic objects to be inserted.</param> /// <param name="batchSize">The batch size of the insertion.</param> /// <param name="fields">The mapping list of <see cref="Field"/> objects to be used.</param> /// <param name="hints">The table hints to be used.</param> /// <param name="commandTimeout">The command timeout in seconds to be used.</param> /// <param name="transaction">The transaction to be used.</param> /// <param name="trace">The trace object to be used.</param> /// <param name="statementBuilder">The statement builder object to be used.</param> /// <param name="skipIdentityCheck">True to skip the identity check.</param> /// <returns>The number of inserted rows in the table.</returns> internal static async Task <int> InsertAllAsyncInternalBase <TEntity>(this IDbConnection connection, string tableName, IEnumerable <TEntity> entities, int batchSize = Constant.DefaultBatchOperationSize, IEnumerable <Field> fields = null, string hints = null, int?commandTimeout = null, IDbTransaction transaction = null, ITrace trace = null, IStatementBuilder statementBuilder = null, bool skipIdentityCheck = false) where TEntity : class { // Variables needed var dbSetting = connection.GetDbSetting(); // Guard the parameters GuardInsertAll(entities); // Validate the batch size batchSize = (dbSetting.IsMultiStatementExecutable == true) ? Math.Min(batchSize, entities.Count()) : 1; // Get the context var context = InsertAllExecutionContextProvider.Create <TEntity>(connection, tableName, batchSize, fields, hints, transaction, statementBuilder, skipIdentityCheck); var sessionId = Guid.Empty; // Before Execution if (trace != null) { sessionId = Guid.NewGuid(); var cancellableTraceLog = new CancellableTraceLog(sessionId, context.CommandText, entities, null); trace.BeforeInsertAll(cancellableTraceLog); if (cancellableTraceLog.IsCancelled) { if (cancellableTraceLog.IsThrowException) { throw new CancelledExecutionException(context.CommandText); } return(0); } context.CommandText = (cancellableTraceLog.Statement ?? context.CommandText); entities = (IEnumerable <TEntity>)(cancellableTraceLog.Parameter ?? entities); } // Before Execution Time var beforeExecutionTime = DateTime.UtcNow; // Execution variables var result = 0; // Make sure to create transaction if there is no passed one var hasTransaction = (transaction != null || Transaction.Current != null); try { // Ensure the connection is open await connection.EnsureOpenAsync(); if (hasTransaction == false) { // Create a transaction transaction = connection.BeginTransaction(); } // Create the command using (var command = (DbCommand)connection.CreateCommand(context.CommandText, CommandType.Text, commandTimeout, transaction)) { // Directly execute if the entities is only 1 (performance) if (context.BatchSize == 1) { foreach (var entity in entities.AsList()) { // Set the values context.SingleDataEntityParametersSetterFunc?.Invoke(command, entity); // Prepare the command if (dbSetting.IsPreparable) { command.Prepare(); } // Actual Execution var returnValue = Converter.DbNullToNull(await command.ExecuteScalarAsync()); // Get explicity if needed if (Equals(returnValue, null) == true && dbSetting.IsMultiStatementExecutable == false) { returnValue = Converter.DbNullToNull(await connection.GetDbHelper().GetScopeIdentityAsync(connection, transaction)); } // Set the return value if (returnValue != null) { context.IdentityPropertySetterFunc?.Invoke(entity, returnValue); } // Iterate the result result++; } } else { foreach (var batchEntities in entities.AsList().Split(batchSize)) { var batchItems = batchEntities.AsList(); // Break if there is no more records if (batchItems.Count <= 0) { break; } // Check if the batch size has changed (probably the last batch on the enumerables) if (batchItems.Count != batchSize) { // Get a new execution context from cache context = await InsertAllExecutionContextProvider.CreateAsync <TEntity>(connection, tableName, batchItems.Count, fields, hints, transaction, statementBuilder, skipIdentityCheck); // Set the command properties command.CommandText = context.CommandText; } // Set the values if (batchItems?.Count == 1) { context.SingleDataEntityParametersSetterFunc?.Invoke(command, batchItems.First()); } else { context.MultipleDataEntitiesParametersSetterFunc?.Invoke(command, batchItems); } // Prepare the command if (dbSetting.IsPreparable) { command.Prepare(); } // Actual Execution if (context.IdentityPropertySetterFunc == null) { result += await command.ExecuteNonQueryAsync(); } else { using (var reader = await command.ExecuteReaderAsync()) { var index = 0; // Get the results do { if (await reader.ReadAsync()) { var value = Converter.DbNullToNull(reader.GetValue(0)); context.IdentityPropertySetterFunc.Invoke(batchItems[index], value); result++; } index++; }while (await reader.NextResultAsync()); } } } } } if (hasTransaction == false) { // Commit the transaction transaction.Commit(); } } catch { if (hasTransaction == false) { // Rollback for any exception transaction.Rollback(); } throw; } finally { if (hasTransaction == false) { // Rollback and dispose the transaction transaction.Dispose(); } } // After Execution if (trace != null) { trace.AfterInsertAll(new TraceLog(sessionId, context.CommandText, entities, result, DateTime.UtcNow.Subtract(beforeExecutionTime))); } // Return the result return(result); }
/// <inheritdoc /> public void Dispose() => m_transaction.Dispose();
/// <summary> /// Saves the chapters. /// </summary> /// <param name="id">The id.</param> /// <param name="chapters">The chapters.</param> /// <param name="cancellationToken">The cancellation token.</param> /// <returns>Task.</returns> /// <exception cref="System.ArgumentNullException"> /// id /// or /// chapters /// or /// cancellationToken /// </exception> public async Task SaveChapters(Guid id, IEnumerable <ChapterInfo> chapters, CancellationToken cancellationToken) { if (id == Guid.Empty) { throw new ArgumentNullException("id"); } if (chapters == null) { throw new ArgumentNullException("chapters"); } cancellationToken.ThrowIfCancellationRequested(); await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false); IDbTransaction transaction = null; try { transaction = _connection.BeginTransaction(); // First delete chapters _deleteChaptersCommand.GetParameter(0).Value = id; _deleteChaptersCommand.Transaction = transaction; _deleteChaptersCommand.ExecuteNonQuery(); var index = 0; foreach (var chapter in chapters) { cancellationToken.ThrowIfCancellationRequested(); _saveChapterCommand.GetParameter(0).Value = id; _saveChapterCommand.GetParameter(1).Value = index; _saveChapterCommand.GetParameter(2).Value = chapter.StartPositionTicks; _saveChapterCommand.GetParameter(3).Value = chapter.Name; _saveChapterCommand.GetParameter(4).Value = chapter.ImagePath; _saveChapterCommand.Transaction = transaction; _saveChapterCommand.ExecuteNonQuery(); index++; } transaction.Commit(); } catch (OperationCanceledException) { if (transaction != null) { transaction.Rollback(); } throw; } catch (Exception e) { _logger.ErrorException("Failed to save chapters:", e); if (transaction != null) { transaction.Rollback(); } throw; } finally { if (transaction != null) { transaction.Dispose(); } _writeLock.Release(); } }
public async Task SaveMetadataStatus(MetadataStatus status, CancellationToken cancellationToken) { if (status == null) { throw new ArgumentNullException("status"); } cancellationToken.ThrowIfCancellationRequested(); await _writeLock.WaitAsync(cancellationToken).ConfigureAwait(false); IDbTransaction transaction = null; try { transaction = _connection.BeginTransaction(); _saveStatusCommand.GetParameter(0).Value = status.ItemId; _saveStatusCommand.GetParameter(1).Value = status.ItemName; _saveStatusCommand.GetParameter(2).Value = status.ItemType; _saveStatusCommand.GetParameter(3).Value = status.SeriesName; _saveStatusCommand.GetParameter(4).Value = status.DateLastMetadataRefresh; _saveStatusCommand.GetParameter(5).Value = status.DateLastImagesRefresh; _saveStatusCommand.GetParameter(6).Value = status.LastStatus.ToString(); _saveStatusCommand.GetParameter(7).Value = status.LastErrorMessage; _saveStatusCommand.GetParameter(8).Value = string.Join("|", status.MetadataProvidersRefreshed.ToArray()); _saveStatusCommand.GetParameter(9).Value = string.Join("|", status.ImageProvidersRefreshed.ToArray()); _saveStatusCommand.GetParameter(10).Value = status.ItemDateModified; _saveStatusCommand.Transaction = transaction; _saveStatusCommand.ExecuteNonQuery(); transaction.Commit(); } catch (OperationCanceledException) { if (transaction != null) { transaction.Rollback(); } throw; } catch (Exception e) { _logger.ErrorException("Failed to save provider info:", e); if (transaction != null) { transaction.Rollback(); } throw; } finally { if (transaction != null) { transaction.Dispose(); } _writeLock.Release(); } }
public void StartupRunEnd() { IDbConnection conn = new SqlConnection(DBConfig.ConnectionString); conn.Open(); ////StarterA: ////{"UserID":"10","UserName":"******","AppName":"SamplePrice","AppInstanceID":"100","ProcessGUID":"072af8c3-482a-4b1c-890b-685ce2fcc75d"} var initiator = new WfAppRunner(); initiator.AppName = "SamplePrice"; initiator.AppInstanceID = 100; initiator.ProcessGUID = Guid.Parse("072af8c3-482a-4b1c-890b-685ce2fcc75d"); initiator.UserID = 10; initiator.UserName = "******"; IWorkflowService service = new WorkflowService(); //流程开始->业务员提交 IDbTransaction trans = conn.BeginTransaction(); try { service.StartProcess(conn, initiator, trans); trans.Commit(); } catch { trans.Rollback(); throw; } finally { trans.Dispose(); } //业务员提交->板房签字 var banFangNodeGuid = "fc8c71c5-8786-450e-af27-9f6a9de8560f"; PerformerList pList = new PerformerList(); pList.Add(new Performer(20, "Zhang")); initiator.NextActivityPerformers = new Dictionary <Guid, PerformerList>(); initiator.NextActivityPerformers.Add(Guid.Parse(banFangNodeGuid), pList); trans = conn.BeginTransaction(); try { service.RunProcessApp(conn, initiator, trans); trans.Commit(); } catch { trans.Rollback(); throw; } finally { trans.Dispose(); } //板房签字->业务员签字 //登录用户身份 initiator.UserID = 20; initiator.UserName = "******"; var salesGuid = "39c71004-d822-4c15-9ff2-94ca1068d745"; pList.Clear(); pList.Add(new Performer(10, "Long")); initiator.NextActivityPerformers.Clear(); initiator.NextActivityPerformers.Add(Guid.Parse(salesGuid), pList); trans = conn.BeginTransaction(); try { service.RunProcessApp(conn, initiator, trans); trans.Commit(); } catch { trans.Rollback(); throw; } finally { trans.Dispose(); } //业务员签字->结束 //登录用户身份 initiator.UserID = 10; initiator.UserName = "******"; var endGuid = "b70e717a-08da-419f-b2eb-7a3d71f054de"; pList.Clear(); pList.Add(new Performer(10, "Long")); initiator.NextActivityPerformers.Clear(); initiator.NextActivityPerformers.Add(Guid.Parse(endGuid), pList); trans = conn.BeginTransaction(); try { service.RunProcessApp(conn, initiator, trans); trans.Commit(); } catch { trans.Rollback(); throw; } finally { trans.Dispose(); } if (conn.State == ConnectionState.Open) { conn.Close(); } }
/// <summary> /// 将文本数据 导入到库 /// </summary> /// <param name="filePath"></param> /// <param name="transaction"></param> /// <returns></returns> private int ImportDataFromFile(string filePath, IDbTransaction transaction = null) { if (!File.Exists(filePath)) { throw new FileNotFoundException(string.Concat("the data file can not be found at ", filePath)); } using (MySqlConnection conn = new MySqlConnection(this.ConnectionString)) { if (conn.State != System.Data.ConnectionState.Open) { conn.Open(); } IDbTransaction dbTran = transaction; bool isInnerTran = false; //没有传递事务,且不再事务环境上下文中 if (null == dbTran && null == Transaction.Current) { dbTran = conn.BeginTransaction(); isInnerTran = true; } try { //step 1 load data to db MySqlBulkLoader bcp = new MySqlBulkLoader(conn); bcp.TableName = this.TableName; bcp.Timeout = this.TimeOut; bcp.FieldTerminator = ","; bcp.FieldQuotationCharacter = '"'; bcp.EscapeCharacter = '"'; bcp.LineTerminator = "\r\n"; bcp.FileName = filePath; bcp.NumberOfLinesToSkip = 0; int result = bcp.Load(); //批量插入模式的 自增id不连续 这是mysql 的设计 预留不确定的自增数 反正自增不连续不影响业务 #region 资料 /* * http://www.cnblogs.com/zhoujinyi/p/3433823.html * 0:通过表锁的方式进行,也就是所有类型的insert都用AUTO-inc locking。 * 1:默认值,对于simple insert 自增长值的产生使用互斥量对内存中的计数器进行累加操作,对于bulk insert 则还是使用表锁的方式进行。 * 2:对所有的insert-like 自增长值的产生使用互斥量机制完成,性能最高,并发插入可能导致自增值不连续,可能会导致Statement 的 Replication 出现不一致,使用该模式,需要用 Row Replication的模式 * * ALTER TABLE students AUTO_INCREMENT = 11; * * show variables like 'innodb_autoinc_lock_mode'; * * SELECT Auto_increment * FROM information_schema.`TABLES` * WHERE Table_Schema='demodb' * AND table_name = 'students' */ #endregion if (isInnerTran == true) { //内部事务,完毕后提交 dbTran.Commit(); } return(result); } catch (Exception ex) { if (null != dbTran) { dbTran.Rollback(); } throw ex; } finally { if (null != dbTran) { dbTran.Dispose(); } //异步删除文件: Task.Factory.StartNew(() => { try { File.Delete(filePath); } catch (Exception ex) { Logger.Error($"执行批量插入后,移除临时文件出粗!文件路径:{filePath},错误信息:{ex.ToString()}"); } }); } } }