private questStatus create(MasterPricingEntities dbContext, List <Quest.Functional.MasterPricing.FilterProcedure> filterProcedureList, out List <Quest.Functional.MasterPricing.FilterProcedure> filterProcedureIdList) { // Initialize filterProcedureIdList = null; // Perform create try { List <Quest.Services.Dbio.MasterPricing.FilterProcedures> _filterProcedureList = new List <Quest.Services.Dbio.MasterPricing.FilterProcedures>(); foreach (Quest.Functional.MasterPricing.FilterProcedure filterProcedure in filterProcedureList) { Quest.Services.Dbio.MasterPricing.FilterProcedures _filterProcedure = new Quest.Services.Dbio.MasterPricing.FilterProcedures(); BufferMgr.TransferBuffer(filterProcedure, _filterProcedure); _filterProcedureList.Add(_filterProcedure); } dbContext.FilterProcedures.AddRange(_filterProcedureList); dbContext.SaveChanges(); filterProcedureIdList = new List <FilterProcedure>(); foreach (Quest.Services.Dbio.MasterPricing.FilterProcedures _filterProcedure in _filterProcedureList) { Quest.Functional.MasterPricing.FilterProcedure filterProcedure = new FilterProcedure(); filterProcedure.Id = _filterProcedure.Id; filterProcedureIdList.Add(filterProcedure); } } catch (System.Exception ex) { return(new questStatus(Severity.Fatal, String.Format("EXCEPTION: {0}.{1}: {2}", this.GetType().Name, MethodBase.GetCurrentMethod().Name, ex.InnerException != null ? ex.InnerException.Message : ex.Message))); } return(new questStatus(Severity.Success)); }
/*================================================================================================================================== * Public Methods *=================================================================================================================================*/ public questStatus PerformBulkInsert(BulkInsertRequest bulkInsertRequest) { // Initialize questStatus status = null; // Read Filter FilterId filterId = new FilterId(bulkInsertRequest.FilterId); Filter filter = null; FiltersMgr filtersMgr = new FiltersMgr(this.UserSession); status = filtersMgr.Read(filterId, out filter); if (!questStatusDef.IsSuccess(status)) { return(status); } bulkInsertRequest.Filter = filter; // Determine if bulk insert filter procedure exists. FilterProcedure filterProcedure = null; status = _dbBulkInsertMgr.GetFilterProcedure(bulkInsertRequest, "Insert", out filterProcedure); if (!questStatusDef.IsSuccessOrWarning(status)) { return(status); } // Perform bulk insert filter procedure if exists. if (questStatusDef.IsSuccess(status)) { return(PerformBulkInsertFilterProcedure(bulkInsertRequest, filterProcedure)); } // Validate bulk insert request BulkInsertResponse bulkInsertResponse = null; status = ValidateRequest(bulkInsertRequest, out bulkInsertResponse); if (!questStatusDef.IsSuccess(status)) { return(status); } // Generate the SQL status = _dbBulkInsertMgr.GenerateBulkInsertSQL(bulkInsertRequest); if (!questStatusDef.IsSuccess(status)) { return(status); } // Perform bulk insert. status = _dbBulkInsertMgr.PerformBulkInsert(bulkInsertRequest); if (!questStatusDef.IsSuccess(status)) { return(status); } return(new questStatus(Severity.Success)); }
/*================================================================================================================================== * Public Methods *=================================================================================================================================*/ public questStatus PerformBulkUpdate(BulkUpdateRequest bulkUpdateRequest, out int numRows) { // Initialize questStatus status = null; numRows = -1; // Get the filter Filter filter = null; FilterId filterId = new FilterId(bulkUpdateRequest.FilterId); FilterMgr filterMgr = new FilterMgr(this.UserSession); status = filterMgr.GetFilter(filterId, out filter); if (!questStatusDef.IsSuccess(status)) { return(status); } bulkUpdateRequest.Filter = filter; // Determine if bulk update filter procedure exists. FilterProcedure filterProcedure = null; status = _dbBulkUpdateMgr.GetFilterProcedure(bulkUpdateRequest, "Update", out filterProcedure); if (!questStatusDef.IsSuccessOrWarning(status)) { return(status); } // Perform bulk update filter procedure if exists. if (questStatusDef.IsSuccess(status)) { return(PerformBulkUpdateFilterProcedure(bulkUpdateRequest, filterProcedure)); } // Generate the SQL status = _dbBulkUpdateMgr.GenerateBulkUpdateSQL(bulkUpdateRequest); if (!questStatusDef.IsSuccess(status)) { return(status); } // Perform bulk update. status = _dbBulkUpdateMgr.PerformBulkUpdate(bulkUpdateRequest, out numRows); if (!questStatusDef.IsSuccess(status)) { return(status); } return(new questStatus(Severity.Success)); }
public questStatus Update(DbMgrTransaction trans, FilterProcedure filterProcedure) { // Initialize questStatus status = null; // Update filterProcedure status = _dbFilterProceduresMgr.Update(trans, filterProcedure); if (!questStatusDef.IsSuccess(status)) { return(status); } return(new questStatus(Severity.Success)); }
public questStatus Read(FilterProcedureId filterProcedureId, out FilterProcedure filterProcedure) { // Initialize questStatus status = null; filterProcedure = null; // Read filterProcedure status = _dbFilterProceduresMgr.Read(filterProcedureId, out filterProcedure); if (!questStatusDef.IsSuccess(status)) { return(status); } return(new questStatus(Severity.Success)); }
// // Filter Procedure Utility Routines // public questStatus GetFilterProcedure(BulkUpdateRequest bulkUpdateRequest, string Action, out FilterProcedure filterProcedure) { // Initialize questStatus status = null; filterProcedure = null; // Get the filter procedures. FilterId filterId = new FilterId(bulkUpdateRequest.FilterId); List <FilterProcedure> filterProcedureList = null; DbFilterProceduresMgr dbFilterProceduresMgr = new DbFilterProceduresMgr(this.UserSession); status = dbFilterProceduresMgr.Read(filterId, out filterProcedureList); if (!questStatusDef.IsSuccess(status)) { return(status); } // Determine if given action exists. FilterProcedure UPDATEsproc = filterProcedureList.Find(delegate(FilterProcedure fp) { return(fp.Action == Action); }); if (UPDATEsproc == null) { return(new questStatus(Severity.Warning, String.Format("No {0} filter procedure", Action))); } // Get parameters FilterProcedureId filterProcedureId = new FilterProcedureId(UPDATEsproc.Id); List <FilterProcedureParameter> filterProcedureParameterList = null; DbFilterProcedureParametersMgr dbFilterProcedureParametersMgr = new DbFilterProcedureParametersMgr(this.UserSession); status = dbFilterProcedureParametersMgr.Read(filterProcedureId, out filterProcedureParameterList); if (!questStatusDef.IsSuccess(status)) { return(status); } // Return filter procedure with parameters. UPDATEsproc.ParameterList = filterProcedureParameterList; filterProcedure = UPDATEsproc; return(new questStatus(Severity.Success)); }
/*================================================================================================================================== * Properties *=================================================================================================================================*/ #endregion #region Public Methods /*================================================================================================================================== * Public Methods *=================================================================================================================================*/ public questStatus Create(DbMgrTransaction trans, Quest.Functional.MasterPricing.Database database, FilterProcedure filterProcedure, out FilterProcedureId filterProcedureId) { // Initialize questStatus status = null; filterProcedureId = null; // Create filterProcedure status = _dbFilterProceduresMgr.Create(trans, filterProcedure, out filterProcedureId); if (!questStatusDef.IsSuccess(status)) { return(status); } // Store parameters for procedure List <FilterProcedureParameter> filterProcedureParameterList = null; status = GetStoredProdecureParameters(database, filterProcedure.Name, out filterProcedureParameterList); if (!questStatusDef.IsSuccess(status)) { return(status); } FilterProcedureParametersMgr filterProcedureParametersMgr = new FilterProcedureParametersMgr(this.UserSession); foreach (FilterProcedureParameter filterProcedureParameter in filterProcedureParameterList) { filterProcedureParameter.FilterProcedureId = filterProcedureId.Id; FilterProcedureParameterId filterProcedureParameterId = null; status = filterProcedureParametersMgr.Create(trans, database, filterProcedureParameter, out filterProcedureParameterId); if (!questStatusDef.IsSuccess(status)) { return(status); } } return(new questStatus(Severity.Success)); }
public questStatus PerformBulkInsertFilterProcedure(BulkInsertRequest bulkInsertRequest, FilterProcedure filterProcedure) { // Initialize questStatus status = null; // Perform operation. status = _dbBulkInsertMgr.PerformBulkInsertFilterProcedure(bulkInsertRequest, filterProcedure); if (!questStatusDef.IsSuccess(status)) { return(status); } return(new questStatus(Severity.Success)); }
public questStatus PerformBulkUpdateFilterProcedure(BulkUpdateRequest bulkUpdateRequest, FilterProcedure filterProcedure, ResultsSet resultsSet) { // Initialize questStatus status = null; BulkUpdateLog bulkUpdateLog = bLogging ? new BulkUpdateLog() : null; BulkUpdateLogId bulkUpdateLogId = null; int numRows = 0; string logMessage = null; List <string> logParameterList = null; try { // Initialize log if (bLogging) { bulkUpdateLog.Event = "Initialize"; bulkUpdateLog.UserSessionId = this.UserSession.Id; bulkUpdateLog.Username = this.UserSession.User.Username; bulkUpdateLog.Batch = Guid.NewGuid().ToString(); string Filter = null; status = _dbBulkUpdateLogsMgr.SetFilter(bulkUpdateRequest.Filter, out Filter); if (!questStatusDef.IsSuccess(status)) { return(status); } bulkUpdateLog.Filter = Filter; } // Get database connection string TablesetId tablesetId = new TablesetId(bulkUpdateRequest.Filter.TablesetId); Tableset tableset = null; DbTablesetsMgr dbTablesetsMgr = new DbTablesetsMgr(this.UserSession); status = dbTablesetsMgr.Read(tablesetId, out tableset); if (!questStatusDef.IsSuccess(status)) { return(status); } if (bLogging) { string Tableset = null; status = _dbBulkUpdateLogsMgr.SetTableset(tableset, out Tableset); if (!questStatusDef.IsSuccess(status)) { return(status); } bulkUpdateLog.Tableset = Tableset; } DatabaseId databaseId = new DatabaseId(tableset.DatabaseId); Quest.Functional.MasterPricing.Database database = null; DbDatabasesMgr dbDatabasesMgr = new DbDatabasesMgr(this.UserSession); status = dbDatabasesMgr.Read(databaseId, out database); if (!questStatusDef.IsSuccess(status)) { return(status); } if (bLogging) { string Database = null; status = _dbBulkUpdateLogsMgr.SetDatabase(database, out Database); if (!questStatusDef.IsSuccess(status)) { return(status); } bulkUpdateLog.Database = Database; } // Connect and execute bool bTransaction = true; // Update all rows are none of them. using (SqlConnection conn = new SqlConnection(database.ConnectionString)) { bulkUpdateLog.Event = "Connect"; conn.Open(); SqlTransaction trans = null; if (bTransaction) { trans = conn.BeginTransaction(); if (bLogging) { bulkUpdateLog.Event = "BeginTransaction"; } } numRows = 0; foreach (dynamic _dynRow in resultsSet.Data) { using (SqlCommand cmd = new SqlCommand(null, conn, trans)) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = filterProcedure.Name; // Initialize logging info if (bLogging) { logParameterList = new List <string>(); bulkUpdateLog.Event = null; bulkUpdateLog.NumRows = numRows; bulkUpdateLog.Parameters = null; bulkUpdateLog.Message = null; bulkUpdateLog.Data = null; } List <string> dataValueList = new List <string>(); foreach (FilterProcedureParameter filterParam in filterProcedure.ParameterList) { if (bLogging) { bulkUpdateLog.Event = "Next Parameter: " + filterParam.ParameterName; } if (filterParam.Direction != "Input") { SqlParameter sqlParam = new SqlParameter(); if (filterParam.Direction == "ReturnValue") { sqlParam.Direction = ParameterDirection.ReturnValue; } else if (filterParam.Direction == "Output") { sqlParam.Direction = ParameterDirection.Output; } else { continue; // Input/ouput TODO } sqlParam.ParameterName = filterParam.ParameterName; // TEMPORARY continue; } if (bLogging) { logParameterList.Add(String.Format(" Id: {0}, ParameterName: {1}, SqlDbType: {2} ", filterParam.Id, filterParam.ParameterName, filterParam.SqlDbType.ToString())); string parameterArray = null; _dbBulkUpdateLogsMgr.SetArray(logParameterList, out parameterArray); bulkUpdateLog.Parameters = parameterArray; } // If a meta-parameter, fill in its value and continue. bool bIsMetaParameter = false; SqlParameter sqlMetaParameter = null; if (filterParam.ParameterName.Equals("@_Username", StringComparison.InvariantCultureIgnoreCase)) { bIsMetaParameter = true; sqlMetaParameter = new SqlParameter(filterParam.ParameterName, SqlDbType.NVarChar); sqlMetaParameter.Value = this.UserSession.User.Username; } else if (filterParam.ParameterName.Equals("@_UserSessionId", StringComparison.InvariantCultureIgnoreCase)) { bIsMetaParameter = true; sqlMetaParameter = new SqlParameter(filterParam.ParameterName, SqlDbType.Int); sqlMetaParameter.Value = this.UserSession.Id; } else if (filterParam.ParameterName.StartsWith("@_", StringComparison.InvariantCultureIgnoreCase)) { logMessage = String.Format("ERROR: unknown meta-parameter: {0}", filterParam.ParameterName); if (bLogging) { bulkUpdateLog.Message = logMessage; _dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId); } return(new questStatus(Severity.Error, logMessage)); } if (bIsMetaParameter) { cmd.Parameters.Add(sqlMetaParameter); continue; } // Get the column name from the parameter name FilterItem bulkUpdateFilterItem = bulkUpdateRequest.Filter.FilterItemList.Find(delegate(FilterItem fi) { return(String.Equals(fi.ParameterName, filterParam.ParameterName, StringComparison.CurrentCultureIgnoreCase)); }); if (bulkUpdateFilterItem == null) { if (bTransaction) { trans.Rollback(); } logMessage = String.Format("ERROR: filter item not found for sproc parameter {0}", filterParam.ParameterName); if (bLogging) { bulkUpdateLog.Message = logMessage; _dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId); } return(new questStatus(Severity.Error, logMessage)); } // Get the bulk update value. // NOTE: THIS COULD BE A TROUBLE SPOT. ORIGINAL REQUIREMENT WAS SINGLE-ENTITY FILTERS ONLY HAD PROCEDURES. THUS, THOSE FILTER ITEMS // WOULD NEVER HAVE NAMES QUALIFIED BY THE ENTITY THEY'RE IN. BUT, FILTERS WITH ENTITIES THAT HAVE NO COLUMNS IN THE FILTER ITEMS // TECHNICALLY QUALIFY AS 'SINGLE-ENTITY FILTER'. THUS, IF THE NAME ALONE DOESN'T MATCH. GO FOR THE ENTITY_NAME AS A MATCH. BulkUpdateColumnValue bulkUpdateColumnValue = bulkUpdateRequest.Columns.Find(delegate(BulkUpdateColumnValue cv) { return(cv.Name == bulkUpdateFilterItem.FilterColumn.Name); }); if (bulkUpdateColumnValue == null) { bulkUpdateColumnValue = bulkUpdateRequest.Columns.Find(delegate(BulkUpdateColumnValue cv) { string[] parts = cv.Name.Split('_'); if (parts.Length == 2) { return(parts[0] == bulkUpdateFilterItem.FilterColumn.ParentEntityType.Name && parts[1] == bulkUpdateFilterItem.FilterColumn.Name); } return(false); }); } if (bulkUpdateColumnValue == null) { return(new questStatus(Severity.Error, String.Format("ERROR: bulk update column value {0} not found in bulk update columns", bulkUpdateFilterItem.FilterColumn.Name))); } if (bLogging) { bulkUpdateLog.BulkUpdateColumn = String.Format(" Name: {0}, Value: {1} ", bulkUpdateColumnValue.Name, bulkUpdateColumnValue.Value); } // Determine bulk update value to use. string updateValue = null; if (bulkUpdateColumnValue.bNull) { updateValue = null; } else if (!string.IsNullOrEmpty(bulkUpdateColumnValue.Value)) { updateValue = bulkUpdateColumnValue.Value; } else if (filterParam.bRequired) { // Indexing not working, but should be ... ////updateValue = _dynRow[bulkUpdateColumnValue.Name]; bool bFound = false; foreach (KeyValuePair <string, object> kvp in _dynRow) { if (kvp.Key == bulkUpdateColumnValue.Name) { updateValue = kvp.Value != null?kvp.Value.ToString() : null; // Not sure if we go w/ Null here. But, oh well ... bFound = true; break; } } if (!bFound) { return(new questStatus(Severity.Error, String.Format("ERROR: filter results column {0} not found to use in bulk update operation", bulkUpdateColumnValue.Name))); } } else // Value is required, use results value since a value not specified in bulk updates. { updateValue = null; } // Bind the parameter // TODO:REFACTOR SqlDbType sqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType), filterParam.SqlDbType, true); SqlParameter sqlParameter = new SqlParameter(filterParam.ParameterName, sqlDbType); if (bLogging) { bulkUpdateLog.Event = "Set Parameter Value"; } if (sqlDbType == SqlDbType.Bit) { bool bValue = updateValue != "0"; sqlParameter.Value = bValue; } else if (sqlDbType == SqlDbType.Int) { int intValue = Convert.ToInt32(updateValue); sqlParameter.Value = intValue; } else if (sqlDbType == SqlDbType.NVarChar) { if (updateValue == null) { sqlParameter.Value = DBNull.Value; } else { sqlParameter.Value = updateValue.ToString(); } } else if (sqlDbType == SqlDbType.VarChar) { if (updateValue == null) { sqlParameter.Value = DBNull.Value; } else { sqlParameter.Value = updateValue.ToString(); } } else if (sqlDbType == SqlDbType.DateTime) { if (updateValue == null) { sqlParameter.Value = DBNull.Value; } else { sqlParameter.Value = Convert.ToDateTime(updateValue); } } else if (sqlDbType == SqlDbType.DateTime2) { if (updateValue == null) { sqlParameter.Value = DBNull.Value; } else { sqlParameter.Value = Convert.ToDateTime(updateValue); } } else if (sqlDbType == SqlDbType.Date) { if (updateValue == null) { sqlParameter.Value = DBNull.Value; } else { sqlParameter.Value = Convert.ToDateTime(updateValue); } } else if (sqlDbType == SqlDbType.Decimal) { if (updateValue == null) { sqlParameter.Value = DBNull.Value; } else { sqlParameter.Value = Convert.ToDecimal(updateValue); } } else { if (updateValue == null) { sqlParameter.Value = DBNull.Value; } else { sqlParameter.Value = updateValue; } } cmd.Parameters.Add(sqlParameter); if (bLogging) { dataValueList.Add(String.Format(" Name: {0}, Value: {1} ", sqlParameter.ParameterName, sqlParameter.Value == DBNull.Value ? "null" : sqlParameter.Value.ToString())); } } // Execute the command try { if (bLogging) { bulkUpdateLog.Event = "ExecuteNonQuery"; } int _numRows = cmd.ExecuteNonQuery(); if (_numRows != 1) { if (bTransaction) { trans.Rollback(); } logMessage = String.Format("ERROR: Bulk update stored procedure failed: Rows: {0}", _numRows); if (bLogging) { bulkUpdateLog.Message = logMessage; _dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId); } return(new questStatus(Severity.Error, logMessage)); } else if (bLoggingPerRow) { bulkUpdateLog.Message = "Successful bulk update"; bulkUpdateLog.NumRows = _numRows; bulkUpdateLog.BulkUpdateColumn = null; string valueArray = null; _dbBulkUpdateLogsMgr.SetArray(dataValueList, out valueArray); bulkUpdateLog.Data = valueArray; _dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId); } } catch (SqlException ex) { if (bTransaction) { trans.Rollback(); } logMessage = String.Format("SQL EXCEPTION: Bulk update stored procedure {0}: {1}", filterProcedure.Name, ex.Message); if (bLogging) { bulkUpdateLog.Message = logMessage; _dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId); } return(new questStatus(Severity.Error, logMessage)); } catch (System.Exception ex) { if (bTransaction) { trans.Rollback(); } logMessage = String.Format("EXCEPTION: Bulk update stored procedure {0}: {1}", filterProcedure.Name, ex.Message); if (bLogging) { bulkUpdateLog.Message = logMessage; _dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId); } return(new questStatus(Severity.Error, logMessage)); } } numRows += 1; } if (bTransaction) { trans.Commit(); } if (bLogging) { bulkUpdateLog.Event = "COMMIT"; bulkUpdateLog.NumRows = numRows; bulkUpdateLog.BulkUpdateColumn = null; bulkUpdateLog.Message = "Bulk update operation successful"; bulkUpdateLog.Data = null; _dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId); } } } catch (System.Exception ex) { logMessage = String.Format("EXCEPTION: Bulk Update Operation: {0}.{1}: {2}", this.GetType().Name, MethodBase.GetCurrentMethod().Name, ex.InnerException != null ? ex.InnerException.Message : ex.Message); if (bLogging) { bulkUpdateLog.Event = bulkUpdateLog.Event == null ? "EXCEPTION" : bulkUpdateLog.Event; bulkUpdateLog.Message = logMessage; bulkUpdateLog.NumRows = numRows; string parameterArray = null; _dbBulkUpdateLogsMgr.SetArray(logParameterList, out parameterArray); bulkUpdateLog.Parameters = parameterArray; string Exception = null; _dbBulkUpdateLogsMgr.SetException(ex, out Exception); bulkUpdateLog.Data = Exception; _dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId); } return(new questStatus(Severity.Fatal, logMessage)); } return(new questStatus(Severity.Success)); }
/*================================================================================================================================== * Public Methods *=================================================================================================================================*/ public questStatus Save(FilterPanelViewModel filterPanelViewModel) { // Initialize questStatus status = null; Quest.Functional.MasterPricing.Filter filter = null; FilterId filterId = null; // Validate if (filterPanelViewModel.Editor.TablesetId < BaseId.VALID_ID) { return(new questStatus(Severity.Error, "Invalid Tableset Id")); } if (filterPanelViewModel.Editor.FilterId < BaseId.VALID_ID) { // For headless agents that want to save all-in-one, not via the Filters panel. filter = new Functional.MasterPricing.Filter(); BufferMgr.TransferBuffer(filterPanelViewModel.Editor, filter); filterId = null; FiltersMgr filtersMgr = new FiltersMgr(this.UserSession); status = filtersMgr.Create(filter, out filterId); if (!questStatusDef.IsSuccess(status)) { return(status); } filterPanelViewModel.Editor.Id = filterId.Id; filterPanelViewModel.Editor.FilterId = filterId.Id; } // Read tableset configuration TablesetId tablesetId = new TablesetId(filterPanelViewModel.Editor.TablesetId); TablesetDataManagement tablesetDataManagement = null; TablesetMgr tablesetMgr = new TablesetMgr(this.UserSession); status = tablesetMgr.ReadTablesetDataManagement(tablesetId, out tablesetDataManagement); if (!questStatusDef.IsSuccess(status)) { return(status); } #region Transfer filter entities // // Transfer filter entities // filter = new Functional.MasterPricing.Filter(); filter.TablesetId = filterPanelViewModel.Editor.TablesetId; filter.Id = filterPanelViewModel.Editor.Id; foreach (BootstrapTreenodeViewModel filterEntity in filterPanelViewModel.Entities) { if (filterEntity.type == "table") { FilterTable filterTable = new FilterTable(); filterTable.FilterId = filterPanelViewModel.Editor.Id; filterTable.Schema = filterEntity.Schema; filterTable.Name = filterEntity.Name; filter.FilterTableList.Add(filterTable); } else if (filterEntity.type == "view") { FilterView filterView = new FilterView(); filterView.FilterId = filterPanelViewModel.Editor.Id; filterView.Schema = filterEntity.Schema; filterView.Name = filterEntity.Name; filter.FilterViewList.Add(filterView); } else if (filterEntity.type == "column") { FilterColumn filterColumn = new FilterColumn(); filterColumn.FilterId = filterPanelViewModel.Editor.Id; filterColumn.Name = filterEntity.Name; filterColumn.TablesetEntityId = filterEntity.ParentId; if (filterEntity.parentType == "table") { TablesetTable parentTablesetTable = tablesetDataManagement.TablesetConfiguration.TablesetTables.Find(delegate(TablesetTable tt) { return(tt.Id == filterEntity.ParentId); }); if (parentTablesetTable == null) { return(new questStatus(Severity.Error, String.Format("ERROR: column filter entity \"{0}\" (Id {1}) table parent Id {2} not found.", filterEntity.text, filterEntity.Id, filterEntity.ParentId))); } filterColumn.FilterEntityTypeId = FilterEntityType.Table; // If column table not in filter entities, add it. BootstrapTreenodeViewModel filterColumnTable = filterPanelViewModel.Entities.Find(delegate(BootstrapTreenodeViewModel n) { return(n.type == "table" && n.Id == filterEntity.ParentId); }); if (filterColumnTable == null) { FilterTable filterTable = filter.FilterTableList.Find(delegate(FilterTable t) { return(filterEntity.ParentId == t.TablesetTable.Id); }); if (filterTable == null) { filterTable = new FilterTable(); filterTable.FilterId = filterPanelViewModel.Editor.Id; filterTable.TablesetTable = parentTablesetTable; filterTable.Schema = parentTablesetTable.Schema; filterTable.Name = parentTablesetTable.Name; filter.FilterTableList.Add(filterTable); } } } else if (filterEntity.parentType == "view") { TablesetView parentTablesetView = tablesetDataManagement.TablesetConfiguration.TablesetViews.Find(delegate(TablesetView tv) { return(tv.Id == filterEntity.ParentId); }); if (parentTablesetView == null) { return(new questStatus(Severity.Error, String.Format("ERROR: column filter entity \"{0}\" (Id {1}) view parent Id {2} not found.", filterEntity.text, filterEntity.Id, filterEntity.ParentId))); } filterColumn.FilterEntityTypeId = FilterEntityType.View; // If column view not in filter entities, add it. BootstrapTreenodeViewModel filterColumnView = filterPanelViewModel.Entities.Find(delegate(BootstrapTreenodeViewModel n) { return(n.type == "view" && n.Id == filterEntity.ParentId); }); if (filterColumnView == null) { FilterView filterView = filter.FilterViewList.Find(delegate(FilterView v) { return(filterEntity.ParentId == v.TablesetView.Id); }); if (filterView == null) { filterView = new FilterView(); filterView.FilterId = filterPanelViewModel.Editor.Id; filterView.TablesetView = parentTablesetView; filterView.Schema = parentTablesetView.Schema; filterView.Name = parentTablesetView.Name; filter.FilterViewList.Add(filterView); } } } else { return(new questStatus(Severity.Error, String.Format("ERROR: column filter entity \"{0}\" (Id {1}) unknown parent parent type: {2}", filterEntity.text, filterEntity.Id, filterEntity.ParentId, (filterEntity.parentType == null ? "null" : filterEntity.parentType)))); } filter.FilterColumnList.Add(filterColumn); } else { return(new questStatus(Severity.Error, String.Format("Invalid filter entity type: {0}", filterEntity.type))); } } #endregion #region Transfer filter items // // Transfer filter items // foreach (FilterItemViewModel filterItemViewModel in filterPanelViewModel.Items) { // Filter Item FilterItem filterItem = new FilterItem(); filterItem.FilterId = filterPanelViewModel.Editor.FilterId; if (filterItemViewModel.Entity.type == "table") { filterItem.FilterEntityTypeId = FilterEntityType.Table; } if (filterItemViewModel.Entity.type == "view") { filterItem.FilterEntityTypeId = FilterEntityType.View; } else if (filterItemViewModel.Entity.type == "column") { filterItem.FilterEntityTypeId = FilterEntityType.Column; } filterItem.TablesetColumnId = filterItemViewModel.Entity.Id; filterItem.Label = filterItemViewModel.Label; filterItem.ParameterName = filterItemViewModel.ParameterName; filterItem.bHidden = filterItemViewModel.bHidden; filterItem.bBulkUpdateValueRequired = filterItemViewModel.bBulkUpdateValueRequired; // Joins foreach (FilterItemJoinViewModel filterItemJoinViewModel in filterItemViewModel.Joins) { FilterItemJoin filterItemJoin = new FilterItemJoin(); filterItemJoin.ColumnId = filterItemJoinViewModel.ColumnId; filterItemJoin.JoinType = filterItemJoinViewModel.JoinType; filterItemJoin.Identifier = filterItemJoinViewModel.Identifier; filterItem.JoinList.Add(filterItemJoin); } // Operations foreach (FilterOperationViewModel filterOperationViewModel in filterItemViewModel.Operations) { FilterOperation filterOperation = new FilterOperation(); filterOperation.FilterOperatorId = filterOperationViewModel.Operator; // Values foreach (FilterValueViewModel filterValueViewModel in filterOperationViewModel.Values) { FilterValue filterValue = new FilterValue(); filterValue.Value = filterValueViewModel.Value; filterOperation.ValueList.Add(filterValue); } filterItem.OperationList.Add(filterOperation); } // Lookup if (filterItemViewModel.Lookup.Id >= BaseId.VALID_ID) { filterItem.LookupId = filterItemViewModel.Lookup.Id; } // TypeList if (filterItemViewModel.TypeList.Id >= BaseId.VALID_ID) { filterItem.TypeListId = filterItemViewModel.TypeList.Id; } filter.FilterItemList.Add(filterItem); } #endregion #region Transfer filter procedures // // Transfer filter procedures // foreach (FilterProcedureViewModel filterProcedureViewModel in filterPanelViewModel.Procedures) { if (filterProcedureViewModel.Id > BaseId.INVALID_ID) { FilterProcedure filterProcedure = new FilterProcedure(); BufferMgr.TransferBuffer(filterProcedureViewModel, filterProcedure); filterProcedure.FilterId = filterPanelViewModel.Editor.FilterId; filter.FilterProcedureList.Add(filterProcedure); } } #endregion #region Save filter // // Save filter // filterId = new FilterId(filterPanelViewModel.Editor.FilterId); FilterMgr filterMgr = new FilterMgr(this.UserSession); status = filterMgr.Save(filterId, filter); if (!questStatusDef.IsSuccess(status)) { if (questStatusDef.IsWarning(status)) { return(status); } return(new questStatus(status.Severity, String.Format("Error saving filter items: {0}", status.Message))); } #endregion #region Return procedure parameters // // Return procedure parameters // // Get filter database Quest.Functional.MasterPricing.Database database = null; filterMgr.GetFilterDatabase(filterId, out database); if (filter.FilterProcedureList.Count > 0) { foreach (FilterProcedureViewModel filterProcedureViewModel in filterPanelViewModel.Procedures) { if ((filterProcedureViewModel.Id == BaseId.INVALID_ID) || (string.IsNullOrEmpty(filterProcedureViewModel.Name))) { continue; } // Get procedure parameters List <Quest.Functional.MasterPricing.FilterProcedureParameter> filterProcedureParameterList = null; status = filterMgr.GetStoredProdecureParameters(database, filterProcedureViewModel.Name, out filterProcedureParameterList); if (!questStatusDef.IsSuccess(status)) { return(status); } foreach (FilterProcedureParameter filterProcedureParameter in filterProcedureParameterList) { FilterProcedureParameterViewModel filterProcedureParameterViewModel = new FilterProcedureParameterViewModel(); BufferMgr.TransferBuffer(filterProcedureParameter, filterProcedureParameterViewModel, true); filterProcedureParameterViewModel.Precision = filterProcedureParameter.Precision[0]; filterProcedureParameterViewModel.Scale = filterProcedureParameter.Scale[0]; filterProcedureViewModel.Parameters.Add(filterProcedureParameterViewModel); } } } #endregion // Return warning if no items in filter. if (filter.FilterItemList.Count == 0) { return(new questStatus(Severity.Warning, "Filter successfully saved, but has no items")); } return(new questStatus(Severity.Success)); }
public questStatus PerformBulkInsertFilterProcedure(BulkInsertRequest bulkInsertRequest, FilterProcedure filterProcedure) { // Initialize questStatus status = null; BulkInsertLog bulkInsertLog = bLogging ? new BulkInsertLog() : null; BulkInsertLogId bulkInsertLogId = null; int numRows = 0; string logMessage = null; List <string> logParameterList = null; try { // Initialize log if (bLogging) { bulkInsertLog.Event = "Initialize"; bulkInsertLog.UserSessionId = this.UserSession.Id; bulkInsertLog.Username = this.UserSession.User.Username; bulkInsertLog.Batch = Guid.NewGuid().ToString(); string Filter = null; status = _dbBulkInsertLogsMgr.SetFilter(bulkInsertRequest.Filter, out Filter); if (!questStatusDef.IsSuccess(status)) { return(status); } bulkInsertLog.Filter = Filter; } // Get database connection string TablesetId tablesetId = new TablesetId(bulkInsertRequest.TablesetId); Tableset tableset = null; DbTablesetsMgr dbTablesetsMgr = new DbTablesetsMgr(this.UserSession); status = dbTablesetsMgr.Read(tablesetId, out tableset); if (!questStatusDef.IsSuccess(status)) { return(status); } if (bLogging) { string Tableset = null; status = _dbBulkInsertLogsMgr.SetTableset(tableset, out Tableset); if (!questStatusDef.IsSuccess(status)) { return(status); } bulkInsertLog.Tableset = Tableset; } DatabaseId databaseId = new DatabaseId(tableset.DatabaseId); Quest.Functional.MasterPricing.Database database = null; DbDatabasesMgr dbDatabasesMgr = new DbDatabasesMgr(this.UserSession); status = dbDatabasesMgr.Read(databaseId, out database); if (!questStatusDef.IsSuccess(status)) { return(status); } if (bLogging) { string Database = null; status = _dbBulkInsertLogsMgr.SetDatabase(database, out Database); if (!questStatusDef.IsSuccess(status)) { return(status); } bulkInsertLog.Database = Database; } // Connect and execute bool bTransaction = false; bulkInsertLog.Event = "Open"; using (SqlConnection conn = new SqlConnection(database.ConnectionString)) { bulkInsertLog.Event = "Connect"; conn.Open(); SqlTransaction trans = null; if (bTransaction) { trans = conn.BeginTransaction(); if (bLogging) { bulkInsertLog.Event = "BeginTransaction"; } } numRows = 0; foreach (BulkInsertRow bulkInsertRow in bulkInsertRequest.Rows) { using (SqlCommand cmd = new SqlCommand(null, conn, trans)) { cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = filterProcedure.Name; // Initialize logging info if (bLogging) { logParameterList = new List <string>(); bulkInsertLog.Event = null; bulkInsertLog.NumRows = numRows; bulkInsertLog.Parameters = null; bulkInsertLog.Message = null; bulkInsertLog.Data = null; } // Build each parameter List <string> dataValueList = new List <string>(); foreach (FilterProcedureParameter filterParam in filterProcedure.ParameterList) { if (bLogging) { bulkInsertLog.Event = "Next Parameter: " + filterParam.ParameterName; } if (filterParam.Direction != "Input") { continue; } if (bLogging) { logParameterList.Add(String.Format(" Id: {0}, ParameterName: {1}, SqlDbType: {2} ", filterParam.Id, filterParam.ParameterName, filterParam.SqlDbType.ToString())); string parameterArray = null; _dbBulkInsertLogsMgr.SetArray(logParameterList, out parameterArray); bulkInsertLog.Parameters = parameterArray; } // If a meta-parameter, fill in its value and continue. bool bIsMetaParameter = false; SqlParameter sqlMetaParameter = null; if (filterParam.ParameterName.Equals("@_Username", StringComparison.InvariantCultureIgnoreCase)) { bIsMetaParameter = true; sqlMetaParameter = new SqlParameter(filterParam.ParameterName, SqlDbType.NVarChar); sqlMetaParameter.Value = this.UserSession.User.Username; } else if (filterParam.ParameterName.Equals("@_UserSessionId", StringComparison.InvariantCultureIgnoreCase)) { bIsMetaParameter = true; sqlMetaParameter = new SqlParameter(filterParam.ParameterName, SqlDbType.Int); sqlMetaParameter.Value = this.UserSession.Id; } else if (filterParam.ParameterName.StartsWith("@_", StringComparison.InvariantCultureIgnoreCase)) { logMessage = String.Format("ERROR: unknown meta-parameter: {0}", filterParam.ParameterName); if (bLogging) { bulkInsertLog.Message = logMessage; _dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId); } return(new questStatus(Severity.Error, logMessage)); } if (bIsMetaParameter) { cmd.Parameters.Add(sqlMetaParameter); continue; } // Not a meta-parameter, so the bulk insert value should be provided. BulkInsertColumnValue bulkInsertColumnValue = bulkInsertRow.Columns.Find(delegate(BulkInsertColumnValue cv) { return(cv.Name == filterParam.ParameterName); }); if (bulkInsertColumnValue == null) { if (bTransaction) { trans.Rollback(); } logMessage = String.Format("ERROR: sproc parameter {0} not found in bulk insert columns", filterParam.ParameterName); if (bLogging) { bulkInsertLog.Message = logMessage; _dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId); } return(new questStatus(Severity.Error, logMessage)); } if (bLogging) { bulkInsertLog.BulkInsertColumn = String.Format(" Name: {0}, Value: {1} ", bulkInsertColumnValue.Name, bulkInsertColumnValue.Value); } // Bind the parameter // TODO:REFACTOR SqlDbType sqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType), filterParam.SqlDbType, true); SqlParameter sqlParameter = new SqlParameter(filterParam.ParameterName, sqlDbType); if (bLogging) { bulkInsertLog.Event = "Set Parameter Value"; } if (sqlDbType == SqlDbType.Bit) { bool bValue = bulkInsertColumnValue.Value != "0"; sqlParameter.Value = bValue; } else if (sqlDbType == SqlDbType.Int) { int intValue = Convert.ToInt32(bulkInsertColumnValue.Value); sqlParameter.Value = intValue; } else if (sqlDbType == SqlDbType.NVarChar) { if (bulkInsertColumnValue.Value == null) { sqlParameter.Value = DBNull.Value; } else { sqlParameter.Value = bulkInsertColumnValue.Value.ToString(); } } else if (sqlDbType == SqlDbType.VarChar) { if (bulkInsertColumnValue.Value == null) { sqlParameter.Value = DBNull.Value; } else { sqlParameter.Value = bulkInsertColumnValue.Value.ToString(); } } else if (sqlDbType == SqlDbType.DateTime) { if (bulkInsertColumnValue.Value == null) { sqlParameter.Value = DBNull.Value; } else { sqlParameter.Value = Convert.ToDateTime(bulkInsertColumnValue.Value); } } else if (sqlDbType == SqlDbType.DateTime2) { if (bulkInsertColumnValue.Value == null) { sqlParameter.Value = DBNull.Value; } else { sqlParameter.Value = Convert.ToDateTime(bulkInsertColumnValue.Value); } } else if (sqlDbType == SqlDbType.Date) { if (bulkInsertColumnValue.Value == null) { sqlParameter.Value = DBNull.Value; } else { sqlParameter.Value = Convert.ToDateTime(bulkInsertColumnValue.Value); } } else if (sqlDbType == SqlDbType.Decimal) { if (bulkInsertColumnValue.Value == null) { sqlParameter.Value = DBNull.Value; } else { sqlParameter.Value = Convert.ToDecimal(bulkInsertColumnValue.Value); } } else { if (bulkInsertColumnValue.Value == null) { sqlParameter.Value = DBNull.Value; } else { sqlParameter.Value = bulkInsertColumnValue.Value; } } cmd.Parameters.Add(sqlParameter); if (bLogging) { dataValueList.Add(String.Format(" Name: {0}, Value: {1} ", sqlParameter.ParameterName, sqlParameter.Value == DBNull.Value ? "null" : sqlParameter.Value.ToString())); } } try { if (bLogging) { bulkInsertLog.Event = "ExecuteNonQuery"; } int _numRows = cmd.ExecuteNonQuery(); if (_numRows != 1) { if (bTransaction) { trans.Rollback(); } logMessage = String.Format("ERROR: Bulk insert stored procedure failed: Rows: {0}", _numRows); if (bLogging) { bulkInsertLog.Message = logMessage; _dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId); } return(new questStatus(Severity.Error, logMessage)); } else if (bLoggingPerRow) { bulkInsertLog.Message = "Successful bulk insert"; bulkInsertLog.NumRows = _numRows; bulkInsertLog.BulkInsertColumn = null; string valueArray = null; _dbBulkInsertLogsMgr.SetArray(dataValueList, out valueArray); bulkInsertLog.Data = valueArray; _dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId); } } catch (SqlException ex) { if (bTransaction) { trans.Rollback(); } logMessage = String.Format("SQL EXCEPTION: Bulk insert stored procedure {1}: {2}", numRows, filterProcedure.Name, ex.Message); if (bLogging) { bulkInsertLog.Message = logMessage; _dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId); } return(new questStatus(Severity.Error, logMessage)); } catch (System.Exception ex) { if (bTransaction) { trans.Rollback(); } logMessage = String.Format("After {0} rows, EXCEPTION: Bulk insert stored procedure {0}: {1}", numRows, filterProcedure.Name, ex.Message); if (bLogging) { bulkInsertLog.Message = logMessage; _dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId); } return(new questStatus(Severity.Error, logMessage)); } } numRows += 1; } if (bTransaction) { trans.Commit(); } if (bLogging) { bulkInsertLog.Event = "COMMIT"; bulkInsertLog.NumRows = numRows; bulkInsertLog.BulkInsertColumn = null; bulkInsertLog.Message = "Bulk isnert operation successful"; bulkInsertLog.Data = null; _dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId); } } } catch (System.Exception ex) { logMessage = String.Format("EXCEPTION: Bulk Insert Operation: {0}.{1}: {2}", this.GetType().Name, MethodBase.GetCurrentMethod().Name, ex.InnerException != null ? ex.InnerException.Message : ex.Message); if (bLogging) { bulkInsertLog.Event = bulkInsertLog.Event == null ? "EXCEPTION" : bulkInsertLog.Event; bulkInsertLog.Message = logMessage; bulkInsertLog.NumRows = numRows; string parameterArray = null; _dbBulkInsertLogsMgr.SetArray(logParameterList, out parameterArray); bulkInsertLog.Parameters = parameterArray; string Exception = null; _dbBulkInsertLogsMgr.SetException(ex, out Exception); bulkInsertLog.Data = Exception; _dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId); } return(new questStatus(Severity.Fatal, logMessage)); } return(new questStatus(Severity.Success)); }
public questStatus PerformBulkUpdateFilterProcedure(BulkUpdateRequest bulkUpdateRequest, FilterProcedure filterProcedure) { // Initialize questStatus status = null; // Execute filter RunFilterRequest runFilterRequest = new RunFilterRequest(); runFilterRequest.FilterId.Id = bulkUpdateRequest.FilterId; ResultsSet resultsSet = null; FilterMgr filterMgr = new FilterMgr(this.UserSession); status = filterMgr.ExecuteFilter(runFilterRequest, out resultsSet); if (!questStatusDef.IsSuccess(status)) { return(new questStatus(status.Severity, String.Format("Error executing filter Id={0}: {1}", runFilterRequest.FilterId.Id, status.Message))); } // Perform operation. status = _dbBulkUpdateMgr.PerformBulkUpdateFilterProcedure(bulkUpdateRequest, filterProcedure, resultsSet); if (!questStatusDef.IsSuccess(status)) { return(status); } return(new questStatus(Severity.Success)); }