public AdoNetTransaction(IDbConnection connection, IsolationLevel? isolation = null) { Assertion.NotNull(connection); if (connection.State == ConnectionState.Broken || connection.State == ConnectionState.Closed) { connection.Open(); } this.transaction = isolation != null ? connection.BeginTransaction(isolation.Value) : connection.BeginTransaction(); }
public CommitScope([CallerMemberName] string callerMemberName = "") { _callerMemberName = callerMemberName; _connection = new SqlConnection(Common.ConnectionString); _connection.Open(); _transaction = _connection.BeginTransaction(); }
public ManagedConnection(IDbConnection connection) { _transaction = connection.BeginTransaction(); _connection = connection; Database.ConnectionStorage.Push(_connection); Database.TransactionStorage.Push(_transaction); }
public UnitOfWork(string nameOrConnectionString) { var connectionFactory = new ConnectionFactory(nameOrConnectionString); _connection = connectionFactory.Create(); _connection.Open(); _transaction = _connection.BeginTransaction(); }
internal DataAccessContext(string databaseFilename) { // initialize a connection and transaction _databaseFilename = databaseFilename; _connection = CreateConnection(); _transaction = _connection.BeginTransaction(IsolationLevel.ReadCommitted); _context = new ConfigurationDataContext(_connection); //_context.Log = Console.Out; lock (_syncLock) { if (_staticConnection == null) { // This is done for performance reasons. It forces a connection to remain open while the // the app domain is running, so that the database is kept in memory. try { _staticConnection = CreateConnection(); } catch (Exception ex) { Platform.Log(LogLevel.Debug, ex, "Failed to initialize static connection to configuration database"); } } } }
public SqlDBTransaction(SqlDB db) : base(db.ConnectionString, db.SqlAnalyzer, db.StrictTables) { _conn = db._getConnection(); _conn.Open(); _transaction = _conn.BeginTransaction(); }
public bool Execute(string qry) { cn = new OleDbConnection(cnString.GetConnString()); cmd = new OleDbCommand(qry, (OleDbConnection)cn); try { cn.Open(); IDbTransaction tran = cn.BeginTransaction(); cmd.Transaction = tran; int affectedRows = cmd.ExecuteNonQuery(); Console.WriteLine(affectedRows); if (affectedRows > 0) { tran.Commit(); return true; } else { tran.Rollback(); } } catch (Exception e) { Console.WriteLine(e.Message); } finally { cn.Close(); } return false; }
public void Setup() { connection = Substitute.For<IDbConnection>(); transaction = Substitute.For<IDbTransaction>(); connection.BeginTransaction().Returns(transaction); strategy = new TransactionPerScriptStrategy(() => connection); }
/// <summary> /// Migrates the specified old file. /// </summary> /// <param name="oldFile">The old file.</param> /// <param name="newDatabase">The new database.</param> /// <param name="logger">The logger.</param> /// <param name="json">The json.</param> /// <returns>Task.</returns> public static async Task Migrate(string oldFile, IDbConnection newDatabase, ILogger logger, IJsonSerializer json) { var oldDb = await SqliteExtensions.ConnectToDb(oldFile).ConfigureAwait(false); using (oldDb) { IDbTransaction transaction = null; var data = GetAllUserData(oldDb, json).ToList(); try { transaction = newDatabase.BeginTransaction(); foreach (var userdata in data) { PersistUserData(userdata, newDatabase, transaction); } transaction.Commit(); } catch (OperationCanceledException) { if (transaction != null) { transaction.Rollback(); } throw; } catch (Exception e) { logger.ErrorException("Failed to save user data:", e); if (transaction != null) { transaction.Rollback(); } throw; } finally { if (transaction != null) { transaction.Dispose(); } } } var backupFile = Path.Combine(Path.GetDirectoryName(oldFile), "userdata_v1.db.bak"); if (File.Exists(backupFile)) { File.Delete(backupFile); } File.Move(oldFile, backupFile); }
private static void PostgreSqlDistributedLock_Init_Transaction(string resource, TimeSpan timeout, IDbConnection connection, PostgreSqlStorageOptions options) { var lockAcquiringTime = Stopwatch.StartNew(); bool tryAcquireLock = true; while (tryAcquireLock) { TryRemoveDeadlock(resource, connection, options); try { int rowsAffected = -1; using (var trx = connection.BeginTransaction(IsolationLevel.RepeatableRead)) { rowsAffected = connection.Execute($@" INSERT INTO ""{options.SchemaName}"".""lock""(""resource"", ""acquired"") SELECT @resource, @acquired WHERE NOT EXISTS ( SELECT 1 FROM ""{options.SchemaName}"".""lock"" WHERE ""resource"" = @resource ); ", new { resource = resource, acquired = DateTime.UtcNow }, trx); trx.Commit(); } if (rowsAffected > 0) return; } catch { } if (lockAcquiringTime.ElapsedMilliseconds > timeout.TotalMilliseconds) { tryAcquireLock = false; } else { int sleepDuration = (int)(timeout.TotalMilliseconds - lockAcquiringTime.ElapsedMilliseconds); if (sleepDuration > 1000) sleepDuration = 1000; if (sleepDuration > 0) { Thread.Sleep(sleepDuration); } else { tryAcquireLock = false; } } } throw new PostgreSqlDistributedLockException( $"Could not place a lock on the resource \'{resource}\': Lock timeout."); }
public void Setup() { connection = Substitute.For<IDbConnection>(); transaction = Substitute.For<IDbTransaction>(); connection.BeginTransaction().Returns(transaction); strategy = new TransactionPerScriptStrategy(); strategy.Initialise(connection, new ConsoleUpgradeLog(), new List<SqlScript>()); }
public DatabaseTransaction(IDbConnection connection) { if (_currentTransaction != null) return; _transaction = connection.BeginTransaction(); _currentTransaction = this; }
public DbStorage(string username) { this.db = DbConfig.GetConnection (); this.Username = username; // start everything as a transaction trans = db.BeginTransaction (); }
public void Setup() { connection = Substitute.For<IDbConnection>(); transaction = Substitute.For<IDbTransaction>(); connection.BeginTransaction().Returns(transaction); strategy = new SingleTrasactionStrategy(() => connection); strategy.Initialise(new ConsoleUpgradeLog()); }
public DbStorage(DBUser user) { this.db = DbConfig.GetConnection (); this.User = user; // start everything as a transaction trans = db.BeginTransaction (); }
/// <summary> /// </summary> /// <param name="connection"> /// A connection may not be shared between multiple transactions, so make sure that the connection /// is unique for this uow /// </param> /// <param name="ownsConnection">This unit of work owns the connection and will close it when being disposed.</param> /// <param name="isolationLevel">Isolation level that the transaction should use.</param> public AdoNetUnitOfWork(IDbConnection connection, bool ownsConnection, IsolationLevel isolationLevel) { if (connection == null) throw new ArgumentNullException("connection"); _connection = connection; _ownsConnection = ownsConnection; _transaction = _connection.BeginTransaction(isolationLevel); }
public BaseTransaction(IDbConnection dbc) { if (dbc.State != ConnectionState.Open) { dbc.Open(); } trans = dbc.BeginTransaction(); }
public PostgresProcessor(IDbConnection connection, IMigrationGenerator generator, IAnnouncer announcer, IMigrationProcessorOptions options, IDbFactory factory) : base(generator, announcer, options) { this.factory = factory; Connection = connection; connection.Open(); Transaction = connection.BeginTransaction(); }
public static List<Object[]> SelectArray( IDbConnection connection, string statement, IEnumerable<object> parameters ) { List<Object[]> retval = null; using ( IDbTransaction transaction = connection.BeginTransaction() ) { retval = SelectArray( transaction, statement, parameters ); transaction.Commit(); } return retval; }
public UnitOfWork(IDbConnection connection) { if (connection == null) throw new ArgumentNullException("connection"); _connection = connection; connection.EnsureOpen(); _transaction = connection.BeginTransaction(); }
internal DataAccessContext(string databaseFilename) { // initialize a connection and transaction _databaseFilename = databaseFilename; _connection = CreateConnection(); _transaction = _connection.BeginTransaction(IsolationLevel.ReadCommitted); _context = new ConfigurationDataContext(_connection); //_context.Log = Console.Out; }
public IDbTransaction BeginTransaction(IDbConnection connection) { ILoggingService loggingService = engine.GetService<ILoggingService>(); if (loggingService != null) loggingService.LogInfo(this, "Beginning transaction."); this.transaction = connection.BeginTransaction(); return transaction; }
private IDbConnection CreateConnection() { logger.Trace("Creating a new connection"); _connection = new SqlConnection(_connectionStringSettings.ConnectionString); _connection.Open(); _transaction = _connection.BeginTransaction(); _transactionRolledBack = false; return _connection; }
/// <summary> /// Begins a transaction conditionally based on the UseTransaction property /// </summary> /// <param name="connection">The IDbConnection object you are working with</param> /// <returns>An open IDbTransaction object or null.</returns> protected IDbTransaction BeginTransaction(IDbConnection connection) { if (UseTransaction) { return connection.BeginTransaction(); } return null; }
internal FileStore(FileInfo storeFile) { StoreFile = storeFile; connection = Sqlite.GetDatabaseConnection(storeFile); using (var transaction = connection.BeginTransaction()) { using (var pragma = connection.CreateCommand()) { pragma.CommandText = "PRAGMA journal_mode = MEMORY"; pragma.ExecuteNonQuery(); pragma.CommandText = "PRAGMA temp_store = MEMORY"; pragma.ExecuteNonQuery(); pragma.CommandText = "PRAGMA synchonous = OFF"; pragma.ExecuteNonQuery(); } using (var create = connection.CreateCommand()) { create.CommandText = "CREATE TABLE IF NOT EXISTS store (key TEXT PRIMARY KEY ON CONFLICT REPLACE, size INT, time INT, data BINARY, cover BINARY)"; create.ExecuteNonQuery(); } transaction.Commit(); } select = connection.CreateCommand(); select.CommandText = "SELECT data FROM store WHERE key = ? AND size = ? AND time = ?"; select.Parameters.Add(selectKey = select.CreateParameter()); selectKey.DbType = DbType.String; select.Parameters.Add(selectSize = select.CreateParameter()); selectSize.DbType = DbType.Int64; select.Parameters.Add(selectTime = select.CreateParameter()); selectTime.DbType = DbType.Int64; selectCover = connection.CreateCommand(); selectCover.CommandText = "SELECT cover FROM store WHERE key = ? AND size = ? AND time = ?"; selectCover.Parameters.Add(selectCoverKey = select.CreateParameter()); selectCoverKey.DbType = DbType.String; selectCover.Parameters.Add(selectCoverSize = select.CreateParameter()); selectCoverSize.DbType = DbType.Int64; selectCover.Parameters.Add(selectCoverTime = select.CreateParameter()); selectCoverTime.DbType = DbType.Int64; insert = connection.CreateCommand(); insert.CommandText = "INSERT OR REPLACE INTO store VALUES(?,?,?,?,?)"; insert.Parameters.Add(insertKey = select.CreateParameter()); insertKey.DbType = DbType.String; insert.Parameters.Add(insertSize = select.CreateParameter()); insertSize.DbType = DbType.Int64; insert.Parameters.Add(insertTime = select.CreateParameter()); insertTime.DbType = DbType.Int64; insert.Parameters.Add(insertData = select.CreateParameter()); insertData.DbType = DbType.Binary; insert.Parameters.Add(insertCover = select.CreateParameter()); insertCover.DbType = DbType.Binary; InfoFormat("FileStore at {0} is ready", storeFile.FullName); vacuumer.Add(connection); }
/// <summary> /// </summary> /// <param name="connection"> /// A connection may not be shared between multiple transactions, so make sure that the connection /// is unique for this uow /// </param> public AdoNetUnitOfWork(IDbConnection connection) { if (connection == null) throw new ArgumentNullException("connection"); if (connection.State != ConnectionState.Open) throw new DataException("Connection '" + connection.ConnectionString + "' is not open."); _connection = connection; _ownsConnection = false; _transaction = _connection.BeginTransaction(); }
public IDbTransaction BeginTransaction(IDbConnection connection, IsolationLevel isolationLevel) { _connection = connection; OpenConnection(); if (_transaction == null) { _transaction = _connection.BeginTransaction(isolationLevel); _isTransactionActive = true; } return _transaction; }
protected SqlTransactionalCommandsContext(SqlDatabaseContext sqlDatabaseContext, IDbConnection dbConnection, Transaction transaction) { this.DbConnection = dbConnection; this.Transaction = transaction; this.SqlDatabaseContext = sqlDatabaseContext; this.DataAccessModel = sqlDatabaseContext.DataAccessModel; if (transaction != null) { this.dbTransaction = dbConnection.BeginTransaction(ConvertIsolationLevel(transaction.IsolationLevel)); } }
public IDbTransaction GetTransaction(IDbConnection con) { if(!_isInTransaction) return null; IDbTransaction tran = _dbTransactionsByDbConnection[con] as IDbTransaction; if(tran == null) { tran = con.BeginTransaction(_isolationLevel); _dbTransactionsByDbConnection.Add(con, tran); } return tran; }
public void Transpor() { conexão = ObterConexãoAberta(); transação = conexão.BeginTransaction(); Dictionary<string, bool> referências = ObterHashExisteReferência(); ApagaFornecedores(); CadastrarFornecedores(ObtemLegadoFornecedoresParaCadastrar()); CadastrarVínculos(); SobrescreveInicio(referências); transação.Commit(); conexão.Close(); }
public async Task <DataResult <int> > HealthStaffImportAsync(QueryData <HealthStaffImportQuery> query) { var result = new DataResult <int>(); string sqlsi = @"insert into [dbo].[health_staff]([StaffNo],[StaffName],[GroupType],[GroupLeader],[GroupLeaderNo],[AggLeader],[AggLeaderNo],[CommandLeader],[CommondLeaderNo],[HrLeader],[HrLeaderNo]) values(@StaffNo,@StaffName,@GroupType,@GroupLeader,@GroupLeaderNo,@AggLeader,@AggLeaderNo,@CommandLeader,@CommondLeaderNo,@HrLeader,@HrLeaderNo)"; string sqlui = @"insert into dbo.health_user_staff([UserNo],[StaffNo],[Creator],[CreateName],[CreateTime]) values(@UserNo,@StaffNo,@Creator,@CreateName,getdate())"; string sqlss = @"select * from [dbo].[health_staff] where [StaffNo]=@StaffNo"; using (IDbConnection dbConn = MssqlHelper.OpenMsSqlConnection(MssqlHelper.GetConn)) { IDbTransaction transaction = dbConn.BeginTransaction(); try { foreach (var item in query.Criteria.LstStaff)//保存员工信息 { result.Data = await MssqlHelper.QueryCountAsync(dbConn, sqlss, new { StaffNo = item.StaffNo }, transaction); if (result.Data > 0) { transaction.Rollback(); result.SetErr(string.Format("员工工号{0}已存在,请重新检查!", item.StaffNo), -101); return(result); } result.Data = await MssqlHelper.ExecuteSqlAsync(dbConn, sqlsi, item, transaction); if (result.Data <= 0) { transaction.Rollback(); result.SetErr(string.Format("员工工号{0}保存失败!", item.StaffNo), -101); return(result); } } #region 添加组长权限 foreach (var item in query.Criteria.LstGroupStaff) { result.Data = await MssqlHelper.ExecuteSqlAsync(dbConn, sqlui, item, transaction); if (result.Data <= 0) { transaction.Rollback(); result.SetErr(string.Format("组长权限{0}|{1}保存失败!", item.UserNo, item.StaffNo), -101); return(result); } } #endregion #region 添加集合组组长权限 foreach (var item in query.Criteria.LstAggStaff) { result.Data = await MssqlHelper.ExecuteSqlAsync(dbConn, sqlui, item, transaction); if (result.Data <= 0) { transaction.Rollback(); result.SetErr(string.Format("集合组长权限{0}|{1}保存失败!", item.UserNo, item.StaffNo), -101); return(result); } } #endregion #region 添加HR负责人权限 foreach (var item in query.Criteria.LstHrStaff) { result.Data = await MssqlHelper.ExecuteSqlAsync(dbConn, sqlui, item, transaction); if (result.Data <= 0) { transaction.Rollback(); result.SetErr(string.Format("HR负责人权限{0}|{1}保存失败!", item.UserNo, item.StaffNo), -101); return(result); } } #endregion transaction.Commit(); } catch (Exception ex) { result.SetErr(ex, -500); result.Data = -1; } } return(result); }
/// <summary> /// Initializes a new instance of the <see cref="UnitOfWork"/> class. /// </summary> /// <param name="connection">The connection.</param> /// <exception cref="ArgumentNullException">connection</exception> public UnitOfWork(IDbConnection connection) { _connection = connection ?? throw new ArgumentNullException("connection"); connection.EnsureOpen(); _transaction = connection.BeginTransaction(); }
public BllResult SyncEquipmentProp(int?id, string plcDb) { using (IDbConnection connection = AppSession.Dal.GetConnection()) { IDbTransaction tran = null; try { connection.Open(); tran = connection.BeginTransaction(); //现获取这个设备,防止同步删除 var a = AppSession.Dal.GetCommonModelByCondition <Equipment>($"where id = {id}"); if (!a.Success) { tran.Rollback(); return(BllResultFactory.Error("同步失败,设备已被同步删除")); } var equipment = a.Data[0]; //查询对应模板的属性 var b = AppSession.Dal.GetCommonModelByCondition <EquipmentTypeTemplate>($"where equipmentTypeId = {equipment.EquipmentTypeId}"); if (!b.Success) { tran.Rollback(); return(BllResultFactory.Error("同步失败,设备无模板属性")); } var templateProps = b.Data; var c = AppSession.Dal.GetCommonModelByCondition <EquipmentProp>($"where equipmentId = {equipment.Id}"); var props = new List <EquipmentProp>(); if (c.Success) { props = c.Data; } var idsFordelete = props.Where(t => templateProps.Count(i => i.Id == t.EquipmentTypeTemplateId) == 0).Select(t => t.Id).ToList(); if (idsFordelete != null && idsFordelete.Count > 0) { connection.DeleteList <EquipmentProp>("where id in @ids", new { ids = idsFordelete }, tran); } var propForAdd = templateProps.Where(t => props.Count(i => i.EquipmentTypeTemplateId == t.Id) == 0).ToList(); List <EquipmentProp> propListToAdd = new List <EquipmentProp>(); foreach (var item in propForAdd) { EquipmentProp equipmentProp = new EquipmentProp(); equipmentProp.EquipmentId = equipment.Id; equipmentProp.EquipmentTypeTemplateId = item.Id; equipmentProp.EquipmentTypeTemplateCode = item.Code; equipmentProp.ServerHandle = 0; if (plcDb == "" || plcDb == null || (item.Address != null && item.Address != "")) { equipmentProp.Address = item.Address; } else { switch (item.DataType) { case "BOOL": { equipmentProp.Address = plcDb + "X" + item.Offset; break; } case "BYTE": { equipmentProp.Address = plcDb + "B" + item.Offset; break; } case "INT": { equipmentProp.Address = plcDb + "W" + item.Offset; break; } case "DINT": { equipmentProp.Address = plcDb + "D" + item.Offset; break; } case "CHAR": { equipmentProp.Address = plcDb + "CHAR" + item.Offset + ",20"; break; } default: { equipmentProp.Address = item.Address; break; } } } equipmentProp.Value = ""; equipmentProp.Remark = item.Name; equipmentProp.CreateTime = DateTime.Now; equipmentProp.CreateBy = ""; propListToAdd.Add(equipmentProp); } propListToAdd.ForEach(t => connection.Insert <EquipmentProp>(t, tran)); tran.Commit(); return(BllResultFactory.Sucess("成功")); } catch (Exception ex) { tran?.Rollback(); AppSession.LogService.WriteDBExceptionLog(ex); return(BllResultFactory.Error($"删除失败:{ex.Message}")); } } }
public bool AddPrjEmpEstPrjMap(int comp_id , string est_id , int estterm_ref_id , int estterm_sub_id , int estterm_step_id , int est_dept_id , int est_emp_id , int prj_ref_id , string status_id , DateTime create_date , int create_user) { int affectedRow = 0; Dac_QuestionEstMaps questionEstMap = new Dac_QuestionEstMaps(); IDbConnection conn = DbAgentHelper.CreateDbConnection(); conn.Open(); IDbTransaction trx = conn.BeginTransaction(); try { // 존재하는 데이터가 있다면 삭제 후 추가 if (IsExist(comp_id , est_id , estterm_ref_id , estterm_sub_id , estterm_step_id , 0 , 0 , prj_ref_id)) { affectedRow += _prjEstPrjMap.Delete(conn , trx , comp_id , est_id , estterm_ref_id , estterm_sub_id , estterm_step_id , 0 , 0 , prj_ref_id); } affectedRow += _prjEstPrjMap.Insert(conn , trx , comp_id , est_id , estterm_ref_id , estterm_sub_id , estterm_step_id , est_dept_id , est_emp_id , prj_ref_id , status_id , create_date , create_user); trx.Commit(); } catch (Exception ex) { trx.Rollback(); return(false); } finally { conn.Close(); } return((affectedRow > 0) ? true : false); }
public void PostgreSqlDistributedLock_Init_Transaction(string resource, TimeSpan timeout, IDbConnection connection, PostgreSqlStorageOptions options) { Stopwatch lockAcquiringTime = new Stopwatch(); lockAcquiringTime.Start(); bool tryAcquireLock = true; while (tryAcquireLock) { try { int rowsAffected = -1; using (var trx = _connection.BeginTransaction(IsolationLevel.RepeatableRead)) { rowsAffected = _connection.Execute(@" INSERT INTO """ + _options.SchemaName + @""".""lock""(""resource"") SELECT @resource WHERE NOT EXISTS ( SELECT 1 FROM """ + _options.SchemaName + @""".""lock"" WHERE ""resource"" = @resource ); ", new { resource = resource }, trx); trx.Commit(); } if (rowsAffected > 0) { return; } } catch (Exception) { } if (lockAcquiringTime.ElapsedMilliseconds > timeout.TotalMilliseconds) { tryAcquireLock = false; } else { int sleepDuration = (int)(timeout.TotalMilliseconds - lockAcquiringTime.ElapsedMilliseconds); if (sleepDuration > 1000) { sleepDuration = 1000; } if (sleepDuration > 0) { Thread.Sleep(sleepDuration); } else { tryAcquireLock = false; } } } throw new PostgreSqlDistributedLockException( String.Format( "Could not place a lock on the resource '{0}': {1}.", _resource, "Lock timeout")); }
public IDbTransaction BeginTransaction() { return(new SimpleProfiledTransaction(_connection.BeginTransaction(), this)); }
/// <summary> /// Default constructor for a dapper unit of work /// </summary> public DapperUnitOfWork(IDbConnection connection) { _connection = connection; _connection.Open(); _transaction = _connection.BeginTransaction(); }
public bool CopyKpiToMbo(int estterm_ref_id , object[] objList , int emp_ref_id , string estYear , string class_type) { Dac_Bsc_Mbo_Kpi_Classification dacBscMboKpiClassification = new Dac_Bsc_Mbo_Kpi_Classification(); Dac_Bsc_Kpi_Target_Version dacBscKpiTargetVersion = new Dac_Bsc_Kpi_Target_Version(); Dac_Bsc_Kpi_Result dacBscKpiResult = new Dac_Bsc_Kpi_Result(); Dac_Com_Emp_Role_Rel dacEmpRoleRel = new Dac_Com_Emp_Role_Rel(); Dac_Bsc_Kpi_Term dacBscKpiTerm = new Dac_Bsc_Kpi_Term(); Dac_Bsc_Kpi_Target dacBscKpiTarget = new Dac_Bsc_Kpi_Target(); Dac_Bsc_Kpi_Threshold_Info dacBscKpiThresholdInfo = new Dac_Bsc_Kpi_Threshold_Info(); Dac_Bsc_Kpi_Initiative dacBscKpiInitiative = new Dac_Bsc_Kpi_Initiative(); int rtn_kpi_ref_id = 0; int affectedRow; bool Result; IDbConnection conn = DbAgentHelper.CreateDbConnection(); conn.Open(); IDbTransaction trx = conn.BeginTransaction(); try { for (int i = 0; i < objList.Length; i++) { DataTable dt = dacBscMboKpiClassification.Select_DB(conn, trx, estterm_ref_id, emp_ref_id, 0, objList[i], ""); if (dt.Rows.Count > 0 && DataTypeUtility.GetToInt32(dt.Rows[0]["ESTTERM_REF_ID"]) > 0) { return(false); } } for (int i = 0; i < objList.Length; i++) { #region 기존 코드에서 strQuery1 //KPI INFO COPY _data.Insert_DB_From_Bsc_Kpi_Info(conn, trx , estterm_ref_id , 0 , "MicroPolis_0" , emp_ref_id , DataTypeUtility.GetToInt32(objList[i])); //MAX KPI_REF_ID //rtn_kpi_ref_id = _data.Select_Max_Kpi_Ref_Id(conn, trx); MicroBSC.BSC.Dac.Dac_Bsc_Kpi_Info dac = new MicroBSC.BSC.Dac.Dac_Bsc_Kpi_Info(); rtn_kpi_ref_id = dac.SelectMaxBscKpiInfo_DB(conn, trx); //KPI TARGET VERSION INSERT dacBscKpiTargetVersion.Delete_DB(conn, trx, estterm_ref_id, rtn_kpi_ref_id); dacBscKpiTargetVersion.Insert_DB(conn, trx , estterm_ref_id , rtn_kpi_ref_id , 1 , "당초계획" , "당초계획(자동생성)" , 1 , 12 , "Y" , emp_ref_id); //KPI RESULT INSERT dacBscKpiResult.DeleteData_DB(conn, trx, estterm_ref_id, rtn_kpi_ref_id); dacBscKpiResult.Insert_DB_From_Estterm_Detail(conn, trx , estterm_ref_id , rtn_kpi_ref_id , emp_ref_id); _data.Update_Bsc_Kpi_Info(conn, trx , 0 , estterm_ref_id , rtn_kpi_ref_id); //Champion Role Insert dacEmpRoleRel.Delete_DB(conn, trx, emp_ref_id, 3); dacEmpRoleRel.Insert_DB(conn, trx, emp_ref_id, 3); //지표구분테이블에 일상업무정보 추가 BSC_MBO_KPI_CLASSIFICATION dacBscMboKpiClassification.Insert_DB(conn, trx , estterm_ref_id , emp_ref_id , rtn_kpi_ref_id , DataTypeUtility.GetToInt32(objList[i]) , class_type , emp_ref_id); #endregion if (rtn_kpi_ref_id > 0) { #region 기존코드에서 strQuery2 affectedRow = dacBscKpiTerm.Insert_DB_From_Bsc_Kpi_Term(conn, trx , estterm_ref_id , rtn_kpi_ref_id , DataTypeUtility.GetToInt32(objList[i]) , emp_ref_id); if (affectedRow != 12) { return(false); } #endregion if (class_type.ToString().Equals("PRS")) { //기존코드에서 strQuery6 dacBscKpiTarget.InsertData_DB_From_BSC_KPI_TARGET(conn, trx , estterm_ref_id , DataTypeUtility.GetToInt32(objList[i]) , rtn_kpi_ref_id , emp_ref_id); } else { for (int j = 0; j < 13; j++) { Result = dacBscKpiTarget.Proc_Update(conn, trx , "A" , estterm_ref_id , rtn_kpi_ref_id , 1 , estYear.ToString() + j.ToString().PadLeft(2, '0') , 0 , 0 , emp_ref_id); if (!Result) { return(false); } } } //////////////////////////////////////////////////// // KPI THRESHOLD //////////////////////////////////////////////////// //기존코드에서 strQuery3 dacBscKpiThresholdInfo.Delete_DB(conn, trx , estterm_ref_id , rtn_kpi_ref_id); //기존코드에서 strQuery4 dacBscKpiThresholdInfo.Insert_DB_From_Bsc_Kpi_Threshold_Info(conn, trx , class_type , estterm_ref_id , rtn_kpi_ref_id , DataTypeUtility.GetToInt32(objList[i]) , emp_ref_id); #region 기존코드에서 strQuery5 if (class_type.ToString().Equals("PRS")) { dacBscKpiInitiative.InsertData_DB_From_Bsc_Kpi_Initiative(conn, trx , estterm_ref_id , DataTypeUtility.GetToInt32(objList[i]) , rtn_kpi_ref_id , emp_ref_id); } else { affectedRow = 0; for (int j = 1; j < 13; j++) { dacBscKpiInitiative.InsertData_DB(conn, trx , estterm_ref_id , rtn_kpi_ref_id , estYear.ToString() + j.ToString().PadLeft(2, '0') , DBNull.Value , DBNull.Value , DBNull.Value , DBNull.Value , emp_ref_id); } } #endregion } else { return(false); } } trx.Commit(); } catch (Exception ex) { trx.Rollback(); rtn_kpi_ref_id = 0; } finally { conn.Close(); } return(rtn_kpi_ref_id > 0 ? true : false); }
public bool AddPDTAndAHPStgEstDeptDatas(int ver_id , int estterm_ref_id , DataTable stgData , DataTable estDeptData , int est_dept_ref_id , int create_update_user) { IDbConnection conn = DbAgentHelper.CreateDbConnection(); conn.Open(); IDbTransaction trx = conn.BeginTransaction(); try { for (int i = 0; i < stgData.Rows.Count; i++) { Biz_PDTAndAHPStgInfos pdtAndAhpStgInfo = new Biz_PDTAndAHPStgInfos(); pdtAndAhpStgInfo.AddPDTAndAHPStgInfo_Dac(conn , trx , ver_id , estterm_ref_id , Convert.ToInt32(stgData.Rows[i]["STG_REF_ID"]) , Convert.ToInt32(stgData.Rows[i]["UP_STG_ID"]) , stgData.Rows[i]["STG_MAP_YN"].ToString() , stgData.Rows[i]["F_YN"].ToString() , stgData.Rows[i]["C_YN"].ToString() , stgData.Rows[i]["P_YN"].ToString() , stgData.Rows[i]["L_YN"].ToString() , "" , DateTime.Now , create_update_user); } Biz_AHPEstDeptStgDatas ahpEstDeptStgDatas = new Biz_AHPEstDeptStgDatas(); ahpEstDeptStgDatas.RemoveAHPEstDeptStgData(conn , trx , ver_id , estterm_ref_id , est_dept_ref_id); RemovePDTAndAHPStgEstDeptData(conn , trx , ver_id , estterm_ref_id , est_dept_ref_id , 0); for (int i = 0; i < estDeptData.Rows.Count; i++) { if (est_dept_ref_id == Convert.ToInt32(estDeptData.Rows[i]["EST_DEPT_REF_ID"])) { AddPDTAndAHPStgEstDeptData_Dac(conn , trx , ver_id , estterm_ref_id , Convert.ToInt32(estDeptData.Rows[i]["EST_DEPT_REF_ID"]) , Convert.ToInt32(estDeptData.Rows[i]["STG_REF_ID"]) , estDeptData.Rows[i]["CHECK_YN"].ToString() , "" , DateTime.Now , create_update_user); } } trx.Commit(); } catch (Exception e) { trx.Rollback(); return(false); } finally { conn.Close(); } return(true); }
protected void Page_Command(object sender, CommandEventArgs e) { try { if (e.CommandName == "Search") { // 10/13/2005 Paul. Make sure to clear the page index prior to applying search. grdMain.CurrentPageIndex = 0; grdMain.ApplySort(); grdMain.DataBind(); } // 12/14/2007 Paul. We need to capture the sort event from the SearchView. else if (e.CommandName == "SortGrid") { grdMain.SetSortFields(e.CommandArgument as string[]); } else if (e.CommandName == "MassUpdate") { string[] arrID = Request.Form.GetValues("chkMain"); if (arrID != null) { // 10/26/2007 Paul. Use a stack to run the update in blocks of under 200 IDs. //string sIDs = Utils.ValidateIDs(arrID); System.Collections.Stack stk = Utils.FilterByACL_Stack(m_sMODULE, "edit", arrID, "DOCUMENTS"); if (stk.Count > 0) { DbProviderFactory dbf = DbProviderFactories.GetFactory(); using (IDbConnection con = dbf.CreateConnection()) { con.Open(); using (IDbTransaction trn = con.BeginTransaction()) { try { while (stk.Count > 0) { string sIDs = Utils.BuildMassIDs(stk); // 07/09/2006 Paul. The date conversion was moved out of the MassUpdate control. // 09/11/2007 Paul. Mass update of teams is now available. SqlProcs.spDOCUMENTS_MassUpdate(sIDs, T10n.ToServerTime(ctlMassUpdate.ACTIVE_DATE), T10n.ToServerTime(ctlMassUpdate.EXP_DATE), ctlMassUpdate.CATEGORY_ID, ctlMassUpdate.SUBCATEGORY_ID, ctlMassUpdate.STATUS, ctlMassUpdate.TEAM_ID, trn); } trn.Commit(); } catch (Exception ex) { trn.Rollback(); throw(new Exception(ex.Message, ex.InnerException)); } } } Response.Redirect("default.aspx"); } } } else if (e.CommandName == "MassDelete") { string[] arrID = Request.Form.GetValues("chkMain"); if (arrID != null) { // 10/26/2007 Paul. Use a stack to run the update in blocks of under 200 IDs. //string sIDs = Utils.ValidateIDs(arrID); System.Collections.Stack stk = Utils.FilterByACL_Stack(m_sMODULE, "delete", arrID, "DOCUMENTS"); if (stk.Count > 0) { DbProviderFactory dbf = DbProviderFactories.GetFactory(); using (IDbConnection con = dbf.CreateConnection()) { con.Open(); using (IDbTransaction trn = con.BeginTransaction()) { try { while (stk.Count > 0) { string sIDs = Utils.BuildMassIDs(stk); SqlProcs.spDOCUMENTS_MassDelete(sIDs, trn); } trn.Commit(); } catch (Exception ex) { trn.Rollback(); throw(new Exception(ex.Message, ex.InnerException)); } } } Response.Redirect("default.aspx"); } } } else if (e.CommandName == "Export") { // 11/03/2006 Paul. Apply ACL rules to Export. int nACLACCESS = SplendidCRM.Security.GetUserAccess(m_sMODULE, "export"); if (nACLACCESS >= 0) { if (nACLACCESS == ACL_ACCESS.OWNER) { vwMain.RowFilter = "ASSIGNED_USER_ID = '" + Security.USER_ID.ToString() + "'"; } string[] arrID = Request.Form.GetValues("chkMain"); SplendidExport.Export(vwMain, m_sMODULE, ctlExportHeader.ExportFormat, ctlExportHeader.ExportRange, grdMain.CurrentPageIndex, grdMain.PageSize, arrID); } } } catch (Exception ex) { SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex); lblError.Text = ex.Message; } }
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; } } }
public IDbTransaction BeginTrans(IsolationLevel isolation = IsolationLevel.ReadCommitted) { _transaction = _connection.BeginTransaction(isolation); return(_transaction); }
public virtual IDbTransaction BeginTransaction() { return(InternalConnection.BeginTransaction()); }
public BllResult CopyEquipment(EquipmentType equipmentType) { using (IDbConnection connection = AppSession.Dal.GetConnection()) { IDbTransaction transaction = null; try { connection.Open(); transaction = connection.BeginTransaction(); var temp = AppSession.Dal.GetCommonModelByCondition <EquipmentType>($"where id = {equipmentType.Id}", connection, transaction); if (temp.Success) { var e = temp.Data[0]; var temp2 = AppSession.Dal.GetCommonModelByCondition <EquipmentTypeTemplate>($"where equipmentTypeId = {e.Id}", connection, transaction); if (temp2.Success) { var props = temp2.Data; //e.Id = null; e.Code = e.Code + "2"; var temp3 = AppSession.Dal.InsertCommonModel <EquipmentType>(e, connection, transaction); if (temp3.Success) { props.ForEach(t => { t.EquipmentTypeId = temp3.Data.Value; }); foreach (var item in props) { var temp4 = AppSession.Dal.InsertCommonModel <EquipmentTypeTemplate>(item, connection, transaction); if (!temp4.Success) { transaction?.Rollback(); return(BllResultFactory.Error($"复制出错:{temp4.Msg}")); } } transaction.Commit(); return(BllResultFactory.Sucess()); } else { transaction?.Rollback(); return(BllResultFactory.Error($"复制出错:{temp3.Msg}")); } } else { transaction?.Rollback(); return(BllResultFactory.Error($"复制出错:{temp2.Msg}")); } } else { transaction?.Rollback(); return(BllResultFactory.Error($"复制出错:{temp.Msg}")); } } catch (Exception ex) { transaction?.Rollback(); return(BllResultFactory.Error($"复制出错:{ex.Message}")); } } }
protected void Page_Command(Object sender, CommandEventArgs e) { // 08/21/2005 Paul. Redirect to parent if that is where the note was originated. Guid gPARENT_ID = Sql.ToGuid(Request["PARENT_ID"]); string sMODULE = String.Empty; string sPARENT_TYPE = String.Empty; string sPARENT_NAME = String.Empty; try { SqlProcs.spPARENT_Get(ref gPARENT_ID, ref sMODULE, ref sPARENT_TYPE, ref sPARENT_NAME); } catch (Exception ex) { SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex); // The only possible error is a connection failure, so just ignore all errors. gPARENT_ID = Guid.Empty; } if (e.CommandName == "Save") { this.ValidateEditViewFields(m_sMODULE + ".EditView"); this.ValidateEditViewFields(m_sMODULE + ".EditAddress"); this.ValidateEditViewFields(m_sMODULE + ".EditDescription"); if (Page.IsValid) { string sCUSTOM_MODULE = "ACCOUNTS"; DataTable dtCustomFields = SplendidCache.FieldsMetaData_Validated(sCUSTOM_MODULE); DbProviderFactory dbf = DbProviderFactories.GetFactory(); using (IDbConnection con = dbf.CreateConnection()) { con.Open(); // 11/18/2007 Paul. Use the current values for any that are not defined in the edit view. DataRow rowCurrent = null; DataTable dtCurrent = new DataTable(); if (!Sql.IsEmptyGuid(gID)) { string sSQL; sSQL = "select * " + ControlChars.CrLf + " from vwACCOUNTS_Edit" + ControlChars.CrLf; using (IDbCommand cmd = con.CreateCommand()) { cmd.CommandText = sSQL; Security.Filter(cmd, m_sMODULE, "edit"); Sql.AppendParameter(cmd, gID, "ID", false); using (DbDataAdapter da = dbf.CreateDataAdapter()) { ((IDbDataAdapter)da).SelectCommand = cmd; da.Fill(dtCurrent); if (dtCurrent.Rows.Count > 0) { rowCurrent = dtCurrent.Rows[0]; } else { // 11/19/2007 Paul. If the record is not found, clear the ID so that the record cannot be updated. // It is possible that the record exists, but that ACL rules prevent it from being selected. gID = Guid.Empty; } } } } using (IDbTransaction trn = con.BeginTransaction()) { try { // 11/18/2007 Paul. Use the current values for any that are not defined in the edit view. // 12/29/2007 Paul. TEAM_ID is now in the stored procedure. SqlProcs.spACCOUNTS_Update (ref gID , new DynamicControl(this, rowCurrent, "ASSIGNED_USER_ID").ID , new DynamicControl(this, rowCurrent, "NAME").Text , new DynamicControl(this, rowCurrent, "ACCOUNT_TYPE").SelectedValue , new DynamicControl(this, rowCurrent, "PARENT_ID").ID , new DynamicControl(this, rowCurrent, "INDUSTRY").SelectedValue , new DynamicControl(this, rowCurrent, "ANNUAL_REVENUE").Text , new DynamicControl(this, rowCurrent, "PHONE_FAX").Text , new DynamicControl(this, rowCurrent, "BILLING_ADDRESS_STREET").Text , new DynamicControl(this, rowCurrent, "BILLING_ADDRESS_CITY").Text , new DynamicControl(this, rowCurrent, "BILLING_ADDRESS_STATE").Text , new DynamicControl(this, rowCurrent, "BILLING_ADDRESS_POSTALCODE").Text , new DynamicControl(this, rowCurrent, "BILLING_ADDRESS_COUNTRY").Text , new DynamicControl(this, rowCurrent, "DESCRIPTION").Text , new DynamicControl(this, rowCurrent, "RATING").Text , new DynamicControl(this, rowCurrent, "PHONE_OFFICE").Text , new DynamicControl(this, rowCurrent, "PHONE_ALTERNATE").Text , new DynamicControl(this, rowCurrent, "EMAIL1").Text , new DynamicControl(this, rowCurrent, "EMAIL2").Text , new DynamicControl(this, rowCurrent, "WEBSITE").Text , new DynamicControl(this, rowCurrent, "OWNERSHIP").Text , new DynamicControl(this, rowCurrent, "EMPLOYEES").Text , new DynamicControl(this, rowCurrent, "SIC_CODE").Text , new DynamicControl(this, rowCurrent, "TICKER_SYMBOL").Text , new DynamicControl(this, rowCurrent, "SHIPPING_ADDRESS_STREET").Text , new DynamicControl(this, rowCurrent, "SHIPPING_ADDRESS_CITY").Text , new DynamicControl(this, rowCurrent, "SHIPPING_ADDRESS_STATE").Text , new DynamicControl(this, rowCurrent, "SHIPPING_ADDRESS_POSTALCODE").Text , new DynamicControl(this, rowCurrent, "SHIPPING_ADDRESS_COUNTRY").Text , new DynamicControl(this, rowCurrent, "TEAM_ID").ID , trn ); SplendidDynamic.UpdateCustomFields(this, trn, gID, sCUSTOM_MODULE, dtCustomFields); trn.Commit(); } catch (Exception ex) { trn.Rollback(); SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex); ctlEditButtons.ErrorText = ex.Message; return; } } } if (!Sql.IsEmptyGuid(gPARENT_ID)) { Response.Redirect("~/Accounts/view.aspx?ID=" + gPARENT_ID.ToString()); } else { Response.Redirect("view.aspx?ID=" + gID.ToString()); } } } else if (e.CommandName == "Cancel") { if (!Sql.IsEmptyGuid(gPARENT_ID)) { Response.Redirect("~/Accounts/view.aspx?ID=" + gPARENT_ID.ToString()); } else if (Sql.IsEmptyGuid(gID)) { Response.Redirect("default.aspx"); } else { Response.Redirect("view.aspx?ID=" + gID.ToString()); } } }
// 탬플릿을 이용한 kpi 복사 public string CopyKpiToMboUsingTemplete(int estterm_ref_id , object[] objKpiPoolList , string templete_id , string templete_name , int emp_ref_id , string nowDate , string result_measurement_step , int unit_type_ref_id , string class_type , DataTable dtBscThresholdStep) { string reVal = string.Empty; MicroBSC.BSC.Dac.Dac_Bsc_Kpi_Info dac = new MicroBSC.BSC.Dac.Dac_Bsc_Kpi_Info(); Dac_Bsc_Mbo_Kpi_Classification dacBscMboKpiClassification = new Dac_Bsc_Mbo_Kpi_Classification(); Dac_Bsc_Kpi_Target_Version dacBscKpiTargetVersion = new Dac_Bsc_Kpi_Target_Version(); Dac_Bsc_Kpi_Result dacBscKpiResult = new Dac_Bsc_Kpi_Result(); Dac_Com_Emp_Role_Rel dacEmpRoleRel = new Dac_Com_Emp_Role_Rel(); Dac_Bsc_Kpi_Term dacBscKpiTerm = new Dac_Bsc_Kpi_Term(); Dac_Bsc_Kpi_Target dacBscKpiTarget = new Dac_Bsc_Kpi_Target(); Dac_Bsc_Kpi_Threshold_Info dacBscKpiThresholdInfo = new Dac_Bsc_Kpi_Threshold_Info(); Dac_Bsc_Kpi_Initiative dacBscKpiInitiative = new Dac_Bsc_Kpi_Initiative(); int rtn_kpi_ref_id = 0; int affectedRow = 0; bool Result; IDbConnection conn = DbAgentHelper.CreateDbConnection(); conn.Open(); IDbTransaction trx = conn.BeginTransaction(); try { //for (int i = 0; i < objList.Length; i++) //{ // DataTable dt = dacBscMboKpiClassification.Select_DB(conn, trx, estterm_ref_id, emp_ref_id, 0, objList[i], ""); // if (dt.Rows.Count > 0 && DataTypeUtility.GetToInt32(dt.Rows[0]["ESTTERM_REF_ID"]) > 0) // return false; //} MicroBSC.Integration.BSC.Biz.Biz_Bsc_Kpi_Result bizBscKpiResult = new Biz_Bsc_Kpi_Result(); for (int i = 0; i < objKpiPoolList.Length; i++) { rtn_kpi_ref_id = dac.SelectMaxBscKpiInfo_DB(conn, trx); object kpi_pool_ref_id = objKpiPoolList[i]; #region 기존 코드에서 strQuery1 //KPI INFO COPY int okCnt = _data.InsertData_DB(conn , trx , estterm_ref_id , rtn_kpi_ref_id , rtn_kpi_ref_id , kpi_pool_ref_id , string.Format("{0}_{1}", templete_id, templete_name) , "" , emp_ref_id , "MAN" , "ATY" , "ETC" , result_measurement_step , "RATE" , unit_type_ref_id , 1 , "" , "" , "N" , "N" , "N" , "Y" , nowDate , emp_ref_id); //MAX KPI_REF_ID //rtn_kpi_ref_id = _data.Select_Max_Kpi_Ref_Id(conn, trx); //KPI TARGET VERSION INSERT dacBscKpiTargetVersion.Insert_DB(conn, trx , estterm_ref_id , rtn_kpi_ref_id , 1 , "당초계획" , "당초계획(자동생성)" , 1 , 12 , "Y" , emp_ref_id); //Champion Role Insert dacEmpRoleRel.Delete_DB(conn, trx, emp_ref_id, 3); dacEmpRoleRel.Insert_DB(conn, trx, emp_ref_id, 3); //지표구분테이블에 일상업무정보 추가 BSC_MBO_KPI_CLASSIFICATION dacBscMboKpiClassification.Insert_DB(conn , trx , estterm_ref_id , emp_ref_id , rtn_kpi_ref_id , rtn_kpi_ref_id , class_type , emp_ref_id); #endregion affectedRow = 0; if (rtn_kpi_ref_id > 0) { string year = DateTime.Now.Year.ToString(); #region 기존코드에서 strQuery2 for (int k = 1; k < 13; k++) { string month = k.ToString().PadLeft(2, '0'); string ymd = year + month; //KPI RESULT INSERT affectedRow += bizBscKpiResult.InsertData_DB(conn , trx , estterm_ref_id , rtn_kpi_ref_id , ymd , 0 , 0 , ymd , 0 , 0 , "N" , "" , "" , "" , "" , "" , "" , "" , "" , nowDate , emp_ref_id); dacBscKpiTerm.Insert_DB(conn , trx , estterm_ref_id , rtn_kpi_ref_id , ymd , "N" , "N" , "N" , emp_ref_id); dacBscKpiTarget.InsertData_DB(conn , trx , estterm_ref_id , rtn_kpi_ref_id , 1 , ymd , 0 , 0 , emp_ref_id); dacBscKpiInitiative.InsertData_DB(conn , trx , estterm_ref_id , rtn_kpi_ref_id , ymd , DBNull.Value , DBNull.Value , DBNull.Value , DBNull.Value , emp_ref_id); } if (affectedRow != 12) { return("false"); } #endregion //////////////////////////////////////////////////// // KPI THRESHOLD //////////////////////////////////////////////////// for (int k = 0; k < dtBscThresholdStep.Rows.Count; k++) { string threshold_ref_id = DataTypeUtility.GetValue(dtBscThresholdStep.Rows[k]["THRESHOLD_REF_ID"]); dacBscKpiThresholdInfo.InsertData_DB(conn , trx , estterm_ref_id , rtn_kpi_ref_id , threshold_ref_id , result_measurement_step , 0 , 0 , 0 , 0 , nowDate , emp_ref_id); } } } trx.Commit(); } catch (Exception ex) { reVal = ex.Message; trx.Rollback(); rtn_kpi_ref_id = 0; } finally { conn.Close(); } return(reVal); }
public BaseUnitOfWork(IConnectionFactory factory) { Connection = factory.Open(); Transaction = Connection.BeginTransaction(); }
public UnitOfWork(IDbConnection connection) { Connection = connection; Transaction = connection.BeginTransaction(); }
/// <summary> /// 기안, 재기안, 수정기안 /// </summary> public void SetDraft() { string KPI_REF_ID = Request["KPI_REF_ID"].ToString(); string ymd = GetRequest("YMD", ""); Biz_Com_Approval_Info objApp = new Biz_Com_Approval_Info(); bool blnRtn = false; //결재완결처리 결재선이 하나인경우 완결처리 string sAppStatus = (ugrdAppLine.Rows.Count == 1) ? Biz_Type.app_status_complete : Biz_Type.app_status_draft; //기안 if (this.IDraft_Status == Biz_Type.app_draft_first) //최초기안 { if (GetRequest("KPI_REF_ID", "").Split(',').Length < 1) { ltrScript.Text = JSHelper.GetAlertScript("결재처리할 KPI코드를 확인하세요!"); return; } IDbConnection conn = MicroBSC.Data.DbAgentHelper.CreateDbConnection(); conn.Open(); IDbTransaction trx = conn.BeginTransaction(); string strTermid = GetRequest("ESTTERM_REF_ID"); //현재 시점에 결재여부 재확인 MicroBSC.BSC.Biz.Biz_Bsc_Kpi_Info objkpi = new MicroBSC.BSC.Biz.Biz_Bsc_Kpi_Info(); DataSet dsKPI = new DataSet(); if (this.IBiz_Type == Biz_Type.biz_type_kpi_docbatch) { dsKPI = objkpi.GetKpiListForBatchDraft(DataTypeUtility.GetToInt32(strTermid) , "", "", "Y", gUserInfo.Emp_Ref_ID, GetRequest("KPI_REF_ID")); } else if (this.IBiz_Type == Biz_Type.biz_type_kpi_rstbatch) { dsKPI = objkpi.GetKpiResultListForBatchDraft(DataTypeUtility.GetToInt32(strTermid) , ymd, "", "", "Y", gUserInfo.Emp_Ref_ID, GetRequest("KPI_REF_ID")); } else if (this.IBiz_Type == Biz_Type.biz_type_target_resultbatch) { dsKPI = objkpi.GetKpiResultListForBatchDraft(DataTypeUtility.GetToInt32(strTermid) , ymd, "", "", "N", gUserInfo.Emp_Ref_ID, GetRequest("KPI_REF_ID")); } if (dsKPI.Tables[0].Rows.Count > 0) { if (dsKPI.Tables[0].Rows.Count != KPI_REF_ID.Split(',').Length) { trx.Rollback(); conn.Close(); conn.Dispose(); this.IApp_Ref_Id = 0; this.IVersion_No = 0; ltrScript.Text = JSHelper.GetAlertScript("결재대상 KPI상태를 확인하세요!", true); return; } if (dsKPI.Tables[0].Select("APP_STATUS NOT IN('NFT', 'MFT', 'AFT', 'RFT')").Length > 0) { trx.Rollback(); conn.Close(); conn.Dispose(); this.IApp_Ref_Id = 0; this.IVersion_No = 0; ltrScript.Text = JSHelper.GetAlertScript("결재대상 KPI상태를 확인하세요!", true); return; } } else { trx.Rollback(); conn.Close(); conn.Dispose(); this.IApp_Ref_Id = 0; this.IVersion_No = 0; ltrScript.Text = JSHelper.GetAlertScript("결재대상 KPI상태를 확인하세요!", true); return; } foreach (string strKpiRefId in KPI_REF_ID.Split(',')) { blnRtn = false; try { NameValueCollection nvc = new NameValueCollection(); nvc.Add("ESTTERM_REF_ID", strTermid); nvc.Add("KPI_REF_ID", strKpiRefId); nvc.Add("YMD", ymd); string strTitle = ""; if (this.IBiz_Type == Biz_Type.biz_type_kpi_docbatch) { blnRtn = objApp.GetOriDocTitle(Biz_Type.biz_type_kpi_doc, nvc, out strTitle); } else if (this.IBiz_Type == Biz_Type.biz_type_kpi_rstbatch) { blnRtn = objApp.GetOriDocTitle(Biz_Type.biz_type_kpi_rst, nvc, out strTitle); } else if (this.IBiz_Type == Biz_Type.biz_type_target_resultbatch) { blnRtn = objApp.GetOriDocTitle(Biz_Type.biz_type_target_result, nvc, out strTitle); } if (blnRtn) { txtTitle.Text = strTitle; } else { trx.Rollback(); conn.Close(); conn.Dispose(); this.IApp_Ref_Id = 0; this.IVersion_No = 0; ltrScript.Text = JSHelper.GetAlertScript("결재처리 중 결재원문을 알수 없습니다.", true); return; } string queryURL = string.Empty; string strFullPath = ""; string strPath = ""; string strParam = string.Empty; if (this.IBiz_Type == Biz_Type.biz_type_kpi_docbatch) { strParam = "ESTTERM_REF_ID=" + strTermid + "&KPI_REF_ID=" + strKpiRefId + "&BIZ_TYPE=" + Biz_Type.biz_type_kpi_doc + "&APP_REF_ID=0&DRAFT_EMP_ID=" + gUserInfo.Emp_Ref_ID.ToString(); strPath = Biz_Com_Approval_Info.GetDraftPagePath(Biz_Type.biz_type_kpi_doc); } else if (this.IBiz_Type == Biz_Type.biz_type_kpi_rstbatch) { strParam = "ESTTERM_REF_ID=" + strTermid + "&KPI_REF_ID=" + strKpiRefId + "&YMD=" + ymd + "&BIZ_TYPE=" + Biz_Type.biz_type_kpi_rst + "&APP_REF_ID=0&DRAFT_EMP_ID=" + gUserInfo.Emp_Ref_ID.ToString(); strPath = Biz_Com_Approval_Info.GetDraftPagePath(Biz_Type.biz_type_kpi_rst); } else if (this.IBiz_Type == Biz_Type.biz_type_target_resultbatch) { strParam = "ESTTERM_REF_ID=" + strTermid + "&KPI_REF_ID=" + strKpiRefId + "&YMD=" + ymd + "&BIZ_TYPE=" + Biz_Type.biz_type_target_result + "&APP_REF_ID=0&DRAFT_EMP_ID=" + gUserInfo.Emp_Ref_ID.ToString(); strPath = Biz_Com_Approval_Info.GetDraftPagePath(Biz_Type.biz_type_kpi_rst); } string strVPath = Request.ApplicationPath; string strSHost = Request.Url.Host; string strSPort = Request.Url.Port.ToString(); string strProto = Request.Url.Scheme; strVPath = (strVPath == "/") ? "" : strVPath; strFullPath = strProto + "://" + strSHost + ":" + strSPort + strVPath + strPath + "?" + strParam; string strHtml = Server.HtmlDecode(Biz_Com_Approval_Info.GetHtmlSource(strFullPath)); string strPos = "{^0^}"; int iSPos = strHtml.IndexOf(strPos) + strPos.Length; int iEPos = strHtml.LastIndexOf(strPos); divArea_M.InnerHtml = strHtml.Substring(iSPos, iEPos - iSPos); NameValueCollection nvc2 = new NameValueCollection(); nvc2.Add("ESTTERM_REF_ID", strTermid); nvc2.Add("KPI_REF_ID", strKpiRefId); if (this.IBiz_Type == Biz_Type.biz_type_kpi_docbatch) { nvc2.Add("BIZ_TYPE", Biz_Type.biz_type_kpi_doc); } else if (this.IBiz_Type == Biz_Type.biz_type_kpi_rstbatch) { nvc2.Add("BIZ_TYPE", Biz_Type.biz_type_kpi_rst); nvc2.Add("YMD", ymd); } else if (this.IBiz_Type == Biz_Type.biz_type_target_resultbatch) { nvc2.Add("BIZ_TYPE", Biz_Type.biz_type_target_result); nvc2.Add("YMD", ymd); } nvc2.Add("APP_REF_ID", "0"); nvc2.Add("DRAFT_EMP_ID", gUserInfo.Emp_Ref_ID.ToString()); if (this.IBiz_Type == Biz_Type.biz_type_kpi_docbatch) { blnRtn = objApp.TxrDraftBatch(conn, trx, Server.HtmlEncode(divArea_M.InnerHtml) , txtTitle.Text , Biz_Type.biz_type_kpi_doc , sAppStatus , Biz_Type.app_draft_first , "" , gUserInfo.Emp_Ref_ID , this.GetAPPLine(), nvc2); } else if (this.IBiz_Type == Biz_Type.biz_type_kpi_rstbatch) { blnRtn = objApp.TxrDraftBatch(conn, trx, Server.HtmlEncode(divArea_M.InnerHtml) , txtTitle.Text , Biz_Type.biz_type_kpi_rst , sAppStatus , Biz_Type.app_draft_first , "" , gUserInfo.Emp_Ref_ID , this.GetAPPLine(), nvc2); } else if (this.IBiz_Type == Biz_Type.biz_type_target_resultbatch) { blnRtn = objApp.TxrDraftBatch(conn, trx, Server.HtmlEncode(divArea_M.InnerHtml) , txtTitle.Text , Biz_Type.biz_type_target_result , sAppStatus , Biz_Type.app_draft_first , "" , gUserInfo.Emp_Ref_ID , this.GetAPPLine(), nvc2); } if (!blnRtn) { trx.Rollback(); conn.Close(); conn.Dispose(); this.IApp_Ref_Id = 0; this.IVersion_No = 0; ltrScript.Text = JSHelper.GetAlertScript("결재 일괄처리 중 오류가 발생하였습니다."); ltrScript.Text = JSHelper.GetAlertOpenerControlCallBackScript(objApp.Transaction_Message, this.IAPP_CCB, true); return; } this.IApp_Ref_Id = objApp.IApp_Ref_Id; this.IVersion_No = objApp.IVersion_No; if (this.IHISTORY_YN == "Y") { DoInsertUpdateReason(); } } catch { trx.Rollback(); conn.Close(); conn.Dispose(); this.IApp_Ref_Id = 0; this.IVersion_No = 0; ltrScript.Text = JSHelper.GetAlertScript("결재 일괄처리 중 오류가 발생하였습니다."); ltrScript.Text = JSHelper.GetAlertOpenerControlCallBackScript(objApp.Transaction_Message, this.IAPP_CCB, true); return; } } trx.Commit(); conn.Close(); conn.Dispose(); try { bool bRtn = this.SendMailBatch(false); } catch { } ltrScript.Text = JSHelper.GetAlertOpenerControlCallBackScript(objApp.Transaction_Message, this.IAPP_CCB, true); } else { ltrScript.Text = JSHelper.GetAlertScript("결재형식이 올바르지 않습니다!"); return; } }
public void Begin() { _transaction = _connection.BeginTransaction(); }
/// <summary> /// 조직KPI에서 복사한 MBO를 삭제 /// </summary> /// <param></param> /// <returns></returns> public string RemoveMboToKpi_DB(int estterm_ref_id , string[] objList) { string rtnValue = string.Empty; int okCnt = 0; IDbConnection conn = DbAgentHelper.CreateDbConnection(); conn.Open(); IDbTransaction trx = conn.BeginTransaction(); try { MicroBSC.Integration.BSC.Dac.Dac_Bsc_Kpi_Info dacBscKpiInfo = new MicroBSC.Integration.BSC.Dac.Dac_Bsc_Kpi_Info(); MicroBSC.Integration.BSC.Dac.Dac_Bsc_Kpi_Target_Version dacBscKpiTargetVersion = new MicroBSC.Integration.BSC.Dac.Dac_Bsc_Kpi_Target_Version(); MicroBSC.Integration.BSC.Dac.Dac_Bsc_Kpi_Result dacBscKpiResult = new MicroBSC.Integration.BSC.Dac.Dac_Bsc_Kpi_Result(); MicroBSC.Integration.BSC.Dac.Dac_Bsc_Kpi_Term dacBscKpiTerm = new MicroBSC.Integration.BSC.Dac.Dac_Bsc_Kpi_Term(); MicroBSC.Integration.BSC.Dac.Dac_Bsc_Kpi_Target dacBscKpiTarget = new MicroBSC.Integration.BSC.Dac.Dac_Bsc_Kpi_Target(); MicroBSC.Integration.BSC.Dac.Dac_Bsc_Kpi_Threshold_Info dacBscKpiThresholdInfo = new MicroBSC.Integration.BSC.Dac.Dac_Bsc_Kpi_Threshold_Info(); MicroBSC.Integration.BSC.Dac.Dac_Bsc_Kpi_Initiative dacBscKpiInitiative = new MicroBSC.Integration.BSC.Dac.Dac_Bsc_Kpi_Initiative(); MicroBSC.Integration.BSC.Dac.Dac_Bsc_Kpi_Classification dacBscKpiClassification = new MicroBSC.Integration.BSC.Dac.Dac_Bsc_Kpi_Classification(); foreach (string obj in objList) { okCnt = dacBscKpiInfo.Delete_DB(conn , trx , estterm_ref_id , obj); okCnt = dacBscKpiTargetVersion.Delete_DB(conn , trx , estterm_ref_id , obj); okCnt = dacBscKpiResult.DeleteData_DB(conn , trx , estterm_ref_id , obj); okCnt = dacBscKpiTerm.Delete_DB(conn , trx , estterm_ref_id , obj); okCnt = dacBscKpiTarget.Delete_DB(conn , trx , estterm_ref_id , obj); okCnt = dacBscKpiThresholdInfo.Delete_DB(conn , trx , estterm_ref_id , obj); okCnt = dacBscKpiInitiative.DeleteData_DB(conn , trx , estterm_ref_id , obj); okCnt = dacBscKpiClassification.Delete_DB(conn , trx , estterm_ref_id , obj); } trx.Commit(); } catch (Exception ex) { rtnValue = ex.Message; trx.Rollback(); } finally { conn.Close(); } return(rtnValue); }
public void IniciarTransacao() { _transacao = _conexao.BeginTransaction(); }
//protected _controls.DateTimePicker ctlDATE_START ; protected void Page_Command(Object sender, CommandEventArgs e) { // 08/21/2005 Paul. Redirect to parent if that is where the note was originated. Guid gPARENT_ID = Sql.ToGuid(Request["PARENT_ID"]); string sMODULE = String.Empty; string sPARENT_TYPE = String.Empty; string sPARENT_NAME = String.Empty; try { SqlProcs.spPARENT_Get(ref gPARENT_ID, ref sMODULE, ref sPARENT_TYPE, ref sPARENT_NAME); } catch (Exception ex) { SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message); // The only possible error is a connection failure, so just ignore all errors. gPARENT_ID = Guid.Empty; } if (e.CommandName == "Save") { /* * DateTimePicker ctlDATE_START = FindControl("DATE_START") as DateTimePicker; * if ( ctlDATE_START != null ) * ctlDATE_START.Validate(); */ // 01/16/2006 Paul. Enable validator before validating page. SplendidDynamic.ValidateEditViewFields(m_sMODULE + ".EditView", this); if (Page.IsValid) { string sCUSTOM_MODULE = "CALLS"; DataTable dtCustomFields = SplendidCache.FieldsMetaData_Validated(sCUSTOM_MODULE); DbProviderFactory dbf = DbProviderFactories.GetFactory(); using (IDbConnection con = dbf.CreateConnection()) { con.Open(); using (IDbTransaction trn = con.BeginTransaction()) { try { SqlProcs.spCALLS_Update(ref gID , new DynamicControl(this, "ASSIGNED_USER_ID").ID , new DynamicControl(this, "NAME").Text , new DynamicControl(this, "DURATION_HOURS").IntegerValue , new DynamicControl(this, "DURATION_MINUTES").IntegerValue , new DynamicControl(this, "DATE_START").DateValue , new DynamicControl(this, "PARENT_TYPE").SelectedValue , new DynamicControl(this, "PARENT_ID").ID , new DynamicControl(this, "STATUS").SelectedValue , new DynamicControl(this, "DIRECTION").SelectedValue , new DynamicControl(this, "SHOULD_REMIND").Checked ? new DynamicControl(this, "REMINDER_TIME").IntegerValue : -1 , new DynamicControl(this, "DESCRIPTION").Text , txtINVITEE_ID.Value , trn ); SplendidDynamic.UpdateCustomFields(this, trn, gID, sCUSTOM_MODULE, dtCustomFields); trn.Commit(); } catch (Exception ex) { trn.Rollback(); SplendidError.SystemError(new StackTrace(true).GetFrame(0), ex.Message); ctlEditButtons.ErrorText = ex.Message; return; } } } if (!Sql.IsEmptyGuid(gPARENT_ID)) { Response.Redirect("~/" + sMODULE + "/view.aspx?ID=" + gPARENT_ID.ToString()); } else { Response.Redirect("view.aspx?ID=" + gID.ToString()); } } } else if (e.CommandName == "Cancel") { if (!Sql.IsEmptyGuid(gPARENT_ID)) { Response.Redirect("~/" + sMODULE + "/view.aspx?ID=" + gPARENT_ID.ToString()); } else if (Sql.IsEmptyGuid(gID)) { Response.Redirect("default.aspx"); } else { Response.Redirect("view.aspx?ID=" + gID.ToString()); } } else if (e.CommandName == "Invitees.Add") { if (txtINVITEE_ID.Value.Length > 0) { txtINVITEE_ID.Value += ","; } txtINVITEE_ID.Value += e.CommandArgument; ctlInviteesView.INVITEES = txtINVITEE_ID.Value.Split(','); BindSchedule(); } else if (e.CommandName == "Invitees.Delete") { string sDELETE_ID = e.CommandArgument.ToString().ToLower(); string[] arrINVITEES = txtINVITEE_ID.Value.Split(','); StringBuilder sb = new StringBuilder(); foreach (string sINVITEE_ID in arrINVITEES) { if (sINVITEE_ID != sDELETE_ID) { if (sb.Length > 0) { sb.Append(","); } sb.Append(sINVITEE_ID); } } txtINVITEE_ID.Value = sb.ToString(); ctlInviteesView.INVITEES = txtINVITEE_ID.Value.Split(','); BindSchedule(); } else if (e.CommandName == "Search") { BindSchedule(); } }
public async Task InsertWithChildrenAsync<TModel>(IEnumerable<TModel> models, bool withMap = true, int tmpLevel = 0, SqlTransaction txn = null) where TModel : IKeyedEntity, IHaveAotMarker { if (withMap) { Console.WriteLine($"{DateTime.Now:o} AOT Marker Creation"); curAot = 0; ConfigureAotMarkers(models); curAot = 0; Console.WriteLine($"{DateTime.Now:o} AOT Marker Creation Completed"); } Console.WriteLine($"{DateTime.Now:o} Commit level {tmpLevel} executing ({typeof(TModel)}) w/ {models.Count()} models"); if (typeof(TModel) == typeof(Db.Models.SingleRendering)) { using (var sqlBulkCopy = new SqlBulkCopier<TModel>((SqlConnection)db, $"Renderings", false, txn)) { sqlBulkCopy.BulkCopy.ColumnMappings.Clear(); sqlBulkCopy.InternalTable.Columns.Remove("AotInsertionMarker"); sqlBulkCopy.InternalTable.Columns.Remove("AotParentMarker"); foreach (DataColumn column in sqlBulkCopy.InternalTable.Columns) sqlBulkCopy.BulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName); foreach (var row in models) { sqlBulkCopy.AddRow(row); } await sqlBulkCopy.WriteToServerAsync(); } return; } if (db.State != ConnectionState.Open) { db.Open(); } if (txn == null) { txn = db.BeginTransaction() as SqlTransaction; } try { if (typeof(TModel) == typeof(Db.Models.Event)) { await db.ExecuteAsync($@"CREATE TABLE #Tmp{tmpLevel} ([Id] INT NULL, [ApplicationId] INT NOT NULL, [Timestamp] DATETIMEOFFSET NOT NULL, [Level] NVARCHAR(20) NOT NULL, [MessageTemplate] NVARCHAR(MAX) NOT NULL, [HasException] BIT, [AotInsertionMarker] INT)", transaction: txn); using (var sqlBulkCopy = new SqlBulkCopier<TModel>((SqlConnection)db, $"#Tmp{tmpLevel}", false, txn)) { foreach (var row in models) { sqlBulkCopy.AddRow(row); } await sqlBulkCopy.WriteToServerAsync(); } var returns = await db.QueryAsync<(int, int)>($@"INSERT INTO [Events] (ApplicationId, Timestamp, Level, MessageTemplate, HasException, AotInsertionMarker) OUTPUT INSERTED.Id, INSERTED.AotInsertionMarker SELECT ApplicationId, Timestamp, Level, MessageTemplate, HasException, AotInsertionMarker FROM #Tmp{tmpLevel}", transaction: txn); var dictReturns = returns.ToDictionary(m => m.Item2, m => m.Item1); foreach (var model in models) { var dbEvent = model as Models.Event; foreach (var child in dbEvent.Properties) child.EventId = dictReturns[dbEvent.AotInsertionMarker]; foreach (var child in dbEvent.RenderingGroups) child.EventId = dictReturns[dbEvent.AotInsertionMarker]; } var obj = (IEnumerable<Models.Event>)(models); await InsertWithChildrenAsync(obj.SelectMany(m => m.Properties), false, 1+tmpLevel, txn); // Manual high levelling to avoid conflicts with lower levels on Property recursion await InsertWithChildrenAsync(obj.SelectMany(m => m.RenderingGroups), false, 69+tmpLevel, txn); } else if (typeof(TModel) == typeof(Db.Models.Property)) { await db.ExecuteAsync($@"CREATE TABLE #Tmp{tmpLevel} ([Id] INT, [EventId] INT, [ParentPropertyId] INT NULL, [Name] NVARCHAR(100), [Value] NVARCHAR(MAX) NULL, [AotInsertionMarker] INT, [AotParentMarker] INT)", transaction: txn); using (var sqlBulkCopy = new SqlBulkCopier<TModel>((SqlConnection)db, $"#Tmp{tmpLevel}", false, txn)) { foreach (var row in models) { sqlBulkCopy.AddRow(row); } await sqlBulkCopy.WriteToServerAsync(); } var returns = await db.QueryAsync<(int, int)>($@"INSERT INTO [Properties] (EventId, ParentPropertyId, Name, Value, AotInsertionMarker) OUTPUT INSERTED.Id, INSERTED.AotInsertionMarker SELECT EventId, ParentPropertyId, Name, Value, AotInsertionMarker FROM #Tmp{tmpLevel}", transaction: txn); var dictReturns = returns.ToDictionary(m => m.Item2, m => m.Item1); foreach (var model in models) { var dbProperty = model as Models.Property; foreach (var child in dbProperty.Children) { child.EventId = dbProperty.EventId; child.ParentPropertyId = dictReturns[dbProperty.AotInsertionMarker]; } } var obj = (IEnumerable<Models.Property>)(models); var children = obj.SelectMany(m => m.Children); if (children.Any()) { await InsertWithChildrenAsync(obj.SelectMany(m => m.Children), false, 1+tmpLevel, txn); } } else if (typeof(TModel) == typeof(Db.Models.RenderingGroup)) { await db.ExecuteAsync($@"CREATE TABLE #Tmp{tmpLevel} ([Id] INT, [EventId] INT, [Name] NVARCHAR(100) NOT NULL, [AotInsertionMarker] INT, [AotParentMarker] INT)", transaction: txn); using (var sqlBulkCopy = new SqlBulkCopier<TModel>((SqlConnection)db, $"#Tmp{tmpLevel}", false, txn)) { foreach (var row in models) { sqlBulkCopy.AddRow(row); } await sqlBulkCopy.WriteToServerAsync(); } var returns = await db.QueryAsync<(int, int)>($@"INSERT INTO [RenderingGroups] (EventId, Name, AotInsertionMarker) OUTPUT INSERTED.Id, INSERTED.AotInsertionMarker SELECT EventId, Name, AotInsertionMarker FROM #Tmp{tmpLevel}", transaction: txn); var dictReturns = returns.ToDictionary(m => m.Item2, m => m.Item1); foreach (var model in models) { var dbRenderingGroup = model as Models.RenderingGroup; foreach (var child in dbRenderingGroup.Renderings) child.RenderingGroupId = dictReturns[dbRenderingGroup.AotInsertionMarker]; } var obj = (IEnumerable<Models.RenderingGroup>)(models); await InsertWithChildrenAsync(obj.SelectMany(m => m.Renderings), false, 1+tmpLevel, txn); } await db.ExecuteAsync($"DROP TABLE IF EXISTS #Tmp{tmpLevel}", transaction: txn); if (tmpLevel == 0) { Console.WriteLine($"{DateTime.Now:o} Commit level 0 transaction completion"); txn.Commit(); Console.WriteLine($"{DateTime.Now:o} DB Transaction committed"); } } catch { if (tmpLevel != 0) throw; txn.Rollback(); } Console.WriteLine($"{DateTime.Now:o} Commit level {tmpLevel} executed"); if (tmpLevel == 0) { txn.Dispose(); } }
/// <summary> /// Deletes the specified entities into the database asynchronous. /// </summary> /// <typeparam name="TEntity">The type of the entity.</typeparam> /// <param name="connection">The connection to the database. This can either be open or closed.</param> /// <param name="entities">The entity to be inserted.</param> /// <param name="transaction">Optional transaction for the command.</param> /// <param name="sqlBulkCopyOptions">The SQL bulk copy options.</param> /// <param name="bulkCopyTimeout">The bulk copy timeout.</param> /// <param name="bulkCopyBatchSize">Size of the bulk copy batch.</param> /// <param name="bulkCopyNotifyAfter">The bulk copy notify after.</param> /// <param name="bulkCopyEnableStreaming">if set to <c>true</c> [bulk copy enable streaming].</param> /// <returns></returns> public static async Task BulkDeleteAsync <TEntity>( this IDbConnection connection, IEnumerable <TEntity> entities, IDbTransaction transaction = null, SqlBulkCopyOptions sqlBulkCopyOptions = SqlBulkCopyOptions.Default, int bulkCopyTimeout = 600, int bulkCopyBatchSize = 5000, int bulkCopyNotifyAfter = 1000, bool bulkCopyEnableStreaming = false ) where TEntity : class { if (!entities.Any()) { return; } var dataTable = GetTemporaryDataTable(typeof(TEntity), out var bulkMetadata, false).Shred(entities, bulkMetadata, null, identifierOnly: true); var sqlConnection = (SqlConnection)connection; sqlConnection.Open(); if (transaction == null) { transaction = connection.BeginTransaction(); } using (var trans = (SqlTransaction)transaction) { try { var command = sqlConnection.CreateCommand(); command.Connection = sqlConnection; command.Transaction = trans; command.CommandTimeout = 600; CheckTemporaryTableQuery(connection, transaction, bulkMetadata, false); //Creating temp table on database command.CommandText = bulkMetadata.TempTableQuery; await command.ExecuteNonQueryAsync(); //Bulk copy into temp table await BulkCopyAsync <TEntity>(sqlConnection, (SqlTransaction)transaction, dataTable, sqlBulkCopyOptions, bulkMetadata.Name, bulkCopyTimeout, bulkCopyBatchSize, bulkCopyNotifyAfter, bulkCopyEnableStreaming); // Updating destination table, and dropping temp table CheckDeleteTableQuery(bulkMetadata); command.CommandText = bulkMetadata.DeleteQuery; await command.ExecuteNonQueryAsync(); transaction.Commit(); } catch (Exception) { transaction.Rollback(); throw; } finally { sqlConnection.Close(); } } }
public int Submit(string login, string password, XmlNode node) { IDbConnection conn = GetConnection(); conn.Open(); try { IDbCommand cmd = conn.CreateCommand(); cmd.CommandText = "select * from person where name=@login and password=@password"; cmd.Parameters.Add(CreateParameter("@login", login)); cmd.Parameters.Add(CreateParameter("@password", password)); IDataReader reader = cmd.ExecuteReader(); int ret_val = -1; if (reader.Read()) { int id = (int)reader["person_id"]; int serial = (int)reader["last_serial"]; reader.Close(); // // Validate the XML // XmlDocument d = new XmlDocument(); d.AppendChild(d.ImportNode(node, true)); XmlNodeReader r = new XmlNodeReader(d); try { object rr = GlobalChangeset.serializer.Deserialize(r); } catch { return(-2); } string dudebase = basedir + id; Directory.CreateDirectory(dudebase); d.Save(dudebase + "/" + serial + ".xml"); IDbTransaction txn = conn.BeginTransaction(); try { cmd.CommandText = "UPDATE person SET last_serial=@last_serial WHERE name=@name AND password=@pwd"; cmd.Parameters.Add(CreateParameter("@last_serial", serial + 1)); cmd.Parameters.Add(CreateParameter("@name", login)); cmd.Parameters.Add(CreateParameter("@pwd", password)); cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO status (person_id, serial, status) VALUES (@id, @serial, 0)"; cmd.Parameters.Add(CreateParameter("@id", id)); cmd.Parameters.Add(CreateParameter("@serial", serial)); cmd.ExecuteNonQuery(); txn.Commit(); } catch (Exception e) { Console.Error.WriteLine("E: " + e); } ret_val = serial + 1; return(ret_val); } Console.Error.WriteLine("Error, going: 4"); return(-4); } catch (Exception e) { Console.Error.WriteLine("Failure in Submit: " + e); return(-3); } finally { conn.Close(); } }
// https://technet.microsoft.com/en-us/library/ms189122(v=sql.105).aspx public static IDbTransaction GetDbTransaction(IDbConnection connection, IsolationLevel level = IsolationLevel.ReadCommitted) { return(connection?.BeginTransaction(level)); }
public virtual void Begin() { mTrans = mConn.BeginTransaction(); }
/// <summary>If connection is not open then open and create command.</summary> /// <param name="query">SQL code.</param> /// <param name="commandType">Command Type.</param> /// <param name="parameter">PropertyName parameterized to PropertyName. if null then no use parameter.</param> /// <param name="extraParameter">CommandName set to __extra__PropertyName.</param> /// <returns>Setuped IDbCommand.</returns> protected IDbCommand PrepareExecute(string query, CommandType commandType, object parameter, object extraParameter = null) { Contract.Requires <ArgumentException>(!String.IsNullOrEmpty(query)); Contract.Ensures(Contract.Result <IDbCommand>() != null); if (connection.State != ConnectionState.Open) { connection.Open(); } if (transaction == null && isUseTransaction) { transaction = connection.BeginTransaction(isolationLevel); } var command = connection.CreateCommand(); command.CommandText = query; command.CommandType = commandType; if (parameter != null) { foreach (var p in AccessorCache.Lookup(parameter.GetType())) { if (!p.IsReadable) { continue; } Contract.Assume(parameter != null); if (p.GetValueDirect(parameter) is IDataParameter) { command.Parameters.Add(p.GetValueDirect(parameter)); } else { var param = command.CreateParameter(); param.ParameterName = p.Name; param.Value = p.GetValueDirect(parameter); command.Parameters.Add(param); } } } if (extraParameter != null) { foreach (var p in AccessorCache.Lookup(extraParameter.GetType())) { if (!p.IsReadable) { continue; } Contract.Assume(extraParameter != null); var param = command.CreateParameter(); param.ParameterName = "__extra__" + p.Name; param.Value = p.GetValueDirect(extraParameter); command.Parameters.Add(param); } } if (transaction != null) { command.Transaction = transaction; } return(command); }
public void BeginTransaction(IsolationLevel level = IsolationLevel.ReadCommitted) { _transaction = _dbConnection.BeginTransaction(level); }