public override async Task OnCommandAsync <TCommand>(Func <TCommand, CancellationToken, Task> next, TCommand cmd, CancellationToken ct)
{
_logger.LogDebug("Starting database transaction");
using var tx = _connection?.BeginTransaction();
await next(cmd, ct);
_logger.LogDebug("Committing database transaction");
tx?.Commit();
}
/// <summary>
/// 开启事务
/// </summary>
public void BeginTransaction()
{
Committed = false;
bool isClosed = DbConnection.State == ConnectionState.Closed;
if (isClosed)
{
DbConnection.Open();
}
DbTransaction = DbConnection?.BeginTransaction();
}
protected override void PagedEntities(int entityCount)
{
using (var db = new DbConnection())
{
db.BeginTransaction();
var results = db.Entities.Skip(0).Take(entityCount).ToList();
db.CommitTransaction();
}
}
protected override void DeleteEntities(List<Entity> entities)
{
using (var db = new DbConnection())
{
db.BeginTransaction();
entities.ForEach(entity => db.Delete(entity));
db.CommitTransaction();
}
}
protected override void SelectFetchEntities(int entityCount)
{
using (var db = new DbConnection())
{
db.BeginTransaction();
var results = db.Entities.Where(x => x.Id <= entityCount).ToList();
db.CommitTransaction();
}
}
protected override void ResetDatabase()
{
using (var db = new DbConnection())
{
db.BeginTransaction();
db.Execute("DELETE FROM Entity");
db.Execute("DELETE FROM sqlite_sequence WHERE name = 'Entity'");
db.CommitTransaction();
}
}
protected override void InsertEntities(List<Entity> entities)
{
using (var db = new DbConnection())
{
db.BeginTransaction();
// Insert doesn't seem to update the Id on the entity, this "work around" is horible
// but at least gives us parity with the other benchmarks! If there's a better
// way... I can't find it :/
entities.ForEach(entity =>
{
var id = db.InsertWithIdentity(entity);
entity.Id = Convert.ToInt32(id);
});
db.CommitTransaction();
}
}
private FR_Base Load(DbConnection Connection, DbTransaction Transaction, Guid ObjectID, string ConnectionString)
{
//Standard return type
FR_Base retStatus = new FR_Base();
bool cleanupConnection = false;
bool cleanupTransaction = false;
try
{
#region Verify/Create Connections
//Create connection if Connection is null
if (Connection == null)
{
cleanupConnection = true;
Connection = CSV2Core_MySQL.Support.DBSQLSupport.CreateConnection(ConnectionString);
Connection.Open();
}
//If transaction is not open/not valid
if (Transaction == null)
{
cleanupTransaction = true;
Transaction = Connection.BeginTransaction();
}
#endregion
//Get the SelectQuerry
string SelectQuery = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("CL1_CMN_STR.CMN_STR_Office_ResponsiblePerson.SQL.Select.sql")).ReadToEnd();
DbCommand command = Connection.CreateCommand();
//Set Connection/Transaction
command.Connection = Connection;
command.Transaction = Transaction;
//Set Query/Timeout
command.CommandText = SelectQuery;
command.CommandTimeout = QueryTimeout;
//Firstly, before loading, set the GUID to empty
//So the entity does not exist, it will have a GUID set to empty
_CMN_STR_Office_ResponsiblePersonID = Guid.Empty;
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "CMN_STR_Office_ResponsiblePersonID", ObjectID);
#region Command Execution
try
{
var loader = new CSV2Core_MySQL.Dictionaries.MultiTable.Loader.DictionaryLoader(Connection, Transaction);
var reader = new CSV2Core_MySQL.Support.DBSQLReader(command.ExecuteReader());
reader.SetOrdinals(new string[] { "CMN_STR_Office_ResponsiblePersonID", "Office_RefID", "CMN_BPT_EMP_Employee_RefID", "Creation_Timestamp", "IsDeleted", "Tenant_RefID" });
if (reader.HasRows == true)
{
reader.Read(); //Single result only
//0:Parameter CMN_STR_Office_ResponsiblePersonID of type Guid
_CMN_STR_Office_ResponsiblePersonID = reader.GetGuid(0);
//1:Parameter Office_RefID of type Guid
_Office_RefID = reader.GetGuid(1);
//2:Parameter CMN_BPT_EMP_Employee_RefID of type Guid
_CMN_BPT_EMP_Employee_RefID = reader.GetGuid(2);
//3:Parameter Creation_Timestamp of type DateTime
_Creation_Timestamp = reader.GetDate(3);
//4:Parameter IsDeleted of type Boolean
_IsDeleted = reader.GetBoolean(4);
//5:Parameter Tenant_RefID of type Guid
_Tenant_RefID = reader.GetGuid(5);
}
//Close the reader so other connections can use it
reader.Close();
loader.Load();
if (_CMN_STR_Office_ResponsiblePersonID != Guid.Empty)
{
//Successfully loaded
Status_IsAlreadySaved = true;
Status_IsDirty = false;
}
else
{
//Fault in loading due to invalid UUID (Guid)
Status_IsAlreadySaved = false;
Status_IsDirty = false;
}
}
catch (Exception ex)
{
throw;
}
#endregion
#region Cleanup Transaction/Connection
//If we started the transaction, we will commit it
if (cleanupTransaction && Transaction != null)
{
Transaction.Commit();
}
//If we opened the connection we will close it
if (cleanupConnection && Connection != null)
{
Connection.Close();
}
#endregion
} catch (Exception ex) {
try
{
if (cleanupTransaction == true && Transaction != null)
{
Transaction.Rollback();
}
}
catch { }
try
{
if (cleanupConnection == true && Connection != null)
{
Connection.Close();
}
}
catch { }
throw;
}
return(retStatus);
}
private FR_Base Load(DbConnection Connection, DbTransaction Transaction, Guid ObjectID, string ConnectionString)
{
//Standard return type
FR_Base retStatus = new FR_Base();
bool cleanupConnection = false;
bool cleanupTransaction = false;
try
{
#region Verify/Create Connections
//Create connection if Connection is null
if (Connection == null)
{
cleanupConnection = true;
Connection = CSV2Core_MySQL.Support.DBSQLSupport.CreateConnection(ConnectionString);
Connection.Open();
}
//If transaction is not open/not valid
if (Transaction == null)
{
cleanupTransaction = true;
Transaction = Connection.BeginTransaction();
}
#endregion
//Get the SelectQuerry
string SelectQuery = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("CL1_LOG_WRH.LOG_WRH_Rack.SQL.Select.sql")).ReadToEnd();
DbCommand command = Connection.CreateCommand();
//Set Connection/Transaction
command.Connection = Connection;
command.Transaction = Transaction;
//Set Query/Timeout
command.CommandText = SelectQuery;
command.CommandTimeout = QueryTimeout;
//Firstly, before loading, set the GUID to empty
//So the entity does not exist, it will have a GUID set to empty
_LOG_WRH_RackID = Guid.Empty;
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "LOG_WRH_RackID", ObjectID);
#region Command Execution
try
{
var loader = new CSV2Core_MySQL.Dictionaries.MultiTable.Loader.DictionaryLoader(Connection, Transaction);
var reader = new CSV2Core_MySQL.Support.DBSQLReader(command.ExecuteReader());
reader.SetOrdinals(new string[] { "LOG_WRH_RackID", "GlobalPropertyMatchingID", "Area_RefID", "Rack_Name", "Shelf_NamePrefix", "CoordinateCode", "Shelves_Use_XCoordinate", "Shelves_Use_YCoordinate", "Shelves_Use_ZCoordinate", "Shelves_XLabel", "Shelves_YLabel", "Shelves_ZLabel", "IsStructureHidden", "Creation_Timestamp", "Tenant_RefID", "IsDeleted" });
if (reader.HasRows == true)
{
reader.Read(); //Single result only
//0:Parameter LOG_WRH_RackID of type Guid
_LOG_WRH_RackID = reader.GetGuid(0);
//1:Parameter GlobalPropertyMatchingID of type String
_GlobalPropertyMatchingID = reader.GetString(1);
//2:Parameter Area_RefID of type Guid
_Area_RefID = reader.GetGuid(2);
//3:Parameter Rack_Name of type String
_Rack_Name = reader.GetString(3);
//4:Parameter Shelf_NamePrefix of type String
_Shelf_NamePrefix = reader.GetString(4);
//5:Parameter CoordinateCode of type String
_CoordinateCode = reader.GetString(5);
//6:Parameter Shelves_Use_XCoordinate of type Boolean
_Shelves_Use_XCoordinate = reader.GetBoolean(6);
//7:Parameter Shelves_Use_YCoordinate of type Boolean
_Shelves_Use_YCoordinate = reader.GetBoolean(7);
//8:Parameter Shelves_Use_ZCoordinate of type Boolean
_Shelves_Use_ZCoordinate = reader.GetBoolean(8);
//9:Parameter Shelves_XLabel of type String
_Shelves_XLabel = reader.GetString(9);
//10:Parameter Shelves_YLabel of type String
_Shelves_YLabel = reader.GetString(10);
//11:Parameter Shelves_ZLabel of type String
_Shelves_ZLabel = reader.GetString(11);
//12:Parameter IsStructureHidden of type Boolean
_IsStructureHidden = reader.GetBoolean(12);
//13:Parameter Creation_Timestamp of type DateTime
_Creation_Timestamp = reader.GetDate(13);
//14:Parameter Tenant_RefID of type Guid
_Tenant_RefID = reader.GetGuid(14);
//15:Parameter IsDeleted of type Boolean
_IsDeleted = reader.GetBoolean(15);
}
//Close the reader so other connections can use it
reader.Close();
loader.Load();
if (_LOG_WRH_RackID != Guid.Empty)
{
//Successfully loaded
Status_IsAlreadySaved = true;
Status_IsDirty = false;
}
else
{
//Fault in loading due to invalid UUID (Guid)
Status_IsAlreadySaved = false;
Status_IsDirty = false;
}
}
catch (Exception ex)
{
throw;
}
#endregion
#region Cleanup Transaction/Connection
//If we started the transaction, we will commit it
if (cleanupTransaction && Transaction != null)
{
Transaction.Commit();
}
//If we opened the connection we will close it
if (cleanupConnection && Connection != null)
{
Connection.Close();
}
#endregion
} catch (Exception ex) {
try
{
if (cleanupTransaction == true && Transaction != null)
{
Transaction.Rollback();
}
}
catch { }
try
{
if (cleanupConnection == true && Connection != null)
{
Connection.Close();
}
}
catch { }
throw;
}
return(retStatus);
}
/// <summary>
/// 开始一个事务
/// </summary>
/// <param name="connection">事务的连接<see cref="DbConnection"/></param>
/// <returns> 开始的事务</returns>
public DbTransaction BeginTransaction(DbConnection connection)
{
DbTransaction tran = connection.BeginTransaction();
return(tran);
}
/// <summary>
/// Decides whether to Call Add/Edit/Delete method
/// And Calls the appropriate method
/// </summary>
/// <returns>TransactionResult - Success / Failure</returns>
public TransactionResult Commit()
{
TransactionResult result = null;
Database db = DatabaseFactory.CreateDatabase(_myConnection.DatabaseName);
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
switch (ScreenMode)
{
case ScreenMode.Add:
//Adding Or Editing Employee
result = AddEditEmployeeBasicSalary(db, transaction);
if (result.Status == TransactionStatus.Failure)
{
transaction.Rollback();
return(result);
}
break;
case ScreenMode.Edit:
//Adding Employee Basic Salary
result = AddEmployeeBasicSalary(db, transaction);
if (result.Status == TransactionStatus.Failure)
{
transaction.Rollback();
return(result);
}
break;
case ScreenMode.Delete:
if (result.Status == TransactionStatus.Failure)
{
transaction.Rollback();
return(result);
}
break;
case ScreenMode.View:
break;
}
transaction.Commit();
return(result);
}
catch (Exception ex)
{
transaction.Rollback();
if (ScreenMode == ScreenMode.Add)
{
ErrorLog.LogErrorMessageToDB("", "EmployeeBasicSalaryDL.cs", "Commit For Add", ex.Message.ToString(), _myConnection);
return(new TransactionResult(TransactionStatus.Failure, "Failure Adding"));
}
if (ScreenMode == ScreenMode.Edit)
{
ErrorLog.LogErrorMessageToDB("", "EmployeeBasicSalaryDL.cs", "Commit For Edit", ex.Message.ToString(), _myConnection);
return(new TransactionResult(TransactionStatus.Failure, "Failure Updating"));
}
if (ScreenMode == ScreenMode.Delete)
{
ErrorLog.LogErrorMessageToDB("", "EmployeeBasicSalaryDL.cs", "Commit For Delete", ex.Message.ToString(), _myConnection);
return(new TransactionResult(TransactionStatus.Failure, "Failure Deleting"));
}
}
}
return(null);
}
/// <summary>
/// begin the database transaction.
/// </summary>
/// <param name="isolationLevel">The isolation level.</param>
/// <returns>The <see cref="DbTransaction"/>.</returns>
protected override DbTransaction BeginDbTransaction(IsolationLevel isolationLevel)
{
return(new ProfiledDbTransaction(_connection.BeginTransaction(isolationLevel), this));
}
protected FR_Base Save(DbConnection Connection, DbTransaction Transaction, string ConnectionString)
{
//Standard return type
FR_Base retStatus = new FR_Base();
bool cleanupConnection = false;
bool cleanupTransaction = false;
try
{
bool saveDictionary = false;
bool saveORMClass = !Status_IsAlreadySaved || Status_IsDirty;
//If Status Is Dirty (Meaning the data has been changed) or Status_IsAlreadySaved (Meaning the data is in the database, when loaded) just return
if (saveORMClass == false && saveDictionary == false)
{
return(FR_Base.Status_OK);
}
#region Verify/Create Connections
//Create Connection if Connection is null
if (Connection == null)
{
cleanupConnection = true;
Connection = CSV2Core_MySQL.Support.DBSQLSupport.CreateConnection(ConnectionString);
Connection.Open();
}
//Create Transaction if null
if (Transaction == null)
{
cleanupTransaction = true;
Transaction = Connection.BeginTransaction();
}
#endregion
#region Dictionary Management
//Save dictionary management
if (saveDictionary == true)
{
var loader = new CSV2Core_MySQL.Dictionaries.MultiTable.Loader.DictionaryLoader(Connection, Transaction);
//Save the dictionary or update based on if it has already been saved to the database
if (Status_IsAlreadySaved)
{
loader.Update();
}
else
{
loader.Save();
}
}
#endregion
#region Command Execution
if (saveORMClass == true)
{
//Retrieve Querry
string Query = "";
if (Status_IsAlreadySaved == true)
{
Query = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("CL1_ORD_CUO.ORD_CUO_CustomerOrder_Header.SQL.Update.sql")).ReadToEnd();
}
else
{
Query = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("CL1_ORD_CUO.ORD_CUO_CustomerOrder_Header.SQL.Insert.sql")).ReadToEnd();
}
DbCommand command = Connection.CreateCommand();
command.Connection = Connection;
command.Transaction = Transaction;
command.CommandText = Query;
command.CommandTimeout = QueryTimeout;
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "ORD_CUO_CustomerOrder_HeaderID", _ORD_CUO_CustomerOrder_HeaderID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "ProcurementOrderITL", _ProcurementOrderITL);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "Current_CustomerOrderStatus_RefID", _Current_CustomerOrderStatus_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "CustomerOrder_Number", _CustomerOrder_Number);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "CustomerOrder_Date", _CustomerOrder_Date);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "OrderingCustomer_BusinessParticipant_RefID", _OrderingCustomer_BusinessParticipant_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "CreatedBy_BusinessParticipant_RefID", _CreatedBy_BusinessParticipant_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "CanceledBy_BusinessParticipant_RefID", _CanceledBy_BusinessParticipant_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "CustomerOrder_Currency_RefID", _CustomerOrder_Currency_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "TotalValue_BeforeTax", _TotalValue_BeforeTax);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "IsCustomerOrderFinalized", _IsCustomerOrderFinalized);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "DeliveryDeadline", _DeliveryDeadline);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "IsPartialShippingAllowed", _IsPartialShippingAllowed);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "BillingAddressUCD_RefID", _BillingAddressUCD_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "ShippingAddressUCD_RefID", _ShippingAddressUCD_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "DeliveryRequestedFrom_Warehouse_RefID", _DeliveryRequestedFrom_Warehouse_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "CreatedAt_PointOfSale_RefID", _CreatedAt_PointOfSale_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "WasAutoApprovedUponReceipt", _WasAutoApprovedUponReceipt);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "Creation_Timestamp", _Creation_Timestamp);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "Tenant_RefID", _Tenant_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "IsDeleted", _IsDeleted);
try
{
var dbChangeCount = command.ExecuteNonQuery();
Status_IsAlreadySaved = true;
Status_IsDirty = false;
}
catch (Exception ex)
{
throw;
}
#endregion
#region Cleanup Transaction/Connection
//If we started the transaction, we will commit it
if (cleanupTransaction && Transaction != null)
{
Transaction.Commit();
}
//If we opened the connection we will close it
if (cleanupConnection && Connection != null)
{
Connection.Close();
}
}
#endregion
}
catch (Exception ex)
{
try
{
if (cleanupTransaction == true && Transaction != null)
{
Transaction.Rollback();
}
}
catch { }
try
{
if (cleanupConnection == true && Connection != null)
{
Connection.Close();
}
}
catch { }
throw;
}
return(retStatus);
}
protected void Page_Load(object sender, EventArgs e)
{
VerifyPage("", true);
var iid = Request.QueryString["iid"];
if (!string.IsNullOrEmpty(iid))
{
try
{
var imodel = _infosDal.GetModel(Convert.ToInt32(iid));
if (null != imodel)
{
Database db = DatabaseFactory.CreateDatabase();
using (DbConnection conn = db.CreateConnection())
{
conn.Open();
DbTransaction trans = conn.BeginTransaction();
try
{
var templatesql = "select TIID from TempInfo where ParentID=" + iid;
DbCommand dbComTemplate = db.GetSqlStringCommand(templatesql);
var dt = db.ExecuteDataSet(dbComTemplate, trans).Tables[0];
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
DelTemplate(db, trans, Convert.ToInt32(dt.Rows[i]["TIID"]));
}
}
var delinfo = "delete from InfoLabel where IID=" + iid + ";delete from AdInfos where Inf_IID=" + iid + ";delete from AdInfos where IID=" + iid + ";delete from AttaList where IID=" + iid + ";delete from Infos where IID=" + iid;
DbCommand dbComDelInfo = db.GetSqlStringCommand(delinfo);
db.ExecuteNonQuery(dbComDelInfo, trans);
trans.Commit();
conn.Close();
Response.Write("0|~|");
Response.End();
}
catch (System.Threading.ThreadAbortException ex)
{
}
catch (Exception ee)
{
trans.Rollback();
conn.Close();
Response.Write("1|~|" + ee.Message);
Response.End();
}
}
}
else
{
Response.Write("1|~|操作失败");
Response.End();
}
}
catch (System.Threading.ThreadAbortException ex)
{
}
catch (Exception ee)
{
Response.Write("1|~|" + ee.Message);
Response.End();
}
}
}
/// <summary>
/// Calls the specified procedure to find the result type.
/// </summary>
/// <param name="procedure">The procedure.</param>
/// <param name="connection">The database connection</param>
public void ExecuteProcedure(DatabaseStoredProcedure procedure, DbConnection connection)
{
//if we can't find the factory, we can't use the data adapter
if (_factory == null)
{
return;
}
var executionName = procedure.Name;
if (!string.IsNullOrEmpty(procedure.Package))
{
executionName = procedure.Package + "." + procedure.Name;
}
//for Oracle, sprocs with REF CURSORs indicate it returns something.
if (_isOracle && !procedure.Arguments.Any(a => a.DatabaseDataType == "REF CURSOR"))
{
return;
}
using (var resultSet = new DataSet {
Locale = CultureInfo.InvariantCulture
})
{
using (var command = connection.CreateCommand())
{
command.Connection = connection;
command.CommandText = executionName;
command.CommandTimeout = 5;
command.CommandType = CommandType.StoredProcedure;
AddParameters(procedure, command);
if (connection.State == ConnectionState.Closed)
{
connection.Open();
}
using (var tx = connection.BeginTransaction())
{
command.Transaction = tx;
using (DbDataAdapter adapter = _factory.CreateDataAdapter())
{
adapter.SelectCommand = command;
try
{
adapter.FillSchema(resultSet, SchemaType.Source);
}
catch (DbException exception)
{
//ignore any db exceptions
Debug.WriteLine(executionName + Environment.NewLine
+ exception.Message);
}
catch (Exception exception) //for exceptions that don't derive from DbException
{
//ignore any db exceptions
Debug.WriteLine(executionName + Environment.NewLine
+ exception.Message);
}
}
tx.Rollback();
}
}
UpdateProcedure(procedure, resultSet);
}
}
/// <summary>
/// 开始事务
/// </summary>
public void BeginTransaction()
{
transcation = connection.BeginTransaction();
}
/// <summary>
/// Deprovision a database. You have to passe a configuration object, containing at least the dmTables
/// </summary>
public async Task DeprovisionAsync(SyncConfiguration configuration, SyncProvision provision)
{
DbConnection connection = null;
try
{
if (configuration.Schema == null || !configuration.Schema.HasTables)
{
throw new ArgumentNullException("tables", "You must set the tables you want to provision");
}
// Load the configuration
await this.ReadSchemaAsync(configuration.Schema);
// Open the connection
using (connection = this.CreateConnection())
{
await connection.OpenAsync();
using (var transaction = connection.BeginTransaction())
{
for (int i = configuration.Count - 1; i >= 0; i--)
{
// Get the table
var dmTable = configuration.Schema.Tables[i];
// get the builder
var builder = GetDatabaseBuilder(dmTable);
builder.UseBulkProcedures = this.SupportBulkOperations;
// adding filters
this.AddFilters(configuration.Filters, dmTable, builder);
if (provision.HasFlag(SyncProvision.TrackingTable) || provision.HasFlag(SyncProvision.All))
{
builder.DropTrackingTable(connection, transaction);
}
if (provision.HasFlag(SyncProvision.StoredProcedures) || provision.HasFlag(SyncProvision.All))
{
builder.DropProcedures(connection, transaction);
}
if (provision.HasFlag(SyncProvision.Triggers) || provision.HasFlag(SyncProvision.All))
{
builder.DropTriggers(connection, transaction);
}
// On purpose, the flag SyncProvision.All does not include the SyncProvision.Table, too dangerous...
if (provision.HasFlag(SyncProvision.Table))
{
builder.DropTable(connection, transaction);
}
}
if (provision.HasFlag(SyncProvision.Scope) || provision.HasFlag(SyncProvision.All))
{
var scopeBuilder = GetScopeBuilder().CreateScopeInfoBuilder(configuration.ScopeInfoTableName, connection, transaction);
if (!scopeBuilder.NeedToCreateScopeInfoTable())
{
scopeBuilder.DropScopeInfoTable();
}
}
transaction.Commit();
}
}
}
catch (Exception ex)
{
throw new SyncException(ex, SyncStage.DatabaseApplying, this.ProviderTypeName);
}
finally
{
if (connection != null && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
/// <summary>
/// Be sure all tables are ready and configured for sync
/// the ScopeSet Configuration MUST be filled by the schema form Database
/// </summary>
public virtual async Task <SyncContext> EnsureDatabaseAsync(SyncContext context, MessageEnsureDatabase message)
{
DbConnection connection = null;
try
{
// Event progress
context.SyncStage = SyncStage.DatabaseApplying;
DatabaseApplyingEventArgs beforeArgs =
new DatabaseApplyingEventArgs(this.ProviderTypeName, context.SyncStage, message.Schema);
this.TryRaiseProgressEvent(beforeArgs, this.DatabaseApplying);
// If scope exists and lastdatetime sync is present, so database exists
// Check if we don't have an OverwriteConfiguration (if true, we force the check)
if (message.ScopeInfo.LastSync.HasValue && !beforeArgs.OverwriteSchema)
{
return(context);
}
StringBuilder script = new StringBuilder();
// Open the connection
using (connection = this.CreateConnection())
{
await connection.OpenAsync();
using (var transaction = connection.BeginTransaction())
{
// Sorting tables based on dependencies between them
var dmTables = message.Schema.Tables
.SortByDependencies(tab => tab.ChildRelations
.Select(r => r.ChildTable));
foreach (var dmTable in dmTables)
{
var builder = GetDatabaseBuilder(dmTable);
// set if the builder supports creating the bulk operations proc stock
builder.UseBulkProcedures = this.SupportBulkOperations;
// adding filter
this.AddFilters(message.Filters, dmTable, builder);
context.SyncStage = SyncStage.DatabaseTableApplying;
DatabaseTableApplyingEventArgs beforeTableArgs =
new DatabaseTableApplyingEventArgs(this.ProviderTypeName, context.SyncStage, dmTable.TableName);
this.TryRaiseProgressEvent(beforeTableArgs, this.DatabaseTableApplying);
string currentScript = null;
if (beforeArgs.GenerateScript)
{
currentScript = builder.ScriptTable(connection, transaction);
currentScript += builder.ScriptForeignKeys(connection, transaction);
script.Append(currentScript);
}
builder.Create(connection, transaction);
builder.CreateForeignKeys(connection, transaction);
context.SyncStage = SyncStage.DatabaseTableApplied;
DatabaseTableAppliedEventArgs afterTableArgs =
new DatabaseTableAppliedEventArgs(this.ProviderTypeName, context.SyncStage, dmTable.TableName, currentScript);
this.TryRaiseProgressEvent(afterTableArgs, this.DatabaseTableApplied);
}
context.SyncStage = SyncStage.DatabaseApplied;
var afterArgs = new DatabaseAppliedEventArgs(this.ProviderTypeName, context.SyncStage, script.ToString());
this.TryRaiseProgressEvent(afterArgs, this.DatabaseApplied);
transaction.Commit();
}
connection.Close();
return(context);
}
}
catch (Exception ex)
{
throw new SyncException(ex, SyncStage.DatabaseApplying, this.ProviderTypeName);
}
finally
{
if (connection != null && connection.State != ConnectionState.Closed)
{
connection.Close();
}
}
}
/// <summary>
/// Date Created: 29/Jul/2015
/// Created By: Josephine Monteza
/// (description) Save Hotel Forecast through Request
/// ----------------------------------------
/// </summary>
public void RequestHotelRoom(DateTime dDate, bool bIsSGL, bool bISDBL, Int32 iBranchID,
Int32 iBranchIDSGL, Int32 iBranchIDDBL, int iRoomCountSGL, int iRoomCountDBL,
string sUserName, string sDescription, string sFunction, string sFileName,
DateTime dDateGMT)
{
Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase();
DbCommand dbCommand = null;
DbConnection connection = db.CreateConnection();
connection.Open();
DbTransaction trans = connection.BeginTransaction();
try
{
string strTimeZone = TimeZone.CurrentTimeZone.StandardName.ToString();
dbCommand = db.GetStoredProcCommand("uspHotelForecastRequest");
db.AddInParameter(dbCommand, "@pDate", DbType.Date, dDate);
db.AddInParameter(dbCommand, "@pIsSGL", DbType.Boolean, bIsSGL);
db.AddInParameter(dbCommand, "@pIsDBL", DbType.Boolean, bISDBL);
db.AddInParameter(dbCommand, "@pBranchID", DbType.Int32, iBranchID);
db.AddInParameter(dbCommand, "@pBranchIDSGL", DbType.Int32, iBranchIDSGL);
db.AddInParameter(dbCommand, "@pBranchIDDBL", DbType.Int32, iBranchIDDBL);
db.AddInParameter(dbCommand, "@pRoomCountSGL", DbType.Int32, iRoomCountSGL);
db.AddInParameter(dbCommand, "@pRoomCountDBL", DbType.Int32, iRoomCountDBL);
db.AddInParameter(dbCommand, "@pUserId", DbType.String, sUserName);
db.AddInParameter(dbCommand, "@pDescription", DbType.String, sDescription);
db.AddInParameter(dbCommand, "@pFunction", DbType.String, sFunction);
db.AddInParameter(dbCommand, "@pFileName", DbType.String, sFileName);
db.AddInParameter(dbCommand, "@pTimezone", DbType.String, strTimeZone);
db.AddInParameter(dbCommand, "@pGMTDATE", DbType.DateTime, dDateGMT);
db.ExecuteNonQuery(dbCommand, trans);
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
if (dbCommand != null)
{
dbCommand.Dispose();
}
if (trans != null)
{
trans.Dispose();
}
}
}
protected override void SelectSingleEntity(int entityCount)
{
using (var db = new DbConnection())
{
db.BeginTransaction();
do
{
var entity = db.Entities.Single(x => x.Id == entityCount);
entityCount--;
}
while (entityCount > 0);
db.CommitTransaction();
}
}
internal virtual void BeginTransaction()
{
_transaction = _connection.BeginTransaction();
}
protected FR_Base Save(DbConnection Connection, DbTransaction Transaction, string ConnectionString)
{
//Standard return type
FR_Base retStatus = new FR_Base();
bool cleanupConnection = false;
bool cleanupTransaction = false;
try
{
bool saveDictionary = false;
bool saveORMClass = !Status_IsAlreadySaved || Status_IsDirty;
//If Status Is Dirty (Meaning the data has been changed) or Status_IsAlreadySaved (Meaning the data is in the database, when loaded) just return
if (saveORMClass == false && saveDictionary == false)
{
return(FR_Base.Status_OK);
}
#region Verify/Create Connections
//Create Connection if Connection is null
if (Connection == null)
{
cleanupConnection = true;
Connection = CSV2Core_MySQL.Support.DBSQLSupport.CreateConnection(ConnectionString);
Connection.Open();
}
//Create Transaction if null
if (Transaction == null)
{
cleanupTransaction = true;
Transaction = Connection.BeginTransaction();
}
#endregion
#region Dictionary Management
//Save dictionary management
if (saveDictionary == true)
{
var loader = new CSV2Core_MySQL.Dictionaries.MultiTable.Loader.DictionaryLoader(Connection, Transaction);
//Save the dictionary or update based on if it has already been saved to the database
if (Status_IsAlreadySaved)
{
loader.Update();
}
else
{
loader.Save();
}
}
#endregion
#region Command Execution
if (saveORMClass == true)
{
//Retrieve Querry
string Query = "";
if (Status_IsAlreadySaved == true)
{
Query = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("CL1_BIL.BIL_BillPosition.SQL.Update.sql")).ReadToEnd();
}
else
{
Query = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("CL1_BIL.BIL_BillPosition.SQL.Insert.sql")).ReadToEnd();
}
DbCommand command = Connection.CreateCommand();
command.Connection = Connection;
command.Transaction = Transaction;
command.CommandText = Query;
command.CommandTimeout = QueryTimeout;
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "BIL_BillPositionID", _BIL_BillPositionID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "BIL_BilHeader_RefID", _BIL_BilHeader_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "BIL_BillPosition_Group_RefID", _BIL_BillPosition_Group_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "ApplicableSalesTax_RefID", _ApplicableSalesTax_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "ApplicableSalesTax_Value", _ApplicableSalesTax_Value);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "ApplicableSalesTax_PerUnitValue", _ApplicableSalesTax_PerUnitValue);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "PositionNumber", _PositionNumber);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "BillPosition_Description", _BillPosition_Description);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "BillPosition_Comment", _BillPosition_Comment);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "PositionIndex", _PositionIndex);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "PositionValue_BeforeTax", _PositionValue_BeforeTax);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "PositionValue_IncludingTax", _PositionValue_IncludingTax);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "PositionPricePerUnitValue_BeforeTax", _PositionPricePerUnitValue_BeforeTax);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "PositionPricePerUnitValue_IncludingTax", _PositionPricePerUnitValue_IncludingTax);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "BillPosition_ProductNumber", _BillPosition_ProductNumber);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "Quantity", _Quantity);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "External_PositionReferenceField", _External_PositionReferenceField);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "External_PositionCode", _External_PositionCode);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "External_PositionType", _External_PositionType);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "IsFullyPaid", _IsFullyPaid);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "Creation_Timestamp", _Creation_Timestamp);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "Tenant_RefID", _Tenant_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "IsDeleted", _IsDeleted);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "Modification_Timestamp", _Modification_Timestamp);
try
{
var dbChangeCount = command.ExecuteNonQuery();
Status_IsAlreadySaved = true;
Status_IsDirty = false;
}
catch (Exception ex)
{
throw;
}
#endregion
#region Cleanup Transaction/Connection
//If we started the transaction, we will commit it
if (cleanupTransaction && Transaction != null)
{
Transaction.Commit();
}
//If we opened the connection we will close it
if (cleanupConnection && Connection != null)
{
Connection.Close();
}
}
#endregion
}
catch (Exception ex)
{
try
{
if (cleanupTransaction == true && Transaction != null)
{
Transaction.Rollback();
}
}
catch { }
try
{
if (cleanupConnection == true && Connection != null)
{
Connection.Close();
}
}
catch { }
throw;
}
return(retStatus);
}
private FR_Base Load(DbConnection Connection, DbTransaction Transaction, Guid ObjectID, string ConnectionString)
{
//Standard return type
FR_Base retStatus = new FR_Base();
bool cleanupConnection = false;
bool cleanupTransaction = false;
try
{
#region Verify/Create Connections
//Create connection if Connection is null
if (Connection == null)
{
cleanupConnection = true;
Connection = CSV2Core_MySQL.Support.DBSQLSupport.CreateConnection(ConnectionString);
Connection.Open();
}
//If transaction is not open/not valid
if (Transaction == null)
{
cleanupTransaction = true;
Transaction = Connection.BeginTransaction();
}
#endregion
//Get the SelectQuerry
string SelectQuery = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("CL1_ORD_CUO.ORD_CUO_CustomerOrder_Header.SQL.Select.sql")).ReadToEnd();
DbCommand command = Connection.CreateCommand();
//Set Connection/Transaction
command.Connection = Connection;
command.Transaction = Transaction;
//Set Query/Timeout
command.CommandText = SelectQuery;
command.CommandTimeout = QueryTimeout;
//Firstly, before loading, set the GUID to empty
//So the entity does not exist, it will have a GUID set to empty
_ORD_CUO_CustomerOrder_HeaderID = Guid.Empty;
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "ORD_CUO_CustomerOrder_HeaderID", ObjectID);
#region Command Execution
try
{
var loader = new CSV2Core_MySQL.Dictionaries.MultiTable.Loader.DictionaryLoader(Connection, Transaction);
var reader = new CSV2Core_MySQL.Support.DBSQLReader(command.ExecuteReader());
reader.SetOrdinals(new string[] { "ORD_CUO_CustomerOrder_HeaderID", "ProcurementOrderITL", "Current_CustomerOrderStatus_RefID", "CustomerOrder_Number", "CustomerOrder_Date", "OrderingCustomer_BusinessParticipant_RefID", "CreatedBy_BusinessParticipant_RefID", "CanceledBy_BusinessParticipant_RefID", "CustomerOrder_Currency_RefID", "TotalValue_BeforeTax", "IsCustomerOrderFinalized", "DeliveryDeadline", "IsPartialShippingAllowed", "BillingAddressUCD_RefID", "ShippingAddressUCD_RefID", "DeliveryRequestedFrom_Warehouse_RefID", "CreatedAt_PointOfSale_RefID", "WasAutoApprovedUponReceipt", "Creation_Timestamp", "Tenant_RefID", "IsDeleted" });
if (reader.HasRows == true)
{
reader.Read(); //Single result only
//0:Parameter ORD_CUO_CustomerOrder_HeaderID of type Guid
_ORD_CUO_CustomerOrder_HeaderID = reader.GetGuid(0);
//1:Parameter ProcurementOrderITL of type String
_ProcurementOrderITL = reader.GetString(1);
//2:Parameter Current_CustomerOrderStatus_RefID of type Guid
_Current_CustomerOrderStatus_RefID = reader.GetGuid(2);
//3:Parameter CustomerOrder_Number of type String
_CustomerOrder_Number = reader.GetString(3);
//4:Parameter CustomerOrder_Date of type DateTime
_CustomerOrder_Date = reader.GetDate(4);
//5:Parameter OrderingCustomer_BusinessParticipant_RefID of type Guid
_OrderingCustomer_BusinessParticipant_RefID = reader.GetGuid(5);
//6:Parameter CreatedBy_BusinessParticipant_RefID of type Guid
_CreatedBy_BusinessParticipant_RefID = reader.GetGuid(6);
//7:Parameter CanceledBy_BusinessParticipant_RefID of type Guid
_CanceledBy_BusinessParticipant_RefID = reader.GetGuid(7);
//8:Parameter CustomerOrder_Currency_RefID of type Guid
_CustomerOrder_Currency_RefID = reader.GetGuid(8);
//9:Parameter TotalValue_BeforeTax of type Decimal
_TotalValue_BeforeTax = reader.GetDecimal(9);
//10:Parameter IsCustomerOrderFinalized of type Boolean
_IsCustomerOrderFinalized = reader.GetBoolean(10);
//11:Parameter DeliveryDeadline of type DateTime
_DeliveryDeadline = reader.GetDate(11);
//12:Parameter IsPartialShippingAllowed of type Boolean
_IsPartialShippingAllowed = reader.GetBoolean(12);
//13:Parameter BillingAddressUCD_RefID of type Guid
_BillingAddressUCD_RefID = reader.GetGuid(13);
//14:Parameter ShippingAddressUCD_RefID of type Guid
_ShippingAddressUCD_RefID = reader.GetGuid(14);
//15:Parameter DeliveryRequestedFrom_Warehouse_RefID of type Guid
_DeliveryRequestedFrom_Warehouse_RefID = reader.GetGuid(15);
//16:Parameter CreatedAt_PointOfSale_RefID of type Guid
_CreatedAt_PointOfSale_RefID = reader.GetGuid(16);
//17:Parameter WasAutoApprovedUponReceipt of type Boolean
_WasAutoApprovedUponReceipt = reader.GetBoolean(17);
//18:Parameter Creation_Timestamp of type DateTime
_Creation_Timestamp = reader.GetDate(18);
//19:Parameter Tenant_RefID of type Guid
_Tenant_RefID = reader.GetGuid(19);
//20:Parameter IsDeleted of type Boolean
_IsDeleted = reader.GetBoolean(20);
}
//Close the reader so other connections can use it
reader.Close();
loader.Load();
if (_ORD_CUO_CustomerOrder_HeaderID != Guid.Empty)
{
//Successfully loaded
Status_IsAlreadySaved = true;
Status_IsDirty = false;
}
else
{
//Fault in loading due to invalid UUID (Guid)
Status_IsAlreadySaved = false;
Status_IsDirty = false;
}
}
catch (Exception ex)
{
throw;
}
#endregion
#region Cleanup Transaction/Connection
//If we started the transaction, we will commit it
if (cleanupTransaction && Transaction != null)
{
Transaction.Commit();
}
//If we opened the connection we will close it
if (cleanupConnection && Connection != null)
{
Connection.Close();
}
#endregion
} catch (Exception ex) {
try
{
if (cleanupTransaction == true && Transaction != null)
{
Transaction.Rollback();
}
}
catch { }
try
{
if (cleanupConnection == true && Connection != null)
{
Connection.Close();
}
}
catch { }
throw;
}
return(retStatus);
}
private FR_Base Load(DbConnection Connection, DbTransaction Transaction, Guid ObjectID, string ConnectionString)
{
//Standard return type
FR_Base retStatus = new FR_Base();
bool cleanupConnection = false;
bool cleanupTransaction = false;
try
{
#region Verify/Create Connections
//Create connection if Connection is null
if (Connection == null)
{
cleanupConnection = true;
Connection = CSV2Core_MySQL.Support.DBSQLSupport.CreateConnection(ConnectionString);
Connection.Open();
}
//If transaction is not open/not valid
if (Transaction == null)
{
cleanupTransaction = true;
Transaction = Connection.BeginTransaction();
}
#endregion
//Get the SelectQuerry
string SelectQuery = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("CL1_BIL.BIL_BillPosition.SQL.Select.sql")).ReadToEnd();
DbCommand command = Connection.CreateCommand();
//Set Connection/Transaction
command.Connection = Connection;
command.Transaction = Transaction;
//Set Query/Timeout
command.CommandText = SelectQuery;
command.CommandTimeout = QueryTimeout;
//Firstly, before loading, set the GUID to empty
//So the entity does not exist, it will have a GUID set to empty
_BIL_BillPositionID = Guid.Empty;
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "BIL_BillPositionID", ObjectID);
#region Command Execution
try
{
var loader = new CSV2Core_MySQL.Dictionaries.MultiTable.Loader.DictionaryLoader(Connection, Transaction);
var reader = new CSV2Core_MySQL.Support.DBSQLReader(command.ExecuteReader());
reader.SetOrdinals(new string[] { "BIL_BillPositionID", "BIL_BilHeader_RefID", "BIL_BillPosition_Group_RefID", "ApplicableSalesTax_RefID", "ApplicableSalesTax_Value", "ApplicableSalesTax_PerUnitValue", "PositionNumber", "BillPosition_Description", "BillPosition_Comment", "PositionIndex", "PositionValue_BeforeTax", "PositionValue_IncludingTax", "PositionPricePerUnitValue_BeforeTax", "PositionPricePerUnitValue_IncludingTax", "BillPosition_ProductNumber", "Quantity", "External_PositionReferenceField", "External_PositionCode", "External_PositionType", "IsFullyPaid", "Creation_Timestamp", "Tenant_RefID", "IsDeleted", "Modification_Timestamp" });
if (reader.HasRows == true)
{
reader.Read(); //Single result only
//0:Parameter BIL_BillPositionID of type Guid
_BIL_BillPositionID = reader.GetGuid(0);
//1:Parameter BIL_BilHeader_RefID of type Guid
_BIL_BilHeader_RefID = reader.GetGuid(1);
//2:Parameter BIL_BillPosition_Group_RefID of type Guid
_BIL_BillPosition_Group_RefID = reader.GetGuid(2);
//3:Parameter ApplicableSalesTax_RefID of type Guid
_ApplicableSalesTax_RefID = reader.GetGuid(3);
//4:Parameter ApplicableSalesTax_Value of type Decimal
_ApplicableSalesTax_Value = reader.GetDecimal(4);
//5:Parameter ApplicableSalesTax_PerUnitValue of type Decimal
_ApplicableSalesTax_PerUnitValue = reader.GetDecimal(5);
//6:Parameter PositionNumber of type String
_PositionNumber = reader.GetString(6);
//7:Parameter BillPosition_Description of type String
_BillPosition_Description = reader.GetString(7);
//8:Parameter BillPosition_Comment of type String
_BillPosition_Comment = reader.GetString(8);
//9:Parameter PositionIndex of type int
_PositionIndex = reader.GetInteger(9);
//10:Parameter PositionValue_BeforeTax of type Decimal
_PositionValue_BeforeTax = reader.GetDecimal(10);
//11:Parameter PositionValue_IncludingTax of type Decimal
_PositionValue_IncludingTax = reader.GetDecimal(11);
//12:Parameter PositionPricePerUnitValue_BeforeTax of type Decimal
_PositionPricePerUnitValue_BeforeTax = reader.GetDecimal(12);
//13:Parameter PositionPricePerUnitValue_IncludingTax of type Decimal
_PositionPricePerUnitValue_IncludingTax = reader.GetDecimal(13);
//14:Parameter BillPosition_ProductNumber of type String
_BillPosition_ProductNumber = reader.GetString(14);
//15:Parameter Quantity of type double
_Quantity = reader.GetDouble(15);
//16:Parameter External_PositionReferenceField of type String
_External_PositionReferenceField = reader.GetString(16);
//17:Parameter External_PositionCode of type String
_External_PositionCode = reader.GetString(17);
//18:Parameter External_PositionType of type String
_External_PositionType = reader.GetString(18);
//19:Parameter IsFullyPaid of type Boolean
_IsFullyPaid = reader.GetBoolean(19);
//20:Parameter Creation_Timestamp of type DateTime
_Creation_Timestamp = reader.GetDate(20);
//21:Parameter Tenant_RefID of type Guid
_Tenant_RefID = reader.GetGuid(21);
//22:Parameter IsDeleted of type Boolean
_IsDeleted = reader.GetBoolean(22);
//23:Parameter Modification_Timestamp of type DateTime
_Modification_Timestamp = reader.GetDate(23);
}
//Close the reader so other connections can use it
reader.Close();
loader.Load();
if (_BIL_BillPositionID != Guid.Empty)
{
//Successfully loaded
Status_IsAlreadySaved = true;
Status_IsDirty = false;
}
else
{
//Fault in loading due to invalid UUID (Guid)
Status_IsAlreadySaved = false;
Status_IsDirty = false;
}
}
catch (Exception ex)
{
throw;
}
#endregion
#region Cleanup Transaction/Connection
//If we started the transaction, we will commit it
if (cleanupTransaction && Transaction != null)
{
Transaction.Commit();
}
//If we opened the connection we will close it
if (cleanupConnection && Connection != null)
{
Connection.Close();
}
#endregion
} catch (Exception ex) {
try
{
if (cleanupTransaction == true && Transaction != null)
{
Transaction.Rollback();
}
}
catch { }
try
{
if (cleanupConnection == true && Connection != null)
{
Connection.Close();
}
}
catch { }
throw;
}
return(retStatus);
}
/// <summary>
/// Retorna o ID gerado para o objeto especificado.
/// </summary>
public int Insert(LancamentoProdutoServicoDTO orcamentoitem)
{
DbConnection connection = null;
bool transactionstart = false;
try
{
using (connection = db.CreateConnection())
{
connection.Open();
if (Transaction == null)
{
Transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
transactionstart = true;
}
dbCommand = db.GetSqlStringCommand(" INSERT INTO " + "public" + ".orcamentoitem(" +
" id_orcamento," +
" id_produto," +
" valorunitario," +
" valortotal," +
" valordesconto," +
" valorliquido" +
") VALUES (" +
" @id_orcamento," +
" @id_produto," +
" @valorunitario," +
" @valortotal," +
" @valordesconto," +
" @valorliquido);" +
" SELECT currval('" + "public" + ".orcamentoitem_id_seq');");
db.AddInParameter(dbCommand, "@id_orcamento", DbType.Int32, orcamentoitem.Id_orcamento);
db.AddInParameter(dbCommand, "@id_produto", DbType.Int32, orcamentoitem.Produto.Id);
db.AddInParameter(dbCommand, "@valorunitario", DbType.Decimal, orcamentoitem.ValorUnitario);
db.AddInParameter(dbCommand, "@valortotal", DbType.Decimal, orcamentoitem.ValorTotal);
db.AddInParameter(dbCommand, "@valordesconto", DbType.Decimal, orcamentoitem.ValorDesconto);
db.AddInParameter(dbCommand, "@valorliquido", DbType.Decimal, orcamentoitem.ValorLiquido);
int id = Convert.ToInt32(db.ExecuteScalar(dbCommand, Transaction));
if (transactionstart)
{
Transaction.Commit();
Transaction = null;
}
connection.Close();
return(id);
}
}
catch
{
if (transactionstart)
{
if (Transaction.Connection.State == ConnectionState.Open)
{
Transaction.Rollback();
}
Transaction = null;
}
if (connection.State == ConnectionState.Open)
{
connection.Close();
}
throw;
}
}
public bool Save <T>(T item) where T : IContract
{
var result = 0;
var apcreditnote = (APCreditNote)(object)item;
if (currentTransaction == null)
{
connection = db.CreateConnection();
connection.Open();
}
var transaction = (currentTransaction == null ? connection.BeginTransaction() : currentTransaction);
try
{
var savecommand = db.GetStoredProcCommand(DBRoutine.SAVEAPCREDITNOTE);
db.AddInParameter(savecommand, "DocumentNo", System.Data.DbType.String, apcreditnote.DocumentNo);
db.AddInParameter(savecommand, "DocumentDate", System.Data.DbType.DateTime, apcreditnote.DocumentDate);
db.AddInParameter(savecommand, "BranchID", System.Data.DbType.Int16, apcreditnote.BranchID);
db.AddInParameter(savecommand, "ReferenceNo", System.Data.DbType.String, apcreditnote.ReferenceNo);
db.AddInParameter(savecommand, "CreditorCode", System.Data.DbType.String, apcreditnote.CreditorCode);
db.AddInParameter(savecommand, "CreditTerm", System.Data.DbType.String, apcreditnote.CreditTerm);
db.AddInParameter(savecommand, "CurrencyCode", System.Data.DbType.String, apcreditnote.CurrencyCode);
db.AddInParameter(savecommand, "ExchangeRate", System.Data.DbType.Decimal, apcreditnote.ExchangeRate);
db.AddInParameter(savecommand, "CreditorAccount", System.Data.DbType.String, apcreditnote.CreditorAccount);
db.AddInParameter(savecommand, "BaseAmount", System.Data.DbType.Decimal, apcreditnote.BaseAmount);
db.AddInParameter(savecommand, "LocalAmount", System.Data.DbType.Decimal, apcreditnote.LocalAmount);
db.AddInParameter(savecommand, "TaxAmount", System.Data.DbType.Decimal, apcreditnote.TaxAmount);
db.AddInParameter(savecommand, "TotalAmount", System.Data.DbType.Decimal, apcreditnote.TotalAmount);
db.AddInParameter(savecommand, "Remark", System.Data.DbType.String, apcreditnote.Remark);
db.AddInParameter(savecommand, "Source", System.Data.DbType.String, apcreditnote.Source);
db.AddInParameter(savecommand, "CreatedBy", System.Data.DbType.String, apcreditnote.CreatedBy);
db.AddInParameter(savecommand, "ModifiedBy", System.Data.DbType.String, apcreditnote.ModifiedBy);
db.AddOutParameter(savecommand, "NewDocumentNo", System.Data.DbType.String, 25);
result = db.ExecuteNonQuery(savecommand, transaction);
if (result > 0)
{
var apDetailsItemDAL = new APCreditNoteDetailDAL();
// Get the New Quotation No.
apcreditnote.DocumentNo = savecommand.Parameters["@NewDocumentNo"].Value.ToString();
apcreditnote.APCreditNoteDetails.ForEach(dt =>
{
dt.DocumentNo = apcreditnote.DocumentNo;
dt.CreatedBy = apcreditnote.CreatedBy;
dt.ModifiedBy = apcreditnote.ModifiedBy;
}
);
result = Convert.ToInt16(apDetailsItemDAL.Delete(apcreditnote.DocumentNo, transaction));
result = apDetailsItemDAL.SaveList(apcreditnote.APCreditNoteDetails, transaction) == true ? 1 : 0;
}
if (result > 0)
{
transaction.Commit();
}
else
{
transaction.Rollback();
}
}
catch (Exception)
{
if (currentTransaction == null)
{
transaction.Rollback();
}
throw;
}
return(result > 0 ? true : false);
}
public bool DelPackType(Model.InfoType model)
{
Database db = DatabaseFactory.CreateDatabase();
bool result = false;
using (DbConnection conn = db.CreateConnection())
{
conn.Open();
DbTransaction trans = conn.BeginTransaction();
try
{
#region 除分类及资讯
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from AdInfos where ");
strSql.Append(
" Inf_IID in(select IID from dbo.Infos where TIID in (select TIID from dbo.TempInfo where ITID=@ITID )); ");
strSql.Append("delete from InfoLabel where ");
strSql.Append(
" IID in(select IID from dbo.Infos where TIID in (select TIID from dbo.TempInfo where ITID=@ITID )); ");
strSql.Append("delete from AttaList where ");
strSql.Append(
" IID in(select IID from dbo.Infos where TIID in (select TIID from dbo.TempInfo where ITID=@ITID )); ");
//strSql.Append(" delete from TmpInfoList where TIID in (select TIID from dbo.TempInfo where ITID=@ITID );");
strSql.Append(" delete from Infos where TIID in (select TIID from dbo.TempInfo where ITID=@ITID );");
strSql.Append(" delete from TempInfo where ITID=@ITID;");
strSql.Append(" delete from InfoType where ITID=@ITID;");
DbCommand dbCommand = db.GetSqlStringCommand(strSql.ToString());
db.AddInParameter(dbCommand, "ITID", DbType.Int32, model.ITID);
object obj = db.ExecuteNonQuery(dbCommand, trans);
#endregion
StringBuilder strSql3 = new StringBuilder();
strSql3.Append("select * from InfoType where SPID=" + model.SPID + " and SortNum>" + model.SortNum);
DbCommand dbCommandDel = db.GetSqlStringCommand(strSql3.ToString());
var dt = db.ExecuteDataSet(dbCommandDel, trans).Tables[0];
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
var sql = "update InfoType set SortNum=SortNum-1 where ITID=" +
dt.Rows[i]["ITID"];
DbCommand dbCommand2 = db.GetSqlStringCommand(sql.ToString());
db.ExecuteNonQuery(dbCommand2, trans);
}
}
trans.Commit();
result = true;
}
catch
{
trans.Rollback();
}
conn.Close();
return(result);
}
}
private FR_Base Load(DbConnection Connection, DbTransaction Transaction, Guid ObjectID, string ConnectionString)
{
//Standard return type
FR_Base retStatus = new FR_Base();
bool cleanupConnection = false;
bool cleanupTransaction = false;
try
{
#region Verify/Create Connections
//Create connection if Connection is null
if (Connection == null)
{
cleanupConnection = true;
Connection = CSV2Core_MySQL.Support.DBSQLSupport.CreateConnection(ConnectionString);
Connection.Open();
}
//If transaction is not open/not valid
if (Transaction == null)
{
cleanupTransaction = true;
Transaction = Connection.BeginTransaction();
}
#endregion
//Get the SelectQuerry
string SelectQuery = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("CL1_IMO.IMO_SubmissionBundle.SQL.Select.sql")).ReadToEnd();
DbCommand command = Connection.CreateCommand();
//Set Connection/Transaction
command.Connection = Connection;
command.Transaction = Transaction;
//Set Query/Timeout
command.CommandText = SelectQuery;
command.CommandTimeout = QueryTimeout;
//Firstly, before loading, set the GUID to empty
//So the entity does not exist, it will have a GUID set to empty
_IMO_SubmissionBundleID = Guid.Empty;
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "IMO_SubmissionBundleID", ObjectID);
#region Command Execution
try
{
var loader = new CSV2Core_MySQL.Dictionaries.MultiTable.Loader.DictionaryLoader(Connection, Transaction);
var reader = new CSV2Core_MySQL.Support.DBSQLReader(command.ExecuteReader());
reader.SetOrdinals(new string[] { "IMO_SubmissionBundleID", "WorkerAccount_RefID", "Language_RefID", "Bundle_Name", "Bundle_Description", "DueDate", "GrabbedAtDate", "GrabbedByAccount_RefID", "IsGlobalBundle", "IsCompleted", "IsGrabbed", "Creation_Timestamp", "IsDeleted", "Tenant_RefID" });
if (reader.HasRows == true)
{
reader.Read(); //Single result only
//0:Parameter IMO_SubmissionBundleID of type Guid
_IMO_SubmissionBundleID = reader.GetGuid(0);
//1:Parameter WorkerAccount_RefID of type Guid
_WorkerAccount_RefID = reader.GetGuid(1);
//2:Parameter Language_RefID of type Guid
_Language_RefID = reader.GetGuid(2);
//3:Parameter Bundle_Name of type String
_Bundle_Name = reader.GetString(3);
//4:Parameter Bundle_Description of type String
_Bundle_Description = reader.GetString(4);
//5:Parameter DueDate of type DateTime
_DueDate = reader.GetDate(5);
//6:Parameter GrabbedAtDate of type DateTime
_GrabbedAtDate = reader.GetDate(6);
//7:Parameter GrabbedByAccount_RefID of type Guid
_GrabbedByAccount_RefID = reader.GetGuid(7);
//8:Parameter IsGlobalBundle of type Boolean
_IsGlobalBundle = reader.GetBoolean(8);
//9:Parameter IsCompleted of type Boolean
_IsCompleted = reader.GetBoolean(9);
//10:Parameter IsGrabbed of type Boolean
_IsGrabbed = reader.GetBoolean(10);
//11:Parameter Creation_Timestamp of type DateTime
_Creation_Timestamp = reader.GetDate(11);
//12:Parameter IsDeleted of type Boolean
_IsDeleted = reader.GetBoolean(12);
//13:Parameter Tenant_RefID of type Guid
_Tenant_RefID = reader.GetGuid(13);
}
//Close the reader so other connections can use it
reader.Close();
loader.Load();
if (_IMO_SubmissionBundleID != Guid.Empty)
{
//Successfully loaded
Status_IsAlreadySaved = true;
Status_IsDirty = false;
}
else
{
//Fault in loading due to invalid UUID (Guid)
Status_IsAlreadySaved = false;
Status_IsDirty = false;
}
}
catch (Exception ex)
{
throw ex;
}
#endregion
#region Cleanup Transaction/Connection
//If we started the transaction, we will commit it
if (cleanupTransaction && Transaction != null)
{
Transaction.Commit();
}
//If we opened the connection we will close it
if (cleanupConnection && Connection != null)
{
Connection.Close();
}
#endregion
} catch (Exception ex) {
try
{
if (cleanupTransaction == true && Transaction != null)
{
Transaction.Rollback();
}
}
catch { }
try
{
if (cleanupConnection == true && Connection != null)
{
Connection.Close();
}
}
catch { }
throw ex;
}
return(retStatus);
}
/// <summary>
/// 开始一个事务
/// </summary>
/// <returns> 开始的事务</returns>
public DbTransaction BeginTransaction(DbConnection connection, IsolationLevel isolateLevel)
{
DbTransaction tran = connection.BeginTransaction(isolateLevel);
return(tran);
}
public virtual void BeginTransaction()
{
_transaction = _connection?.BeginTransaction();
}
protected FR_Base Save(DbConnection Connection, DbTransaction Transaction, string ConnectionString)
{
//Standard return type
FR_Base retStatus = new FR_Base();
bool cleanupConnection = false;
bool cleanupTransaction = false;
try
{
bool saveDictionary = false;
bool saveORMClass = !Status_IsAlreadySaved || Status_IsDirty;
//If Status Is Dirty (Meaning the data has been changed) or Status_IsAlreadySaved (Meaning the data is in the database, when loaded) just return
if (saveORMClass == false && saveDictionary == false)
{
return(FR_Base.Status_OK);
}
#region Verify/Create Connections
//Create Connection if Connection is null
if (Connection == null)
{
cleanupConnection = true;
Connection = CSV2Core_MySQL.Support.DBSQLSupport.CreateConnection(ConnectionString);
Connection.Open();
}
//Create Transaction if null
if (Transaction == null)
{
cleanupTransaction = true;
Transaction = Connection.BeginTransaction();
}
#endregion
#region Dictionary Management
//Save dictionary management
if (saveDictionary == true)
{
var loader = new CSV2Core_MySQL.Dictionaries.MultiTable.Loader.DictionaryLoader(Connection, Transaction);
//Save the dictionary or update based on if it has already been saved to the database
if (Status_IsAlreadySaved)
{
loader.Update();
}
else
{
loader.Save();
}
}
#endregion
#region Command Execution
if (saveORMClass == true)
{
//Retrieve Querry
string Query = "";
if (Status_IsAlreadySaved == true)
{
Query = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("CL1_CMN_STR.CMN_STR_Office_ResponsiblePerson.SQL.Update.sql")).ReadToEnd();
}
else
{
Query = new System.IO.StreamReader(System.Reflection.Assembly.GetExecutingAssembly().GetManifestResourceStream("CL1_CMN_STR.CMN_STR_Office_ResponsiblePerson.SQL.Insert.sql")).ReadToEnd();
}
DbCommand command = Connection.CreateCommand();
command.Connection = Connection;
command.Transaction = Transaction;
command.CommandText = Query;
command.CommandTimeout = QueryTimeout;
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "CMN_STR_Office_ResponsiblePersonID", _CMN_STR_Office_ResponsiblePersonID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "Office_RefID", _Office_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "CMN_BPT_EMP_Employee_RefID", _CMN_BPT_EMP_Employee_RefID);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "Creation_Timestamp", _Creation_Timestamp);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "IsDeleted", _IsDeleted);
CSV2Core_MySQL.Support.DBSQLSupport.SetParameter(command, "Tenant_RefID", _Tenant_RefID);
try
{
var dbChangeCount = command.ExecuteNonQuery();
Status_IsAlreadySaved = true;
Status_IsDirty = false;
}
catch (Exception ex)
{
throw;
}
#endregion
#region Cleanup Transaction/Connection
//If we started the transaction, we will commit it
if (cleanupTransaction && Transaction != null)
{
Transaction.Commit();
}
//If we opened the connection we will close it
if (cleanupConnection && Connection != null)
{
Connection.Close();
}
}
#endregion
}
catch (Exception ex)
{
try
{
if (cleanupTransaction == true && Transaction != null)
{
Transaction.Rollback();
}
}
catch { }
try
{
if (cleanupConnection == true && Connection != null)
{
Connection.Close();
}
}
catch { }
throw;
}
return(retStatus);
}
/// <summary>
/// Inclui um AdmFuncionalidadeRelGrupo
/// </summary>
/// <autor>Fernando Carvalho</autor>
/// <param name="objAdmFuncionalidadeRelGrupo">Entidade do AdmFuncionalidadeRelGrupo</param>
/// <returns>Entidade de AdmFuncionalidadeRelGrupo</returns>
public String InserirCustom(List <EntAdmFuncionalidadeRelGrupoCustom> lstAdmFuncionalidadeRelGrupo, Int32 IdPrograma)
{
String msg = "";
using (DbConnection connection = db.CreateConnection())
{
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
if (lstAdmFuncionalidadeRelGrupo.Count > 0)
{
EntAdmGrupo objAdmGrupo = lstAdmFuncionalidadeRelGrupo[0].AdmGrupo;
if (objAdmGrupo.IdGrupo == 0)
{
if (dalAdmGrupo.ExisteGrupo(objAdmGrupo.Grupo, IdPrograma, transaction, db))
{
msg = "Grupo de Acesso já existente!";
}
else
{
dalAdmGrupo.Inserir(objAdmGrupo, IdPrograma, transaction, db);
msg = "Grupo de Acesso inserido com sucesso!";
}
}
else
{
dalAdmGrupo.Alterar(objAdmGrupo, transaction, db);
msg = "Grupo de Acesso Alterado com sucesso!";
}
foreach (EntAdmFuncionalidadeRelGrupoCustom objAdmFuncionalidadeRelGrupoCustom in lstAdmFuncionalidadeRelGrupo)
{
EntAdmFuncionalidadeRelGrupo objAdmFuncionalidadeRelGrupo = new EntAdmFuncionalidadeRelGrupo();
if (objAdmFuncionalidadeRelGrupoCustom.Visualizar)
{
objAdmFuncionalidadeRelGrupo.AdmGrupo = objAdmGrupo;
objAdmFuncionalidadeRelGrupo.AdmFuncionalidade.IdFuncionalidade = objAdmFuncionalidadeRelGrupoCustom.IdFuncionalidade;
objAdmFuncionalidadeRelGrupo.Inserir = objAdmFuncionalidadeRelGrupoCustom.Inserir;
objAdmFuncionalidadeRelGrupo.Atualizar = objAdmFuncionalidadeRelGrupoCustom.Atualizar;
objAdmFuncionalidadeRelGrupo.Excluir = objAdmFuncionalidadeRelGrupoCustom.Excluir;
objAdmFuncionalidadeRelGrupo.Visualizar = objAdmFuncionalidadeRelGrupoCustom.Visualizar;
if (this.ExisteFuncionalidadeRelGrupo(objAdmFuncionalidadeRelGrupo.AdmFuncionalidade.IdFuncionalidade, objAdmFuncionalidadeRelGrupo.AdmGrupo.IdGrupo))
{
dalAdmFuncionalidadeRelGrupo.Alterar(objAdmFuncionalidadeRelGrupo, transaction, db);
}
else
{
dalAdmFuncionalidadeRelGrupo.Inserir(objAdmFuncionalidadeRelGrupo, transaction, db);
}
}
else
{
objAdmFuncionalidadeRelGrupo.AdmGrupo = objAdmGrupo;
objAdmFuncionalidadeRelGrupo.AdmFuncionalidade.IdFuncionalidade = objAdmFuncionalidadeRelGrupoCustom.IdFuncionalidade;
dalAdmFuncionalidadeRelGrupo.Excluir(objAdmFuncionalidadeRelGrupo.AdmFuncionalidade.IdFuncionalidade, objAdmFuncionalidadeRelGrupoCustom.AdmGrupo.IdGrupo, transaction, db);
}
}
transaction.Commit();
}
return(msg);
}
catch
{
transaction.Rollback();
return("Erro ao tentar Gravar o Grupo de Acesso!");
throw;
}
finally
{
connection.Close();
}
}
}
public Int32 SaveXRayIssueDetails(Entities.XRayIssue xrayIssue)
{
var XRayIssueId = 0;
var db = DBConnect.getDBConnection();
using (DbConnection conn = db.CreateConnection())
{
conn.Open();
using (DbTransaction dbTransaction = conn.BeginTransaction())
{
try
{
var xrayFilmUsedId = 0;
if (xrayIssue != null)
{
if (xrayIssue.XRayIssueId == null || xrayIssue.XRayIssueId == 0)
{
XRayIssueId = AddXRayIssueDetails(xrayIssue, dbTransaction);
}
else if (xrayIssue.ModifiedBy != null || xrayIssue.ModifiedBy > 0)
{
XRayIssueId = UpdateXRayIssueDetails(xrayIssue, dbTransaction);
}
else if (xrayIssue.IsDeleted == true)
{
var result = DeleteXRayIssueDetails(xrayIssue, dbTransaction);
if (result)
{
XRayIssueId = (int)xrayIssue.XRayIssueId;
}
else
{
XRayIssueId = -1;
}
}
if (XRayIssueId > 0)
{
if (xrayIssue.IsDeleted == true)
{
XRayFilmUsed xRayFilmUsed = new XRayFilmUsed();
var result = xRayFilmUsed.DeleteXRayFilmUsedDetailsByXRayIssueId((int)xrayIssue.XRayIssueId, (int)xrayIssue.DeletedBy, xrayIssue.DeletedByIP, dbTransaction);
if (result)
{
XRayIssueId = (int)xrayIssue.XRayIssueId;
}
}
if (xrayIssue.XRayFilmsUsed != null)
{
if (xrayIssue.XRayFilmsUsed.Count > 0)
{
foreach (Entities.XRayFilmUsed xrayFilmUsed in xrayIssue.XRayFilmsUsed)
{
XRayFilmUsed xRayFilmUsedDB = new XRayFilmUsed();
xrayFilmUsed.XRayIssueId = XRayIssueId;
xrayFilmUsedId = xRayFilmUsedDB.SaveXRayFilmUsed(xrayFilmUsed, dbTransaction);
if (xrayFilmUsedId < 0)
{
XRayIssueId = -1;
}
}
}
}
}
}
if (XRayIssueId > 0)
{
dbTransaction.Commit();
}
else
{
dbTransaction.Rollback();
}
}
catch (Exception ex)
{
XRayIssueId = -1;
dbTransaction.Rollback();
throw ex;
}
}
return(XRayIssueId);
}
}
private Int64 UpdateTran(PRMSupplierItemMapEntity pRMSupplierItemMapEntity, String filterExpression, DatabaseOperationType option)
{
long returnCode = -99;
const string SP = "dbo.PRMSupplierItemMap_SET";
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand cmd = db.GetStoredProcCommand(SP))
{
AddOptionParameter(cmd, option, db);
AddOutputParameter(cmd, db);
AddFilterExpressionParameter(cmd, filterExpression, db);
db.AddInParameter(cmd, "@SupplierItemMapID", DbType.Int64, pRMSupplierItemMapEntity.SupplierItemMapID);
db.AddInParameter(cmd, "@SupplierID", DbType.Int64, pRMSupplierItemMapEntity.SupplierID);
db.AddInParameter(cmd, "@BrandID", DbType.Int64, pRMSupplierItemMapEntity.BrandID);
db.AddInParameter(cmd, "@ItemID", DbType.Int64, pRMSupplierItemMapEntity.ItemID);
db.AddInParameter(cmd, "@OriginRegionID", DbType.Int64, pRMSupplierItemMapEntity.OriginRegionID);
db.AddInParameter(cmd, "@OriginCountryID", DbType.Int64, pRMSupplierItemMapEntity.OriginCountryID);
db.AddInParameter(cmd, "@ActualPrice", DbType.Decimal, pRMSupplierItemMapEntity.ActualPrice);
db.AddInParameter(cmd, "@DiscountPercentage", DbType.Decimal, pRMSupplierItemMapEntity.DiscountPercentage);
db.AddInParameter(cmd, "@Price", DbType.Decimal, pRMSupplierItemMapEntity.Price);
db.AddInParameter(cmd, "@Model", DbType.String, pRMSupplierItemMapEntity.Model);
db.AddInParameter(cmd, "@WarrantyPeriod", DbType.Int64, pRMSupplierItemMapEntity.WarrantyPeriod);
db.AddInParameter(cmd, "@EntryDate", DbType.DateTime, pRMSupplierItemMapEntity.EntryDate);
db.AddInParameter(cmd, "@IsRemoved", DbType.Boolean, pRMSupplierItemMapEntity.IsRemoved);
DbConnection connection = db.CreateConnection();
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
using (IDataReader reader = db.ExecuteReader(cmd, transaction))
{
returnCode = GetReturnCodeFromParameter(cmd);
}
if (returnCode > 0)
{
transaction.Commit();
}
else
{
throw new ArgumentException("Error Code." + returnCode.ToString());
}
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
transaction.Dispose();
connection.Close();
connection = null;
}
}
return(returnCode);
}
/// <summary>
/// Date Created: 07/Oct/2015
/// Created By: Josephine Monteza
/// (description) Save Hotel Forecast to Override Room Blocks By RCCL
/// ----------------------------------------
/// </summary>
public void ApproveForecastManifestByRCCL(int iHotelID,
string sUserName, string sDescription, string sFunction, string sFileName,
DateTime dDateGMT, DateTime dtDateCreated, DataTable dtRoomToBeAdded)
{
Database db = ConnectionSetting.GetConnection(); // DatabaseFactory.CreateDatabase();
DbCommand dbCommand = null;
DbConnection connection = db.CreateConnection();
connection.Open();
DbTransaction trans = connection.BeginTransaction();
try
{
string strTimeZone = TimeZone.CurrentTimeZone.StandardName.ToString();
dbCommand = db.GetStoredProcCommand("uspHotelForecastApproveWithEmail_ByRCCL");
db.AddInParameter(dbCommand, "@pBranchIDInt", DbType.Int32, iHotelID);
db.AddInParameter(dbCommand, "@pUserId", DbType.String, sUserName);
db.AddInParameter(dbCommand, "@pDescription", DbType.String, sDescription);
db.AddInParameter(dbCommand, "@pFunction", DbType.String, sFunction);
db.AddInParameter(dbCommand, "@pFileName", DbType.String, sFileName);
db.AddInParameter(dbCommand, "@pTimezone", DbType.String, strTimeZone);
db.AddInParameter(dbCommand, "@pGMTDATE", DbType.DateTime, dDateGMT);
db.AddInParameter(dbCommand, "@pCreateDate", DbType.DateTime, dtDateCreated);
SqlParameter param = new SqlParameter("@pTempHotelForecast", dtRoomToBeAdded);
param.Direction = ParameterDirection.Input;
param.SqlDbType = SqlDbType.Structured;
dbCommand.Parameters.Add(param);
db.ExecuteNonQuery(dbCommand, trans);
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
if (dbCommand != null)
{
dbCommand.Dispose();
}
if (trans != null)
{
trans.Dispose();
}
if (dtRoomToBeAdded != null)
{
dtRoomToBeAdded.Dispose();
}
}
}
private Int64 UpdateTran(CRMCommunicationEntity cRMCommunicationEntity, String filterExpression, DatabaseOperationType option)
{
long returnCode = -99;
const string SP = "dbo.CRMCommunication_SET";
Database db = DatabaseFactory.CreateDatabase();
using (DbCommand cmd = db.GetStoredProcCommand(SP))
{
AddOptionParameter(cmd, option, db);
AddOutputParameter(cmd, db);
AddFilterExpressionParameter(cmd, filterExpression, db);
db.AddInParameter(cmd, "@CommunicationID", DbType.Int64, cRMCommunicationEntity.CommunicationID);
db.AddInParameter(cmd, "@ProcessCategoryID", DbType.Int64, cRMCommunicationEntity.ProcessCategoryID);
db.AddInParameter(cmd, "@ReferenceID", DbType.Int64, cRMCommunicationEntity.ReferenceID);
db.AddInParameter(cmd, "@CommuicationDiscussionTypeID", DbType.Int64, cRMCommunicationEntity.CommuicationDiscussionTypeID);
db.AddInParameter(cmd, "@DiscussionDate", DbType.DateTime, cRMCommunicationEntity.DiscussionDate);
db.AddInParameter(cmd, "@CommunicateByEmployeeID", DbType.Int64, cRMCommunicationEntity.CommunicateByEmployeeID);
db.AddInParameter(cmd, "@Topics", DbType.String, cRMCommunicationEntity.Topics);
db.AddInParameter(cmd, "@Note", DbType.String, cRMCommunicationEntity.Note);
db.AddInParameter(cmd, "@IsFollowUpNeeded", DbType.Boolean, cRMCommunicationEntity.IsFollowUpNeeded);
db.AddInParameter(cmd, "@IsIWillCall", DbType.Boolean, cRMCommunicationEntity.IsIWillCall);
db.AddInParameter(cmd, "@IsField1", DbType.Boolean, cRMCommunicationEntity.IsField1);
db.AddInParameter(cmd, "@NextAction", DbType.String, cRMCommunicationEntity.NextAction);
db.AddInParameter(cmd, "@NextCommunicationDate", DbType.DateTime, cRMCommunicationEntity.NextCommunicationDate);
db.AddInParameter(cmd, "@CommunicationStatusID", DbType.Int64, cRMCommunicationEntity.CommunicationStatusID);
db.AddInParameter(cmd, "@CreatedByEmployeeID", DbType.Int64, cRMCommunicationEntity.CreatedByEmployeeID);
db.AddInParameter(cmd, "@IP", DbType.String, cRMCommunicationEntity.IP);
db.AddInParameter(cmd, "@CreateDate", DbType.DateTime, cRMCommunicationEntity.CreateDate);
DbConnection connection = db.CreateConnection();
connection.Open();
DbTransaction transaction = connection.BeginTransaction();
try
{
using (IDataReader reader = db.ExecuteReader(cmd, transaction))
{
returnCode = GetReturnCodeFromParameter(cmd);
}
if (returnCode > 0)
{
transaction.Commit();
}
else
{
throw new ArgumentException("Error Code." + returnCode.ToString());
}
}
catch (Exception ex)
{
transaction.Rollback();
throw ex;
}
finally
{
transaction.Dispose();
connection.Close();
connection = null;
}
}
return(returnCode);
}
