예제 #1
0
        /*==================================================================================================================================
        * 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));
        }
예제 #2
0
        /*==================================================================================================================================
        * 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));
        }
예제 #3
0
        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));
        }
예제 #6
0
        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));
        }
예제 #9
0
        /*==================================================================================================================================
        * 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));
        }
예제 #10
0
        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));
        }
예제 #11
0
        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));
        }
예제 #12
0
        /*==================================================================================================================================
        * 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));
        }
예제 #13
0
        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));
        }
예제 #14
0
        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));
        }
예제 #15
0
        /*----------------------------------------------------------------------------------------------------------------------------------
        * 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));
        }
예제 #16
0
        /*==================================================================================================================================
        * 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));
        }
예제 #17
0
        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));
        }
예제 #18
0
        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));
        }
예제 #19
0
        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));
        }
예제 #20
0
        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));
        }
예제 #21
0
        /*==================================================================================================================================
        * 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));
        }
예제 #23
0
        /* 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));
        }
예제 #24
0
        /*==================================================================================================================================
        * 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));
        }
예제 #25
0
        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));
        }
예제 #26
0
        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));
        }
예제 #27
0
        public questStatus RefreshSchema(Quest.Functional.MasterPricing.Database database)
        {
            DatabaseId databaseId = new DatabaseId(database.Id);

            return(RefreshSchema(databaseId));
        }
예제 #28
0
        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));
        }
예제 #29
0
        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));
        }
예제 #30
0
        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));
        }