Ejemplo n.º 1
0
        /*----------------------------------------------------------------------------------------------------------------------------------
        * Logging
        *---------------------------------------------------------------------------------------------------------------------------------*/
        public questStatus LogBulkUpdateEvent(BulkUpdateLog bulkUpdateLog)
        {
            // Initialize
            questStatus status = null;


            // Check if logging on.
            if (!this._logSetting.bLogBulkUpdates)
            {
                return(new questStatus(Severity.Warning, "Bulk Update Logging OFF"));
            }


            // Log event
            BulkUpdateLogId bulkUpdateLogId = null;

            status = _dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId);
            if (!questStatusDef.IsSuccess(status))
            {
                return(status);
            }
            bulkUpdateLog.Id = bulkUpdateLogId.Id;


            return(new questStatus(Severity.Success));
        }
Ejemplo n.º 2
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));
        }