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