/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus GetDatabase(DatabaseId databaseId, out DatabaseBaseViewModel databaseBaseViewModel)
{
// Initialize
questStatus status = null;
databaseBaseViewModel = null;
// Read the database
Quest.Functional.MasterPricing.Database database = null;
DatabasesMgr databasesMgr = new DatabasesMgr(this.UserSession);
status = databasesMgr.Read(databaseId, out database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Transfer model.
databaseBaseViewModel = new DatabaseBaseViewModel(this.UserSession);
BufferMgr.TransferBuffer(database, databaseBaseViewModel);
databaseBaseViewModel.LastRefresh = database.LastRefresh.HasValue ?
database.LastRefresh.Value.ToString("MM/dd/yyyy HH:mm:ss") : "";
return(new questStatus(Severity.Success));
}
/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
#region Identifer-based Usage
//
// Identifer-based Usage
//
public questStatus Create(Quest.Functional.MasterPricing.Database database, out DatabaseId databaseId)
{
// Initialize
questStatus status = null;
databaseId = null;
// Create the database
DatabasesMgr databasesMgr = new DatabasesMgr(this.UserSession);
status = databasesMgr.Create(database, out databaseId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Populate the database metadata
status = RefreshSchema(databaseId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
public questStatus Read(DatabaseId databaseId, out DatabaseEditorViewModel databaseEditorViewModel)
{
// Initialize
questStatus status = null;
databaseEditorViewModel = null;
// Read
Quest.Functional.MasterPricing.Database database = null;
DatabasesMgr databasesMgr = new DatabasesMgr(this.UserSession);
status = databasesMgr.Read(databaseId, out database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Transfer model.
databaseEditorViewModel = new DatabaseEditorViewModel(this.UserSession);
BufferMgr.TransferBuffer(database, databaseEditorViewModel);
return(new questStatus(Severity.Success));
}
public questStatus Update(DbMgrTransaction trans, Quest.Functional.MasterPricing.Database database)
{
// Initialize
questStatus status = null;
bool bCreateTransaction = trans == null;
// Perform update in this transaction.
status = update((MasterPricingEntities)trans.DbContext, database);
if (bLogging)
{
DatabaseLog databaseLog = new DatabaseLog();
databaseLog.Name = database.Name == null ? "(null)" : database.Name;
databaseLog.Event = "UPDATE";
databaseLog.Data = status.ToString();
DatabaseLogId databaseLogId = null;
_dbDatabaseLogsMgr.Create(databaseLog, out databaseLogId);
}
if (!questStatusDef.IsSuccess(status))
{
RollbackTransaction(trans);
return(status);
}
return(new questStatus(Severity.Success));
}
/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus Create(Quest.Functional.MasterPricing.Database database, out Quest.Functional.MasterPricing.DatabaseId databaseId)
{
// Initialize
questStatus status = null;
databaseId = null;
// Data rules.
// Create the database
using (MasterPricingEntities dbContext = new MasterPricingEntities())
{
status = create(dbContext, database, out databaseId);
if (bLogging)
{
DatabaseLog databaseLog = new DatabaseLog();
databaseLog.Name = database.Name == null ? "(null)" : database.Name;
databaseLog.Event = "CREATE";
databaseLog.Data = status.ToString();
DatabaseLogId databaseLogId = null;
_dbDatabaseLogsMgr.Create(databaseLog, out databaseLogId);
}
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
return(new questStatus(Severity.Success));
}
public questStatus Update(Quest.Functional.MasterPricing.Database database, bool bRefreshSchema = false)
{
// Initialize
questStatus status = null;
// Update the database
DatabasesMgr databasesMgr = new DatabasesMgr(this.UserSession);
status = databasesMgr.Update(database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Populate the database metadata
if (bRefreshSchema)
{
status = RefreshSchema(database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
return(new questStatus(Severity.Success));
}
public TablesetConfigurationViewModel()
{
Tableset = new Tableset();
Database = new Quest.Functional.MasterPricing.Database();
TableList = new List <BootstrapTreenodeViewModel>();
ViewList = new List <BootstrapTreenodeViewModel>();
DBTableList = new List <BootstrapTreenodeViewModel>();
DBViewList = new List <BootstrapTreenodeViewModel>();
}
public questStatus PerformBulkInsert(BulkInsertRequest bulkInsertRequest)
{
// Initialize
questStatus status = null;
// Execute bulk insert SQL
try
{
// Get tableset
TablesetId tablesetId = new TablesetId(bulkInsertRequest.Filter.TablesetId);
Tableset tableset = null;
DbTablesetsMgr dbTablesetsMgr = new DbTablesetsMgr(this.UserSession);
status = dbTablesetsMgr.Read(tablesetId, out tableset);
if (!questStatusDef.IsSuccessOrWarning(status))
{
return(status);
}
// Get database
DatabaseId databaseId = new DatabaseId(tableset.DatabaseId);
Quest.Functional.MasterPricing.Database database = null;
DbDatabasesMgr dbDatabasesMgr = new DbDatabasesMgr(this.UserSession);
status = dbDatabasesMgr.Read(databaseId, out database);
if (!questStatusDef.IsSuccessOrWarning(status))
{
return(status);
}
// Execute sql
using (SqlConnection sqlConnection = new SqlConnection(database.ConnectionString))
{
sqlConnection.Open();
using (SqlCommand cmd = sqlConnection.CreateCommand())
{
cmd.CommandText = bulkInsertRequest.SQL;
cmd.CommandType = CommandType.Text;
int numRows = cmd.ExecuteNonQuery();
if (numRows != bulkInsertRequest.Rows.Count)
{
return(new questStatus(Severity.Error, String.Format("ERROR: Bulk insert SQL execution failed: Rows: {0}", numRows)));
}
}
}
}
catch (System.Exception ex)
{
return(new questStatus(Severity.Fatal, String.Format("EXCEPTION: executing bulk insert SQL {0} SQL: {1}",
bulkInsertRequest.SQL, ex.Message)));
}
return(new questStatus(Severity.Success));
}
/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
#region Set Logging Field Routines
/*----------------------------------------------------------------------------------------------------------------------------------
* Set Logging Field Routines
*---------------------------------------------------------------------------------------------------------------------------------*/
public questStatus SetDatabase(Quest.Functional.MasterPricing.Database database, out string Database)
{
if (database == null)
{
Database = " Id: \"null\", Name: \"null\", ConnString: \"null\" ";
}
else
{
Database = String.Format(" Id: {0}, Name: {1}, ConnString: {2} ", database.Id, database.Name,
database.ConnectionString == null ? "(null)" : database.ConnectionString);
}
return(new questStatus(Severity.Success));
}
public questStatus Update(Quest.Functional.MasterPricing.Database database)
{
// Initialize
questStatus status = null;
// Update database
status = _dbDatabasesMgr.Update(database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
public questStatus List(QueryOptions queryOptions, out List <Quest.Functional.MasterPricing.Database> databaseList, out QueryResponse queryResponse)
{
// Initialize
questStatus status = null;
databaseList = null;
queryResponse = null;
using (MasterPricingEntities dbContext = new MasterPricingEntities())
{
using (DbContextTransaction tx = dbContext.Database.BeginTransaction(IsolationLevel.ReadUncommitted))
{
try
{
PropertyInfo[] dbProperties = typeof(Quest.Services.Dbio.MasterPricing.Databases).GetProperties().ToArray();
int totalRecords = dbContext.Databases.Where(BuildWhereClause(queryOptions, dbProperties)).Count();
List <Quest.Services.Dbio.MasterPricing.Databases> _databasesList = dbContext.Databases.Where(BuildWhereClause(queryOptions, dbProperties))
.OrderBy(BuildSortString(queryOptions.SortColumns))
.Skip(queryOptions.Paging.PageSize * (queryOptions.Paging.PageNumber - 1))
.Take(queryOptions.Paging.PageSize).ToList();
if (_databasesList == null)
{
return(new questStatus(Severity.Warning));
}
databaseList = new List <Quest.Functional.MasterPricing.Database>();
foreach (Quest.Services.Dbio.MasterPricing.Databases _database in _databasesList)
{
Quest.Functional.MasterPricing.Database database = new Quest.Functional.MasterPricing.Database();
BufferMgr.TransferBuffer(_database, database);
databaseList.Add(database);
}
status = BuildQueryResponse(totalRecords, queryOptions, out queryResponse);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
catch (System.Exception ex)
{
status = new questStatus(Severity.Fatal, String.Format("EXCEPTION: {0}.{1}: {2}",
this.GetType().Name, MethodBase.GetCurrentMethod().Name,
ex.InnerException != null ? ex.InnerException.Message : ex.Message));
LogException(ex, status);
return(status);
}
}
}
return(new questStatus(Severity.Success));
}
/*==================================================================================================================================
* Properties
*=================================================================================================================================*/
#endregion
#region Public Methods
/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus Create(Quest.Functional.MasterPricing.Database database, out Quest.Functional.MasterPricing.DatabaseId databaseId)
{
// Initialize
databaseId = null;
questStatus status = null;
// Create database
status = _dbDatabasesMgr.Create(database, out databaseId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
public questStatus Create(DbMgrTransaction trans, Quest.Functional.MasterPricing.Database database, FilterProcedureParameter filterProcedureParameter, out FilterProcedureParameterId filterProcedureParameterId)
{
// Initialize
questStatus status = null;
filterProcedureParameterId = null;
// Create filterProcedureParameter
status = _dbFilterProcedureParametersMgr.Create(trans, filterProcedureParameter, out filterProcedureParameterId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
private questStatus readDatabase(DatabaseId databaseId, out Quest.Functional.MasterPricing.Database database)
{
// Initialize
questStatus status = null;
database = null;
// Read the database.
DbDatabasesMgr dbDatabasesMgr = new DbDatabasesMgr(this._userSession);
status = dbDatabasesMgr.Read(databaseId, out database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
/*----------------------------------------------------------------------------------------------------------------------------------
* Databases
*---------------------------------------------------------------------------------------------------------------------------------*/
private questStatus create(MasterPricingEntities dbContext, Quest.Functional.MasterPricing.Database database, out DatabaseId databaseId)
{
// Initialize
questStatus status = null;
databaseId = null;
// Perform create
try
{
Quest.Services.Dbio.MasterPricing.Databases _database = new Quest.Services.Dbio.MasterPricing.Databases();
BufferMgr.TransferBuffer(database, _database);
dbContext.Databases.Add(_database);
dbContext.SaveChanges();
if (_database.Id == 0)
{
return(new questStatus(Severity.Error, "Quest.Functional.MasterPricing.Database not created"));
}
databaseId = new DatabaseId(_database.Id);
}
catch (DbEntityValidationException ex)
{
var errorMessages = ex.EntityValidationErrors.SelectMany(x => x.ValidationErrors).Select(x => x.ErrorMessage);
String fullErrorMessage = string.Join("; ", errorMessages);
String exceptionMessage = string.Concat(ex.Message, fullErrorMessage);
status = new questStatus(Severity.Fatal, String.Format("EXCEPTION: {0}.{1}: {2}",
this.GetType().Name, MethodBase.GetCurrentMethod().Name,
exceptionMessage));
LogException(ex, status);
return(status);
}
catch (System.Exception ex)
{
status = new questStatus(Severity.Fatal, String.Format("EXCEPTION: {0}.{1}: {2}",
this.GetType().Name, MethodBase.GetCurrentMethod().Name,
ex.InnerException != null ? ex.InnerException.Message : ex.Message));
LogException(ex, status);
return(status);
}
return(new questStatus(Severity.Success));
}
/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
#region CRUD
//----------------------------------------------------------------------------------------------------------------------------------
// CRUD
//----------------------------------------------------------------------------------------------------------------------------------
public questStatus Save(DatabaseEditorViewModel databaseEditorViewModel)
{
// Initialize
questStatus status = null;
// Transfer model
Quest.Functional.MasterPricing.Database database = new Quest.Functional.MasterPricing.Database();
BufferMgr.TransferBuffer(databaseEditorViewModel, database);
// Determine if this is a create or update
DatabaseMgr databaseMgr = new DatabaseMgr(this.UserSession);
if (databaseEditorViewModel.Id < BaseId.VALID_ID)
{
// Create
DatabaseId databaseId = null;
status = databaseMgr.Create(database, out databaseId);
if (!questStatusDef.IsSuccess(status))
{
if (databaseId != null && databaseId.Id >= BaseId.VALID_ID)
{
databaseEditorViewModel.Id = databaseId.Id;
}
FormatErrorMessage(status, databaseEditorViewModel);
return(status);
}
databaseEditorViewModel.Id = databaseId.Id;
}
else
{
// Update
status = databaseMgr.Update(database);
if (!questStatusDef.IsSuccess(status))
{
FormatErrorMessage(status, databaseEditorViewModel);
return(status);
}
}
return(new questStatus(Severity.Success));
}
public questStatus GetDatabaseStoredProcedures(TablesetId tablesetId, out List <StoredProcedure> storedProcedureList)
{
// Initialize
questStatus status = null;
storedProcedureList = null;
// Get tableset
Tableset tableset = null;
TablesetsMgr tablesetsMgr = new TablesetsMgr(this.UserSession);
status = tablesetsMgr.Read(tablesetId, out tableset);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Get database
Quest.Functional.MasterPricing.Database database = null;
DatabaseId databaseId = new DatabaseId(tableset.DatabaseId);
DatabasesMgr databasesMgr = new DatabasesMgr(this.UserSession);
status = databasesMgr.Read(databaseId, out database);
if (!questStatusDef.IsSuccessOrWarning(status))
{
return(status);
}
// Get database stored procedures
DbFilterProceduresMgr dbFilterProceduresMgr = new DbFilterProceduresMgr(this.UserSession);
status = GetDatabaseStoredProcedures(database, out storedProcedureList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
public new questStatus GetStoredProdecureParameters(Quest.Functional.MasterPricing.Database database, string filterProcedureName, out List <FilterProcedureParameter> filterProcedureParameterList)
{
// Initialize
questStatus status = null;
filterProcedureParameterList = null;
// Get stored procedure
DatabaseId databaseId = new DatabaseId(database.Id);
StoredProcedure storedProcedure = null;
StoredProceduresMgr storedProceduresMgr = new StoredProceduresMgr(this.UserSession);
status = storedProceduresMgr.Read(databaseId, filterProcedureName, out storedProcedure);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Get stored procedure parameters
StoredProcedureId storedProcedureId = new StoredProcedureId(storedProcedure.Id);
List <StoredProcedureParameter> storedProcedureParameterList = null;
StoredProcedureParametersMgr storedProcedureParametersMgr = new StoredProcedureParametersMgr(this.UserSession);
storedProcedureParametersMgr.Read(storedProcedureId, out storedProcedureParameterList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Transfer over to filter procedure.
filterProcedureParameterList = new List <FilterProcedureParameter>();
foreach (StoredProcedureParameter storedProcedureParameter in storedProcedureParameterList)
{
FilterProcedureParameter filterProcedureParameter = new FilterProcedureParameter();
BufferMgr.TransferBuffer(storedProcedureParameter, filterProcedureParameter);
filterProcedureParameterList.Add(filterProcedureParameter);
}
return(new questStatus(Severity.Success));
}
private questStatus getDatabaseViews(DatabaseId datbaseId, out List <DBView> dbViewList)
{
// Initialize
questStatus status = null;
dbViewList = null;
Quest.Functional.MasterPricing.Database database = null;
SqlConnection sqlConnection = null;
try
{
// Read database
status = readDatabase(datbaseId, out database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Open database
status = OpenDatabase(database, out sqlConnection);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Get views
status = getDatabaseViews(sqlConnection, out dbViewList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
catch (System.Exception ex)
{
status = new questStatus(Severity.Fatal, String.Format("EXCEPTION: {0}.{1}: {2}",
this.GetType().ToString(), MethodInfo.GetCurrentMethod().Name, ex.Message));
return(status);
}
return(new questStatus(Severity.Success));
}
public questStatus Read(DbMgrTransaction trans, Quest.Functional.MasterPricing.DatabaseId databaseId, out Quest.Functional.MasterPricing.Database database)
{
// Initialize
questStatus status = null;
database = null;
// Perform read.
using (MasterPricingEntities dbContext = new MasterPricingEntities())
{
Quest.Services.Dbio.MasterPricing.Databases _database = null;
status = read((MasterPricingEntities)trans.DbContext, databaseId, out _database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
database = new Quest.Functional.MasterPricing.Database();
BufferMgr.TransferBuffer(_database, database);
}
return(new questStatus(Severity.Success));
}
/*==================================================================================================================================
* Properties
*=================================================================================================================================*/
#endregion
#region Public Methods
/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus Create(DbMgrTransaction trans, Quest.Functional.MasterPricing.Database database, FilterProcedure filterProcedure, out FilterProcedureId filterProcedureId)
{
// Initialize
questStatus status = null;
filterProcedureId = null;
// Create filterProcedure
status = _dbFilterProceduresMgr.Create(trans, filterProcedure, out filterProcedureId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Store parameters for procedure
List <FilterProcedureParameter> filterProcedureParameterList = null;
status = GetStoredProdecureParameters(database, filterProcedure.Name, out filterProcedureParameterList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
FilterProcedureParametersMgr filterProcedureParametersMgr = new FilterProcedureParametersMgr(this.UserSession);
foreach (FilterProcedureParameter filterProcedureParameter in filterProcedureParameterList)
{
filterProcedureParameter.FilterProcedureId = filterProcedureId.Id;
FilterProcedureParameterId filterProcedureParameterId = null;
status = filterProcedureParametersMgr.Create(trans, database, filterProcedureParameter, out filterProcedureParameterId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
return(new questStatus(Severity.Success));
}
/*==================================================================================================================================
* Properties
*=================================================================================================================================*/
#endregion
#region Public Methods
/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus Create(DbMgrTransaction trans, Quest.Functional.MasterPricing.Database database, Quest.Functional.MasterPricing.StoredProcedure storedProcedure, out Quest.Functional.MasterPricing.StoredProcedureId storedProcedureId)
{
// Initialize
questStatus status = null;
storedProcedureId = null;
// Create storedProcedure
status = _dbStoredProceduresMgr.Create(trans, storedProcedure, out storedProcedureId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Store parameters for procedure
List <StoredProcedureParameter> storedProcedureParameterList = null;
status = GetStoredProdecureParameters(database, storedProcedure.Name, out storedProcedureParameterList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
StoredProcedureParametersMgr storedProcedureParametersMgr = new StoredProcedureParametersMgr(this.UserSession);
foreach (StoredProcedureParameter storedProcedureParameter in storedProcedureParameterList)
{
storedProcedureParameter.StoredProcedureId = storedProcedureId.Id;
StoredProcedureParameterId storedProcedureParameterId = null;
status = storedProcedureParametersMgr.Create(trans, database, storedProcedureParameter, out storedProcedureParameterId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
return(new questStatus(Severity.Success));
}
/* Used or useful? */
public questStatus GetColumnInfo(TablesetColumn tablesetColumn, out Column column)
{
// Initialize
questStatus status = null;
column = null;
DbTablesetTablesMgr dbTablesetTablesMgr = new DbTablesetTablesMgr(this._userSession);
DbTablesetViewsMgr dbTablesetViewsMgr = new DbTablesetViewsMgr(this._userSession);
TablesetId tablesetId = null;
TablesetTable tablesetTable = null;
TablesetView tablesetView = null;
if (tablesetColumn.EntityTypeId == EntityType.Table)
{
// Get TablesetTable
TablesetTableId tablesetTableId = new TablesetTableId(tablesetColumn.TableSetEntityId);
status = dbTablesetTablesMgr.Read(tablesetTableId, out tablesetTable);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
tablesetId = new TablesetId(tablesetTable.TablesetId);
}
else if (tablesetColumn.EntityTypeId == EntityType.View)
{
// Get TablesetView
TablesetViewId tablesetViewId = new TablesetViewId(tablesetColumn.TableSetEntityId);
status = dbTablesetViewsMgr.Read(tablesetViewId, out tablesetView);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
tablesetId = new TablesetId(tablesetView.TablesetId);
}
else
{
return(new questStatus(Severity.Error, String.Format("ERROR: unsupported EntityTypeId {0} for TablesetColumn {1}",
tablesetColumn.EntityTypeId, tablesetColumn.Id)));
}
// Get the Tableset
DbTablesetsMgr dbTablesetsMgr = new DbTablesetsMgr(this._userSession);
Tableset tableset = null;
status = dbTablesetsMgr.Read(tablesetId, out tableset);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Get the database
DatabaseId databaseId = new DatabaseId(tableset.DatabaseId);
Quest.Functional.MasterPricing.Database database = null;
DbDatabasesMgr dbDatabasesMgr = new DbDatabasesMgr(this._userSession);
status = dbDatabasesMgr.Read(databaseId, out database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Get the Table or View by identifier within this database
DbTablesMgr dbTablesMgr = new DbTablesMgr(this._userSession);
DbViewsMgr dbViewsMgr = new DbViewsMgr(this._userSession);
EntityTypeId entityTypeId = null;
EntityId entityId = null;
if (tablesetColumn.EntityTypeId == EntityType.Table)
{
TableId tableId = new TableId();
Table table = null;
status = dbTablesMgr.Read(databaseId, tablesetTable.Schema, tablesetTable.Name, out table);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
entityTypeId = new EntityTypeId(EntityType.Table);
entityId = new EntityId(table.Id);
}
else if (tablesetColumn.EntityTypeId == EntityType.View)
{
}
// Get column info
DbColumnsMgr dbColumnsMgr = new DbColumnsMgr(this._userSession);
status = dbColumnsMgr.Read(entityTypeId, entityId, tablesetColumn.Name, out column);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
/*==================================================================================================================================
* Properties
*=================================================================================================================================*/
#endregion
#region Public Methods
/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus GetTypeListOptions(TypeListId typeListId, List <TypeListArgument> typeListArgumentList, out List <OptionValuePair> typeListOptionList)
{
// Initialize
questStatus status = null;
typeListOptionList = null;
// Get the typeList.
TypeList typeList = null;
DbTypeListsMgr dbTypeListsMgr = new DbTypeListsMgr(this.UserSession);
status = dbTypeListsMgr.Read(typeListId, out typeList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Get the database.
DatabaseId databaseId = new DatabaseId(typeList.DatabaseId);
Quest.Functional.MasterPricing.Database database = null;
DbDatabasesMgr dbDatabasesMgr = new DbDatabasesMgr(this.UserSession);
status = dbDatabasesMgr.Read(databaseId, out database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Apply typeList arguments.
if (typeListArgumentList == null)
{
typeListArgumentList = new List <TypeListArgument>();
}
string sql = typeList.SQL;
foreach (TypeListArgument typeListArgument in typeListArgumentList)
{
sql = sql.Replace(typeListArgument.Name, typeListArgument.Value);
}
try
{
using (SqlConnection conn = new SqlConnection(database.ConnectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(null, conn))
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;
using (SqlDataReader rdr = cmd.ExecuteReader())
{
typeListOptionList = new List <OptionValuePair>();
while (rdr.Read())
{
OptionValuePair optionValuePair = new OptionValuePair();
optionValuePair.Id = rdr[0].ToString();
optionValuePair.Label = rdr[0].ToString();
typeListOptionList.Add(optionValuePair);
}
}
}
}
}
catch (System.Exception ex)
{
status = new questStatus(Severity.Fatal, String.Format("EXCEPTION: {0}.{1}: {2}",
this.GetType().ToString(), MethodInfo.GetCurrentMethod().Name, ex.Message));
return(status);
}
return(new questStatus(Severity.Success));
}
private questStatus getDatabaseColumns(DatabaseId databaseId, out Dictionary <DBView, List <Column> > dictDBColumns)
{
// Initialize
questStatus status = null;
dictDBColumns = null;
List <DBView> dbViewList = null;
SqlConnection sqlConnection = null;
DBView dbView = null;
try
{
// Get database
Quest.Functional.MasterPricing.Database database = null;
DbDatabasesMgr dbDatabasesMgr = new DbDatabasesMgr(this._userSession);
status = dbDatabasesMgr.Read(databaseId, out database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Open database
status = OpenDatabase(database, out sqlConnection);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Get views.
status = getDatabaseViews(databaseId, out dbViewList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
dictDBColumns = new Dictionary <DBView, List <Column> >();
// Get column information.
for (int vidx = 0; vidx < dbViewList.Count; vidx += 1)
{
dbView = dbViewList[vidx];
using (SqlCommand sqlCommand = sqlConnection.CreateCommand())
{
sqlCommand.CommandText = String.Format("SELECT TOP 1 * FROM [{0}].[{1}] (NOLOCK) ", dbView.Schema, dbView.Name);
sqlCommand.CommandType = CommandType.Text;
using (SqlDataReader rdr = sqlCommand.ExecuteReader())
{
DataTable schemaTable = rdr.GetSchemaTable();
List <Column> columnList = new List <Column>();
foreach (DataRow myField in schemaTable.Rows)
{
Column column = new Column();
column.Name = myField["ColumnName"].ToString();
column.DisplayOrder = Convert.ToInt32(myField["ColumnOrdinal"]);
column.ColumnSize = Convert.ToInt32(myField["ColumnSize"]);
column.DataType = myField["DataType"].ToString();
column.DataTypeName = myField["DataTypeName"].ToString();
column.bIsIdentity = Convert.ToBoolean(myField["IsIdentity"]);
column.bAllowDbNull = Convert.ToBoolean(myField["AllowDbNull"]);
columnList.Add(column);
}
dictDBColumns.Add(dbView, columnList);
}
}
}
}
catch (System.Exception ex)
{
status = new questStatus(Severity.Fatal, String.Format("EXCEPTION: loading view [{0}].[{1}] columns {2}.{3}: {4}",
dbView.Schema, dbView.Name, this.GetType().ToString(), MethodInfo.GetCurrentMethod().Name, ex.Message));
return(status);
}
return(new questStatus(Severity.Success));
}
public questStatus Save(DbMgrTransaction trans, FilterId filterId, Filter filter)
{
// Initialize
questStatus status = null;
Mgr mgr = new Mgr(this.UserSession);
ColumnsMgr columnMgr = new ColumnsMgr(this.UserSession);
try
{
// Validate filter
status = Verify(filterId, filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Remove filter entities, items and values. Also, tables and columns.
status = _dbFilterMgr.Clear(trans, filterId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Save filter tables
FilterTablesMgr filterTablesMgr = new FilterTablesMgr(this.UserSession);
foreach (FilterTable filterTable in filter.FilterTableList)
{
filterTable.FilterId = filter.Id;
FilterTableId filterTableId = null;
status = filterTablesMgr.Create(trans, filterTable, out filterTableId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
filterTable.Id = filterTableId.Id;
}
// Save filter views
FilterViewsMgr filterViewsMgr = new FilterViewsMgr(this.UserSession);
foreach (FilterView _filterView in filter.FilterViewList)
{
_filterView.FilterId = filter.Id;
FilterViewId filterViewId = null;
status = filterViewsMgr.Create(trans, _filterView, out filterViewId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
_filterView.Id = filterViewId.Id;
}
// Save filter columns
DbFilterMgr dbFilterMgr = new DbFilterMgr(this.UserSession);
FilterColumnsMgr filterColumnsMgr = new FilterColumnsMgr(this.UserSession);
foreach (FilterColumn filterColumn in filter.FilterColumnList)
{
if (filterColumn.FilterEntityId < BaseId.VALID_ID)
{
// Get the FilterTable or FilterView Id.
// This is a klugie. Gotta rework DataManager panel first, though. No time to do that.
if (filterColumn.FilterEntityTypeId == FilterEntityType.Table)
{
TablesetTable tablesetTable = null;
status = dbFilterMgr.GetTablesetTable(filterColumn, out tablesetTable);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
FilterTable filterTable = filter.FilterTableList.Find(delegate(FilterTable t) { return(t.Schema == tablesetTable.Schema && t.Name == tablesetTable.Name); });
if (filterTable == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: FilterTable not found for TableSetTable Schema:{0} Name:{1}",
tablesetTable.Schema, tablesetTable.Name)));
}
filterColumn.FilterEntityId = filterTable.Id;
}
else if (filterColumn.FilterEntityTypeId == FilterEntityType.View)
{
TablesetView tablesetView = null;
status = dbFilterMgr.GetTablesetView(filterColumn, out tablesetView);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
FilterView filterView = filter.FilterViewList.Find(delegate(FilterView v) { return(v.Schema == tablesetView.Schema && v.Name == tablesetView.Name); });
if (filterView == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: FilterView not found for TableSetTable Schema:{0} Name:{1}",
tablesetView.Schema, tablesetView.Name)));
}
filterColumn.FilterEntityId = filterView.Id;
}
else
{
return(new questStatus(Severity.Error, String.Format("ERROR: FilterColumn FilterEntityTypeId {0} not supported. FilterColumn {1}",
filterColumn.FilterEntityTypeId, filterColumn.Id)));
}
}
// Save
FilterColumnId filterColumnId = null;
status = filterColumnsMgr.Create(trans, filterColumn, out filterColumnId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
filterColumn.Id = filterColumnId.Id;
}
// Save filter items.
FilterItemsMgr filterItemsMgr = new FilterItemsMgr(this.UserSession);
FilterItemJoinsMgr filterItemJoinsMgr = new FilterItemJoinsMgr(this.UserSession);
FilterOperationsMgr filterOperationsMgr = new FilterOperationsMgr(this.UserSession);
FilterValuesMgr filterValuesMgr = new FilterValuesMgr(this.UserSession);
foreach (FilterItem filterItem in filter.FilterItemList)
{
FilterColumn filterColumn = null;
// Get filterItem FilterEntityId
if (filterItem.FilterEntityId < BaseId.VALID_ID)
{
status = dbFilterMgr.GetFilterColumn(filter, filterItem, out filterColumn);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
filterItem.FilterEntityId = filterColumn.Id;
filterItem.FilterColumn = filterColumn; // helps with bookkeeping further down.
}
// Save FilterItem
FilterItemId filterItemId = null;
status = filterItemsMgr.Create(trans, filterItem, out filterItemId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
filterItem.Id = filterItemId.Id;
// Save filter item joins
foreach (FilterItemJoin filterItemJoin in filterItem.JoinList)
{
filterItemJoin.FilterItemId = filterItemId.Id;
if (string.IsNullOrEmpty(filterItemJoin.SourceSchema) || string.IsNullOrEmpty(filterItemJoin.SourceEntityName) || string.IsNullOrEmpty(filterItemJoin.SourceColumnName))
{
// Get join target type (Table or View)
TablesetColumnId tablesetColumnId = new TablesetColumnId(filterItemJoin.ColumnId);
TablesetTable tablesetTable = null;
TablesetView tablesetView = null;
status = getJoinTarget(tablesetColumnId, out tablesetTable, out tablesetView);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
filterItemJoin.TargetEntityTypeId = tablesetTable == null ? FilterEntityType.View : FilterEntityType.Table;
// Get target identifier parts.
string targetSchema = null;
string targetEntityName = null;
string targetColumnName = null;
SQLIdentifier.ParseThreePartIdentifier(filterItemJoin.Identifier, out targetSchema, out targetEntityName, out targetColumnName);
filterItemJoin.TargetSchema = targetSchema;
filterItemJoin.TargetEntityName = targetEntityName;
filterItemJoin.TargetColumnName = targetColumnName;
// Get source identifier parts.
string sourceSchema = null;
string sourceEntityName = null;
string sourceColumnName = null;
if (filterColumn.FilterEntityTypeId == FilterEntityType.Table)
{
FilterTable filterTable = filter.FilterTableList.Find(delegate(FilterTable t) { return(filterColumn.FilterEntityId == t.Id); });
if (filterTable == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: building Join on {0}, FilterTable {1} not found",
filterItemJoin.Identifier, filterColumn.FilterEntityId)));
}
sourceSchema = filterTable.Schema;
sourceEntityName = filterTable.Name;
}
else if (filterColumn.FilterEntityTypeId == FilterEntityType.View)
{
FilterView filterView = filter.FilterViewList.Find(delegate(FilterView v) { return(filterColumn.FilterEntityId == v.Id); });
if (filterView == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: building Join on {0}, FilterView {1} not found",
filterItemJoin.Identifier, filterColumn.FilterEntityId)));
}
sourceSchema = filterView.Schema;
sourceEntityName = filterView.Name;
}
sourceColumnName = filterColumn.Name;
filterItemJoin.SourceSchema = sourceSchema;
filterItemJoin.SourceEntityName = sourceEntityName;
filterItemJoin.SourceColumnName = sourceColumnName;
}
// Create FilterItemJoin
FilterItemJoinId filterItemJoinId = null;
status = filterItemJoinsMgr.Create(trans, filterItemJoin, out filterItemJoinId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
// Save filter operations
foreach (FilterOperation filterOperation in filterItem.OperationList)
{
filterOperation.FilterItemId = filterItem.Id;
FilterOperationId filterOperationId = null;
status = filterOperationsMgr.Create(trans, filterOperation, out filterOperationId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Save filter values
foreach (FilterValue filterValue in filterOperation.ValueList)
{
filterValue.FilterOperationId = filterOperationId.Id;
FilterValueId filterValueId = null;
status = filterValuesMgr.Create(trans, filterValue, out filterValueId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
}
}
// Save filter procedures
// Get the filter's database.
Quest.Functional.MasterPricing.Database database = null;
status = GetFilterDatabase(filterId, out database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// NOTE: business rules reads in procedure parameters and writes them.
FilterProceduresMgr filterProceduresMgr = new FilterProceduresMgr(this.UserSession);
foreach (FilterProcedure filterProcedure in filter.FilterProcedureList)
{
if (string.IsNullOrEmpty(filterProcedure.Name))
{
continue;
}
filterProcedure.FilterId = filter.Id;
FilterProcedureId filterProcedureId = null;
status = filterProceduresMgr.Create(trans, database, filterProcedure, out filterProcedureId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
// NOTE: YOU STILL HAVE TO UPDATE THE FILTER'S SQL. BUT, CAN'T HERE, B/C YOU'RE IN A TRANSACTION.
// TODO: REFACTOR SO YOU CAN, BUT GOTTA GET A PARAMETERS MAKE-REQUIRED UPDATE OUT THERE FIRST.
}
catch (System.Exception ex)
{
return(new questStatus(Severity.Fatal, String.Format("EXCEPTION: {0}.{1}: {2}",
this.GetType().Name, MethodBase.GetCurrentMethod().Name,
ex.InnerException != null ? ex.InnerException.Message : ex.Message)));
}
return(new questStatus(Severity.Success));
}
public questStatus RefreshSchema(Quest.Functional.MasterPricing.Database database)
{
DatabaseId databaseId = new DatabaseId(database.Id);
return(RefreshSchema(databaseId));
}
public questStatus GetFilterDatabase(Quest.Functional.MasterPricing.FilterId filterId, out Quest.Functional.MasterPricing.Database database)
{
// Initialize
questStatus status = null;
database = null;
// Get filter
Filter filter = null;
FiltersMgr filtersMgr = new FiltersMgr(this.UserSession);
status = filtersMgr.Read(filterId, out filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Get tableset
Tableset tableset = null;
TablesetId tablesetId = new TablesetId(filter.TablesetId);
TablesetsMgr tablesetsMgr = new TablesetsMgr(this.UserSession);
status = tablesetsMgr.Read(tablesetId, out tableset);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Get database
DatabaseId databaseId = new DatabaseId(tableset.DatabaseId);
DatabasesMgr databasesMgr = new DatabasesMgr(this.UserSession);
status = databasesMgr.Read(databaseId, out database);
if (!questStatusDef.IsSuccessOrWarning(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
public questStatus PerformBulkUpdateFilterProcedure(BulkUpdateRequest bulkUpdateRequest, FilterProcedure filterProcedure, ResultsSet resultsSet)
{
// Initialize
questStatus status = null;
BulkUpdateLog bulkUpdateLog = bLogging ? new BulkUpdateLog() : null;
BulkUpdateLogId bulkUpdateLogId = null;
int numRows = 0;
string logMessage = null;
List <string> logParameterList = null;
try
{
// Initialize log
if (bLogging)
{
bulkUpdateLog.Event = "Initialize";
bulkUpdateLog.UserSessionId = this.UserSession.Id;
bulkUpdateLog.Username = this.UserSession.User.Username;
bulkUpdateLog.Batch = Guid.NewGuid().ToString();
string Filter = null;
status = _dbBulkUpdateLogsMgr.SetFilter(bulkUpdateRequest.Filter, out Filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
bulkUpdateLog.Filter = Filter;
}
// Get database connection string
TablesetId tablesetId = new TablesetId(bulkUpdateRequest.Filter.TablesetId);
Tableset tableset = null;
DbTablesetsMgr dbTablesetsMgr = new DbTablesetsMgr(this.UserSession);
status = dbTablesetsMgr.Read(tablesetId, out tableset);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
if (bLogging)
{
string Tableset = null;
status = _dbBulkUpdateLogsMgr.SetTableset(tableset, out Tableset);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
bulkUpdateLog.Tableset = Tableset;
}
DatabaseId databaseId = new DatabaseId(tableset.DatabaseId);
Quest.Functional.MasterPricing.Database database = null;
DbDatabasesMgr dbDatabasesMgr = new DbDatabasesMgr(this.UserSession);
status = dbDatabasesMgr.Read(databaseId, out database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
if (bLogging)
{
string Database = null;
status = _dbBulkUpdateLogsMgr.SetDatabase(database, out Database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
bulkUpdateLog.Database = Database;
}
// Connect and execute
bool bTransaction = true; // Update all rows are none of them.
using (SqlConnection conn = new SqlConnection(database.ConnectionString))
{
bulkUpdateLog.Event = "Connect";
conn.Open();
SqlTransaction trans = null;
if (bTransaction)
{
trans = conn.BeginTransaction();
if (bLogging)
{
bulkUpdateLog.Event = "BeginTransaction";
}
}
numRows = 0;
foreach (dynamic _dynRow in resultsSet.Data)
{
using (SqlCommand cmd = new SqlCommand(null, conn, trans))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = filterProcedure.Name;
// Initialize logging info
if (bLogging)
{
logParameterList = new List <string>();
bulkUpdateLog.Event = null;
bulkUpdateLog.NumRows = numRows;
bulkUpdateLog.Parameters = null;
bulkUpdateLog.Message = null;
bulkUpdateLog.Data = null;
}
List <string> dataValueList = new List <string>();
foreach (FilterProcedureParameter filterParam in filterProcedure.ParameterList)
{
if (bLogging)
{
bulkUpdateLog.Event = "Next Parameter: " + filterParam.ParameterName;
}
if (filterParam.Direction != "Input")
{
SqlParameter sqlParam = new SqlParameter();
if (filterParam.Direction == "ReturnValue")
{
sqlParam.Direction = ParameterDirection.ReturnValue;
}
else if (filterParam.Direction == "Output")
{
sqlParam.Direction = ParameterDirection.Output;
}
else
{
continue; // Input/ouput TODO
}
sqlParam.ParameterName = filterParam.ParameterName;
// TEMPORARY
continue;
}
if (bLogging)
{
logParameterList.Add(String.Format(" Id: {0}, ParameterName: {1}, SqlDbType: {2} ", filterParam.Id, filterParam.ParameterName, filterParam.SqlDbType.ToString()));
string parameterArray = null;
_dbBulkUpdateLogsMgr.SetArray(logParameterList, out parameterArray);
bulkUpdateLog.Parameters = parameterArray;
}
// If a meta-parameter, fill in its value and continue.
bool bIsMetaParameter = false;
SqlParameter sqlMetaParameter = null;
if (filterParam.ParameterName.Equals("@_Username", StringComparison.InvariantCultureIgnoreCase))
{
bIsMetaParameter = true;
sqlMetaParameter = new SqlParameter(filterParam.ParameterName, SqlDbType.NVarChar);
sqlMetaParameter.Value = this.UserSession.User.Username;
}
else if (filterParam.ParameterName.Equals("@_UserSessionId", StringComparison.InvariantCultureIgnoreCase))
{
bIsMetaParameter = true;
sqlMetaParameter = new SqlParameter(filterParam.ParameterName, SqlDbType.Int);
sqlMetaParameter.Value = this.UserSession.Id;
}
else if (filterParam.ParameterName.StartsWith("@_", StringComparison.InvariantCultureIgnoreCase))
{
logMessage = String.Format("ERROR: unknown meta-parameter: {0}", filterParam.ParameterName);
if (bLogging)
{
bulkUpdateLog.Message = logMessage;
_dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId);
}
return(new questStatus(Severity.Error, logMessage));
}
if (bIsMetaParameter)
{
cmd.Parameters.Add(sqlMetaParameter);
continue;
}
// Get the column name from the parameter name
FilterItem bulkUpdateFilterItem = bulkUpdateRequest.Filter.FilterItemList.Find(delegate(FilterItem fi)
{
return(String.Equals(fi.ParameterName, filterParam.ParameterName, StringComparison.CurrentCultureIgnoreCase));
});
if (bulkUpdateFilterItem == null)
{
if (bTransaction)
{
trans.Rollback();
}
logMessage = String.Format("ERROR: filter item not found for sproc parameter {0}",
filterParam.ParameterName);
if (bLogging)
{
bulkUpdateLog.Message = logMessage;
_dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId);
}
return(new questStatus(Severity.Error, logMessage));
}
// Get the bulk update value.
// NOTE: THIS COULD BE A TROUBLE SPOT. ORIGINAL REQUIREMENT WAS SINGLE-ENTITY FILTERS ONLY HAD PROCEDURES. THUS, THOSE FILTER ITEMS
// WOULD NEVER HAVE NAMES QUALIFIED BY THE ENTITY THEY'RE IN. BUT, FILTERS WITH ENTITIES THAT HAVE NO COLUMNS IN THE FILTER ITEMS
// TECHNICALLY QUALIFY AS 'SINGLE-ENTITY FILTER'. THUS, IF THE NAME ALONE DOESN'T MATCH. GO FOR THE ENTITY_NAME AS A MATCH.
BulkUpdateColumnValue bulkUpdateColumnValue = bulkUpdateRequest.Columns.Find(delegate(BulkUpdateColumnValue cv)
{
return(cv.Name == bulkUpdateFilterItem.FilterColumn.Name);
});
if (bulkUpdateColumnValue == null)
{
bulkUpdateColumnValue = bulkUpdateRequest.Columns.Find(delegate(BulkUpdateColumnValue cv)
{
string[] parts = cv.Name.Split('_');
if (parts.Length == 2)
{
return(parts[0] == bulkUpdateFilterItem.FilterColumn.ParentEntityType.Name && parts[1] == bulkUpdateFilterItem.FilterColumn.Name);
}
return(false);
});
}
if (bulkUpdateColumnValue == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: bulk update column value {0} not found in bulk update columns",
bulkUpdateFilterItem.FilterColumn.Name)));
}
if (bLogging)
{
bulkUpdateLog.BulkUpdateColumn = String.Format(" Name: {0}, Value: {1} ", bulkUpdateColumnValue.Name, bulkUpdateColumnValue.Value);
}
// Determine bulk update value to use.
string updateValue = null;
if (bulkUpdateColumnValue.bNull)
{
updateValue = null;
}
else if (!string.IsNullOrEmpty(bulkUpdateColumnValue.Value))
{
updateValue = bulkUpdateColumnValue.Value;
}
else if (filterParam.bRequired)
{
// Indexing not working, but should be ...
////updateValue = _dynRow[bulkUpdateColumnValue.Name];
bool bFound = false;
foreach (KeyValuePair <string, object> kvp in _dynRow)
{
if (kvp.Key == bulkUpdateColumnValue.Name)
{
updateValue = kvp.Value != null?kvp.Value.ToString() : null; // Not sure if we go w/ Null here. But, oh well ...
bFound = true;
break;
}
}
if (!bFound)
{
return(new questStatus(Severity.Error, String.Format("ERROR: filter results column {0} not found to use in bulk update operation",
bulkUpdateColumnValue.Name)));
}
}
else // Value is required, use results value since a value not specified in bulk updates.
{
updateValue = null;
}
// Bind the parameter
// TODO:REFACTOR
SqlDbType sqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType), filterParam.SqlDbType, true);
SqlParameter sqlParameter = new SqlParameter(filterParam.ParameterName, sqlDbType);
if (bLogging)
{
bulkUpdateLog.Event = "Set Parameter Value";
}
if (sqlDbType == SqlDbType.Bit)
{
bool bValue = updateValue != "0";
sqlParameter.Value = bValue;
}
else if (sqlDbType == SqlDbType.Int)
{
int intValue = Convert.ToInt32(updateValue);
sqlParameter.Value = intValue;
}
else if (sqlDbType == SqlDbType.NVarChar)
{
if (updateValue == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = updateValue.ToString();
}
}
else if (sqlDbType == SqlDbType.VarChar)
{
if (updateValue == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = updateValue.ToString();
}
}
else if (sqlDbType == SqlDbType.DateTime)
{
if (updateValue == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = Convert.ToDateTime(updateValue);
}
}
else if (sqlDbType == SqlDbType.DateTime2)
{
if (updateValue == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = Convert.ToDateTime(updateValue);
}
}
else if (sqlDbType == SqlDbType.Date)
{
if (updateValue == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = Convert.ToDateTime(updateValue);
}
}
else if (sqlDbType == SqlDbType.Decimal)
{
if (updateValue == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = Convert.ToDecimal(updateValue);
}
}
else
{
if (updateValue == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = updateValue;
}
}
cmd.Parameters.Add(sqlParameter);
if (bLogging)
{
dataValueList.Add(String.Format(" Name: {0}, Value: {1} ", sqlParameter.ParameterName,
sqlParameter.Value == DBNull.Value ? "null" : sqlParameter.Value.ToString()));
}
}
// Execute the command
try
{
if (bLogging)
{
bulkUpdateLog.Event = "ExecuteNonQuery";
}
int _numRows = cmd.ExecuteNonQuery();
if (_numRows != 1)
{
if (bTransaction)
{
trans.Rollback();
}
logMessage = String.Format("ERROR: Bulk update stored procedure failed: Rows: {0}", _numRows);
if (bLogging)
{
bulkUpdateLog.Message = logMessage;
_dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId);
}
return(new questStatus(Severity.Error, logMessage));
}
else if (bLoggingPerRow)
{
bulkUpdateLog.Message = "Successful bulk update";
bulkUpdateLog.NumRows = _numRows;
bulkUpdateLog.BulkUpdateColumn = null;
string valueArray = null;
_dbBulkUpdateLogsMgr.SetArray(dataValueList, out valueArray);
bulkUpdateLog.Data = valueArray;
_dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId);
}
}
catch (SqlException ex)
{
if (bTransaction)
{
trans.Rollback();
}
logMessage = String.Format("SQL EXCEPTION: Bulk update stored procedure {0}: {1}",
filterProcedure.Name, ex.Message);
if (bLogging)
{
bulkUpdateLog.Message = logMessage;
_dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId);
}
return(new questStatus(Severity.Error, logMessage));
}
catch (System.Exception ex)
{
if (bTransaction)
{
trans.Rollback();
}
logMessage = String.Format("EXCEPTION: Bulk update stored procedure {0}: {1}",
filterProcedure.Name, ex.Message);
if (bLogging)
{
bulkUpdateLog.Message = logMessage;
_dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId);
}
return(new questStatus(Severity.Error, logMessage));
}
}
numRows += 1;
}
if (bTransaction)
{
trans.Commit();
}
if (bLogging)
{
bulkUpdateLog.Event = "COMMIT";
bulkUpdateLog.NumRows = numRows;
bulkUpdateLog.BulkUpdateColumn = null;
bulkUpdateLog.Message = "Bulk update operation successful";
bulkUpdateLog.Data = null;
_dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId);
}
}
}
catch (System.Exception ex)
{
logMessage = String.Format("EXCEPTION: Bulk Update Operation: {0}.{1}: {2}",
this.GetType().Name, MethodBase.GetCurrentMethod().Name,
ex.InnerException != null ? ex.InnerException.Message : ex.Message);
if (bLogging)
{
bulkUpdateLog.Event = bulkUpdateLog.Event == null ? "EXCEPTION" : bulkUpdateLog.Event;
bulkUpdateLog.Message = logMessage;
bulkUpdateLog.NumRows = numRows;
string parameterArray = null;
_dbBulkUpdateLogsMgr.SetArray(logParameterList, out parameterArray);
bulkUpdateLog.Parameters = parameterArray;
string Exception = null;
_dbBulkUpdateLogsMgr.SetException(ex, out Exception);
bulkUpdateLog.Data = Exception;
_dbBulkUpdateLogsMgr.Create(bulkUpdateLog, out bulkUpdateLogId);
}
return(new questStatus(Severity.Fatal, logMessage));
}
return(new questStatus(Severity.Success));
}
public questStatus PerformBulkUpdate(BulkUpdateRequest bulkUpdateRequest, out int numRows)
{
// Initialize
questStatus status = null;
numRows = -1;
try
{
// Get database connection string
TablesetId tablesetId = new TablesetId(bulkUpdateRequest.Filter.TablesetId);
Tableset tableset = null;
DbTablesetsMgr dbTablesetsMgr = new DbTablesetsMgr(this.UserSession);
status = dbTablesetsMgr.Read(tablesetId, out tableset);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
DatabaseId databaseId = new DatabaseId(tableset.DatabaseId);
Quest.Functional.MasterPricing.Database database = null;
DbDatabasesMgr dbDatabasesMgr = new DbDatabasesMgr(this.UserSession);
status = dbDatabasesMgr.Read(databaseId, out database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
using (SqlConnection conn = new SqlConnection(database.ConnectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(null, conn))
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = bulkUpdateRequest.SQL;
try
{
int retval = cmd.ExecuteNonQuery();
if (retval < 0)
{
return(new questStatus(Severity.Error, String.Format("ERROR: Bulk update failed: {0}", numRows)));
}
if (retval == 0)
{
return(new questStatus(Severity.Warning, String.Format("WARNING: {0} rows bulk updated", numRows)));
}
numRows += retval;
}
catch (System.Exception ex)
{
return(new questStatus(Severity.Error, String.Format("EXCEPTION: Bulk update failed: {0}",
ex.Message)));
}
}
}
}
catch (System.Exception ex)
{
return(new questStatus(Severity.Fatal, String.Format("EXCEPTION: {0}.{1}: {2}",
this.GetType().Name, MethodBase.GetCurrentMethod().Name,
ex.InnerException != null ? ex.InnerException.Message : ex.Message)));
}
return(new questStatus(Severity.Success));
}
