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