/*================================================================================================================================== * Public Methods *=================================================================================================================================*/ public questStatus GetDatabase(DatabaseId databaseId, out DatabaseBaseViewModel databaseBaseViewModel) { // Initialize questStatus status = null; databaseBaseViewModel = null; // Read the database Quest.Functional.MasterPricing.Database database = null; DatabasesMgr databasesMgr = new DatabasesMgr(this.UserSession); status = databasesMgr.Read(databaseId, out database); if (!questStatusDef.IsSuccess(status)) { return(status); } // Transfer model. databaseBaseViewModel = new DatabaseBaseViewModel(this.UserSession); BufferMgr.TransferBuffer(database, databaseBaseViewModel); databaseBaseViewModel.LastRefresh = database.LastRefresh.HasValue ? database.LastRefresh.Value.ToString("MM/dd/yyyy HH:mm:ss") : ""; return(new questStatus(Severity.Success)); }
/*================================================================================================================================== * Public Methods *=================================================================================================================================*/ #region Identifer-based Usage // // Identifer-based Usage // public questStatus Create(Quest.Functional.MasterPricing.Database database, out DatabaseId databaseId) { // Initialize questStatus status = null; databaseId = null; // Create the database DatabasesMgr databasesMgr = new DatabasesMgr(this.UserSession); status = databasesMgr.Create(database, out databaseId); if (!questStatusDef.IsSuccess(status)) { return(status); } // Populate the database metadata status = RefreshSchema(databaseId); if (!questStatusDef.IsSuccess(status)) { return(status); } return(new questStatus(Severity.Success)); }
public questStatus Read(DatabaseId databaseId, out DatabaseEditorViewModel databaseEditorViewModel) { // Initialize questStatus status = null; databaseEditorViewModel = null; // Read Quest.Functional.MasterPricing.Database database = null; DatabasesMgr databasesMgr = new DatabasesMgr(this.UserSession); status = databasesMgr.Read(databaseId, out database); if (!questStatusDef.IsSuccess(status)) { return(status); } // Transfer model. databaseEditorViewModel = new DatabaseEditorViewModel(this.UserSession); BufferMgr.TransferBuffer(database, databaseEditorViewModel); return(new questStatus(Severity.Success)); }
public questStatus Update(DbMgrTransaction trans, Quest.Functional.MasterPricing.Database database) { // Initialize questStatus status = null; bool bCreateTransaction = trans == null; // Perform update in this transaction. status = update((MasterPricingEntities)trans.DbContext, database); if (bLogging) { DatabaseLog databaseLog = new DatabaseLog(); databaseLog.Name = database.Name == null ? "(null)" : database.Name; databaseLog.Event = "UPDATE"; databaseLog.Data = status.ToString(); DatabaseLogId databaseLogId = null; _dbDatabaseLogsMgr.Create(databaseLog, out databaseLogId); } if (!questStatusDef.IsSuccess(status)) { RollbackTransaction(trans); return(status); } return(new questStatus(Severity.Success)); }
/*================================================================================================================================== * Public Methods *=================================================================================================================================*/ public questStatus Create(Quest.Functional.MasterPricing.Database database, out Quest.Functional.MasterPricing.DatabaseId databaseId) { // Initialize questStatus status = null; databaseId = null; // Data rules. // Create the database using (MasterPricingEntities dbContext = new MasterPricingEntities()) { status = create(dbContext, database, out databaseId); if (bLogging) { DatabaseLog databaseLog = new DatabaseLog(); databaseLog.Name = database.Name == null ? "(null)" : database.Name; databaseLog.Event = "CREATE"; databaseLog.Data = status.ToString(); DatabaseLogId databaseLogId = null; _dbDatabaseLogsMgr.Create(databaseLog, out databaseLogId); } if (!questStatusDef.IsSuccess(status)) { return(status); } } return(new questStatus(Severity.Success)); }
public questStatus Update(Quest.Functional.MasterPricing.Database database, bool bRefreshSchema = false) { // Initialize questStatus status = null; // Update the database DatabasesMgr databasesMgr = new DatabasesMgr(this.UserSession); status = databasesMgr.Update(database); if (!questStatusDef.IsSuccess(status)) { return(status); } // Populate the database metadata if (bRefreshSchema) { status = RefreshSchema(database); if (!questStatusDef.IsSuccess(status)) { return(status); } } return(new questStatus(Severity.Success)); }
public TablesetConfigurationViewModel() { Tableset = new Tableset(); Database = new Quest.Functional.MasterPricing.Database(); TableList = new List <BootstrapTreenodeViewModel>(); ViewList = new List <BootstrapTreenodeViewModel>(); DBTableList = new List <BootstrapTreenodeViewModel>(); DBViewList = new List <BootstrapTreenodeViewModel>(); }
public questStatus PerformBulkInsert(BulkInsertRequest bulkInsertRequest) { // Initialize questStatus status = null; // Execute bulk insert SQL try { // Get tableset TablesetId tablesetId = new TablesetId(bulkInsertRequest.Filter.TablesetId); Tableset tableset = null; DbTablesetsMgr dbTablesetsMgr = new DbTablesetsMgr(this.UserSession); status = dbTablesetsMgr.Read(tablesetId, out tableset); if (!questStatusDef.IsSuccessOrWarning(status)) { return(status); } // Get database 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.IsSuccessOrWarning(status)) { return(status); } // Execute sql using (SqlConnection sqlConnection = new SqlConnection(database.ConnectionString)) { sqlConnection.Open(); using (SqlCommand cmd = sqlConnection.CreateCommand()) { cmd.CommandText = bulkInsertRequest.SQL; cmd.CommandType = CommandType.Text; int numRows = cmd.ExecuteNonQuery(); if (numRows != bulkInsertRequest.Rows.Count) { return(new questStatus(Severity.Error, String.Format("ERROR: Bulk insert SQL execution failed: Rows: {0}", numRows))); } } } } catch (System.Exception ex) { return(new questStatus(Severity.Fatal, String.Format("EXCEPTION: executing bulk insert SQL {0} SQL: {1}", bulkInsertRequest.SQL, ex.Message))); } return(new questStatus(Severity.Success)); }
/*================================================================================================================================== * Public Methods *=================================================================================================================================*/ #region Set Logging Field Routines /*---------------------------------------------------------------------------------------------------------------------------------- * Set Logging Field Routines *---------------------------------------------------------------------------------------------------------------------------------*/ public questStatus SetDatabase(Quest.Functional.MasterPricing.Database database, out string Database) { if (database == null) { Database = " Id: \"null\", Name: \"null\", ConnString: \"null\" "; } else { Database = String.Format(" Id: {0}, Name: {1}, ConnString: {2} ", database.Id, database.Name, database.ConnectionString == null ? "(null)" : database.ConnectionString); } return(new questStatus(Severity.Success)); }
public questStatus Update(Quest.Functional.MasterPricing.Database database) { // Initialize questStatus status = null; // Update database status = _dbDatabasesMgr.Update(database); if (!questStatusDef.IsSuccess(status)) { return(status); } return(new questStatus(Severity.Success)); }
public questStatus List(QueryOptions queryOptions, out List <Quest.Functional.MasterPricing.Database> databaseList, out QueryResponse queryResponse) { // Initialize questStatus status = null; databaseList = null; queryResponse = null; using (MasterPricingEntities dbContext = new MasterPricingEntities()) { using (DbContextTransaction tx = dbContext.Database.BeginTransaction(IsolationLevel.ReadUncommitted)) { try { PropertyInfo[] dbProperties = typeof(Quest.Services.Dbio.MasterPricing.Databases).GetProperties().ToArray(); int totalRecords = dbContext.Databases.Where(BuildWhereClause(queryOptions, dbProperties)).Count(); List <Quest.Services.Dbio.MasterPricing.Databases> _databasesList = dbContext.Databases.Where(BuildWhereClause(queryOptions, dbProperties)) .OrderBy(BuildSortString(queryOptions.SortColumns)) .Skip(queryOptions.Paging.PageSize * (queryOptions.Paging.PageNumber - 1)) .Take(queryOptions.Paging.PageSize).ToList(); if (_databasesList == null) { return(new questStatus(Severity.Warning)); } databaseList = new List <Quest.Functional.MasterPricing.Database>(); foreach (Quest.Services.Dbio.MasterPricing.Databases _database in _databasesList) { Quest.Functional.MasterPricing.Database database = new Quest.Functional.MasterPricing.Database(); BufferMgr.TransferBuffer(_database, database); databaseList.Add(database); } status = BuildQueryResponse(totalRecords, queryOptions, out queryResponse); if (!questStatusDef.IsSuccess(status)) { return(status); } } catch (System.Exception ex) { status = new questStatus(Severity.Fatal, String.Format("EXCEPTION: {0}.{1}: {2}", this.GetType().Name, MethodBase.GetCurrentMethod().Name, ex.InnerException != null ? ex.InnerException.Message : ex.Message)); LogException(ex, status); return(status); } } } return(new questStatus(Severity.Success)); }
/*================================================================================================================================== * Properties *=================================================================================================================================*/ #endregion #region Public Methods /*================================================================================================================================== * Public Methods *=================================================================================================================================*/ public questStatus Create(Quest.Functional.MasterPricing.Database database, out Quest.Functional.MasterPricing.DatabaseId databaseId) { // Initialize databaseId = null; questStatus status = null; // Create database status = _dbDatabasesMgr.Create(database, out databaseId); if (!questStatusDef.IsSuccess(status)) { return(status); } return(new questStatus(Severity.Success)); }
public questStatus Create(DbMgrTransaction trans, Quest.Functional.MasterPricing.Database database, FilterProcedureParameter filterProcedureParameter, out FilterProcedureParameterId filterProcedureParameterId) { // Initialize questStatus status = null; filterProcedureParameterId = null; // Create filterProcedureParameter status = _dbFilterProcedureParametersMgr.Create(trans, filterProcedureParameter, out filterProcedureParameterId); if (!questStatusDef.IsSuccess(status)) { return(status); } return(new questStatus(Severity.Success)); }
private questStatus readDatabase(DatabaseId databaseId, out Quest.Functional.MasterPricing.Database database) { // Initialize questStatus status = null; database = null; // Read the database. DbDatabasesMgr dbDatabasesMgr = new DbDatabasesMgr(this._userSession); status = dbDatabasesMgr.Read(databaseId, out database); if (!questStatusDef.IsSuccess(status)) { return(status); } return(new questStatus(Severity.Success)); }
/*---------------------------------------------------------------------------------------------------------------------------------- * Databases *---------------------------------------------------------------------------------------------------------------------------------*/ private questStatus create(MasterPricingEntities dbContext, Quest.Functional.MasterPricing.Database database, out DatabaseId databaseId) { // Initialize questStatus status = null; databaseId = null; // Perform create try { Quest.Services.Dbio.MasterPricing.Databases _database = new Quest.Services.Dbio.MasterPricing.Databases(); BufferMgr.TransferBuffer(database, _database); dbContext.Databases.Add(_database); dbContext.SaveChanges(); if (_database.Id == 0) { return(new questStatus(Severity.Error, "Quest.Functional.MasterPricing.Database not created")); } databaseId = new DatabaseId(_database.Id); } catch (DbEntityValidationException ex) { var errorMessages = ex.EntityValidationErrors.SelectMany(x => x.ValidationErrors).Select(x => x.ErrorMessage); String fullErrorMessage = string.Join("; ", errorMessages); String exceptionMessage = string.Concat(ex.Message, fullErrorMessage); status = new questStatus(Severity.Fatal, String.Format("EXCEPTION: {0}.{1}: {2}", this.GetType().Name, MethodBase.GetCurrentMethod().Name, exceptionMessage)); LogException(ex, status); return(status); } catch (System.Exception ex) { status = new questStatus(Severity.Fatal, String.Format("EXCEPTION: {0}.{1}: {2}", this.GetType().Name, MethodBase.GetCurrentMethod().Name, ex.InnerException != null ? ex.InnerException.Message : ex.Message)); LogException(ex, status); return(status); } return(new questStatus(Severity.Success)); }
/*================================================================================================================================== * Public Methods *=================================================================================================================================*/ #region CRUD //---------------------------------------------------------------------------------------------------------------------------------- // CRUD //---------------------------------------------------------------------------------------------------------------------------------- public questStatus Save(DatabaseEditorViewModel databaseEditorViewModel) { // Initialize questStatus status = null; // Transfer model Quest.Functional.MasterPricing.Database database = new Quest.Functional.MasterPricing.Database(); BufferMgr.TransferBuffer(databaseEditorViewModel, database); // Determine if this is a create or update DatabaseMgr databaseMgr = new DatabaseMgr(this.UserSession); if (databaseEditorViewModel.Id < BaseId.VALID_ID) { // Create DatabaseId databaseId = null; status = databaseMgr.Create(database, out databaseId); if (!questStatusDef.IsSuccess(status)) { if (databaseId != null && databaseId.Id >= BaseId.VALID_ID) { databaseEditorViewModel.Id = databaseId.Id; } FormatErrorMessage(status, databaseEditorViewModel); return(status); } databaseEditorViewModel.Id = databaseId.Id; } else { // Update status = databaseMgr.Update(database); if (!questStatusDef.IsSuccess(status)) { FormatErrorMessage(status, databaseEditorViewModel); return(status); } } return(new questStatus(Severity.Success)); }
public questStatus GetDatabaseStoredProcedures(TablesetId tablesetId, out List <StoredProcedure> storedProcedureList) { // Initialize questStatus status = null; storedProcedureList = null; // Get tableset Tableset tableset = null; TablesetsMgr tablesetsMgr = new TablesetsMgr(this.UserSession); status = tablesetsMgr.Read(tablesetId, out tableset); if (!questStatusDef.IsSuccess(status)) { return(status); } // Get database Quest.Functional.MasterPricing.Database database = null; DatabaseId databaseId = new DatabaseId(tableset.DatabaseId); DatabasesMgr databasesMgr = new DatabasesMgr(this.UserSession); status = databasesMgr.Read(databaseId, out database); if (!questStatusDef.IsSuccessOrWarning(status)) { return(status); } // Get database stored procedures DbFilterProceduresMgr dbFilterProceduresMgr = new DbFilterProceduresMgr(this.UserSession); status = GetDatabaseStoredProcedures(database, out storedProcedureList); if (!questStatusDef.IsSuccess(status)) { return(status); } return(new questStatus(Severity.Success)); }
public new questStatus GetStoredProdecureParameters(Quest.Functional.MasterPricing.Database database, string filterProcedureName, out List <FilterProcedureParameter> filterProcedureParameterList) { // Initialize questStatus status = null; filterProcedureParameterList = null; // Get stored procedure DatabaseId databaseId = new DatabaseId(database.Id); StoredProcedure storedProcedure = null; StoredProceduresMgr storedProceduresMgr = new StoredProceduresMgr(this.UserSession); status = storedProceduresMgr.Read(databaseId, filterProcedureName, out storedProcedure); if (!questStatusDef.IsSuccess(status)) { return(status); } // Get stored procedure parameters StoredProcedureId storedProcedureId = new StoredProcedureId(storedProcedure.Id); List <StoredProcedureParameter> storedProcedureParameterList = null; StoredProcedureParametersMgr storedProcedureParametersMgr = new StoredProcedureParametersMgr(this.UserSession); storedProcedureParametersMgr.Read(storedProcedureId, out storedProcedureParameterList); if (!questStatusDef.IsSuccess(status)) { return(status); } // Transfer over to filter procedure. filterProcedureParameterList = new List <FilterProcedureParameter>(); foreach (StoredProcedureParameter storedProcedureParameter in storedProcedureParameterList) { FilterProcedureParameter filterProcedureParameter = new FilterProcedureParameter(); BufferMgr.TransferBuffer(storedProcedureParameter, filterProcedureParameter); filterProcedureParameterList.Add(filterProcedureParameter); } return(new questStatus(Severity.Success)); }
private questStatus getDatabaseViews(DatabaseId datbaseId, out List <DBView> dbViewList) { // Initialize questStatus status = null; dbViewList = null; Quest.Functional.MasterPricing.Database database = null; SqlConnection sqlConnection = null; try { // Read database status = readDatabase(datbaseId, out database); if (!questStatusDef.IsSuccess(status)) { return(status); } // Open database status = OpenDatabase(database, out sqlConnection); if (!questStatusDef.IsSuccess(status)) { return(status); } // Get views status = getDatabaseViews(sqlConnection, out dbViewList); if (!questStatusDef.IsSuccess(status)) { return(status); } } catch (System.Exception ex) { status = new questStatus(Severity.Fatal, String.Format("EXCEPTION: {0}.{1}: {2}", this.GetType().ToString(), MethodInfo.GetCurrentMethod().Name, ex.Message)); return(status); } return(new questStatus(Severity.Success)); }
public questStatus Read(DbMgrTransaction trans, Quest.Functional.MasterPricing.DatabaseId databaseId, out Quest.Functional.MasterPricing.Database database) { // Initialize questStatus status = null; database = null; // Perform read. using (MasterPricingEntities dbContext = new MasterPricingEntities()) { Quest.Services.Dbio.MasterPricing.Databases _database = null; status = read((MasterPricingEntities)trans.DbContext, databaseId, out _database); if (!questStatusDef.IsSuccess(status)) { return(status); } database = new Quest.Functional.MasterPricing.Database(); BufferMgr.TransferBuffer(_database, database); } 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)); }
/*================================================================================================================================== * Properties *=================================================================================================================================*/ #endregion #region Public Methods /*================================================================================================================================== * Public Methods *=================================================================================================================================*/ public questStatus Create(DbMgrTransaction trans, Quest.Functional.MasterPricing.Database database, Quest.Functional.MasterPricing.StoredProcedure storedProcedure, out Quest.Functional.MasterPricing.StoredProcedureId storedProcedureId) { // Initialize questStatus status = null; storedProcedureId = null; // Create storedProcedure status = _dbStoredProceduresMgr.Create(trans, storedProcedure, out storedProcedureId); if (!questStatusDef.IsSuccess(status)) { return(status); } // Store parameters for procedure List <StoredProcedureParameter> storedProcedureParameterList = null; status = GetStoredProdecureParameters(database, storedProcedure.Name, out storedProcedureParameterList); if (!questStatusDef.IsSuccess(status)) { return(status); } StoredProcedureParametersMgr storedProcedureParametersMgr = new StoredProcedureParametersMgr(this.UserSession); foreach (StoredProcedureParameter storedProcedureParameter in storedProcedureParameterList) { storedProcedureParameter.StoredProcedureId = storedProcedureId.Id; StoredProcedureParameterId storedProcedureParameterId = null; status = storedProcedureParametersMgr.Create(trans, database, storedProcedureParameter, out storedProcedureParameterId); if (!questStatusDef.IsSuccess(status)) { return(status); } } return(new questStatus(Severity.Success)); }
/* Used or useful? */ public questStatus GetColumnInfo(TablesetColumn tablesetColumn, out Column column) { // Initialize questStatus status = null; column = null; DbTablesetTablesMgr dbTablesetTablesMgr = new DbTablesetTablesMgr(this._userSession); DbTablesetViewsMgr dbTablesetViewsMgr = new DbTablesetViewsMgr(this._userSession); TablesetId tablesetId = null; TablesetTable tablesetTable = null; TablesetView tablesetView = null; if (tablesetColumn.EntityTypeId == EntityType.Table) { // Get TablesetTable TablesetTableId tablesetTableId = new TablesetTableId(tablesetColumn.TableSetEntityId); status = dbTablesetTablesMgr.Read(tablesetTableId, out tablesetTable); if (!questStatusDef.IsSuccess(status)) { return(status); } tablesetId = new TablesetId(tablesetTable.TablesetId); } else if (tablesetColumn.EntityTypeId == EntityType.View) { // Get TablesetView TablesetViewId tablesetViewId = new TablesetViewId(tablesetColumn.TableSetEntityId); status = dbTablesetViewsMgr.Read(tablesetViewId, out tablesetView); if (!questStatusDef.IsSuccess(status)) { return(status); } tablesetId = new TablesetId(tablesetView.TablesetId); } else { return(new questStatus(Severity.Error, String.Format("ERROR: unsupported EntityTypeId {0} for TablesetColumn {1}", tablesetColumn.EntityTypeId, tablesetColumn.Id))); } // Get the Tableset DbTablesetsMgr dbTablesetsMgr = new DbTablesetsMgr(this._userSession); Tableset tableset = null; status = dbTablesetsMgr.Read(tablesetId, out tableset); if (!questStatusDef.IsSuccess(status)) { return(status); } // Get the database 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); } // Get the Table or View by identifier within this database DbTablesMgr dbTablesMgr = new DbTablesMgr(this._userSession); DbViewsMgr dbViewsMgr = new DbViewsMgr(this._userSession); EntityTypeId entityTypeId = null; EntityId entityId = null; if (tablesetColumn.EntityTypeId == EntityType.Table) { TableId tableId = new TableId(); Table table = null; status = dbTablesMgr.Read(databaseId, tablesetTable.Schema, tablesetTable.Name, out table); if (!questStatusDef.IsSuccess(status)) { return(status); } entityTypeId = new EntityTypeId(EntityType.Table); entityId = new EntityId(table.Id); } else if (tablesetColumn.EntityTypeId == EntityType.View) { } // Get column info DbColumnsMgr dbColumnsMgr = new DbColumnsMgr(this._userSession); status = dbColumnsMgr.Read(entityTypeId, entityId, tablesetColumn.Name, out column); if (!questStatusDef.IsSuccess(status)) { return(status); } return(new questStatus(Severity.Success)); }
/*================================================================================================================================== * Properties *=================================================================================================================================*/ #endregion #region Public Methods /*================================================================================================================================== * Public Methods *=================================================================================================================================*/ public questStatus GetTypeListOptions(TypeListId typeListId, List <TypeListArgument> typeListArgumentList, out List <OptionValuePair> typeListOptionList) { // Initialize questStatus status = null; typeListOptionList = null; // Get the typeList. TypeList typeList = null; DbTypeListsMgr dbTypeListsMgr = new DbTypeListsMgr(this.UserSession); status = dbTypeListsMgr.Read(typeListId, out typeList); if (!questStatusDef.IsSuccess(status)) { return(status); } // Get the database. DatabaseId databaseId = new DatabaseId(typeList.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); } // Apply typeList arguments. if (typeListArgumentList == null) { typeListArgumentList = new List <TypeListArgument>(); } string sql = typeList.SQL; foreach (TypeListArgument typeListArgument in typeListArgumentList) { sql = sql.Replace(typeListArgument.Name, typeListArgument.Value); } try { using (SqlConnection conn = new SqlConnection(database.ConnectionString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand(null, conn)) { cmd.CommandType = CommandType.Text; cmd.CommandText = sql; using (SqlDataReader rdr = cmd.ExecuteReader()) { typeListOptionList = new List <OptionValuePair>(); while (rdr.Read()) { OptionValuePair optionValuePair = new OptionValuePair(); optionValuePair.Id = rdr[0].ToString(); optionValuePair.Label = rdr[0].ToString(); typeListOptionList.Add(optionValuePair); } } } } } catch (System.Exception ex) { status = new questStatus(Severity.Fatal, String.Format("EXCEPTION: {0}.{1}: {2}", this.GetType().ToString(), MethodInfo.GetCurrentMethod().Name, ex.Message)); return(status); } return(new questStatus(Severity.Success)); }
private questStatus getDatabaseColumns(DatabaseId databaseId, out Dictionary <DBView, List <Column> > dictDBColumns) { // Initialize questStatus status = null; dictDBColumns = null; List <DBView> dbViewList = null; SqlConnection sqlConnection = null; DBView dbView = null; try { // Get database Quest.Functional.MasterPricing.Database database = null; DbDatabasesMgr dbDatabasesMgr = new DbDatabasesMgr(this._userSession); status = dbDatabasesMgr.Read(databaseId, out database); if (!questStatusDef.IsSuccess(status)) { return(status); } // Open database status = OpenDatabase(database, out sqlConnection); if (!questStatusDef.IsSuccess(status)) { return(status); } // Get views. status = getDatabaseViews(databaseId, out dbViewList); if (!questStatusDef.IsSuccess(status)) { return(status); } dictDBColumns = new Dictionary <DBView, List <Column> >(); // Get column information. for (int vidx = 0; vidx < dbViewList.Count; vidx += 1) { dbView = dbViewList[vidx]; using (SqlCommand sqlCommand = sqlConnection.CreateCommand()) { sqlCommand.CommandText = String.Format("SELECT TOP 1 * FROM [{0}].[{1}] (NOLOCK) ", dbView.Schema, dbView.Name); sqlCommand.CommandType = CommandType.Text; using (SqlDataReader rdr = sqlCommand.ExecuteReader()) { DataTable schemaTable = rdr.GetSchemaTable(); List <Column> columnList = new List <Column>(); foreach (DataRow myField in schemaTable.Rows) { Column column = new Column(); column.Name = myField["ColumnName"].ToString(); column.DisplayOrder = Convert.ToInt32(myField["ColumnOrdinal"]); column.ColumnSize = Convert.ToInt32(myField["ColumnSize"]); column.DataType = myField["DataType"].ToString(); column.DataTypeName = myField["DataTypeName"].ToString(); column.bIsIdentity = Convert.ToBoolean(myField["IsIdentity"]); column.bAllowDbNull = Convert.ToBoolean(myField["AllowDbNull"]); columnList.Add(column); } dictDBColumns.Add(dbView, columnList); } } } } catch (System.Exception ex) { status = new questStatus(Severity.Fatal, String.Format("EXCEPTION: loading view [{0}].[{1}] columns {2}.{3}: {4}", dbView.Schema, dbView.Name, this.GetType().ToString(), MethodInfo.GetCurrentMethod().Name, ex.Message)); return(status); } return(new questStatus(Severity.Success)); }
public questStatus Save(DbMgrTransaction trans, FilterId filterId, Filter filter) { // Initialize questStatus status = null; Mgr mgr = new Mgr(this.UserSession); ColumnsMgr columnMgr = new ColumnsMgr(this.UserSession); try { // Validate filter status = Verify(filterId, filter); if (!questStatusDef.IsSuccess(status)) { return(status); } // Remove filter entities, items and values. Also, tables and columns. status = _dbFilterMgr.Clear(trans, filterId); if (!questStatusDef.IsSuccess(status)) { return(status); } // Save filter tables FilterTablesMgr filterTablesMgr = new FilterTablesMgr(this.UserSession); foreach (FilterTable filterTable in filter.FilterTableList) { filterTable.FilterId = filter.Id; FilterTableId filterTableId = null; status = filterTablesMgr.Create(trans, filterTable, out filterTableId); if (!questStatusDef.IsSuccess(status)) { return(status); } filterTable.Id = filterTableId.Id; } // Save filter views FilterViewsMgr filterViewsMgr = new FilterViewsMgr(this.UserSession); foreach (FilterView _filterView in filter.FilterViewList) { _filterView.FilterId = filter.Id; FilterViewId filterViewId = null; status = filterViewsMgr.Create(trans, _filterView, out filterViewId); if (!questStatusDef.IsSuccess(status)) { return(status); } _filterView.Id = filterViewId.Id; } // Save filter columns DbFilterMgr dbFilterMgr = new DbFilterMgr(this.UserSession); FilterColumnsMgr filterColumnsMgr = new FilterColumnsMgr(this.UserSession); foreach (FilterColumn filterColumn in filter.FilterColumnList) { if (filterColumn.FilterEntityId < BaseId.VALID_ID) { // Get the FilterTable or FilterView Id. // This is a klugie. Gotta rework DataManager panel first, though. No time to do that. if (filterColumn.FilterEntityTypeId == FilterEntityType.Table) { TablesetTable tablesetTable = null; status = dbFilterMgr.GetTablesetTable(filterColumn, out tablesetTable); if (!questStatusDef.IsSuccess(status)) { return(status); } FilterTable filterTable = filter.FilterTableList.Find(delegate(FilterTable t) { return(t.Schema == tablesetTable.Schema && t.Name == tablesetTable.Name); }); if (filterTable == null) { return(new questStatus(Severity.Error, String.Format("ERROR: FilterTable not found for TableSetTable Schema:{0} Name:{1}", tablesetTable.Schema, tablesetTable.Name))); } filterColumn.FilterEntityId = filterTable.Id; } else if (filterColumn.FilterEntityTypeId == FilterEntityType.View) { TablesetView tablesetView = null; status = dbFilterMgr.GetTablesetView(filterColumn, out tablesetView); if (!questStatusDef.IsSuccess(status)) { return(status); } FilterView filterView = filter.FilterViewList.Find(delegate(FilterView v) { return(v.Schema == tablesetView.Schema && v.Name == tablesetView.Name); }); if (filterView == null) { return(new questStatus(Severity.Error, String.Format("ERROR: FilterView not found for TableSetTable Schema:{0} Name:{1}", tablesetView.Schema, tablesetView.Name))); } filterColumn.FilterEntityId = filterView.Id; } else { return(new questStatus(Severity.Error, String.Format("ERROR: FilterColumn FilterEntityTypeId {0} not supported. FilterColumn {1}", filterColumn.FilterEntityTypeId, filterColumn.Id))); } } // Save FilterColumnId filterColumnId = null; status = filterColumnsMgr.Create(trans, filterColumn, out filterColumnId); if (!questStatusDef.IsSuccess(status)) { return(status); } filterColumn.Id = filterColumnId.Id; } // Save filter items. FilterItemsMgr filterItemsMgr = new FilterItemsMgr(this.UserSession); FilterItemJoinsMgr filterItemJoinsMgr = new FilterItemJoinsMgr(this.UserSession); FilterOperationsMgr filterOperationsMgr = new FilterOperationsMgr(this.UserSession); FilterValuesMgr filterValuesMgr = new FilterValuesMgr(this.UserSession); foreach (FilterItem filterItem in filter.FilterItemList) { FilterColumn filterColumn = null; // Get filterItem FilterEntityId if (filterItem.FilterEntityId < BaseId.VALID_ID) { status = dbFilterMgr.GetFilterColumn(filter, filterItem, out filterColumn); if (!questStatusDef.IsSuccess(status)) { return(status); } filterItem.FilterEntityId = filterColumn.Id; filterItem.FilterColumn = filterColumn; // helps with bookkeeping further down. } // Save FilterItem FilterItemId filterItemId = null; status = filterItemsMgr.Create(trans, filterItem, out filterItemId); if (!questStatusDef.IsSuccess(status)) { return(status); } filterItem.Id = filterItemId.Id; // Save filter item joins foreach (FilterItemJoin filterItemJoin in filterItem.JoinList) { filterItemJoin.FilterItemId = filterItemId.Id; if (string.IsNullOrEmpty(filterItemJoin.SourceSchema) || string.IsNullOrEmpty(filterItemJoin.SourceEntityName) || string.IsNullOrEmpty(filterItemJoin.SourceColumnName)) { // Get join target type (Table or View) TablesetColumnId tablesetColumnId = new TablesetColumnId(filterItemJoin.ColumnId); TablesetTable tablesetTable = null; TablesetView tablesetView = null; status = getJoinTarget(tablesetColumnId, out tablesetTable, out tablesetView); if (!questStatusDef.IsSuccess(status)) { return(status); } filterItemJoin.TargetEntityTypeId = tablesetTable == null ? FilterEntityType.View : FilterEntityType.Table; // Get target identifier parts. string targetSchema = null; string targetEntityName = null; string targetColumnName = null; SQLIdentifier.ParseThreePartIdentifier(filterItemJoin.Identifier, out targetSchema, out targetEntityName, out targetColumnName); filterItemJoin.TargetSchema = targetSchema; filterItemJoin.TargetEntityName = targetEntityName; filterItemJoin.TargetColumnName = targetColumnName; // Get source identifier parts. string sourceSchema = null; string sourceEntityName = null; string sourceColumnName = null; if (filterColumn.FilterEntityTypeId == FilterEntityType.Table) { FilterTable filterTable = filter.FilterTableList.Find(delegate(FilterTable t) { return(filterColumn.FilterEntityId == t.Id); }); if (filterTable == null) { return(new questStatus(Severity.Error, String.Format("ERROR: building Join on {0}, FilterTable {1} not found", filterItemJoin.Identifier, filterColumn.FilterEntityId))); } sourceSchema = filterTable.Schema; sourceEntityName = filterTable.Name; } else if (filterColumn.FilterEntityTypeId == FilterEntityType.View) { FilterView filterView = filter.FilterViewList.Find(delegate(FilterView v) { return(filterColumn.FilterEntityId == v.Id); }); if (filterView == null) { return(new questStatus(Severity.Error, String.Format("ERROR: building Join on {0}, FilterView {1} not found", filterItemJoin.Identifier, filterColumn.FilterEntityId))); } sourceSchema = filterView.Schema; sourceEntityName = filterView.Name; } sourceColumnName = filterColumn.Name; filterItemJoin.SourceSchema = sourceSchema; filterItemJoin.SourceEntityName = sourceEntityName; filterItemJoin.SourceColumnName = sourceColumnName; } // Create FilterItemJoin FilterItemJoinId filterItemJoinId = null; status = filterItemJoinsMgr.Create(trans, filterItemJoin, out filterItemJoinId); if (!questStatusDef.IsSuccess(status)) { return(status); } } // Save filter operations foreach (FilterOperation filterOperation in filterItem.OperationList) { filterOperation.FilterItemId = filterItem.Id; FilterOperationId filterOperationId = null; status = filterOperationsMgr.Create(trans, filterOperation, out filterOperationId); if (!questStatusDef.IsSuccess(status)) { return(status); } // Save filter values foreach (FilterValue filterValue in filterOperation.ValueList) { filterValue.FilterOperationId = filterOperationId.Id; FilterValueId filterValueId = null; status = filterValuesMgr.Create(trans, filterValue, out filterValueId); if (!questStatusDef.IsSuccess(status)) { return(status); } } } } // Save filter procedures // Get the filter's database. Quest.Functional.MasterPricing.Database database = null; status = GetFilterDatabase(filterId, out database); if (!questStatusDef.IsSuccess(status)) { return(status); } // NOTE: business rules reads in procedure parameters and writes them. FilterProceduresMgr filterProceduresMgr = new FilterProceduresMgr(this.UserSession); foreach (FilterProcedure filterProcedure in filter.FilterProcedureList) { if (string.IsNullOrEmpty(filterProcedure.Name)) { continue; } filterProcedure.FilterId = filter.Id; FilterProcedureId filterProcedureId = null; status = filterProceduresMgr.Create(trans, database, filterProcedure, out filterProcedureId); if (!questStatusDef.IsSuccess(status)) { return(status); } } // NOTE: YOU STILL HAVE TO UPDATE THE FILTER'S SQL. BUT, CAN'T HERE, B/C YOU'RE IN A TRANSACTION. // TODO: REFACTOR SO YOU CAN, BUT GOTTA GET A PARAMETERS MAKE-REQUIRED UPDATE OUT THERE FIRST. } 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 questStatus RefreshSchema(Quest.Functional.MasterPricing.Database database) { DatabaseId databaseId = new DatabaseId(database.Id); return(RefreshSchema(databaseId)); }
public questStatus GetFilterDatabase(Quest.Functional.MasterPricing.FilterId filterId, out Quest.Functional.MasterPricing.Database database) { // Initialize questStatus status = null; database = null; // Get filter Filter filter = null; FiltersMgr filtersMgr = new FiltersMgr(this.UserSession); status = filtersMgr.Read(filterId, out filter); if (!questStatusDef.IsSuccess(status)) { return(status); } // Get tableset Tableset tableset = null; TablesetId tablesetId = new TablesetId(filter.TablesetId); TablesetsMgr tablesetsMgr = new TablesetsMgr(this.UserSession); status = tablesetsMgr.Read(tablesetId, out tableset); if (!questStatusDef.IsSuccess(status)) { return(status); } // Get database DatabaseId databaseId = new DatabaseId(tableset.DatabaseId); DatabasesMgr databasesMgr = new DatabasesMgr(this.UserSession); status = databasesMgr.Read(databaseId, out database); if (!questStatusDef.IsSuccessOrWarning(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 questStatus PerformBulkUpdate(BulkUpdateRequest bulkUpdateRequest, out int numRows) { // Initialize questStatus status = null; numRows = -1; try { // 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); } 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); } using (SqlConnection conn = new SqlConnection(database.ConnectionString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand(null, conn)) { cmd.CommandType = CommandType.Text; cmd.CommandText = bulkUpdateRequest.SQL; try { int retval = cmd.ExecuteNonQuery(); if (retval < 0) { return(new questStatus(Severity.Error, String.Format("ERROR: Bulk update failed: {0}", numRows))); } if (retval == 0) { return(new questStatus(Severity.Warning, String.Format("WARNING: {0} rows bulk updated", numRows))); } numRows += retval; } catch (System.Exception ex) { return(new questStatus(Severity.Error, String.Format("EXCEPTION: Bulk update failed: {0}", ex.Message))); } } } } 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)); }