public void DeleteFromImageView()
{
// First call the FormManager and have it close all open tabs, except NETWORK_DEFINITION, ASSETS, CONSTRUCTION_HISTORY
// other ATTRIBUTE (RAW) tabs and Calculated fields.
ConnectionParameters cp = DBMgr.GetAttributeConnectionObject(m_strAttributeToDelete);
List <String> listCommandText = new List <String>();
m_strAttributeToDelete = m_strAttributeToDelete.Replace("_", "!_");
String strDelete = "DELETE FROM ATTRIBUTES_ WHERE ATTRIBUTE_ = '" + m_strAttributeToDelete + "'";
listCommandText.Add(strDelete);
DeleteFromNetworks(listCommandText);
DBMgr.ExecuteBatchNonQuery(listCommandText);
if (cp.IsNative)
{
strDelete = "DROP TABLE " + m_strAttributeToDelete;
DBMgr.ExecuteNonQuery(strDelete);
}
else
{
String dropView = "DROP VIEW " + m_strAttributeToDelete;
DBMgr.ExecuteNonQuery(dropView, cp);
}
}
/// <summary>
/// Saves any changes made to the currently selected compound treatment, including its associated elements to the database.
/// </summary>
private void SaveCurrentCompoundTreatmentChanges()
{
// Navigation off of a blank row will produce a null value for _compoundTreatment. No changes are necessary in the database in this case.
if (_compoundTreatment != null)
{
// Statements for database changes.
List <string> changeStatements = new List <string>();
// Delete from the compound treatment table our current compound treatment. This should cascade and remove its elements from teh element table as well.
string delete = "DELETE FROM COMPOUND_TREATMENTS WHERE COMPOUND_TREATMENT_ID = '" + _compoundTreatment.CompoundTreatmentID + "'";
changeStatements.Add(delete);
// Now insert the current compound treatment
string insert = "INSERT INTO COMPOUND_TREATMENTS (COMPOUND_TREATMENT_NAME, AFFECTED_ATTRIBUTE, COMPOUND_TREATMENT_ID) VALUES ('" +
_compoundTreatment.CompoundTreatmentName +
"', '" + _compoundTreatment.AffectedAttribute +
"', '" + _compoundTreatment.CompoundTreatmentID + "')";
changeStatements.Add(insert);
foreach (DataGridViewRow elementRow in dataGridViewCompoundTreatmentElements.Rows)
{
CompoundTreatmentElement toInsert = (CompoundTreatmentElement)elementRow.Cells["colCompoundTreatmentElement"].Value;
if (toInsert != null)
{
// Insert the data.
insert = "INSERT INTO COMPOUND_TREATMENT_ELEMENTS (ATTRIBUTE_FROM, ATTRIBUTE_TO, EXTENT_, QUANTITY_, CRITERIA_, COST_, COMPOUND_TREATMENT_ID) VALUES ('" +
toInsert.AttributeFrom +
"', '" + toInsert.AttributeTo +
"', '" + toInsert.ExtentEquation.m_expression +
"', '" + toInsert.Quantity.m_expression +
"', '" + toInsert.CriteriaEquation.Criteria +
"', '" + toInsert.CostEquation.m_expression +
"', '" + toInsert.CompoundTreatmentID + "')";
changeStatements.Add(insert);
}
}
try
{
DBMgr.ExecuteBatchNonQuery(changeStatements);
}
catch (Exception exc)
{
SimulationMessaging.AddMessage(new SimulationMessage("Error updating database with new compound treatment values. Transaction aborted. " + exc.Message));
}
}
}
public void Delete()
{
// Completely remove an asset and all of its references from the RoadCare database and UI.
ConnectionParameters cp = DBMgr.GetAssetConnectionObject(m_assetToDelete);
List <string> transactions = new List <string>();
if (cp.IsNative)
{
string dropAssetTable = "DROP TABLE " + m_assetToDelete;
transactions.Add(dropAssetTable);
dropAssetTable = "DROP TABLE " + m_assetToDelete + "_CHANGELOG";
transactions.Add(dropAssetTable);
}
else
{
string dropAssetView = "DROP VIEW " + m_assetToDelete;
transactions.Add(dropAssetView);
}
DBMgr.ExecuteBatchNonQuery(transactions, cp);
string deleteAsset = "DELETE FROM ASSETS WHERE ASSET = '" + m_assetToDelete + "'";
DBMgr.ExecuteNonQuery(deleteAsset);
}
public void Delete()
{
// First call the FormManager and have it close all open tabs, except NETWORK_DEFINITION, ASSETS, CONSTRUCTION_HISTORY
// other ATTRIBUTE (RAW) tabs and Calculated fields.
FormManager.CloseSegmentationTabs();
FormManager.CloseAttributeTab(m_strAttributeToDelete);
FormManager.CloseNetworkTabs();
FormManager.CloseSimulationTabs();
ConnectionParameters cp = DBMgr.GetAttributeConnectionObject(m_strAttributeToDelete);
List <String> listCommandText = new List <String>();
//dsmelser 2009.02.08
//!!!THERE IS NO DEFAULT ESCAPE CHARACTER IN SQL, IT MUST BE EXPLICITLY DEFINED!!!
String strDelete = "DELETE FROM ATTRIBUTES_ WHERE ATTRIBUTE_ = '" + m_strAttributeToDelete + "'";
listCommandText.Add(strDelete);
String strUpdate = "";
string attributeSearch = m_strAttributeToDelete.Replace("_", "!_");
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strDelete = "DELETE FROM FEASIBILITY WHERE CRITERIA LIKE '%![" + attributeSearch + "!]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM COSTS WHERE CRITERIA LIKE '%![" + attributeSearch + "!]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM CONSEQUENCES WHERE CRITERIA LIKE '%![" + attributeSearch + "!]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM PERFORMANCE WHERE CRITERIA LIKE '%![" + attributeSearch + "!]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM PERFORMANCE WHERE EQUATION LIKE '%![" + attributeSearch + "!]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strUpdate = "UPDATE SIMULATIONS SET JURISDICTION = NULL WHERE JURISDICTION LIKE " +
"'%![" + attributeSearch + "!]%' ESCAPE '!'";
listCommandText.Add(strUpdate);
strDelete = "DELETE FROM DEFICIENTS WHERE CRITERIA LIKE '%![" + attributeSearch + "!]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM TARGETS WHERE CRITERIA LIKE '%![" + attributeSearch + "!]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM PRIORITY WHERE CRITERIA LIKE '%![" + attributeSearch + "!]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM CRITERIA_SEGMENT WHERE FAMILY_EXPRESSION LIKE '%![" + attributeSearch + "!]%' ESCAPE '!'";
listCommandText.Add(strDelete);
break;
case "ORACLE":
//we don't need to escape the brackets in ORACLE so it throws an error if we attempt it.
strDelete = "DELETE FROM FEASIBILITY WHERE CRITERIA LIKE '%[" + attributeSearch + "]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM COSTS WHERE CRITERIA LIKE '%[" + attributeSearch + "]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM CONSEQUENCES WHERE CRITERIA LIKE '%[" + attributeSearch + "]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM PERFORMANCE WHERE CRITERIA LIKE '%[" + attributeSearch + "]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM PERFORMANCE WHERE EQUATION LIKE '%[" + attributeSearch + "]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strUpdate = "UPDATE SIMULATIONS SET JURISDICTION = NULL WHERE JURISDICTION LIKE " +
"'%[" + attributeSearch + "]%' ESCAPE '!'";
listCommandText.Add(strUpdate);
strDelete = "DELETE FROM DEFICIENTS WHERE CRITERIA LIKE '%[" + attributeSearch + "]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM TARGETS WHERE CRITERIA LIKE '%[" + attributeSearch + "]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM PRIORITY WHERE CRITERIA LIKE '%[" + attributeSearch + "]%' ESCAPE '!'";
listCommandText.Add(strDelete);
strDelete = "DELETE FROM CRITERIA_SEGMENT WHERE FAMILY_EXPRESSION LIKE '%[" + attributeSearch + "]%' ESCAPE '!'";
listCommandText.Add(strDelete);
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
DeleteFromNetworks(listCommandText);
try
{
DBMgr.ExecuteBatchNonQuery(listCommandText);
}
catch (Exception exc)
{
Global.WriteOutput("Error: Failed to remove attribute. " + exc.Message);
return;
}
if (!m_IsAttributeCalculated)
{
if (cp.IsNative)
{
strDelete = "DROP TABLE " + m_strAttributeToDelete;
try
{
DBMgr.ExecuteNonQuery(strDelete);
}
catch (Exception exc)
{
Global.WriteOutput("Error: Could not drop attribute table " + m_strAttributeToDelete + ". " + exc.Message);
}
}
else
{
String dropView = "DROP VIEW " + m_strAttributeToDelete;
try
{
DBMgr.ExecuteNonQuery(dropView, cp);
}
catch (Exception exc)
{
Global.WriteOutput("Error: Could not drop non native view. " + exc.Message);
}
}
}
else
{
try
{
DBMgr.ExecuteNonQuery("DELETE FROM ATTRIBUTES_CALCULATED WHERE ATTRIBUTE_ = '" + m_strAttributeToDelete + "'");
}
catch (Exception ex)
{
Global.WriteOutput("Error: Could not delete calculated attribute formulas: " + ex.Message);
}
}
Global.SecurityOperations.RemoveAction(new RoadCareAction("ATTRIBUTE", m_strAttributeToDelete));
}
public static void PasteAnalysis(String strNewSimulation)
{
if (String.IsNullOrEmpty(m_strCopyAnalysisSimulationID))
{
return;
}
List <string> listInserts = new List <string>();
String strSelect = "SELECT * FROM SIMULATIONS WHERE SIMULATIONID = " + m_strCopyAnalysisSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count != 1)
{
return;
}
DataRow dr = ds.Tables[0].Rows[0];
String strComments = dr["COMMENTS"].ToString();
String strDateCreated = dr["DATE_CREATED"].ToString();
String strDateLastRun = dr["DATE_LAST_RUN"].ToString();
String strCreatorID = dr["CREATOR_ID"].ToString();
String strUserName = dr["USERNAME"].ToString();
String strPermission = dr["PERMISSIONS"].ToString();
String strJurisdiction = dr["JURISDICTION"].ToString();
String strAnalysis = dr["ANALYSIS"].ToString();
String strBudgetConstraint = dr["BUDGET_CONSTRAINT"].ToString();
String strWeighting = dr["WEIGHTING"].ToString();
String strBenefitVariable = dr["BENEFIT_VARIABLE"].ToString();
String strBenefitLimit = dr["BENEFIT_LIMIT"].ToString();
String strCommittedStart = dr["COMMITTED_START"].ToString();
String strCommittedPeriod = dr["COMMITTED_PERIOD"].ToString();
String strSimulationVariable = dr["SIMULATION_VARIABLES"].ToString();
String strUpdate = "UPDATE SIMULATIONS SET ";
if (strComments != "")
{
strUpdate += "COMMENTS" + "='" + strComments + "',";
}
if (strDateCreated != "")
{
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strUpdate += "DATE_CREATED" + "='" + strDateCreated + "',";
break;
case "ORACLE":
strUpdate += "DATE_CREATED=to_date('" + DateTime.Parse(strDateCreated).ToString("MM/dd/yyyy") + "','MM/DD/YYYY'),";
break;
default:
throw new NotImplementedException("TODO: Create ANSI version of PasteAnalysis()");
//break;
}
}
if (strDateLastRun != "")
{
strUpdate += "DATE_LAST_RUN" + "='" + strDateLastRun + "',";
}
if (strCreatorID != "")
{
strUpdate += "CREATOR_ID" + "='" + strCreatorID + "',";
}
if (strUserName != "")
{
strUpdate += "USERNAME" + "='" + strUserName + "',";
}
if (strPermission != "")
{
strUpdate += "PERMISSIONS" + "='" + strPermission + "',";
}
if (strJurisdiction != "")
{
strUpdate += "JURISDICTION" + "='" + strJurisdiction + "',";
}
if (strAnalysis != "")
{
strUpdate += "ANALYSIS" + "='" + strAnalysis + "',";
}
if (strBudgetConstraint != "")
{
strUpdate += "BUDGET_CONSTRAINT" + "='" + strBudgetConstraint + "',";
}
if (strWeighting != "")
{
strUpdate += "WEIGHTING" + "='" + strWeighting + "',";
}
if (strBenefitVariable != "")
{
strUpdate += "BENEFIT_VARIABLE" + "='" + strBenefitVariable + "',";
}
if (strBenefitLimit != "")
{
strUpdate += "BENEFIT_LIMIT" + "='" + strBenefitLimit + "',";
}
if (strBenefitLimit != "")
{
strUpdate += "COMMITTED_START" + "='" + strCommittedStart + "',";
}
if (strCommittedPeriod != "")
{
strUpdate += "COMMITTED_PERIOD" + "='" + strCommittedPeriod + "',";
}
if (strSimulationVariable != "")
{
strUpdate += "SIMULATION_VARIABLES" + "='" + strSimulationVariable + "',";
}
if (strUpdate.Substring(strUpdate.Length - 1) == ",")
{
strUpdate = strUpdate.Substring(0, strUpdate.Length - 1);
strUpdate += " WHERE SIMULATIONID=" + strNewSimulation;
listInserts.Add(strUpdate);
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Copying ANALYSIS information." + exception.Message);
return;
}
//Get TARGETS
String strDelete = "DELETE FROM TARGETS WHERE SIMULATIONID = " + strNewSimulation;
listInserts.Add(strDelete);
strSelect = "SELECT * FROM TARGETS WHERE SIMULATIONID = " + m_strCopyAnalysisSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
String strAttribute = dr["ATTRIBUTE_"].ToString();
String strYears = dr["YEARS"].ToString();
String strTargetMean = dr["TARGETMEAN"].ToString();
String strTargetName = dr["TARGETNAME"].ToString();
String strCriteria = dr["CRITERIA"].ToString();
String strInsert = "INSERT INTO TARGETS (SIMULATIONID,ATTRIBUTE_,";
String strValue = "VALUES(" + strNewSimulation + ",'" + strAttribute + "',";
if (!String.IsNullOrEmpty(strYears))
{
strInsert += "YEARS,";
strValue += "'" + strYears + "',";
}
if (!String.IsNullOrEmpty(strTargetMean))
{
strInsert += "TARGETMEAN,";
strValue += "'" + strTargetMean + "',";
}
if (!String.IsNullOrEmpty(strTargetName))
{
strInsert += "TARGETNAME,";
strValue += "'" + strTargetName + "',";
}
if (!String.IsNullOrEmpty(strCriteria))
{
strInsert += "CRITERIA,";
strValue += "'" + strCriteria + "',";
}
strInsert = strInsert.Substring(0, strInsert.Length - 1) + ") ";
strValue = strValue.Substring(0, strValue.Length - 1) + ")";
strInsert += strValue;
listInserts.Add(strInsert);
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Pasting TARGETS. Operation aborted." + exception.Message);
return;
}
//Get DEFICIENT
strDelete = "DELETE FROM DEFICIENTS WHERE SIMULATIONID = " + strNewSimulation;
listInserts.Add(strDelete);
strSelect = "SELECT * FROM DEFICIENTS WHERE SIMULATIONID = " + m_strCopyAnalysisSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
String strAttribute = dr["ATTRIBUTE_"].ToString();
String strDeficientName = dr["DEFICIENTNAME"].ToString();
String strDeficient = dr["DEFICIENT"].ToString();
String strPercentDeficient = dr["PERCENTDEFICIENT"].ToString();
String strCriteria = dr["CRITERIA"].ToString();
String strInsert = "INSERT INTO DEFICIENTS (SIMULATIONID,ATTRIBUTE_,";
String strValue = "VALUES(" + strNewSimulation + ",'" + strAttribute + "',";
if (!String.IsNullOrEmpty(strDeficientName))
{
strInsert += "DEFICIENTNAME,";
strValue += "'" + strDeficientName + "',";
}
if (!String.IsNullOrEmpty(strDeficient))
{
strInsert += "DEFICIENT,";
strValue += "'" + strDeficient + "',";
}
if (!String.IsNullOrEmpty(strPercentDeficient))
{
strInsert += "PERCENTDEFICIENT,";
strValue += "'" + strPercentDeficient + "',";
}
if (!String.IsNullOrEmpty(strCriteria))
{
strInsert += "CRITERIA,";
strValue += "'" + strCriteria + "',";
}
strInsert = strInsert.Substring(0, strInsert.Length - 1) + ") ";
strValue = strValue.Substring(0, strValue.Length - 1) + ")";
strInsert += strValue;
listInserts.Add(strInsert);
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Pasting DEFICIENT. Operation aborted." + exception.Message);
return;
}
strDelete = "DELETE FROM PRIORITY WHERE SIMULATIONID = " + strNewSimulation;
listInserts.Add(strDelete);
//Run as much of this that can be batch in one try.
try
{
DBMgr.ExecuteBatchNonQuery(listInserts);
}
catch (Exception exception)
{
Global.WriteOutput("Error: Copying ANALYSIS. Operation aborted." + exception.Message);
}
//Now get PRIORITY and PRIORITYFUND
strSelect = "SELECT * FROM PRIORITY WHERE SIMULATIONID=" + m_strCopyAnalysisSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
String strPriorityID = dr["PRIORITYID"].ToString();
String strPriorityLevel = dr["PRIORITYLEVEL"].ToString();
String strCriteria = dr["CRITERIA"].ToString();
string years = null;
if (dr["YEARS"] != DBNull.Value)
{
years = dr["YEARS"].ToString();
}
String strInsert;
if (years == null)
{
if (String.IsNullOrEmpty(strCriteria))
{
strInsert = "INSERT INTO PRIORITY (SIMULATIONID,PRIORITYLEVEL) VALUES(" + strNewSimulation + "," + strPriorityLevel + ")";
}
else
{
strInsert = "INSERT INTO PRIORITY (SIMULATIONID,PRIORITYLEVEL,CRITERIA) VALUES(" + strNewSimulation + "," + strPriorityLevel + ",'" + strCriteria + "')";
}
}
else
{
if (String.IsNullOrEmpty(strCriteria))
{
strInsert = "INSERT INTO PRIORITY (SIMULATIONID,PRIORITYLEVEL,YEARS) VALUES(" + strNewSimulation + "," + strPriorityLevel + ",'" + years + "')";
}
else
{
strInsert = "INSERT INTO PRIORITY (SIMULATIONID,PRIORITYLEVEL,CRITERIA,YEARS) VALUES(" + strNewSimulation + "," + strPriorityLevel + ",'" + strCriteria + "','" + years + "')";
}
}
DBMgr.ExecuteNonQuery(strInsert);
String strIdentity;
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strIdentity = "SELECT IDENT_CURRENT ('PRIORITY') FROM PRIORITY";
break;
case "ORACLE":
//strIdentity = "SELECT PRIORITY_PRIORITYID_SEQ.CURRVAL FROM DUAL";
//strIdentity = "SELECT SELECT LAST_NUMBER - CACHE_SIZE FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'PRIORITY_PRIORITYID_SEQ'";
strIdentity = "SELECT MAX(PRIORITYID) FROM PRIORITY";
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
//break;
}
DataSet dsIdentity = DBMgr.ExecuteQuery(strIdentity);
strIdentity = dsIdentity.Tables[0].Rows[0].ItemArray[0].ToString();
strSelect = "SELECT * FROM PRIORITYFUND WHERE PRIORITYID =" + strPriorityID;
DataSet dsPriorityFund = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow drPriorityFund in dsPriorityFund.Tables[0].Rows)
{
String strBudget = drPriorityFund["BUDGET"].ToString();
String strFunding = drPriorityFund["FUNDING"].ToString();
strInsert = "INSERT INTO PRIORITYFUND (PRIORITYID,BUDGET,FUNDING) VALUES(" + strIdentity + ",'" + strBudget + "'," + strFunding + ")";
DBMgr.ExecuteNonQuery(strInsert);
}
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Failure in copying Priority Levels and Funding. Rest of Analysis copied correctly." + exception.Message);
}
}
public static void PasteInvestment(String strNewSimulationID)
{
if (String.IsNullOrEmpty(m_strCopyInvestmentSimulationID))
{
return;
}
List <string> listInserts = new List <string>();
//Delete existing SIMULATIONID FROM
String strDelete = "DELETE FROM INVESTMENTS WHERE SIMULATIONID=" + strNewSimulationID;
listInserts.Add(strDelete);
strDelete = "DELETE FROM YEARLYINVESTMENT WHERE SIMULATIONID=" + strNewSimulationID;
listInserts.Add(strDelete);
String strSelect = "SELECT * FROM INVESTMENTS WHERE SIMULATIONID=" + m_strCopyInvestmentSimulationID;
String strSimulationID;
String strFirstYear;
String strNumberYear;
String strInflation;
String strDiscount;
String strBudgetOrder;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
strSimulationID = dr["SIMULATIONID"].ToString();
strFirstYear = dr["FIRSTYEAR"].ToString();
strNumberYear = dr["NUMBERYEARS"].ToString();
strInflation = dr["INFLATIONRATE"].ToString();
strDiscount = dr["DISCOUNTRATE"].ToString();
strBudgetOrder = dr["BUDGETORDER"].ToString();
String strInsert = "INSERT INTO INVESTMENTS (SIMULATIONID,FIRSTYEAR,NUMBERYEARS,INFLATIONRATE,DISCOUNTRATE,BUDGETORDER) VALUES (" + strNewSimulationID + "," + strFirstYear + "," + strNumberYear + "," + strInflation + "," + strDiscount + ",'" + strBudgetOrder + "')";
listInserts.Add(strInsert);
}
else
{
return;
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Retrieving existing Investment Information." + exception.Message);
return;
}
strSelect = "SELECT * FROM YEARLYINVESTMENT WHERE SIMULATIONID=" + m_strCopyInvestmentSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
String strYear = dr["YEAR_"].ToString();
String strBudget = dr["BUDGETNAME"].ToString();
String strAmount = dr["AMOUNT"].ToString();
String strInsert = "INSERT INTO YEARLYINVESTMENT (SIMULATIONID,YEAR_,BUDGETNAME,AMOUNT) VALUES (" + strNewSimulationID + "," + strYear + ",'" + strBudget + "'," + strAmount + ")";
listInserts.Add(strInsert);
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Retrieving existing YearlyInvestment Information." + exception.Message);
return;
}
try
{
DBMgr.ExecuteBatchNonQuery(listInserts);
}
catch (Exception exception)
{
Global.WriteOutput("Error: Copying Investments from one simulation to another" + exception.Message);
}
}
public int Update(DataTable dataTable)
{
int affectedRows = 0;
if (SDA != null)
{
return(SDA.Update(dataTable));
}
else
{
switch (adapterConnectionInfo.Provider)
{
case "MSSQL":
affectedRows = ODA.Update(dataTable);
break;
case "ORACLE":
string query = "";
List <string> updateCommands = new List <string>();
List <DataRow> updatedRows = new List <DataRow>();
foreach (DataRow row in dataTable.Rows)
{
switch (row.RowState)
{
case DataRowState.Added:
if (!EmptyRow(row))
{
String valuesClauseString;
query = "INSERT INTO " + dataTable.TableName + " (";
valuesClauseString = " VALUES( ";
foreach (DataColumn column in row.Table.Columns)
{
if (column.ColumnName.ToUpper() != "ID_") //need to skip the auto-increment columns
{
if (column.ColumnName.ToUpper() == dataTable.TableName.ToUpper())
{
query += "\"DATA_\", ";
}
else
{
query += "\"" + column.ColumnName + "\", ";
}
if (column.ColumnName.ToUpper().Contains("DATE"))
{
valuesClauseString += "TO_DATE('" + (( DateTime )row[column]).ToShortDateString() + "','MM/DD/YYYY'), ";
}
else
{
valuesClauseString += "'" + row[column].ToString() + "', ";
}
}
}
valuesClauseString = valuesClauseString.Remove(valuesClauseString.Length - 2);
query = query.Remove(query.Length - 2);
valuesClauseString += ")";
query += ")" + valuesClauseString;
updateCommands.Add(query);
updatedRows.Add(row);
}
break;
case DataRowState.Deleted:
query = "DELETE FROM " + dataTable.TableName + " " + BuildWhereClause(row, dataTable.TableName);
updateCommands.Add(query);
updatedRows.Add(row);
break;
case DataRowState.Modified:
query = "UPDATE " + dataTable.TableName + " SET ";
foreach (DataColumn column in row.Table.Columns)
{
if (column.ColumnName.ToUpper() != "ID_" && column.ColumnName.ToUpper() != "ROWID") //don't want to update primary key columns
{
if (column.ColumnName.ToUpper() == dataTable.TableName.ToUpper())
{
query += "DATA_ = ";
}
else
{
query += "\"" + column.ColumnName + "\" = ";
}
if (column.ColumnName.ToUpper().Contains("DATE"))
{
query += "TO_DATE('" + (( DateTime )row[column]).ToShortDateString() + "','MM/DD/YYYY'), ";
}
else
{
query += "'" + row[column].ToString() + "', ";
}
}
}
query = query.Remove(query.Length - 2);
query += " " + BuildWhereClause(row, dataTable.TableName); //WHERE \"ID_\" = '" + row["ID_", DataRowVersion.Original].ToString() + "'";
updateCommands.Add(query);
updatedRows.Add(row);
break;
}
}
int rowsAffected = -1;
try
{
rowsAffected = DBMgr.ExecuteBatchNonQuery(updateCommands);
foreach (DataRow row in updatedRows)
{
row.AcceptChanges();
}
}
catch (Exception ex)
{
throw ex; //this seems odd, but it's really just to jump over marking the rows as updated incorrectly
}
break;
default:
throw new NotImplementedException("TODO: Create ANSI implementation for XXXXXXXXXXXX");
}
return(affectedRows);
}
}
public static void PastePerformance(String strNewSimulationID)
{
if (String.IsNullOrEmpty(m_strCopyPerformanceSimulationID))
{
return;
}
List <string> listInserts = new List <string>();
//Delete existing SIMULATIONID FROM
String strDelete = "DELETE FROM PERFORMANCE WHERE SIMULATIONID=" + strNewSimulationID;
listInserts.Add(strDelete);
String strSelect = "SELECT * FROM PERFORMANCE WHERE SIMULATIONID=" + m_strCopyPerformanceSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
String strAttribute = dr["ATTRIBUTE_"].ToString();
String strEquationName = dr["EQUATIONNAME"].ToString();
String strCritera = dr["CRITERIA"].ToString();
String strEquations = dr["EQUATION"].ToString();
String strShift = dr["SHIFT"].ToString();
string strPiecewise = dr["PIECEWISE"].ToString();
string isFunction = "0";
bool bIsFunction = false;
if (dr["ISFUNCTION"] != DBNull.Value)
{
bIsFunction = Convert.ToBoolean(dr["ISFUNCTION"]);
}
if (bIsFunction)
{
isFunction = "1";
}
String strInsert = "";
switch (DBMgr.NativeConnectionParameters.Provider)
{
case "MSSQL":
strInsert = "INSERT INTO PERFORMANCE (SIMULATIONID,ATTRIBUTE_,EQUATIONNAME,CRITERIA,EQUATION,SHIFT,PIECEWISE,ISFUNCTION)VALUES(" + strNewSimulationID + ",'" + strAttribute + "','" + strEquationName + "','" + strCritera + "','" + strEquations + "','" + strShift + "','" + strPiecewise + "','" + isFunction + "')";
break;
case "ORACLE":
strInsert = "INSERT INTO PERFORMANCE (SIMULATIONID,ATTRIBUTE_,EQUATIONNAME,CRITERIA,EQUATION,SHIFT,PIECEWISE,ISFUNCTION)VALUES(" + strNewSimulationID + ",'" + strAttribute + "','" + strEquationName + "','" + strCritera + "','" + strEquations + "','" + strShift + "','" + strPiecewise + "','" + isFunction + "')";
break;
default:
throw new NotImplementedException("TODO: Implement ANSI version of PastePerformance()");
//break;
}
listInserts.Add(strInsert);
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Copy PERFORMANCE table." + exception.Message);
return;
}
try
{
DBMgr.ExecuteBatchNonQuery(listInserts);
Global.WriteOutput("Performance equations successfully copied.");
}
catch (Exception exception)
{
Global.WriteOutput("Error: Copying Performance equations from one simulation to another" + exception.Message);
}
}
public static void PasteInvestment(String strNewSimulationID)
{
if (String.IsNullOrEmpty(m_strCopyInvestmentSimulationID))
{
return;
}
List <string> listInserts = new List <string>();
//Delete existing SIMULATIONID FROM
var deleteInvestments = "DELETE FROM INVESTMENTS WHERE SIMULATIONID=" + strNewSimulationID;
listInserts.Add(deleteInvestments);
var deleteYearlyInvestment = "DELETE FROM YEARLYINVESTMENT WHERE SIMULATIONID=" + strNewSimulationID;
listInserts.Add(deleteYearlyInvestment);
var delete = "DELETE FROM BUDGET_CRITERIA WHERE SIMULATIONID=" + strNewSimulationID;
listInserts.Add(delete);
var deleteSplitTreatment = "DELETE FROM SPLIT_TREATMENT WHERE SIMULATIONID=" + strNewSimulationID;
listInserts.Add(deleteSplitTreatment);
String strSelect = "SELECT * FROM INVESTMENTS WHERE SIMULATIONID=" + m_strCopyInvestmentSimulationID;
String strSimulationID;
String strFirstYear;
String strNumberYear;
String strInflation;
String strDiscount;
String strBudgetOrder;
String strDescription = "";
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
if (ds.Tables[0].Rows.Count == 1)
{
DataRow dr = ds.Tables[0].Rows[0];
strSimulationID = dr["SIMULATIONID"].ToString();
strFirstYear = dr["FIRSTYEAR"].ToString();
strNumberYear = dr["NUMBERYEARS"].ToString();
strInflation = dr["INFLATIONRATE"].ToString();
strDiscount = dr["DISCOUNTRATE"].ToString();
strBudgetOrder = dr["BUDGETORDER"].ToString();
if (dr["DESCRIPTION"] != DBNull.Value)
{
strDescription = dr["DESCRIPTION"].ToString();
}
String strInsert = "INSERT INTO INVESTMENTS (SIMULATIONID,FIRSTYEAR,NUMBERYEARS,INFLATIONRATE,DISCOUNTRATE,BUDGETORDER,DESCRIPTION) VALUES ('" + strNewSimulationID + "','" + strFirstYear + "','" + strNumberYear + "','" + strInflation + "','" + strDiscount + "','" + strBudgetOrder + "','" + strDescription + "')";
listInserts.Add(strInsert);
}
else
{
return;
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Retrieving existing Investment Information." + exception.Message);
return;
}
strSelect = "SELECT * FROM YEARLYINVESTMENT WHERE SIMULATIONID=" + m_strCopyInvestmentSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(strSelect);
foreach (DataRow dr in ds.Tables[0].Rows)
{
String strYear = dr["YEAR_"].ToString();
String strBudget = dr["BUDGETNAME"].ToString();
String strAmount = dr["AMOUNT"].ToString();
String strInsert = "INSERT INTO YEARLYINVESTMENT (SIMULATIONID,YEAR_,BUDGETNAME,AMOUNT) VALUES (" + strNewSimulationID + "," + strYear + ",'" + strBudget + "'," + strAmount + ")";
listInserts.Add(strInsert);
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Retrieving existing YearlyInvestment Information." + exception.Message);
return;
}
var selectBudgetCriteria = "SELECT * FROM BUDGET_CRITERIA WHERE SIMULATIONID=" + m_strCopyInvestmentSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(selectBudgetCriteria);
foreach (DataRow dr in ds.Tables[0].Rows)
{
var budgetName = dr["BUDGET_NAME"].ToString();
var criteria = dr["CRITERIA"].ToString();
String strInsert = "INSERT INTO BUDGET_CRITERIA (SIMULATIONID,BUDGET_NAME,CRITERIA) VALUES ('" + strNewSimulationID + "','" + budgetName + "','" + criteria + "')";
listInserts.Add(strInsert);
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Retrieving existing YearlyInvestment Information." + exception.Message);
return;
}
try
{
DBMgr.ExecuteBatchNonQuery(listInserts);
Global.WriteOutput("Investment and Budget Criteria successfully copied.");
}
catch (Exception exception)
{
Global.WriteOutput("Error: Copying Investments from one simulation to another" + exception.Message);
}
var selectSplitTreatment = "SELECT * FROM SPLIT_TREATMENT WHERE SIMULATIONID=" + m_strCopyInvestmentSimulationID;
try
{
DataSet ds = DBMgr.ExecuteQuery(selectSplitTreatment);
foreach (DataRow dr in ds.Tables[0].Rows)
{
var description = dr["DESCRIPTION"].ToString();
var criteria = dr["CRITERIA"].ToString();
var splitTreatmentIdToCopy = dr["SPLIT_TREATMENT_ID"].ToString();
String strInsert = "INSERT INTO SPLIT_TREATMENT(SIMULATIONID,DESCRIPTION,CRITERIA) VALUES ('" + strNewSimulationID + "','" + description + "','" + criteria + "')";
DBMgr.ExecuteNonQuery(strInsert);
//Get the identity
string selectIdentity = "SELECT IDENT_CURRENT ('SPLIT_TREATMENT') FROM SPLIT_TREATMENT";
var splitTreatmentIdToPaste = DBMgr.ExecuteScalar(selectIdentity).ToString();
var splitTreatmentLimit = "SELECT * FROM SPLIT_TREATMENT_LIMIT WHERE SPLIT_TREATMENT_ID=" + splitTreatmentIdToCopy;
DataSet dataSetLimit = DBMgr.ExecuteQuery(splitTreatmentLimit);
foreach (DataRow limit in dataSetLimit.Tables[0].Rows)
{
int rank = -1;
float amount = -1;
string percentage = "";
if (limit["RANK"] != DBNull.Value)
{
rank = Convert.ToInt32(limit["RANK"]);
}
if (limit["AMOUNT"] != DBNull.Value)
{
amount = Convert.ToSingle(limit["AMOUNT"]);
}
if (limit["PERCENTAGE"] != DBNull.Value)
{
percentage = limit["PERCENTAGE"].ToString();
}
String limitInsert = "INSERT INTO SPLIT_TREATMENT_LIMIT(SPLIT_TREATMENT_ID,RANK,PERCENTAGE) VALUES ('" + splitTreatmentIdToPaste + "','" + rank + "','" + percentage + "')";
if (amount > 0)
{
limitInsert = "INSERT INTO SPLIT_TREATMENT_LIMIT(SPLIT_TREATMENT_ID,RANK,PERCENTAGE,AMOUNT) VALUES ('" + splitTreatmentIdToPaste + "','" + rank + "','" + percentage + "','" + amount + "')";
}
DBMgr.ExecuteNonQuery(limitInsert);
}
}
}
catch (Exception exception)
{
Global.WriteOutput("Error: Retrieving existing split treatment information." + exception.Message);
return;
}
}