public questStatus Read(DbMgrTransaction trans, DatabaseId databaseId, out List <Quest.Functional.MasterPricing.Tableset> tablesetList)
{
// Initialize
questStatus status = null;
tablesetList = null;
// Perform read
List <Quest.Services.Dbio.MasterPricing.Tablesets> _tablesetList = null;
status = read((MasterPricingEntities)trans.DbContext, databaseId, out _tablesetList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
tablesetList = new List <Quest.Functional.MasterPricing.Tableset>();
foreach (Quest.Services.Dbio.MasterPricing.Tablesets _tableSet in _tablesetList)
{
Quest.Functional.MasterPricing.Tableset tableset = new Tableset();
BufferMgr.TransferBuffer(_tableSet, tableset);
tablesetList.Add(tableset);
}
return(new questStatus(Severity.Success));
}
public questStatus RemoveTablesetInfoIFDbChanged(DbMgrTransaction trans, Tableset tableset)
{
// Initialize
questStatus status = null;
// If the database was changed, delete the tablset configuration and any filters based on the tableset
TablesetId tablesetId = new TablesetId(tableset.Id);
Quest.Functional.MasterPricing.Tableset _tableset = null;
status = Read(tablesetId, out _tableset);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
if (tableset.DatabaseId != _tableset.DatabaseId)
{
// Remove all tableset filters
status = RemoveTablesetFilters(trans, tablesetId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Remove all tableset configuration
status = ClearTablesetEntities(trans, tablesetId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Warning, "Tableset database was changed. All tableset filters were deleted."));
}
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 questStatus SetTableset(Tableset tableset, out string Tableset)
{
if (tableset == null)
{
Tableset = " Id: \"null\", Name: \"null\" ";
}
else
{
Tableset = String.Format("Id: {0}, Name: {1}", tableset.Id, tableset.Name);
}
return(new questStatus(Severity.Success));
}
/*==================================================================================================================================
* Properties
*=================================================================================================================================*/
#endregion
#region Public Methods
/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus List(QueryOptions queryOptions, out List <Quest.Functional.MasterPricing.Tableset> tablesetList, out QueryResponse queryResponse)
{
// Initialize
questStatus status = null;
tablesetList = null;
queryResponse = null;
string assemblyName = Assembly.GetCallingAssembly().FullName;
// Get tablesets
using (MasterPricingEntities dbContext = new MasterPricingEntities())
{
using (DbContextTransaction tx = dbContext.Database.BeginTransaction(IsolationLevel.ReadUncommitted))
{
try
{
PropertyInfo[] dbProperties = typeof(Quest.Services.Dbio.MasterPricing.Tablesets).GetProperties().ToArray();
int totalRecords = dbContext.Tablesets.Where(BuildWhereClause(queryOptions, dbProperties)).Count();
List <Quest.Services.Dbio.MasterPricing.Tablesets> _tablesetList = dbContext.Tablesets.Where(BuildWhereClause(queryOptions, dbProperties))
.OrderBy(BuildSortString(queryOptions.SortColumns))
.Skip(queryOptions.Paging.PageSize * (queryOptions.Paging.PageNumber - 1))
.Take(queryOptions.Paging.PageSize).ToList();
if (_tablesetList == null)
{
return(new questStatus(Severity.Warning));
}
tablesetList = new List <Tableset>();
foreach (Quest.Services.Dbio.MasterPricing.Tablesets _instantMessageContact in _tablesetList)
{
Tableset tableset = new Tableset();
BufferMgr.TransferBuffer(_instantMessageContact, tableset);
tablesetList.Add(tableset);
}
status = BuildQueryResponse(totalRecords, queryOptions, out queryResponse);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
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 Read(TablesetId tablesetId, out Tableset tableset)
{
// Initialize
tableset = null;
questStatus status = null;
// Read tableset
status = _dbTablesetsMgr.Read(tablesetId, out tableset);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
public questStatus RemoveTablesetInfoIFDbChanged(Tableset tableset)
{
// Initialize
questStatus status = null;
// Start a new transaction
string transactionName = null;
status = GetUniqueTransactionName("ClearTablesetConfiguration" + tableset.Id.ToString(), out transactionName);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
DbMgrTransaction trans = null;
status = BeginTransaction(transactionName, out trans);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Remove all tableset configuration and filters based on the tableset.
questStatus status2 = RemoveTablesetInfoIFDbChanged(trans, tableset);
if (!questStatusDef.IsSuccess(status2))
{
// IF warning, it means the filters were removed due to the database changing on the tableset.
if (!questStatusDef.IsWarning(status2))
{
RollbackTransaction(trans);
return(status);
}
}
// Commit transaction
status = CommitTransaction(trans);
if (!questStatusDef.IsSuccess(status))
{
RollbackTransaction(trans);
return(status);
}
return(status2);
}
public questStatus Update(DbMgrTransaction trans, Tableset tableset)
{
// Initialize
questStatus status = null;
// Last refresh is creation date
tableset.LastRefresh = DateTime.Now;
// Update tableset
status = _dbTablesetsMgr.Update(trans, tableset);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
/*==================================================================================================================================
* Properties
*=================================================================================================================================*/
#endregion
#region Public Methods
/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus Create(Tableset tableset, out TablesetId tablesetId)
{
// Initialize
tablesetId = null;
questStatus status = null;
// Last refresh is creation date
tableset.LastRefresh = DateTime.Now;
// Create tableset
status = _dbTablesetsMgr.Create(tableset, out tablesetId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
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));
}
private questStatus klugieGetFilterItemInfo(FilterItem filterItem, out DatabaseId databaseId, out TablesetId tablesetId)
{
// Initialize
questStatus status = null;
databaseId = null;
tablesetId = null;
// Klugie: temporary
// Just back up and get stuff we need. (All this due to refactoring, more to do).
// Get the filter
FilterId filterId = new FilterId(filterItem.FilterId);
Filter filter = null;
DbFiltersMgr dbFiltersMgr = new DbFiltersMgr(this.UserSession);
status = dbFiltersMgr.Read(filterId, out filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Get the tableset
DbTablesetsMgr dbTablesetsMgr = new DbTablesetsMgr(this.UserSession);
tablesetId = new TablesetId(filter.TablesetId);
Tableset tableset = null;
status = dbTablesetsMgr.Read(tablesetId, out tableset);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Return info.
databaseId = new DatabaseId(tableset.DatabaseId);
tablesetId = new TablesetId(tableset.Id);
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 questStatus Verify(FilterId filterId, Filter filter)
{
// Initialize
questStatus status = null;
// Verify filter Id
Filter _filter = null;
FiltersMgr filtersMgr = new FiltersMgr(this.UserSession);
status = filtersMgr.Read(filterId, out _filter);
if (!questStatusDef.IsSuccess(status))
{
return(new questStatus(Severity.Error, String.Format("Error reading FilterId {0} not found: {0}",
filterId.Id, status.Message)));
}
// Verify tableset Id
TablesetId tablesetId = new TablesetId(filter.TablesetId);
Tableset _tableset = null;
TablesetsMgr tablesetsMgr = new TablesetsMgr(this.UserSession);
status = tablesetsMgr.Read(tablesetId, out _tableset);
if (!questStatusDef.IsSuccess(status))
{
return(new questStatus(Severity.Error, String.Format("Error reading TablesetId not found: {0}",
tablesetId, status.Message)));
}
// Make shift validation for now.
status = Verify(filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus GetFilterProcedureOptions(TablesetId tablesetId, out List <OptionValuePair> optionsList, string Value = null, string Name = null)
{
// Initialize
questStatus status = null;
optionsList = null;
////// Get stored procedures for filter's database.
////FilterMgr filterMgr = new FilterMgr(this.UserSession);
////List<StoredProcedure> storedProcedureList = null;
////status = filterMgr.GetDatabaseStoredProcedures(tablesetId, out storedProcedureList);
////if (!questStatusDef.IsSuccess(status))
////{
//// return (status);
////}
// Get the database Id.
Tableset tableset = null;
TablesetsMgr tablesetsMgr = new TablesetsMgr(this.UserSession);
status = tablesetsMgr.Read(tablesetId, out tableset);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Get the stored procedures.
DatabaseId databaseId = new DatabaseId(tableset.DatabaseId);
List <StoredProcedure> storedProcedureList = null;
StoredProceduresMgr storedProceduresMgr = new StoredProceduresMgr(this.UserSession);
status = storedProceduresMgr.Read(databaseId, out storedProcedureList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Sort
storedProcedureList.Sort(delegate(StoredProcedure i1, StoredProcedure i2) { return(i1.Name.CompareTo(i2.Name)); });
// Build options
// Set selected if specified.
optionsList = new List <OptionValuePair>();
foreach (StoredProcedure storedProcedure in storedProcedureList)
{
OptionValuePair optionValuePair = new OptionValuePair();
optionValuePair.Id = storedProcedure.Id.ToString();
optionValuePair.Label = storedProcedure.Name;
if (Value != null && Value == storedProcedure.Id.ToString())
{
optionValuePair.bSelected = true;
}
else if (Name != null && Name == storedProcedure.Name)
{
optionValuePair.bSelected = true;
}
optionsList.Add(optionValuePair);
}
// Insert default option
status = AddDefaultOptions(optionsList, "-1", "Select one ...");
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
return(new questStatus(Severity.Success));
}
/*==================================================================================================================================
* Public Methods
*=================================================================================================================================*/
public questStatus ExecuteFilter(RunFilterRequest runFilterRequest, out ResultsSet resultsSet)
{
// Initialize
questStatus status = null;
resultsSet = null;
// Get the filter
FilterId filterId = new FilterId(runFilterRequest.FilterId.Id);
Filter filter = null;
DbFilterMgr dbFilterMgr = new DbFilterMgr(this.UserSession);
status = dbFilterMgr.GetFilter(filterId, out filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// If no SQL, return.
if (string.IsNullOrEmpty(filter.SQL))
{
return(new questStatus(Severity.Error, "Filter has no SQL"));
}
// Read the tableset
TablesetId tablesetId = new TablesetId(filter.TablesetId);
Tableset tableset = null;
DbTablesetsMgr dbTablesetsMgr = new DbTablesetsMgr(this.UserSession);
status = dbTablesetsMgr.Read(tablesetId, out tableset);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Read 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);
}
// Execute SQL.
try {
status = executeSQL(runFilterRequest, database, filter, out resultsSet);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
catch (System.Exception ex)
{
return(new questStatus(Severity.Error, String.Format("EXCEPTION: executing filter SQL: {0}", ex.Message)));
}
// klugie: Get number of FROM entities
string FROMClause = null;
List <FilterEntity> FROMEntityList = null;
List <JoinEntity> joinEntityList = null;
DbFilterSQLMgr dbFilterSQLMgr = new DbFilterSQLMgr();
status = dbFilterSQLMgr.GetFROMEntities(filter, out FROMClause, out FROMEntityList, out joinEntityList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
int numItemEntities = FROMEntityList.Count + joinEntityList.Count;
// Append lookup or type list Id's to result columns with lookups.
// NOTE: Lookups and typeList are mutually exclusive.
FilterItem filterItem = null;
try {
for (int idx = 0; idx < filter.FilterItemList.Count; idx += 1)
{
filterItem = filter.FilterItemList[idx];
string columnIdentifier = null;
FilterColumn filterColumn = null;
status = GetResultsColumnIdentifier(filter, filterItem, numItemEntities, out columnIdentifier, out filterColumn);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
if (columnIdentifier == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: columnIdentifier is NULL for filterItem {0} FilterId: {1}",
filterItem.Id, filterItem.FilterId)));
}
if (filterColumn == null)
{
return(new questStatus(Severity.Error, String.Format("ERROR: filterColumn is NULL for filterItem {0} FilterId: {1}",
filterItem.Id, filterItem.FilterId)));
}
if (!string.IsNullOrEmpty(filterItem.Label))
{
resultsSet.ResultColumns[columnIdentifier].Name = filterColumn.TablesetColumn.Name;
resultsSet.ResultColumns[columnIdentifier].Label = filterItem.Label;
}
if (filterItem.LookupId.HasValue)
{
resultsSet.ResultColumns[columnIdentifier].LookupId = filterItem.LookupId;
}
if (filterItem.TypeListId.HasValue)
{
resultsSet.ResultColumns[columnIdentifier].TypeListId = filterItem.TypeListId;
}
resultsSet.ResultColumns[columnIdentifier].bIsHidden = filterItem.bHidden;
}
}
catch (System.Exception ex)
{
return(new questStatus(Severity.Error, String.Format("EXCEPTION: building filter results set with FilterItem {0}: {1}",
filterItem.Id, ex.Message)));
}
return(new questStatus(Severity.Success));
}
public questStatus Save(TablesetConfigurationViewModel tablesetConfigurationViewModel)
{
// Initialize
questStatus status = null;
TablesetId tablesetId = null;
// Validate: must have a valid TablesetId
if (tablesetConfigurationViewModel.Tableset.Id < BaseId.VALID_ID)
{
return(new questStatus(Severity.Error, "Invalid Tableset identity"));
}
tablesetId = new TablesetId(tablesetConfigurationViewModel.Tableset.Id);
Tableset tableset = null;
TablesetsMgr tablesetsMgr = new TablesetsMgr(this.UserSession);
status = tablesetsMgr.Read(tablesetId, out tableset);
if (!questStatusDef.IsSuccess(status))
{
return(new questStatus(Severity.Error, String.Format("Tableset identity not found: {0}", tablesetId.Id)));
}
// Transfer model
if (tablesetConfigurationViewModel.TableList == null)
{
tablesetConfigurationViewModel.TableList = new List <BootstrapTreenodeViewModel>();
}
TablesetConfiguration tablesetConfiguration = new TablesetConfiguration();
BufferMgr.TransferBuffer(tablesetConfigurationViewModel, tablesetConfiguration, true);
foreach (BootstrapTreenodeViewModel _table in tablesetConfigurationViewModel.TableList)
{
TablesetTable tablesetTable = new TablesetTable();
string[] parts = _table.text.Split(new[] { '[', '.', ']' }, StringSplitOptions.RemoveEmptyEntries);
tablesetTable.Schema = parts[0];
tablesetTable.Name = parts[1];
tablesetConfiguration.TablesetTables.Add(tablesetTable);
}
foreach (BootstrapTreenodeViewModel _view in tablesetConfigurationViewModel.ViewList)
{
TablesetView tablesetView = new TablesetView();
string[] parts = _view.text.Split(new[] { '[', '.', ']' }, StringSplitOptions.RemoveEmptyEntries);
tablesetView.Schema = parts[0];
tablesetView.Name = parts[1];
tablesetConfiguration.TablesetViews.Add(tablesetView);
}
// Save
tablesetId = null;
TablesetMgr tablesetMgr = new TablesetMgr(this.UserSession);
status = tablesetMgr.SaveTablesetConfiguration(tablesetConfiguration, out tablesetId);
if (!questStatusDef.IsSuccess(status))
{
FormatErrorMessage(status, tablesetConfigurationViewModel);
return(status);
}
tablesetConfigurationViewModel.Tableset.Id = tablesetId.Id;
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));
}
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 ClearTablesetEntities(DbMgrTransaction trans, TablesetId tablesetId)
{
// Initialize
questStatus status = null;
// Read tableset.
Tableset tableset = null;
DbTablesetsMgr dbTablesetsMgr = new DbTablesetsMgr(this.UserSession);
status = dbTablesetsMgr.Read(tablesetId, out tableset);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
DbTablesetColumnsMgr dbTablesetColumnsMgr = new DbTablesetColumnsMgr(this.UserSession);
// Read all tableset tables
List <TablesetTable> tablesetTableList = null;
DbTablesetTablesMgr dbTablesetTablesMgr = new DbTablesetTablesMgr(this.UserSession);
status = dbTablesetTablesMgr.Read(tablesetId, out tablesetTableList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Delete all tablesetColumns to these tables. Then delete all tables in the tableset.
EntityType entityType = new EntityType();
entityType.Id = EntityType.Table;
foreach (TablesetTable tablesetTable in tablesetTableList)
{
TableSetEntityId tableSetEntityId = new TableSetEntityId(tablesetTable.Id);
status = dbTablesetColumnsMgr.Delete(trans, entityType, tableSetEntityId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
dbTablesetTablesMgr.Delete(trans, tablesetId);
// Read all tableset views
List <TablesetView> tablesetViewList = null;
DbTablesetViewsMgr dbTablesetViewsMgr = new DbTablesetViewsMgr(this.UserSession);
status = dbTablesetViewsMgr.Read(tablesetId, out tablesetViewList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Delete all tablesetColumns to these views. Then delete all views in the tableset.
entityType.Id = EntityType.View;
foreach (TablesetView tablesetView in tablesetViewList)
{
TableSetEntityId tableSetEntityId = new TableSetEntityId(tablesetView.Id);
status = dbTablesetColumnsMgr.Delete(trans, entityType, tableSetEntityId);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
dbTablesetViewsMgr.Delete(trans, tablesetId);
return(new questStatus(Severity.Success));
}
public questStatus PerformBulkInsertFilterProcedure(BulkInsertRequest bulkInsertRequest, FilterProcedure filterProcedure)
{
// Initialize
questStatus status = null;
BulkInsertLog bulkInsertLog = bLogging ? new BulkInsertLog() : null;
BulkInsertLogId bulkInsertLogId = null;
int numRows = 0;
string logMessage = null;
List <string> logParameterList = null;
try
{
// Initialize log
if (bLogging)
{
bulkInsertLog.Event = "Initialize";
bulkInsertLog.UserSessionId = this.UserSession.Id;
bulkInsertLog.Username = this.UserSession.User.Username;
bulkInsertLog.Batch = Guid.NewGuid().ToString();
string Filter = null;
status = _dbBulkInsertLogsMgr.SetFilter(bulkInsertRequest.Filter, out Filter);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
bulkInsertLog.Filter = Filter;
}
// Get database connection string
TablesetId tablesetId = new TablesetId(bulkInsertRequest.TablesetId);
Tableset tableset = null;
DbTablesetsMgr dbTablesetsMgr = new DbTablesetsMgr(this.UserSession);
status = dbTablesetsMgr.Read(tablesetId, out tableset);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
if (bLogging)
{
string Tableset = null;
status = _dbBulkInsertLogsMgr.SetTableset(tableset, out Tableset);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
bulkInsertLog.Tableset = Tableset;
}
DatabaseId databaseId = new DatabaseId(tableset.DatabaseId);
Quest.Functional.MasterPricing.Database database = null;
DbDatabasesMgr dbDatabasesMgr = new DbDatabasesMgr(this.UserSession);
status = dbDatabasesMgr.Read(databaseId, out database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
if (bLogging)
{
string Database = null;
status = _dbBulkInsertLogsMgr.SetDatabase(database, out Database);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
bulkInsertLog.Database = Database;
}
// Connect and execute
bool bTransaction = false;
bulkInsertLog.Event = "Open";
using (SqlConnection conn = new SqlConnection(database.ConnectionString))
{
bulkInsertLog.Event = "Connect";
conn.Open();
SqlTransaction trans = null;
if (bTransaction)
{
trans = conn.BeginTransaction();
if (bLogging)
{
bulkInsertLog.Event = "BeginTransaction";
}
}
numRows = 0;
foreach (BulkInsertRow bulkInsertRow in bulkInsertRequest.Rows)
{
using (SqlCommand cmd = new SqlCommand(null, conn, trans))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = filterProcedure.Name;
// Initialize logging info
if (bLogging)
{
logParameterList = new List <string>();
bulkInsertLog.Event = null;
bulkInsertLog.NumRows = numRows;
bulkInsertLog.Parameters = null;
bulkInsertLog.Message = null;
bulkInsertLog.Data = null;
}
// Build each parameter
List <string> dataValueList = new List <string>();
foreach (FilterProcedureParameter filterParam in filterProcedure.ParameterList)
{
if (bLogging)
{
bulkInsertLog.Event = "Next Parameter: " + filterParam.ParameterName;
}
if (filterParam.Direction != "Input")
{
continue;
}
if (bLogging)
{
logParameterList.Add(String.Format(" Id: {0}, ParameterName: {1}, SqlDbType: {2} ", filterParam.Id, filterParam.ParameterName, filterParam.SqlDbType.ToString()));
string parameterArray = null;
_dbBulkInsertLogsMgr.SetArray(logParameterList, out parameterArray);
bulkInsertLog.Parameters = parameterArray;
}
// If a meta-parameter, fill in its value and continue.
bool bIsMetaParameter = false;
SqlParameter sqlMetaParameter = null;
if (filterParam.ParameterName.Equals("@_Username", StringComparison.InvariantCultureIgnoreCase))
{
bIsMetaParameter = true;
sqlMetaParameter = new SqlParameter(filterParam.ParameterName, SqlDbType.NVarChar);
sqlMetaParameter.Value = this.UserSession.User.Username;
}
else if (filterParam.ParameterName.Equals("@_UserSessionId", StringComparison.InvariantCultureIgnoreCase))
{
bIsMetaParameter = true;
sqlMetaParameter = new SqlParameter(filterParam.ParameterName, SqlDbType.Int);
sqlMetaParameter.Value = this.UserSession.Id;
}
else if (filterParam.ParameterName.StartsWith("@_", StringComparison.InvariantCultureIgnoreCase))
{
logMessage = String.Format("ERROR: unknown meta-parameter: {0}", filterParam.ParameterName);
if (bLogging)
{
bulkInsertLog.Message = logMessage;
_dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId);
}
return(new questStatus(Severity.Error, logMessage));
}
if (bIsMetaParameter)
{
cmd.Parameters.Add(sqlMetaParameter);
continue;
}
// Not a meta-parameter, so the bulk insert value should be provided.
BulkInsertColumnValue bulkInsertColumnValue = bulkInsertRow.Columns.Find(delegate(BulkInsertColumnValue cv) {
return(cv.Name == filterParam.ParameterName);
});
if (bulkInsertColumnValue == null)
{
if (bTransaction)
{
trans.Rollback();
}
logMessage = String.Format("ERROR: sproc parameter {0} not found in bulk insert columns",
filterParam.ParameterName);
if (bLogging)
{
bulkInsertLog.Message = logMessage;
_dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId);
}
return(new questStatus(Severity.Error, logMessage));
}
if (bLogging)
{
bulkInsertLog.BulkInsertColumn = String.Format(" Name: {0}, Value: {1} ", bulkInsertColumnValue.Name, bulkInsertColumnValue.Value);
}
// Bind the parameter
// TODO:REFACTOR
SqlDbType sqlDbType = (SqlDbType)Enum.Parse(typeof(SqlDbType), filterParam.SqlDbType, true);
SqlParameter sqlParameter = new SqlParameter(filterParam.ParameterName, sqlDbType);
if (bLogging)
{
bulkInsertLog.Event = "Set Parameter Value";
}
if (sqlDbType == SqlDbType.Bit)
{
bool bValue = bulkInsertColumnValue.Value != "0";
sqlParameter.Value = bValue;
}
else if (sqlDbType == SqlDbType.Int)
{
int intValue = Convert.ToInt32(bulkInsertColumnValue.Value);
sqlParameter.Value = intValue;
}
else if (sqlDbType == SqlDbType.NVarChar)
{
if (bulkInsertColumnValue.Value == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = bulkInsertColumnValue.Value.ToString();
}
}
else if (sqlDbType == SqlDbType.VarChar)
{
if (bulkInsertColumnValue.Value == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = bulkInsertColumnValue.Value.ToString();
}
}
else if (sqlDbType == SqlDbType.DateTime)
{
if (bulkInsertColumnValue.Value == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = Convert.ToDateTime(bulkInsertColumnValue.Value);
}
}
else if (sqlDbType == SqlDbType.DateTime2)
{
if (bulkInsertColumnValue.Value == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = Convert.ToDateTime(bulkInsertColumnValue.Value);
}
}
else if (sqlDbType == SqlDbType.Date)
{
if (bulkInsertColumnValue.Value == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = Convert.ToDateTime(bulkInsertColumnValue.Value);
}
}
else if (sqlDbType == SqlDbType.Decimal)
{
if (bulkInsertColumnValue.Value == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = Convert.ToDecimal(bulkInsertColumnValue.Value);
}
}
else
{
if (bulkInsertColumnValue.Value == null)
{
sqlParameter.Value = DBNull.Value;
}
else
{
sqlParameter.Value = bulkInsertColumnValue.Value;
}
}
cmd.Parameters.Add(sqlParameter);
if (bLogging)
{
dataValueList.Add(String.Format(" Name: {0}, Value: {1} ", sqlParameter.ParameterName,
sqlParameter.Value == DBNull.Value ? "null" : sqlParameter.Value.ToString()));
}
}
try
{
if (bLogging)
{
bulkInsertLog.Event = "ExecuteNonQuery";
}
int _numRows = cmd.ExecuteNonQuery();
if (_numRows != 1)
{
if (bTransaction)
{
trans.Rollback();
}
logMessage = String.Format("ERROR: Bulk insert stored procedure failed: Rows: {0}", _numRows);
if (bLogging)
{
bulkInsertLog.Message = logMessage;
_dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId);
}
return(new questStatus(Severity.Error, logMessage));
}
else if (bLoggingPerRow)
{
bulkInsertLog.Message = "Successful bulk insert";
bulkInsertLog.NumRows = _numRows;
bulkInsertLog.BulkInsertColumn = null;
string valueArray = null;
_dbBulkInsertLogsMgr.SetArray(dataValueList, out valueArray);
bulkInsertLog.Data = valueArray;
_dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId);
}
}
catch (SqlException ex)
{
if (bTransaction)
{
trans.Rollback();
}
logMessage = String.Format("SQL EXCEPTION: Bulk insert stored procedure {1}: {2}",
numRows, filterProcedure.Name, ex.Message);
if (bLogging)
{
bulkInsertLog.Message = logMessage;
_dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId);
}
return(new questStatus(Severity.Error, logMessage));
}
catch (System.Exception ex)
{
if (bTransaction)
{
trans.Rollback();
}
logMessage = String.Format("After {0} rows, EXCEPTION: Bulk insert stored procedure {0}: {1}",
numRows, filterProcedure.Name, ex.Message);
if (bLogging)
{
bulkInsertLog.Message = logMessage;
_dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId);
}
return(new questStatus(Severity.Error, logMessage));
}
}
numRows += 1;
}
if (bTransaction)
{
trans.Commit();
}
if (bLogging)
{
bulkInsertLog.Event = "COMMIT";
bulkInsertLog.NumRows = numRows;
bulkInsertLog.BulkInsertColumn = null;
bulkInsertLog.Message = "Bulk isnert operation successful";
bulkInsertLog.Data = null;
_dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId);
}
}
}
catch (System.Exception ex)
{
logMessage = String.Format("EXCEPTION: Bulk Insert Operation: {0}.{1}: {2}",
this.GetType().Name, MethodBase.GetCurrentMethod().Name,
ex.InnerException != null ? ex.InnerException.Message : ex.Message);
if (bLogging)
{
bulkInsertLog.Event = bulkInsertLog.Event == null ? "EXCEPTION" : bulkInsertLog.Event;
bulkInsertLog.Message = logMessage;
bulkInsertLog.NumRows = numRows;
string parameterArray = null;
_dbBulkInsertLogsMgr.SetArray(logParameterList, out parameterArray);
bulkInsertLog.Parameters = parameterArray;
string Exception = null;
_dbBulkInsertLogsMgr.SetException(ex, out Exception);
bulkInsertLog.Data = Exception;
_dbBulkInsertLogsMgr.Create(bulkInsertLog, out bulkInsertLogId);
}
return(new questStatus(Severity.Fatal, logMessage));
}
return(new questStatus(Severity.Success));
}
public questStatus SaveTablesetConfiguration(TablesetConfiguration tablesetConfiguration, out TablesetId tablesetId)
{
// Initialize
questStatus status = null;
tablesetId = null;
DbMgrTransaction trans = null;
bool bFiltersRemoved = false;
questStatus status2 = null;
try
{
// BEGIN TRANSACTION
status = BeginTransaction("SaveTablesetConfiguration" + Guid.NewGuid().ToString(), out trans);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
/*
* Update tableset info.
*/
// Read the tableset
TablesetsMgr tablesetsMgr = new TablesetsMgr(this.UserSession);
TablesetId _tablesetId = new TablesetId(tablesetConfiguration.Tableset.Id);
Tableset _tableset = null;
status = tablesetsMgr.Read(trans, _tablesetId, out _tableset);
if (!questStatusDef.IsSuccess(status))
{
RollbackTransaction(trans);
return(status);
}
/*
* Remove all tableset entities.
*/
status = ClearTablesetEntities(trans, _tablesetId);
if (!questStatusDef.IsSuccess(status))
{
RollbackTransaction(trans);
return(status);
}
// TESTING ONLY: COMMIT TRANSACTION
bool bKlugie = false;
if (bKlugie)
{
status = CommitTransaction(trans);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
}
/*
* Get database entites.
*/
DatabaseId databaseId = new DatabaseId(tablesetConfiguration.Database.Id);
DatabaseMgr databaseMgr = new DatabaseMgr(this.UserSession);
DatabaseEntities databaseEntities = null;
status = databaseMgr.ReadDatabaseEntities(databaseId, out databaseEntities);
if (!questStatusDef.IsSuccess(status))
{
RollbackTransaction(trans);
return(status);
}
#region Save tableset info.
/*
* Save tableset info.
*/
DbTablesetColumnsMgr dbTablesetColumnsMgr = new DbTablesetColumnsMgr(this.UserSession);
// Save table info.
DbTablesetTablesMgr dbTablesetTablesMgr = new DbTablesetTablesMgr(this.UserSession);
List <TablesetTable> tablesetTableList = new List <TablesetTable>();
foreach (TablesetTable tablesetTable in tablesetConfiguration.TablesetTables)
{
Table _table = databaseEntities.TableList.Find(delegate(Table t) { return(t.Schema == tablesetTable.Schema && t.Name == tablesetTable.Name); });
if (_table == null)
{
RollbackTransaction(trans);
return(new questStatus(Severity.Error, String.Format("ERROR: tableset table [{0}].[{1}] not found in database metainfo. Try refreshing database schema info",
tablesetTable.Schema, tablesetTable.Name)));
}
tablesetTable.TablesetId = _tableset.Id;
tablesetTable.Table = _table;
tablesetTableList.Add(tablesetTable);
// Create tableset table.
TablesetTableId tablesetTableId = null;
status = dbTablesetTablesMgr.Create(trans, tablesetTable, out tablesetTableId);
if (!questStatusDef.IsSuccess(status))
{
RollbackTransaction(trans);
return(status);
}
foreach (Column column in _table.ColumnList)
{
Column _column = _table.ColumnList.Find(delegate(Column c) { return(c.Name == column.Name); });
if (_column == null)
{
RollbackTransaction(trans);
return(new questStatus(Severity.Error, String.Format("ERROR: column [{0}] not found in table [{1}].[{2}] in database metainfo. Try refreshing database schema info",
column.Name, _table.Schema, _table.Name)));
}
TablesetColumn tablesetColumn = new TablesetColumn();
tablesetColumn.EntityTypeId = EntityType.Table;
tablesetColumn.TableSetEntityId = tablesetTableId.Id;
tablesetColumn.Name = column.Name;
TablesetColumnId tablesetColumnId = null;
status = dbTablesetColumnsMgr.Create(trans, tablesetColumn, out tablesetColumnId);
if (!questStatusDef.IsSuccess(status))
{
RollbackTransaction(trans);
return(status);
}
}
}
// Save view info.
DbTablesetViewsMgr dbTablesetViewsMgr = new DbTablesetViewsMgr(this.UserSession);
List <TablesetView> tablesetViewList = new List <TablesetView>();
foreach (TablesetView tablesetView in tablesetConfiguration.TablesetViews)
{
View _view = databaseEntities.ViewList.Find(delegate(View v) { return(v.Schema == tablesetView.Schema && v.Name == tablesetView.Name); });
if (_view == null)
{
RollbackTransaction(trans);
return(new questStatus(Severity.Error, String.Format("ERROR: tableset view [{0}].[{1}] not found in database metainfo. Try refreshing database schema info",
tablesetView.Schema, tablesetView.Name)));
}
tablesetView.TablesetId = _tableset.Id;
tablesetView.View = _view;
tablesetViewList.Add(tablesetView);
// Create tableset view.
TablesetViewId tablesetViewId = null;
status = dbTablesetViewsMgr.Create(trans, tablesetView, out tablesetViewId);
if (!questStatusDef.IsSuccess(status))
{
RollbackTransaction(trans);
return(status);
}
foreach (Column column in _view.ColumnList)
{
Column _column = _view.ColumnList.Find(delegate(Column c) { return(c.Name == column.Name); });
if (_column == null)
{
RollbackTransaction(trans);
return(new questStatus(Severity.Error, String.Format("ERROR: column [{0}] not found in view [{1}].[{2}] in database metainfo. Try refreshing database schema info",
column.Name, _view.Schema, _view.Name)));
}
TablesetColumn tablesetColumn = new TablesetColumn();
tablesetColumn.EntityTypeId = EntityType.View;
tablesetColumn.TableSetEntityId = tablesetViewId.Id;
tablesetColumn.Name = column.Name;
TablesetColumnId tablesetColumnId = null;
status = dbTablesetColumnsMgr.Create(trans, tablesetColumn, out tablesetColumnId);
if (!questStatusDef.IsSuccess(status))
{
RollbackTransaction(trans);
return(status);
}
}
}
#endregion
// Update tableset.
bFiltersRemoved = false;
_tableset.DatabaseId = tablesetConfiguration.Database.Id;
status2 = tablesetsMgr.Update(trans, _tableset);
if (!questStatusDef.IsSuccess(status2))
{
if (questStatusDef.IsWarning(status2))
{
bFiltersRemoved = true;
}
else
{
RollbackTransaction(trans);
return(status);
}
}
// COMMIT TRANSACTION
status = CommitTransaction(trans);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Return the tableset id
tablesetId = new TablesetId(tablesetConfiguration.Tableset.Id);
}
catch (System.Exception ex)
{
if (trans != null)
{
RollbackTransaction(trans);
}
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)));
}
if (bFiltersRemoved)
{
return(status2);
}
return(new questStatus(Severity.Success));
}
public questStatus ReadTablesetConfiguration(TablesetId tablesetId, out TablesetConfiguration tablesetConfiguration)
{
// Initialize
questStatus status = null;
tablesetConfiguration = null;
// Read tableset
Tableset tableset = null;
DbTablesetsMgr dbTablesetsMgr = new DbTablesetsMgr(this._userSession);
status = dbTablesetsMgr.Read(tablesetId, out tableset);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Read 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);
}
/*
* Load tableset entities.
*/
DbTablesetColumnsMgr dbTablesetColumnsMgr = new DbTablesetColumnsMgr(this._userSession);
// Read tables in tableset
List <Quest.Functional.MasterPricing.TablesetTable> tablesetTableList = null;
DbTablesetTablesMgr dbTablesetTablesMgr = new DbTablesetTablesMgr(this._userSession);
status = dbTablesetTablesMgr.Read(tablesetId, out tablesetTableList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Read table info.
EntityType entityType = new EntityType();
entityType.Id = EntityType.Table;
DbTablesMgr dbTablesMgr = new DbTablesMgr(this._userSession);
DbColumnsMgr dbColumnsMgr = new DbColumnsMgr(this._userSession);
foreach (TablesetTable tablesetTable in tablesetTableList)
{
TablesetId tableSetId = new TablesetId(tablesetTable.Id);
List <Quest.Functional.MasterPricing.TablesetColumn> tablesetColumnList = null;
TableSetEntityId tableSetEntityId = new TableSetEntityId(tablesetTable.Id);
status = dbTablesetColumnsMgr.Read(entityType, tableSetEntityId, out tablesetColumnList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
tablesetTable.TablesetColumnList = tablesetColumnList;
Table table = null;
status = dbTablesMgr.Read(databaseId, tablesetTable.Schema, tablesetTable.Name, out table);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
tablesetTable.Table = table;
// Get column metadata
EntityTypeId entityTypeId = new EntityTypeId(EntityType.Table);
EntityId entityId = new EntityId(table.Id);
foreach (TablesetColumn tablesetColumn in tablesetColumnList)
{
Column column = null;
status = dbColumnsMgr.Read(entityTypeId, entityId, tablesetColumn.Name, out column);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
tablesetColumn.Column = column;
}
}
// Read views in tableset
List <Quest.Functional.MasterPricing.TablesetView> tablesetViewList = null;
DbTablesetViewsMgr dbTablesetViewsMgr = new DbTablesetViewsMgr(this._userSession);
status = dbTablesetViewsMgr.Read(tablesetId, out tablesetViewList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
// Read views columns in tableset
entityType.Id = EntityType.View;
DbViewsMgr dbViewsMgr = new DbViewsMgr(this._userSession);
foreach (TablesetView tablesetView in tablesetViewList)
{
TablesetViewId tablesetViewId = new TablesetViewId(tablesetView.Id);
List <Quest.Functional.MasterPricing.TablesetColumn> tablesetColumnList = null;
TableSetEntityId tableSetEntityId = new TableSetEntityId(tablesetView.Id);
status = dbTablesetColumnsMgr.Read(entityType, tableSetEntityId, out tablesetColumnList);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
tablesetView.TablesetColumnList = tablesetColumnList;
View view = null;
status = dbViewsMgr.Read(databaseId, tablesetView.Schema, tablesetView.Name, out view);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
tablesetView.View = view;
// Get column metadata
EntityTypeId entityTypeId = new EntityTypeId(EntityType.View);
EntityId entityId = new EntityId(view.Id);
foreach (TablesetColumn tablesetColumn in tablesetColumnList)
{
Column column = null;
status = dbColumnsMgr.Read(entityTypeId, entityId, tablesetColumn.Name, out column);
if (!questStatusDef.IsSuccess(status))
{
return(status);
}
tablesetColumn.Column = column;
}
}
// Build tableset configuration
tablesetConfiguration = new TablesetConfiguration();
tablesetConfiguration.Tableset = tableset;
tablesetConfiguration.Database = database;
tablesetConfiguration.TablesetTables = tablesetTableList;
tablesetConfiguration.TablesetViews = tablesetViewList;
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));
}
