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()}");
}
});
}
}
}
