private void saveprivate() { ado_data_access p_ado = new ado_data_access(); string strMDB = ((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.txtPersonal.Text.Trim() + "\\personal_project_links_and_notes.mdb"; string strConn = p_ado.getMDBConnString(strMDB, "admin", ""); //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strMDB + ";User Id=admin;Password=;"; string strSQL = ""; p_ado.OpenConnection(strConn); if (p_ado.m_intError == 0) { try { string strNotes = this.txtNotesPersonal.Text; strNotes = p_ado.FixString(strNotes, "'", "''"); if ((int)p_ado.getRecordCount(p_ado.m_OleDbConnection, "SELECT COUNT(*) FROM NOTES", "NOTES") > 0) { strSQL = "UPDATE NOTES SET NOTES = '" + strNotes + "'"; p_ado.SqlNonQuery(p_ado.m_OleDbConnection, strSQL); } else { strSQL = "INSERT INTO NOTES (NOTES) VALUES ('" + strNotes + "')"; p_ado.SqlNonQuery(p_ado.m_OleDbConnection, strSQL); } } catch { } } p_ado = null; }
public void DeleteScenario() { string strSQL = "Delete Scenario '" + this.txtScenarioId.Text + "' (Y/N)?"; DialogResult result = MessageBox.Show(strSQL, "Delete Scenario", MessageBoxButtons.YesNo, MessageBoxIcon.Question); switch (result) { case DialogResult.Yes: break; case DialogResult.No: return; } string strScenarioPath = this.txtScenarioPath.Text; string strScenarioFile = "scenario_" + ScenarioType + "_rule_definitions.mdb"; string strScenarioDir = ((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectDirectory + "\\" + ScenarioType + "\\db"; string strFile = "scenario_" + ScenarioType + "_rule_definitions.mdb"; //((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectFile; StringBuilder strFullPath = new StringBuilder(strScenarioDir); strFullPath.Append("\\"); strFullPath.Append(strFile); System.Data.OleDb.OleDbConnection oConn = new System.Data.OleDb.OleDbConnection(); ado_data_access p_ado = new ado_data_access(); string strConn = p_ado.getMDBConnString(strFullPath.ToString(), "admin", ""); //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFullPath.ToString() + ";User Id=admin;Password=;"; strSQL = "DELETE * FROM scenario WHERE scenario_id = " + "'" + this.txtScenarioId.Text.Trim() + "'"; p_ado.SqlNonQuery(strConn, strSQL); if (p_ado.m_intError == 0) { strSQL = "DELETE * FROM scenario_datasource WHERE scenario_id = " + "'" + this.txtScenarioId.Text.Trim() + "'"; p_ado.SqlNonQuery(strConn, strSQL); } if (p_ado.m_intError == 0) { strSQL = this.txtScenarioId.Text + " was successfully deleted from the scenario tables. Do you wish to delete the scenario file and directory (Y/N)?"; result = MessageBox.Show(strSQL, "Delete Scenario", MessageBoxButtons.YesNo, MessageBoxIcon.Question); switch (result) { case DialogResult.Yes: strSQL = strScenarioPath + "\\" + strScenarioFile; try { System.IO.Directory.Delete(strScenarioPath, true); } catch { } break; case DialogResult.No: break; } } p_ado = null; this.ParentForm.Close(); }
public void UpdateDescription() { string strDesc = ""; FIA_Biosum_Manager.ado_data_access oAdo = new ado_data_access(); string strScenarioDBDir = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\" + ScenarioType + "\\db"; string strScenarioFile = "scenario_" + ScenarioType + "_rule_definitions.mdb"; StringBuilder strScenarioFullPath = new StringBuilder(strScenarioDBDir); strScenarioFullPath.Append("\\"); strScenarioFullPath.Append(strScenarioFile); string strScenarioConn = oAdo.getMDBConnString(strScenarioFullPath.ToString(), "admin", ""); oAdo.OpenConnection(strScenarioConn); if (this.txtDescription.Text.Trim().Length > 0) { strDesc = oAdo.FixString(this.txtDescription.Text.Trim(), "'", "''"); } oAdo.m_strSQL = "UPDATE scenario SET description='" + strDesc.Trim() + "' WHERE TRIM(scenario_id)='" + this.txtScenarioId.Text.Trim() + "'"; oAdo.SqlNonQuery(oAdo.m_OleDbConnection, oAdo.m_strSQL); oAdo.m_OleDbConnection.Close(); while (oAdo.m_OleDbConnection.State != System.Data.ConnectionState.Closed) { oAdo.m_OleDbConnection.Close(); System.Threading.Thread.Sleep(1000); } oAdo = null; }
public void SaveScenarioNotes() { ado_data_access p_ado = new ado_data_access(); System.Data.OleDb.OleDbConnection oConn = new System.Data.OleDb.OleDbConnection(); string strProjDir = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim(); string strScenarioDir = strProjDir + "\\" + ScenarioType + "\\db"; string strFile = "scenario_" + ScenarioType + "_rule_definitions.mdb"; //((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectFile; StringBuilder strFullPath = new StringBuilder(strScenarioDir); strFullPath.Append("\\"); strFullPath.Append(strFile); string strNotes = this.txtNotes.Text; string strSQL = ""; strNotes = p_ado.FixString(strNotes, "'", "''"); string strConn = p_ado.getMDBConnString(strFullPath.ToString(), "admin", ""); //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFullPath.ToString() + ";User Id=admin;Password=;"; if (ScenarioType.Trim().ToUpper() == "OPTIMIZER") { strSQL = "UPDATE scenario SET notes = '" + strNotes + "' WHERE trim(lcase(scenario_id)) = '" + ((frmOptimizerScenario)this.ParentForm).uc_scenario1.txtScenarioId.Text.Trim().ToLower() + "';"; } else { strSQL = "UPDATE scenario SET notes = '" + strNotes + "' WHERE trim(lcase(scenario_id)) = '" + this.ReferenceProcessorScenarioForm.uc_scenario1.txtScenarioId.Text.Trim().ToLower() + "';"; } p_ado.SqlNonQuery(strConn, strSQL); p_ado = null; }
public void SaveScenarioNotes() { ado_data_access p_ado = null; SQLite.ADO.DataMgr oDataMgr = null; string strNotes = this.txtNotes.Text; string strProjDir = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim(); string strScenarioDir = strProjDir + "\\" + ScenarioType + "\\db"; if ((ReferenceProcessorScenarioForm != null && !ReferenceProcessorScenarioForm.m_bUsingSqlite) || ScenarioType.Trim().ToUpper() == "OPTIMIZER") { p_ado = new ado_data_access(); strNotes = p_ado.FixString(strNotes, "'", "''"); string strSQL = ""; if (ScenarioType.Trim().ToUpper() == "OPTIMIZER") { strSQL = "UPDATE scenario SET notes = '" + strNotes + "' WHERE trim(lcase(scenario_id)) = '" + ((frmOptimizerScenario)this.ParentForm).uc_scenario1.txtScenarioId.Text.Trim().ToLower() + "';"; } else { strSQL = "UPDATE scenario SET notes = '" + strNotes + "' WHERE trim(lcase(scenario_id)) = '" + this.ReferenceProcessorScenarioForm.uc_scenario1.txtScenarioId.Text.Trim().ToLower() + "';"; } System.Data.OleDb.OleDbConnection oConn = new System.Data.OleDb.OleDbConnection(); string strFile = "scenario_" + ScenarioType + "_rule_definitions.mdb"; StringBuilder strFullPath = new StringBuilder(strScenarioDir); strFullPath.Append("\\"); strFullPath.Append(strFile); string strConn = p_ado.getMDBConnString(strFullPath.ToString(), "admin", ""); p_ado.SqlNonQuery(strConn, strSQL); p_ado = null; } else { oDataMgr = new SQLite.ADO.DataMgr(); strNotes = oDataMgr.FixString(strNotes, "'", "''"); //@ToDo: Only support Processor at this time string strSQL = "UPDATE scenario SET notes = '" + strNotes + "' WHERE trim(lower(scenario_id)) = '" + this.ReferenceProcessorScenarioForm.uc_scenario1.txtScenarioId.Text.Trim().ToLower() + "';"; string strFile = "scenario_" + ScenarioType + "_rule_definitions.db"; StringBuilder strFullPath = new StringBuilder(strScenarioDir); strFullPath.Append("\\"); strFullPath.Append(strFile); string strConn = oDataMgr.GetConnectionString(strFullPath.ToString()); using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(strConn)) { conn.Open(); oDataMgr.SqlNonQuery(conn, strSQL); } oDataMgr = null; } }
public void savevalues() { ado_data_access oAdo = new ado_data_access(); string strScenarioId = this.ReferenceOptimizerScenarioForm.uc_scenario1.txtScenarioId.Text.Trim().ToLower(); string strScenarioMDB = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\" + Tables.OptimizerScenarioRuleDefinitions.DefaultScenarioTableDbFile; oAdo.OpenConnection(oAdo.getMDBConnString(strScenarioMDB, "", "")); oAdo.m_strSQL = "DELETE FROM " + Tables.OptimizerScenarioRuleDefinitions.DefaultScenarioProcessorScenarioSelectTableName + " " + "WHERE TRIM(UCASE(scenario_id)) = '" + ReferenceOptimizerScenarioForm.uc_scenario1.txtScenarioId.Text.Trim().ToUpper() + "';"; oAdo.SqlNonQuery(oAdo.m_OleDbConnection, oAdo.m_strSQL); if (lvProcessorScenario.CheckedItems.Count > 0) { string strColumnsList = "scenario_id,processor_scenario_id,FullDetailsYN"; string strValuesList = ""; strValuesList = "'" + ReferenceOptimizerScenarioForm.uc_scenario1.txtScenarioId.Text.Trim() + "',"; strValuesList = strValuesList + "'" + lvProcessorScenario.CheckedItems[0].SubItems[COL_SCENARIOID].Text.Trim() + "',"; if (this.chkFullDetails.Checked) { strValuesList = strValuesList + "'Y'"; } else { strValuesList = strValuesList + "'N'"; } oAdo.m_strSQL = "INSERT INTO " + Tables.OptimizerScenarioRuleDefinitions.DefaultScenarioProcessorScenarioSelectTableName + " " + "(" + strColumnsList + ") " + "VALUES " + "(" + strValuesList + ")"; oAdo.SqlNonQuery(oAdo.m_OleDbConnection, oAdo.m_strSQL); } oAdo.CloseConnection(oAdo.m_OleDbConnection); }
private void Save() { string strPath = ""; string strDatasource = ""; string strTableType = ""; string strScenario = ""; ado_data_access oAdo = new ado_data_access(); oAdo.OpenConnection(oAdo.getMDBConnString(m_strRandomPathAndFile, "", "")); for (int x = 0; x <= lvDatasources.Items.Count - 1; x++) { strDatasource = lvDatasources.Items[x].SubItems[COLUMN_DATASOURCE].Text.Trim(); strTableType = lvDatasources.Items[x].SubItems[COLUMN_TABLETYPE].Text.Trim(); strPath = lvDatasources.Items[x].SubItems[COLUMN_PATH].Text.Trim(); strScenario = lvDatasources.Items[x].SubItems[COLUMN_SCENARIO].Text.Trim(); oAdo.m_strSQL = ""; if (strDatasource == "Project") { oAdo.m_strSQL = "UPDATE project_datasource SET path = '" + strPath + "' WHERE TRIM(Table_Type) = '" + strTableType + "'"; } else if (strDatasource == "TreatmentOptimizer" && strTableType == "NA") { oAdo.m_strSQL = "UPDATE optimizer_scenario SET path = '" + strPath + "' WHERE TRIM(scenario_id) = '" + strScenario + "'"; } else if (strDatasource == "TreatmentOptimizer") { oAdo.m_strSQL = "UPDATE optimizer_scenario_datasource SET path = '" + strPath + "' WHERE TRIM(scenario_id) = '" + strScenario + "' AND TRIM(Table_Type) = '" + strTableType + "'"; } else if (strDatasource == "Processor" && strTableType == "NA") { oAdo.m_strSQL = "UPDATE processor_scenario SET path = '" + strPath + "' WHERE TRIM(scenario_id) = '" + strScenario + "'"; } else if (strDatasource == "Processor") { oAdo.m_strSQL = "UPDATE processor_scenario_datasource SET path = '" + strPath + "' WHERE TRIM(scenario_id) = '" + strScenario + "' AND TRIM(Table_Type) = '" + strTableType + "'"; } if (oAdo.m_strSQL.Trim().Length > 0) { oAdo.SqlNonQuery(oAdo.m_OleDbConnection, oAdo.m_strSQL); } } oAdo.CloseConnection(oAdo.m_OleDbConnection); MessageBox.Show("Done", "FIA Biosum"); m_bSyncd = false; }
public int savevalues() { int x = 0; string strSQL = ""; ado_data_access p_ado = new ado_data_access(); p_ado.m_intError = 0; try { for (x = 0; x <= this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Rows.Count - 1; x++) { if (m_DataSet.Tables["scenario_last_tiebreak_rank"].Rows[x]["last_tiebreak_rank"] != System.DBNull.Value && m_DataSet.Tables["scenario_last_tiebreak_rank"].Rows[x]["last_tiebreak_rank"].ToString().Trim().Length > 0) { strSQL = "UPDATE scenario_last_tiebreak_rank SET last_tiebreak_rank = " + this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Rows[x]["last_tiebreak_rank"] + " WHERE TRIM(rxpackage) = '" + this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Rows[x]["rxpackage"].ToString().Trim() + "';"; } else { strSQL = "UPDATE scenario_last_tiebreak_rank SET last_tiebreak_rank = null " + " WHERE TRIM(rxpackage) = '" + this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Rows[x]["rxpackage"].ToString().Trim() + "';"; } p_ado.SqlNonQuery(this.m_OleDbConnectionScenario, strSQL); if (p_ado.m_intError < 0) { break; } } } catch (Exception caught) { MessageBox.Show("Function: uc_optimizer_scenario_last_tiebreak_rank.savevalues ErrMsg:" + caught.Message + " Failed updating scenario_last_tiebreak_rank table with last tiebreak rank ratings"); } x = p_ado.m_intError; p_ado = null; return(x); }
public void savevalues() { int x; string strSpcGrp = ""; string strDbhGrp = ""; string strMerchValue = ""; string strChipValue = ""; string strWoodBin = ""; string strSql = ""; SQLite.ADO.DataMgr oDataMgr = null; if (!ReferenceProcessorScenarioForm.m_bUsingSqlite) { // //DELETE THE CURRENT SCENARIO RECORDS // m_oAdo.m_strSQL = "DELETE FROM scenario_tree_species_diam_dollar_values " + "WHERE TRIM(scenario_id)='" + this.ScenarioId.Trim() + "'"; m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL); // //DELETE THE WORK TABLE // if (m_oAdo.TableExist(m_oAdo.m_OleDbConnection, "spcgrp_dbhgrp")) { m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, "DROP TABLE spcgrp_dbhgrp"); } // //CREATE AND POPULATE WORK TABLE // m_oAdo.m_strSQL = "CREATE TABLE spcgrp_dbhgrp (" + "species_group INTEGER," + "species_label CHAR(50)," + "diam_group INTEGER," + "diam_class CHAR(15))"; m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL); foreach (ProcessorScenarioItem.SpcGroupItem objSpcGroup in ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oSpcGroupItem_Collection) { foreach (ProcessorScenarioItem.TreeDiamGroupsItem objDiamGroup in ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oTreeDiamGroupsItem_Collection) { // INITIALIZE RECORDS IN WORK TABLE m_oAdo.m_strSQL = "INSERT INTO spcgrp_dbhgrp (species_group,species_label, diam_group, diam_class) " + "VALUES (" + objSpcGroup.SpeciesGroup + ",'" + objSpcGroup.SpeciesGroupLabel + "'," + objDiamGroup.DiamGroup + ",'" + objDiamGroup.DiamClass + "')"; m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL); // //INSERT SCENARIO RECORDS // m_oAdo.m_strSQL = "INSERT INTO scenario_tree_species_diam_dollar_values (scenario_id,species_group,diam_group) " + "VALUES ('" + ScenarioId.Trim() + "'," + objSpcGroup.SpeciesGroup + "," + objDiamGroup.DiamGroup + ")"; m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL); } } } else { oDataMgr = new SQLite.ADO.DataMgr(); string strScenarioDB = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\processor\\" + Tables.ProcessorScenarioRuleDefinitions.DefaultSqliteDbFile; oDataMgr.OpenConnection(oDataMgr.GetConnectionString(strScenarioDB)); if (oDataMgr.m_intError != 0) { m_intError = oDataMgr.m_intError; m_strError = oDataMgr.m_strError; oDataMgr = null; return; } m_intError = 0; m_strError = ""; // //DELETE THE CURRENT SCENARIO RECORDS // oDataMgr.m_strSQL = "DELETE FROM scenario_tree_species_diam_dollar_values " + "WHERE TRIM(scenario_id)='" + this.ScenarioId.Trim() + "'"; oDataMgr.SqlNonQuery(oDataMgr.m_Connection, oDataMgr.m_strSQL); // //DELETE THE WORK TABLE // if (oDataMgr.TableExist(oDataMgr.m_Connection, "spcgrp_dbhgrp")) { oDataMgr.SqlNonQuery(oDataMgr.m_Connection, "DROP TABLE spcgrp_dbhgrp"); } // //CREATE AND POPULATE WORK TABLE // oDataMgr.m_strSQL = "CREATE TABLE spcgrp_dbhgrp (" + "species_group INTEGER," + "species_label TEXT," + "diam_group INTEGER," + "diam_class TEXT)"; oDataMgr.SqlNonQuery(oDataMgr.m_Connection, oDataMgr.m_strSQL); foreach (ProcessorScenarioItem.SpcGroupItem objSpcGroup in ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oSpcGroupItem_Collection) { foreach (ProcessorScenarioItem.TreeDiamGroupsItem objDiamGroup in ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oTreeDiamGroupsItem_Collection) { // INITIALIZE RECORDS IN WORK TABLE oDataMgr.m_strSQL = "INSERT INTO spcgrp_dbhgrp (species_group,species_label, diam_group, diam_class) " + "VALUES (" + objSpcGroup.SpeciesGroup + ",'" + objSpcGroup.SpeciesGroupLabel + "'," + objDiamGroup.DiamGroup + ",'" + objDiamGroup.DiamClass + "')"; oDataMgr.SqlNonQuery(oDataMgr.m_Connection, oDataMgr.m_strSQL); // //INSERT SCENARIO RECORDS // oDataMgr.m_strSQL = "INSERT INTO scenario_tree_species_diam_dollar_values (scenario_id,species_group,diam_group) " + "VALUES ('" + ScenarioId.Trim() + "'," + objSpcGroup.SpeciesGroup + "," + objDiamGroup.DiamGroup + ")"; oDataMgr.SqlNonQuery(oDataMgr.m_Connection, oDataMgr.m_strSQL); } } } // //UPDATE SCENARIO RECORDS WITH MERCH AND CHIP VALUES // for (x = 0; x <= this.uc_processor_scenario_spc_dbh_group_value_collection1.Count - 1; x++) { strSpcGrp = uc_processor_scenario_spc_dbh_group_value_collection1.Item(x).SpeciesGroup.Trim(); strDbhGrp = uc_processor_scenario_spc_dbh_group_value_collection1.Item(x).DbhGroup.Trim(); strWoodBin = uc_processor_scenario_spc_dbh_group_value_collection1.Item(x).GetWoodBin(); strMerchValue = uc_processor_scenario_spc_dbh_group_value_collection1.Item(x).CubicFootDollarValue.Trim(); strMerchValue = strMerchValue.Replace("$", ""); strChipValue = this.txtChipValue.Text.Trim(); strChipValue = strChipValue.Replace("$", ""); if (!ReferenceProcessorScenarioForm.m_bUsingSqlite) { strSql = "UPDATE scenario_tree_species_diam_dollar_values a " + "INNER JOIN spcgrp_dbhgrp b " + "ON a.species_group=b.species_group AND " + "a.diam_group=b.diam_group " + "SET a.merch_value=" + strMerchValue + "," + "a.chip_value=" + strChipValue + ", " + "a.wood_bin='" + strWoodBin.Trim() + "' " + "WHERE TRIM(a.scenario_id)='" + ScenarioId.Trim() + "' AND " + "TRIM(b.species_label)='" + strSpcGrp + "' AND " + "TRIM(b.diam_class)='" + strDbhGrp + "'"; m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, strSql); } else { strSql = "UPDATE scenario_tree_species_diam_dollar_values " + "SET merch_value = " + strMerchValue + "," + "chip_value = " + strChipValue + "," + "wood_bin = '" + strWoodBin.Trim() + "' WHERE EXISTS (" + "SELECT * FROM spcgrp_dbhgrp " + "WHERE scenario_tree_species_diam_dollar_values.species_group = spcgrp_dbhgrp.species_group " + "AND scenario_tree_species_diam_dollar_values.diam_group = spcgrp_dbhgrp.diam_group " + "AND TRIM(scenario_tree_species_diam_dollar_values.scenario_id) = '" + ScenarioId.Trim() + "' " + "AND TRIM(spcgrp_dbhgrp.species_label) = '" + strSpcGrp + "' " + "AND TRIM(spcgrp_dbhgrp.diam_class) = '" + strDbhGrp + "')"; oDataMgr.SqlNonQuery(oDataMgr.m_Connection, strSql); } uc_processor_scenario_spc_dbh_group_value_collection1.Item(x).SaveValues(); } this.m_strChipValueSave = this.txtChipValue.Text; if (ReferenceProcessorScenarioForm.m_bUsingSqlite) { // //DELETE THE WORK TABLE AND CLOSE CONNECTION // if (oDataMgr.TableExist(oDataMgr.m_Connection, "spcgrp_dbhgrp")) { oDataMgr.SqlNonQuery(oDataMgr.m_Connection, "DROP TABLE spcgrp_dbhgrp"); } m_intError = oDataMgr.m_intError; oDataMgr.CloseConnection(oDataMgr.m_Connection); oDataMgr = null; } }
public void SaveScenarioProperties() { string strTemp1; string strTemp2; string strSQL = ""; bool bCore = false; string strDesc = ""; if (this.lstScenario.Visible == true) //new scenario { //validate the input //Optimization id if (this.txtScenarioId.Text.Length == 0) { MessageBox.Show("Enter A Unique Optimization scenario Id"); this.txtScenarioId.Focus(); return; } //check for duplicate scenario id if (this.lstScenario.Items.Count > 0) { strTemp2 = this.txtScenarioId.Text.Trim(); for (int x = 0; x <= this.lstScenario.Items.Count - 1; x++) { strTemp1 = this.lstScenario.Items[x].ToString().Trim(); if (strTemp1.ToUpper() == strTemp2.ToUpper()) { MessageBox.Show("Cannot have a duplicate Optimization scenario id"); this.txtScenarioId.Focus(); return; } } } //make sure user entered scenario path if (this.txtScenarioPath.Text.Length > 0) { //create the scenario path if it does not exist and //copy the scenario_results.mdb to it try { if (!System.IO.Directory.Exists(this.txtScenarioPath.Text)) { System.IO.Directory.CreateDirectory(this.txtScenarioPath.Text); System.IO.Directory.CreateDirectory(this.txtScenarioPath.Text.ToString() + "\\db"); //copy default scenario_results database to the new project directory string strSourceFile = ((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectDirectory + "\\" + ScenarioType + "\\db\\scenario_results.mdb"; string strDestFile = this.txtScenarioPath.Text + "\\db\\scenario_results.mdb"; System.IO.File.Copy(strSourceFile, strDestFile, true); } } catch { MessageBox.Show("Error Creating Folder"); return; } } else { MessageBox.Show("Enter A Directory Location To Save Optimization scenario Files"); this.txtScenarioPath.Focus(); return; } //copy the project data source values to the scenario data source ado_data_access p_ado = new ado_data_access(); string strProjDBDir = ((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectDirectory + "\\db"; string strProjFile = "project.mdb"; StringBuilder strProjFullPath = new StringBuilder(strProjDBDir); strProjFullPath.Append("\\"); strProjFullPath.Append(strProjFile); string strProjConn = p_ado.getMDBConnString(strProjFullPath.ToString(), "admin", ""); System.Data.OleDb.OleDbConnection p_OleDbProjConn = new System.Data.OleDb.OleDbConnection(); p_ado.OpenConnection(strProjConn, ref p_OleDbProjConn); string strScenarioDBDir = ((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectDirectory + "\\" + ScenarioType + "\\db"; string strScenarioFile = "scenario_" + ScenarioType + "_rule_definitions.mdb"; StringBuilder strScenarioFullPath = new StringBuilder(strScenarioDBDir); strScenarioFullPath.Append("\\"); strScenarioFullPath.Append(strScenarioFile); string strScenarioConn = p_ado.getMDBConnString(strScenarioFullPath.ToString(), "admin", ""); p_ado.OpenConnection(strScenarioConn); if (p_ado.m_intError == 0) { if (this.txtDescription.Text.Trim().Length > 0) { strDesc = p_ado.FixString(this.txtDescription.Text.Trim(), "'", "''"); } strSQL = "INSERT INTO scenario (scenario_id,description,Path,File) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," + "'" + strDesc + "'," + "'" + this.txtScenarioPath.Text.Trim() + "','scenario_" + ScenarioType + "_rule_definitions.mdb');"; //"'" + this.txtScenarioMDBFile.Text.Trim() + "');"; p_ado.SqlNonQuery(p_ado.m_OleDbConnection, strSQL); p_ado.SqlQueryReader(p_OleDbProjConn, "select * from datasource"); if (p_ado.m_intError == 0) { try { while (p_ado.m_OleDbDataReader.Read()) { bCore = false; switch (p_ado.m_OleDbDataReader["table_type"].ToString().Trim().ToUpper()) { case "PLOT": bCore = true; break; case "CONDITION": bCore = true; break; //case "FIRE AND FUEL EFFECTS": // bCore = true; // break; case "HARVEST COSTS": bCore = true; break; case "TREATMENT PRESCRIPTIONS": bCore = true; break; case "TREE VOLUMES AND VALUES BY SPECIES AND DIAMETER GROUPS": bCore = true; break; case "TRAVEL TIMES": bCore = true; break; case "PROCESSING SITES": bCore = true; break; case "TREE SPECIES AND DIAMETER GROUPS DOLLAR VALUES": bCore = true; break; case "PLOT AND CONDITION RECORD AUDIT": bCore = true; break; case "PLOT, CONDITION AND TREATMENT RECORD AUDIT": bCore = true; break; default: break; } if (bCore == true) { strSQL = "INSERT INTO scenario_datasource (scenario_id,table_type,Path,file,table_name) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," + "'" + p_ado.m_OleDbDataReader["table_type"].ToString().Trim() + "'," + "'" + p_ado.m_OleDbDataReader["path"].ToString().Trim() + "'," + "'" + p_ado.m_OleDbDataReader["file"].ToString().Trim() + "'," + "'" + p_ado.m_OleDbDataReader["table_name"].ToString().Trim() + "');"; p_ado.SqlNonQuery(p_ado.m_OleDbConnection, strSQL); } } } catch (Exception caught) { intError = -1; strError = caught.Message; MessageBox.Show(strError); } if (p_ado.m_intError == 0) { if (ScenarioType.Trim().ToUpper() == "OPTIMIZER") { ((frmOptimizerScenario)ParentForm).uc_datasource1.strScenarioId = this.txtScenarioId.Text.Trim(); ((frmOptimizerScenario)ParentForm).uc_datasource1.strDataSourceMDBFile = ((frmMain)ParentForm.ParentForm).frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\" + ScenarioType + "\\db\\scenario_" + ScenarioType + "_rule_definitions.mdb"; ((frmOptimizerScenario)ParentForm).uc_datasource1.strDataSourceTable = "scenario_datasource"; ((frmOptimizerScenario)ParentForm).uc_datasource1.strProjectDirectory = ((frmMain)ParentForm.ParentForm).frmProject.uc_project1.txtRootDirectory.Text.Trim(); } else { this.ReferenceProcessorScenarioForm.uc_datasource1.strScenarioId = this.txtScenarioId.Text.Trim(); this.ReferenceProcessorScenarioForm.uc_datasource1.strDataSourceMDBFile = ((frmMain)ParentForm.ParentForm).frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\" + ScenarioType + "\\db\\scenario_" + ScenarioType + "_rule_definitions.mdb"; this.ReferenceProcessorScenarioForm.uc_datasource1.strDataSourceTable = "scenario_datasource"; this.ReferenceProcessorScenarioForm.uc_datasource1.strProjectDirectory = ((frmMain)ParentForm.ParentForm).frmProject.uc_project1.txtRootDirectory.Text.Trim(); } } p_ado.m_OleDbDataReader.Close(); p_ado.m_OleDbDataReader = null; p_ado.m_OleDbCommand = null; p_OleDbProjConn.Close(); p_OleDbProjConn = null; } if (ScenarioType.Trim().ToUpper() == "OPTIMIZER") { string strTemp = p_ado.FixString("SELECT @@PlotTable@@.* FROM @@PlotTable@@ WHERE @@PlotTable@@.plot_accessible_yn='Y'", "'", "''"); strSQL = "INSERT INTO scenario_plot_filter (scenario_id,sql_command,current_yn) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," + "'" + strTemp + "'," + "'Y');"; p_ado.SqlNonQuery(p_ado.m_OleDbConnection, strSQL); strTemp = p_ado.FixString("SELECT @@CondTable@@.* FROM @@CondTable@@", "'", "''"); strSQL = "INSERT INTO scenario_cond_filter (scenario_id,sql_command,current_yn) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," + "'" + strTemp + "'," + "'Y');"; p_ado.SqlNonQuery(p_ado.m_OleDbConnection, strSQL); } } p_ado.m_OleDbConnection.Close(); p_ado.m_OleDbConnection = null; p_ado = null; this.btnCancel.Enabled = false; this.btnOpen.Enabled = true; this.lstScenario.Enabled = true; this.txtScenarioId.Visible = false; this.lblNewScenario.Visible = false; this.txtScenarioPath.Enabled = false; this.lstScenario.Items.Add(this.txtScenarioId.Text); this.lstScenario.SelectedIndex = this.lstScenario.Items.Count - 1; } else { ado_data_access p_ado = new ado_data_access(); System.Data.OleDb.OleDbConnection oConn = new System.Data.OleDb.OleDbConnection(); string strProjDir = ((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectDirectory; string strScenarioDir = ((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectDirectory + "\\" + ScenarioType + "\\db"; string strFile = "scenario_" + ScenarioType + "_rule_definitions.mdb"; if (ScenarioType.Trim().ToUpper() == "OPTIMIZER") { ((frmOptimizerScenario)ParentForm).uc_datasource1.strScenarioId = this.txtScenarioId.Text.Trim(); ((frmOptimizerScenario)ParentForm).uc_datasource1.strDataSourceMDBFile = strScenarioDir + "\\scenario_" + ScenarioType + "_rule_definitions.mdb"; ((frmOptimizerScenario)ParentForm).uc_datasource1.strDataSourceTable = "scenario_datasource"; ((frmOptimizerScenario)ParentForm).uc_datasource1.strProjectDirectory = strProjDir; } else { this.ReferenceProcessorScenarioForm.uc_datasource1.strScenarioId = this.txtScenarioId.Text.Trim(); this.ReferenceProcessorScenarioForm.uc_datasource1.strDataSourceMDBFile = strScenarioDir + "\\scenario_" + ScenarioType + "_rule_definitions.mdb"; this.ReferenceProcessorScenarioForm.uc_datasource1.strDataSourceTable = "scenario_datasource"; this.ReferenceProcessorScenarioForm.uc_datasource1.strProjectDirectory = strProjDir; } StringBuilder strFullPath = new StringBuilder(strScenarioDir); strFullPath.Append("\\"); strFullPath.Append(strFile); if (this.txtDescription.Text.Trim().Length > 0) { strDesc = p_ado.FixString(this.txtDescription.Text.Trim(), "'", "''"); } string strConn = p_ado.getMDBConnString(strFullPath.ToString(), "admin", ""); //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strFullPath.ToString() + ";User Id=admin;Password=;"; strSQL = "UPDATE scenario SET description = '" + strDesc + "' WHERE scenario_id = '" + this.txtScenarioId.Text.ToLower() + "';"; p_ado.SqlNonQuery(strConn, strSQL); p_ado = null; } if (ScenarioType.Trim().ToUpper() == "OPTIMIZER") { if (((frmOptimizerScenario)this.ParentForm).m_bScenarioOpen == false) { ((frmOptimizerScenario)this.ParentForm).Text = "Core Analysis: Optimization Scenario (" + this.txtScenarioId.Text.Trim() + ")"; ((frmOptimizerScenario)this.ParentForm).SetMenu("scenario"); ((frmOptimizerScenario)this.ParentForm).m_bScenarioOpen = true; this.lblTitle.Text = ""; this.Visible = false; } } else { if (this.ReferenceProcessorScenarioForm.m_bScenarioOpen == false) { this.ReferenceProcessorScenarioForm.Text = "Processor: Scenario (" + this.txtScenarioId.Text.Trim() + ")"; this.ReferenceProcessorScenarioForm.m_bScenarioOpen = true; this.lblTitle.Text = ""; this.Visible = false; } } }
public void loadgrid(bool p_bScenarioCopy) { string[] strDeleteSQL = new string[25]; string strSQL = ""; int intArrayCount; int x = 0; string strConn = ""; string strScenarioMDB = ""; ado_data_access p_ado = new ado_data_access(); this.strScenarioId = ReferenceOptimizerScenarioForm.uc_scenario1.txtScenarioId.Text.Trim().ToLower(); /*************************************************** **scenario mdb connection ***************************************************/ if (p_ado.m_intError != 0) { return; } p_ado.getScenarioConnStringAndMDBFile(ref strScenarioMDB, ref strConn, frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim()); this.m_OleDbConnectionScenario = new System.Data.OleDb.OleDbConnection(); p_ado.OpenConnection(strConn, ref this.m_OleDbConnectionScenario); /************************************************************************* **get the treatment prescription mdb file,table, and connection strings *************************************************************************/ p_ado.getScenarioDataSourceConnStringAndTable(ref this.strRxMDBFile, ref this.strRxPackageTableName, ref this.strRxConn, "Treatment Packages", this.strScenarioId, this.m_OleDbConnectionScenario); this.m_OleDbRxConn = new System.Data.OleDb.OleDbConnection(); p_ado.OpenConnection(this.strRxConn, ref this.m_OleDbRxConn); if (p_ado.m_intError != 0) { this.m_OleDbConnectionScenario.Close(); this.m_OleDbConnectionScenario = null; this.m_OleDbRxConn = null; return; } strSQL = "select * from " + this.strRxPackageTableName; p_ado.SqlQueryReader(this.m_OleDbRxConn, strSQL); /******************************************************************************** **insert records into the scenario_last_tiebreak_rank table from the master rxpackage table ********************************************************************************/ if (p_ado.m_intError == 0) { this.m_DataSet = new System.Data.DataSet(); this.m_OleDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter(); while (p_ado.m_OleDbDataReader.Read()) { strSQL = "select * from scenario_last_tiebreak_rank " + " where scenario_id = '" + this.strScenarioId + "' and " + "rxpackage = '" + p_ado.m_OleDbDataReader["rxpackage"].ToString() + "';"; this.m_OleDbCommand = this.m_OleDbConnectionScenario.CreateCommand(); this.m_OleDbCommand.CommandText = strSQL; this.m_OleDbDataAdapter.SelectCommand = this.m_OleDbCommand; this.m_OleDbDataAdapter.Fill(this.m_DataSet, "scenario_last_tiebreak_rank"); /******************************************************************************* **if the master treatment record is not found in the scenario db than insert it *******************************************************************************/ if (this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Rows.Count == 0) { strSQL = "INSERT INTO scenario_last_tiebreak_rank (scenario_id," + "rxpackage, last_tiebreak_rank) VALUES " + "('" + this.strScenarioId + "'," + "'" + p_ado.m_OleDbDataReader["rxpackage"].ToString() + "'," + "0);"; p_ado.SqlNonQuery(this.m_OleDbConnectionScenario, strSQL); } this.m_DataSet.Tables.Clear(); } p_ado.m_OleDbDataReader.Close(); this.m_DataSet.Dispose(); this.m_OleDbDataAdapter.Dispose(); intArrayCount = 0; /**************************************************************************************************** **delete any prescriptions from the scenario db that do not exist in the master ****************************************************************************************************/ strSQL = "select * from scenario_last_tiebreak_rank where scenario_id = '" + this.strScenarioId + "';"; p_ado.SqlQueryReader(this.m_OleDbConnectionScenario, strSQL); if (p_ado.m_intError == 0) { this.m_DataSet = new System.Data.DataSet(); this.m_OleDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter(); while (p_ado.m_OleDbDataReader.Read()) { /************************************************************************ **query the scenario treatment in the master db. If it is not found **in the master db then delete the scenario treatment record ************************************************************************/ strSQL = "select * from " + this.strRxPackageTableName + " where rxpackage = '" + p_ado.m_OleDbDataReader["rxpackage"].ToString() + "';"; this.m_OleDbCommand = this.m_OleDbRxConn.CreateCommand(); this.m_OleDbCommand.CommandText = strSQL; this.m_OleDbDataAdapter.SelectCommand = this.m_OleDbCommand; this.m_OleDbDataAdapter.Fill(this.m_DataSet, this.strRxPackageTableName); if (this.m_DataSet.Tables[this.strRxPackageTableName].Rows.Count == 0) { strDeleteSQL[intArrayCount] = "DELETE FROM scenario_last_tiebreak_rank" + " WHERE scenario_id = '" + this.strScenarioId + "'" + " AND rxpackage = '" + p_ado.m_OleDbDataReader["rxpackage"] + "';"; intArrayCount++; } this.m_DataSet.Tables.Clear(); } p_ado.m_OleDbDataReader.Close(); this.m_DataSet.Dispose(); this.m_OleDbDataAdapter.Dispose(); /********************************************************************************** **if there were any treatments that were loaded into sql delete **arrays then perform the sql to delete the treatments out of the table **********************************************************************************/ if (intArrayCount > 0) { for (x = 0; x <= intArrayCount - 1; x++) { p_ado.SqlNonQuery(this.m_OleDbConnectionScenario, strDeleteSQL[x].ToString()); } } } /*************************************************************************************** **okay, now that the table has been validated and updated lets load the grid to **display the treatments to the user ***************************************************************************************/ this.m_DataSet = new System.Data.DataSet(); this.m_OleDbDataAdapter = new System.Data.OleDb.OleDbDataAdapter(); this.m_OleDbCommand = this.m_OleDbRxConn.CreateCommand(); this.m_OleDbCommand.CommandText = "select * from " + this.strRxPackageTableName; this.m_OleDbDataAdapter.SelectCommand = this.m_OleDbCommand; this.m_OleDbDataAdapter.Fill(this.m_DataSet, this.strRxPackageTableName); this.m_OleDbCommand = this.m_OleDbConnectionScenario.CreateCommand(); strSQL = ""; for (x = 0; x <= this.m_DataSet.Tables[this.strRxPackageTableName].Rows.Count - 1; x++) { if (this.m_DataSet.Tables[this.strRxPackageTableName].Rows[x]["rxpackage"].ToString().Length > 0) { strSQL = "select scenario_id,rxpackage,last_tiebreak_rank from scenario_last_tiebreak_rank where scenario_id = '" + this.strScenarioId + "';"; break; } } /************************************************************ **if no records in the master prescription table then return ************************************************************/ if (strSQL.Length == 0) { this.m_DataSet.Clear(); this.m_DataSet.Dispose(); this.m_OleDbDataAdapter.Dispose(); this.m_OleDbCommand.Dispose(); this.m_OleDbConnectionScenario.Close(); this.m_OleDbConnectionScenario = null; this.m_OleDbRxConn.Close(); this.m_OleDbRxConn = null; return; } /******************************* **create the data sets *******************************/ this.m_OleDbCommand = this.m_OleDbConnectionScenario.CreateCommand(); this.m_OleDbCommand.CommandText = "select scenario_id,rxpackage,last_tiebreak_rank from scenario_last_tiebreak_rank where scenario_id = '" + this.strScenarioId + "';"; this.m_OleDbDataAdapter.SelectCommand = this.m_OleDbCommand; this.m_OleDbDataAdapter.Fill(this.m_DataSet, "scenario_last_tiebreak_rank"); /***************************************************************************** **add the description column to the scenario last tiebreak rank dataset *****************************************************************************/ this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Columns.Add("Description"); /******************************************************************************** **add the treatment description value to the scenrario rx last tiebreak rank data set. **the description is only in the master rx table and is added to the **scenario last tiebreak rank data set for information purposes. ********************************************************************************/ /*********************************************************************************** **for loop through the master db rx dataset adding the description field to the **scenenario db scenario_last_tiebreak_rank dataset ***********************************************************************************/ for (x = 0; x <= this.m_DataSet.Tables[this.strRxPackageTableName].Rows.Count - 1; x++) { if (this.m_DataSet.Tables[this.strRxPackageTableName].Rows[x]["rxpackage"].ToString().Length > 0) { /*************************************************************************************** **build the expression to filter only the scenario row that meets the expression ***************************************************************************************/ strSQL = "rxpackage = '" + this.m_DataSet.Tables[this.strRxPackageTableName].Rows[x]["rxpackage"] + "'"; /*************************************************************************************** **create a datarow that will hold the results from the query expression ***************************************************************************************/ System.Data.DataRow[] dr1; dr1 = this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Select(strSQL); /*************************************************************************************** **check to see if it found the master rx treatment in the sceanrio last tiebreak rank dataset ***************************************************************************************/ if (dr1.Length != 0) { /*************************************************************************************** **it found it, loop through the dataset and find the row that matches the row **returned from the search expression ***************************************************************************************/ for (int y = 0; y <= this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Rows.Count - 1; y++) { if (dr1[0]["rxpackage"] == this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Rows[y]["rxpackage"]) { /********************************************************************************** **update the description row/column with the master db rx table description value **********************************************************************************/ this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Rows[y]["description"] = this.m_DataSet.Tables[this.strRxPackageTableName].Rows[x]["description"]; break; } } this.m_DataSet.Tables["scenario_last_tiebreak_rank"].AcceptChanges(); } } } /************************************************************************************************** **place the dataset table into a view class so as to not allow new records to be appended **************************************************************************************************/ DataView firstView = new DataView(this.m_DataSet.Tables["scenario_last_tiebreak_rank"]); firstView.AllowNew = false; //cannot append new records firstView.AllowDelete = false; //cannot delete records /*************************************************************** **custom define the grid style ***************************************************************/ DataGridTableStyle tableStyle = new DataGridTableStyle(); /*********************************************************************** **map the data grid table style to the last tiebreak rank dataset ***********************************************************************/ tableStyle.MappingName = "scenario_last_tiebreak_rank"; tableStyle.AlternatingBackColor = frmMain.g_oGridViewAlternateRowBackgroundColor; tableStyle.BackColor = frmMain.g_oGridViewRowBackgroundColor; tableStyle.ForeColor = frmMain.g_oGridViewRowForegroundColor; tableStyle.SelectionBackColor = frmMain.g_oGridViewSelectedRowBackgroundColor; /****************************************************************************** **since the dataset has things like field name and number of columns, **we will use those to create new columnstyles for the columns in our grid ******************************************************************************/ //get the number of columns from the scenario_last_tiebreak_rank data set int numCols = this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Columns.Count; /*********************************************************************************** **assign the aColumnTextColumn as type DataGridColoredTextBoxColumn object class ***********************************************************************************/ DataGridColoredTextBoxColumn aColumnTextColumn; //loop through all the columns in the dataset for (int i = 0; i < numCols; ++i) { //create a new instance of the DataGridColoredTextBoxColumn class aColumnTextColumn = new DataGridColoredTextBoxColumn(); aColumnTextColumn.HeaderText = this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Columns[i].ColumnName; //all columns are read-only except the last_tiebreak_rank column if (aColumnTextColumn.HeaderText != "last_tiebreak_rank") { aColumnTextColumn.ReadOnly = true; } //assign the mappingname property the data sets column name aColumnTextColumn.MappingName = this.m_DataSet.Tables["scenario_last_tiebreak_rank"].Columns[i].ColumnName; //add the datagridcoloredtextboxcolumn object to the data grid table style object tableStyle.GridColumnStyles.Add(aColumnTextColumn); //set wider width for some columns switch (aColumnTextColumn.HeaderText) { case "scenario_id": aColumnTextColumn.Width = 150; break; case "Description": aColumnTextColumn.Width = 475; break; } } dataGrid1.BackgroundColor = frmMain.g_oGridViewBackgroundColor; dataGrid1.BackColor = frmMain.g_oGridViewRowBackgroundColor; if (frmMain.g_oGridViewFont != null) { dataGrid1.Font = frmMain.g_oGridViewFont; } // make the dataGrid use our new tablestyle and bind it to our table this.dataGrid1.TableStyles.Clear(); this.dataGrid1.TableStyles.Add(tableStyle); // If this is a copied scenario, we will have a reference form to get the values if (p_bScenarioCopy == true) { if (ReferenceOptimizerScenarioForm.m_oOptimizerScenarioItem_Collection.Item(0).m_oLastTieBreakRankItem_Collection != null) { OptimizerScenarioItem.LastTieBreakRankItem_Collection oLastTieBreakRankItem_Collection = ReferenceOptimizerScenarioForm.m_oOptimizerScenarioItem_Collection.Item(0).m_oLastTieBreakRankItem_Collection; for (int i = 0; i < firstView.Count - 1; ++i) { for (x = 0; x <= oLastTieBreakRankItem_Collection.Count - 1; x++) { if (oLastTieBreakRankItem_Collection.Item(x).RxPackage.Equals(firstView[i]["rxpackage"])) { firstView[i]["last_tiebreak_rank"] = oLastTieBreakRankItem_Collection.Item(x).LastTieBreakRank; } } } } } this.dataGrid1.DataSource = firstView; this.dataGrid1.Expand(-1); } }
public void saveTreeGroupings_FromProperties() { if (!ReferenceProcessorScenarioForm.m_bUsingSqlite) { ado_data_access _objAdo = new ado_data_access(); string strDbFile = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\processor\\" + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeDiamGroupsDbFile; _objAdo.OpenConnection(_objAdo.getMDBConnString(strDbFile, "", "")); if (_objAdo.m_intError == 0) { string strScenarioId = this.ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.ScenarioId; //delete the current records _objAdo.m_strSQL = "DELETE FROM " + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeDiamGroupsTableName + " WHERE TRIM(UCASE(scenario_id)) = '" + strScenarioId.Trim() + "'"; _objAdo.SqlNonQuery(_objAdo.m_OleDbConnection, _objAdo.m_strSQL); // saving tree diameter groups if (_objAdo.m_intError == 0) { string strMin; string strMax; string strDef; string strId; for (int x = 0; x <= ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oTreeDiamGroupsItem_Collection.Count - 1; x++) { FIA_Biosum_Manager.ProcessorScenarioItem.TreeDiamGroupsItem oItem = ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oTreeDiamGroupsItem_Collection.Item(x); strId = oItem.DiamGroup; strMin = oItem.MinDiam; strMax = oItem.MaxDiam; strDef = oItem.DiamClass; _objAdo.m_strSQL = "INSERT INTO " + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeDiamGroupsTableName + " " + "(diam_group,diam_class,min_diam,max_diam,scenario_id) VALUES " + "(" + strId + ",'" + strDef.Trim() + "'," + strMin + "," + strMax + ",'" + strScenarioId.Trim() + "');"; _objAdo.SqlNonQuery(_objAdo.m_OleDbConnection, _objAdo.m_strSQL); if (_objAdo.m_intError != 0) { break; } } } // saving tree species groups if (_objAdo.m_intError == 0) { string strCommonName; int intSpCd; int intSpcGrp; string strGrpLabel; int x; //delete all records from the tree species group table _objAdo.m_strSQL = "DELETE FROM " + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeSpeciesGroupsTableName + " WHERE TRIM(UCASE(scenario_id))='" + strScenarioId.Trim().ToUpper() + " '"; _objAdo.SqlNonQuery(_objAdo.m_OleDbConnection, _objAdo.m_strSQL); if (_objAdo.m_intError != 0) { return; } //delete all records from the tree species group list table _objAdo.m_strSQL = "DELETE FROM " + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeSpeciesGroupsListTableName + " WHERE TRIM(UCASE(scenario_id))='" + strScenarioId.Trim().ToUpper() + " '"; _objAdo.SqlNonQuery(_objAdo.m_OleDbConnection, _objAdo.m_strSQL); if (_objAdo.m_intError == 0) { for (x = 0; x <= this.ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oSpcGroupItem_Collection.Count - 1; x++) { FIA_Biosum_Manager.ProcessorScenarioItem.SpcGroupItem oItem = ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oSpcGroupItem_Collection.Item(x); intSpcGrp = oItem.SpeciesGroup; strGrpLabel = oItem.SpeciesGroupLabel; _objAdo.m_strSQL = "INSERT INTO " + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeSpeciesGroupsTableName + " " + "(SPECIES_GROUP,SPECIES_LABEL,SCENARIO_ID) VALUES " + "(" + Convert.ToString(intSpcGrp).Trim() + ",'" + strGrpLabel.Trim() + "','" + strScenarioId.Trim() + "');"; _objAdo.SqlNonQuery(_objAdo.m_OleDbConnection, _objAdo.m_strSQL); } } if (_objAdo.m_intError == 0) { for (x = 0; x <= this.ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oSpcGroupListItem_Collection.Count - 1; x++) { FIA_Biosum_Manager.ProcessorScenarioItem.SpcGroupListItem oItem = ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oSpcGroupListItem_Collection.Item(x); intSpcGrp = oItem.SpeciesGroup; strCommonName = oItem.CommonName; strCommonName = _objAdo.FixString(strCommonName.Trim(), "'", "''"); intSpCd = oItem.SpeciesCode; _objAdo.m_strSQL = "INSERT INTO " + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeSpeciesGroupsListTableName + " " + "(SPECIES_GROUP,common_name,SCENARIO_ID,SPCD) VALUES " + "(" + Convert.ToString(intSpcGrp).Trim() + ",'" + strCommonName + "','" + strScenarioId.Trim() + "', " + intSpCd + " );"; _objAdo.SqlNonQuery(_objAdo.m_OleDbConnection, _objAdo.m_strSQL); } } } ReferenceProcessorScenarioForm.m_bTreeGroupsCopied = false; } _objAdo.CloseConnection(_objAdo.m_OleDbConnection); _objAdo = null; } else { saveTreeGroupings_FromPropertiesSqlite(); } }
public void savevalues() { m_intError = 0; m_strError = ""; int x; string strValues = ""; string strFields = "scenario_id," + "EscalatorOperatingCosts_Cycle2," + "EscalatorOperatingCosts_Cycle3," + "EscalatorOperatingCosts_Cycle4," + "EscalatorMerchWoodRevenue_Cycle2," + "EscalatorMerchWoodRevenue_Cycle3," + "EscalatorMerchWoodRevenue_Cycle4," + "EscalatorEnergyWoodRevenue_Cycle2," + "EscalatorEnergyWoodRevenue_Cycle3," + "EscalatorEnergyWoodRevenue_Cycle4"; try { // //DELETE THE CURRENT SCENARIO RECORDS // m_oAdo.m_strSQL = "DELETE FROM scenario_cost_revenue_escalators " + "WHERE TRIM(scenario_id)='" + this.ScenarioId.Trim() + "'"; m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL); // //scenario id // strValues = "'" + ScenarioId.Trim() + "',"; // //Operating Cost Cycle1 // strValues = strValues + this.uc_processor_scenario_escalators_value1.Cycle1.Trim() + ","; // //Operating Cost Cycle2 // strValues = strValues + this.uc_processor_scenario_escalators_value1.Cycle2.Trim() + ","; // //Operating Cost Cycle3 // strValues = strValues + this.uc_processor_scenario_escalators_value1.Cycle3.Trim() + ","; // //Merch Wood Revenue Cycle1 // strValues = strValues + this.uc_processor_scenario_escalators_value2.Cycle1.Trim() + ","; // //Merch Wood Revenue Cycle2 // strValues = strValues + this.uc_processor_scenario_escalators_value2.Cycle2.Trim() + ","; // //Merch Wood Revenue Cycle3 // strValues = strValues + this.uc_processor_scenario_escalators_value2.Cycle3.Trim() + ","; // //Energy Wood Revenue Cycle1 // strValues = strValues + this.uc_processor_scenario_escalators_value3.Cycle1.Trim() + ","; // //Energy Wood Revenue Cycle2 // strValues = strValues + this.uc_processor_scenario_escalators_value3.Cycle2.Trim() + ","; // //Energy Wood Revenue Cycle3 // strValues = strValues + this.uc_processor_scenario_escalators_value3.Cycle3.Trim(); m_oAdo.m_strSQL = Queries.GetInsertSQL(strFields, strValues, "scenario_cost_revenue_escalators"); m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL); this.uc_processor_scenario_escalators_value1.SaveValues(); this.uc_processor_scenario_escalators_value2.SaveValues(); this.uc_processor_scenario_escalators_value3.SaveValues(); } catch (Exception e) { m_intError = -1; m_strError = e.Message; } }
public void savevalues() { val_data(); if (this.m_intError == 0) { //string strSQL; int x; string strMin; string strMax; string strDef; string strId; if (!ReferenceProcessorScenarioForm.m_bUsingSqlite) { // //OPEN CONNECTION TO DB FILE CONTAINING PROCESSOR SCENARIO TABLES // //scenario mdb connection string strScenarioMDB = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\processor\\db\\scenario_processor_rule_definitions.mdb"; ado_data_access oAdo = new ado_data_access(); oAdo.OpenConnection(oAdo.getMDBConnString(strScenarioMDB, "", "")); if (oAdo.m_intError != 0) { m_intError = m_ado.m_intError; m_strError = m_ado.m_strError; oAdo = null; return; } if (this.m_intError == 0) { //delete the current records oAdo.m_strSQL = "DELETE FROM " + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeDiamGroupsTableName + " WHERE TRIM(UCASE(scenario_id)) = '" + ScenarioId.Trim().ToUpper() + "'"; oAdo.SqlNonQuery(oAdo.m_OleDbConnection, oAdo.m_strSQL); if (oAdo.m_intError == 0) { for (x = 0; x <= this.lstTreeDiam.Items.Count - 1; x++) { strId = this.lstTreeDiam.Items[x].Text; strMin = this.lstTreeDiam.Items[x].SubItems[1].Text; strMax = this.lstTreeDiam.Items[x].SubItems[2].Text; strDef = this.lstTreeDiam.Items[x].SubItems[3].Text; oAdo.m_strSQL = "INSERT INTO " + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeDiamGroupsTableName + " " + "(diam_group,diam_class,min_diam,max_diam,scenario_id) VALUES " + "(" + strId + ",'" + strDef.Trim() + "'," + strMin + "," + strMax + ",'" + ScenarioId + "');"; oAdo.SqlNonQuery(oAdo.m_OleDbConnection, oAdo.m_strSQL); if (oAdo.m_intError != 0) { break; } } } } oAdo.CloseConnection(oAdo.m_OleDbConnection); this.m_intError = oAdo.m_intError; oAdo = null; } else { string strScenarioDB = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\processor\\" + Tables.ProcessorScenarioRuleDefinitions.DefaultSqliteDbFile; SQLite.ADO.DataMgr oDataMgr = new SQLite.ADO.DataMgr(); using (System.Data.SQLite.SQLiteConnection conn = new System.Data.SQLite.SQLiteConnection(oDataMgr.GetConnectionString(strScenarioDB))) { conn.Open(); if (oDataMgr.m_intError != 0) { m_intError = oDataMgr.m_intError; m_strError = oDataMgr.m_strError; oDataMgr = null; return; } if (this.m_intError == 0) { //delete the current records oDataMgr.m_strSQL = "DELETE FROM " + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeDiamGroupsTableName + " WHERE TRIM(UPPER(scenario_id)) = '" + ScenarioId.Trim().ToUpper() + "'"; oDataMgr.SqlNonQuery(conn, oDataMgr.m_strSQL); if (oDataMgr.m_intError == 0) { for (x = 0; x <= this.lstTreeDiam.Items.Count - 1; x++) { strId = this.lstTreeDiam.Items[x].Text; strMin = this.lstTreeDiam.Items[x].SubItems[1].Text; strMax = this.lstTreeDiam.Items[x].SubItems[2].Text; strDef = this.lstTreeDiam.Items[x].SubItems[3].Text; oDataMgr.m_strSQL = "INSERT INTO " + Tables.ProcessorScenarioRuleDefinitions.DefaultTreeDiamGroupsTableName + " " + "(diam_group,diam_class,min_diam,max_diam,scenario_id) VALUES " + "(" + strId + ",'" + strDef.Trim() + "'," + strMin + "," + strMax + ",'" + ScenarioId + "');"; oDataMgr.SqlNonQuery(conn, oDataMgr.m_strSQL); if (oDataMgr.m_intError != 0) { break; } } this.m_intError = oDataMgr.m_intError; oDataMgr = null; } } } } if (this.m_intError == 0) { this.btnSave.Enabled = false; } } }
public void DeleteRecords() { int row = 0; int col = 0; bool lprompt = false; DialogResult result = DialogResult.None; ado_data_access p_ado; p_ado = new ado_data_access(); //check to see if there are any records to delete for (row = 0; row <= this.listView1.Items.Count - 1; row++) { if (this.listView1.Items[row].Text == "*") { if (lprompt == false) { result = MessageBox.Show("Permenently delete those items marked for deletion? Y/N", "SQL Expressions", MessageBoxButtons.YesNo, MessageBoxIcon.Question); } if (result == DialogResult.Yes || lprompt == true) { if (lprompt == false) { p_ado.OpenConnection(this.m_strCurrentConnection); if (p_ado.m_intError != 0) { p_ado = null; return; } lprompt = true; } System.Text.StringBuilder sb = new System.Text.StringBuilder(); string strSQL = "DELETE * FROM " + this.m_strTable + " WHERE "; sb.Append(strSQL); for (col = 1; col <= this.listView1.Columns.Count - 1; col++) { if (this.m_strFieldTypeAString_YN[col - 1] == "Y") { if (this.listView1.Items[row].SubItems[col].Text.Trim().Length == 0) { strSQL = "(" + this.listView1.Columns[col].Text.Trim() + " IS NULL OR LEN(TRIM(" + this.listView1.Columns[col].Text.Trim() + ")) = 0)"; //if (strSQL.IndexOf("'",0) > 0) strSQL=p_ado.FixString(strSQL); sb.Append(strSQL); } else { strSQL = "TRIM(UCASE(" + this.listView1.Columns[col].Text.Trim() + "))="; sb.Append(strSQL); sb.Append("'"); if (this.listView1.Items[row].SubItems[col].Text.IndexOf("'", 0) > 0) { strSQL = p_ado.FixString(this.listView1.Items[row].SubItems[col].Text, "'", "''"); sb.Append(strSQL); } else { strSQL = this.listView1.Items[row].SubItems[col].Text.Trim().ToUpper(); sb.Append(strSQL); } sb.Append("'"); } } else { if (this.listView1.Items[row].SubItems[col].Text.Trim().Length == 0) { strSQL = this.listView1.Columns[col].Text.Trim() + " IS NULL"; sb.Append(strSQL); } else { strSQL = this.listView1.Columns[col].Text.Trim() + "="; sb.Append(strSQL); strSQL = this.listView1.Items[row].SubItems[col].Text.Trim(); sb.Append(strSQL); } } if (col < this.listView1.Columns.Count - 1) { strSQL = " AND "; sb.Append(strSQL); } else { strSQL = ";"; sb.Append(strSQL); } } if (lprompt == true) { p_ado.SqlNonQuery(p_ado.m_OleDbConnection, sb.ToString()); } sb = null; } } } if (lprompt == true) { p_ado.m_OleDbConnection.Close(); p_ado.m_OleDbConnection = null; } p_ado = null; }
public void create_travel_times() { int intRandomNumber; string strRandomNumber; string strRandomSave; string strMDBFile; string strConn; string strSQL; //string strPsiteRailTravel_YN="N"; int x; int y; System.Data.OleDb.OleDbConnection p_conn; p_conn = new System.Data.OleDb.OleDbConnection(); ado_data_access p_ado = new ado_data_access(); utils p_utils = new utils(); strMDBFile = this.m_frmMain.frmProject.uc_project1.txtRootDirectory.Text + "\\gis\\db\\gis_travel_times.mdb"; strConn = p_ado.getMDBConnString(strMDBFile, "admin", ""); p_ado.OpenConnection(strConn, ref p_conn); p_ado.SqlNonQuery(p_conn, "delete from travel_time"); if (p_ado.m_intError == 0) { p_ado.CreateDataSet(p_conn, "select * from plot", "plot"); if (p_ado.m_intError == 0) { p_ado.AddSQLQueryToDataSet(p_conn, ref p_ado.m_OleDbDataAdapter, ref p_ado.m_DataSet, "select * from processing_site where ucase(name) like 'TEST%'", "processing_site"); if (p_ado.m_intError == 0) { this.m_frmTherm = new frmTherm(); this.m_frmTherm.AbortProcess = false; this.m_frmTherm.progressBar1.Minimum = 1; this.m_frmTherm.progressBar1.Maximum = p_ado.m_DataSet.Tables["plot"].Rows.Count; m_frmTherm.btnCancel.Visible = true; m_frmTherm.Show(); m_frmTherm.Focus(); m_frmTherm.Text = "Generating Random Travel Times"; m_frmTherm.Refresh(); this.m_frmTherm.btnCancel.Click += new System.EventHandler(this.ThermCancel); for (x = 0; x <= p_ado.m_DataSet.Tables["plot"].Rows.Count - 1; x++) { this.m_frmTherm.Increment(x + 1); strRandomSave = ""; for (y = 0; y <= p_ado.m_DataSet.Tables["processing_site"].Rows.Count - 1; y++) { for (;;) { intRandomNumber = p_utils.RandomNumber(1, 18); strRandomNumber = Convert.ToString(intRandomNumber); intRandomNumber = p_utils.RandomNumber(0, 9); strRandomNumber += "." + Convert.ToString(intRandomNumber); intRandomNumber = p_utils.RandomNumber(0, 9); strRandomNumber += Convert.ToString(intRandomNumber); if (strRandomNumber != strRandomSave) { strRandomSave = strRandomNumber; break; } } strSQL = "insert into travel_time (psite_id,biosum_plot_id,travel_mode,travel_time) VALUES "; strSQL += "(" + p_ado.m_DataSet.Tables["processing_site"].Rows[y]["psite_id"].ToString() + ",'" + p_ado.m_DataSet.Tables["plot"].Rows[x]["biosum_plot_id"].ToString() + "',1" + "," + strRandomNumber + ");"; p_ado.SqlNonQuery(p_conn, strSQL); } System.Windows.Forms.Application.DoEvents(); if (this.m_frmTherm.AbortProcess == true) { break; } } int intStart = 1; this.m_frmTherm.progressBar1.Maximum = p_ado.m_DataSet.Tables["processing_site"].Rows.Count; strRandomSave = ""; for (x = 0; x <= p_ado.m_DataSet.Tables["processing_site"].Rows.Count - 1; x++) { this.m_frmTherm.Increment(x + 1); for (y = intStart; y <= p_ado.m_DataSet.Tables["processing_site"].Rows.Count - 1; y++) { strSQL = ""; if (p_ado.m_DataSet.Tables["processing_site"].Rows[y]["trancd"].ToString().Trim() == "2") { strSQL = "insert into travel_time (psite_id,railhead_id,travel_mode,travel_time) values "; } else if (p_ado.m_DataSet.Tables["processing_site"].Rows[y]["trancd"].ToString().Trim() == "3") { strSQL = "insert into travel_time (psite_id,collector_id,travel_mode,travel_time) values "; } if (strSQL.Trim().Length > 0) { for (;;) { intRandomNumber = p_utils.RandomNumber(1, 18); strRandomNumber = Convert.ToString(intRandomNumber); intRandomNumber = p_utils.RandomNumber(0, 9); strRandomNumber += "." + Convert.ToString(intRandomNumber); intRandomNumber = p_utils.RandomNumber(0, 9); strRandomNumber += Convert.ToString(intRandomNumber); if (strRandomNumber != strRandomSave) { strRandomSave = strRandomNumber; break; } } strSQL += "(" + p_ado.m_DataSet.Tables["processing_site"].Rows[x]["psite_id"].ToString() + "," + p_ado.m_DataSet.Tables["processing_site"].Rows[y]["psite_id"].ToString() + ",2" + "," + strRandomNumber + ");"; p_ado.SqlNonQuery(p_conn, strSQL); if (p_ado.m_intError != 0) { break; } } } if (p_ado.m_intError != 0) { break; } intStart++; System.Windows.Forms.Application.DoEvents(); if (this.m_frmTherm.AbortProcess == true) { break; } } if (p_ado.m_intError != 0) { } else { MessageBox.Show("Finished Generating Travel Times"); } this.m_frmTherm.Close(); this.m_frmTherm = null; } p_ado.m_DataSet.Clear(); p_ado.m_DataSet = null; p_ado.m_OleDbDataAdapter.Dispose(); p_ado.m_OleDbDataAdapter = null; p_conn.Close(); p_ado.m_OleDbConnection = null; } } p_conn = null; p_ado = null; p_utils = null; }
public void savevalues() { // //OPEN CONNECTION TO DB FILE CONTAINING PROCESSOR SCENARIO TABLES // //scenario mdb connection ado_data_access oAdo = new ado_data_access(); string strScenarioMDB = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\processor\\db\\scenario_processor_rule_definitions.mdb"; oAdo.OpenConnection(oAdo.getMDBConnString(strScenarioMDB, "", "")); if (oAdo.m_intError != 0) { m_intError = oAdo.m_intError; m_strError = oAdo.m_strError; oAdo = null; return; } m_intError = 0; m_strError = ""; string strFields = "scenario_id,yard_dist_threshold,assumed_harvest_area_ac," + "move_in_time_multiplier," + "move_in_hours_addend"; string strValues = ""; oAdo.m_strSQL = "DELETE FROM " + Tables.ProcessorScenarioRuleDefinitions.DefaultMoveInCostsTableName + " " + "WHERE TRIM(UCASE(scenario_id)) = '" + ScenarioId.Trim().ToUpper() + "'"; oAdo.SqlNonQuery(oAdo.m_OleDbConnection, oAdo.m_strSQL); // //SCENARIOID // strValues = "'" + ScenarioId + "',"; // //YARDING DISTANCE THRESHOLD // if (this.txtYardDistThreshold.Text.Trim().Length > 0) { strValues = strValues + this.txtYardDistThreshold.Text.Trim() + ","; } else { strValues = strValues + "null,"; } // //ASSUMED HARVEST AREA // if (this.txtAssumedHarvestArea.Text.Trim().Length > 0) { strValues = strValues + this.txtAssumedHarvestArea.Text.Trim() + ","; } else { strValues = strValues + "null,"; } // //MOVE IN TIME MULTIPLIER // if (this.txtMoveInTimeMultiplier.Text.Trim().Length > 0) { strValues = strValues + this.txtMoveInTimeMultiplier.Text.Trim() + ","; } else { strValues = strValues + "null,"; } // //MOVE IN TIME ADDEND // if (this.txtMoveInAddend.Text.Trim().Length > 0) { strValues = strValues + this.txtMoveInAddend.Text.Trim(); } else { strValues = strValues + "null"; } // oAdo.m_strSQL = Queries.GetInsertSQL(strFields, strValues, Tables.ProcessorScenarioRuleDefinitions.DefaultMoveInCostsTableName); oAdo.SqlNonQuery(oAdo.m_OleDbConnection, oAdo.m_strSQL); m_intError = oAdo.m_intError; oAdo.CloseConnection(oAdo.m_OleDbConnection); oAdo = null; }
public int savevalues() { int x = 0; //string str=""; string strSQL = ""; string strConn = ""; string strRevPerGreenTon = ""; string strHaulCost; string strRailHaulCost; string strRailBioTransferCost; string strRailMerchTransferCost; //ldp strRevPerGreenTon = this.txtRevPerGreenTon_subclass.Text.Replace("$",""); //ldp strRevPerGreenTon = strRevPerGreenTon.Replace(",",""); //ldp if (strRevPerGreenTon.Trim().Length == 1) strRevPerGreenTon = "0.00"; //strHaulCost = this.txtHaulCost_subclass.Text.Replace("$",""); strHaulCost = RoadHaulCostDollarsPerGreenTonPerHour.Replace("$", ""); strHaulCost = strHaulCost.Replace(",", ""); if (strHaulCost.Trim().Length == 1) { strHaulCost = "0.00"; } //ldp strRailHaulCost = this.txtRailHaulCost_subclass.Text.Replace("$",""); strRailHaulCost = RailHaulCostDollarsPerGreenTonPerMile.Replace("$", ""); strRailHaulCost = strRailHaulCost.Replace(",", ""); if (strRailHaulCost.Trim().Length == 1) { strRailHaulCost = "0.00"; } //ldp strRailBioTransferCost = this.txtRailChipTransfer_subclass.Text.Replace("$",""); strRailBioTransferCost = RailChipTransferCostDollarsPerGreenTonPerHour.Replace("$", ""); strRailBioTransferCost = strRailBioTransferCost.Replace(",", ""); if (strRailBioTransferCost.Trim().Length == 1) { strRailBioTransferCost = "0.00"; } //ldp strRailMerchTransferCost = this.txtRailMerchTransfer_subclass.Text.Replace("$",""); strRailMerchTransferCost = RailMerchTransferCostDollarsPerGreenTonPerHour.Replace("$", ""); strRailMerchTransferCost = strRailMerchTransferCost.Replace(",", ""); if (strRailMerchTransferCost.Trim().Length == 1) { strRailMerchTransferCost = "0.00"; } ado_data_access p_ado = new ado_data_access(); string strScenarioId = this.ReferenceOptimizerScenarioForm.uc_scenario1.txtScenarioId.Text.Trim().ToLower(); string strScenarioMDB = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\" + Tables.OptimizerScenarioRuleDefinitions.DefaultScenarioTableDbFile; this.m_OleDbConnectionScenario = new System.Data.OleDb.OleDbConnection(); strConn = p_ado.getMDBConnString(strScenarioMDB, "admin", ""); //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strScenarioMDB + ";User Id=admin;Password=;"; p_ado.OpenConnection(strConn, ref this.m_OleDbConnectionScenario); if (p_ado.m_intError != 0) { x = p_ado.m_intError; p_ado = null; return(x); } //delete all records from the scenario wind speed class table strSQL = "DELETE FROM scenario_costs WHERE " + " scenario_id = '" + strScenarioId + "';"; p_ado.SqlNonQuery(this.m_OleDbConnectionScenario, strSQL); if (p_ado.m_intError < 0) { this.m_OleDbConnectionScenario.Close(); x = p_ado.m_intError; p_ado = null; return(x); } strSQL = "INSERT INTO scenario_costs (scenario_id,road_haul_cost_pgt_per_hour,rail_haul_cost_pgt_per_mile,rail_chip_transfer_pgt_per_hour,rail_merch_transfer_pgt_per_hour)" + " VALUES ('" + strScenarioId + "'," + strHaulCost + "," + strRailHaulCost + "," + strRailBioTransferCost + "," + strRailMerchTransferCost + ");"; p_ado.SqlNonQuery(this.m_OleDbConnectionScenario, strSQL); this.m_OleDbConnectionScenario.Close(); p_ado = null; return(0); }
public void savevalues() { int x; string strSpcGrp = ""; string strDbhGrp = ""; string strMerchValue = ""; string strChipValue = ""; string strWoodBin = ""; string strFields = "scenario_id,species_group,diam_group,wood_bin,merch_value,chip_value"; string strValues = ""; // //DELETE THE CURRENT SCENARIO RECORDS // m_oAdo.m_strSQL = "DELETE FROM scenario_tree_species_diam_dollar_values " + "WHERE TRIM(scenario_id)='" + this.ScenarioId.Trim() + "'"; m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL); // //DELETE THE WORK TABLE // if (m_oAdo.TableExist(m_oAdo.m_OleDbConnection, "spcgrp_dbhgrp")) { m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, "DROP TABLE spcgrp_dbhgrp"); } // //CREATE AND POPULATE WORK TABLE // m_oAdo.m_strSQL = "CREATE TABLE spcgrp_dbhgrp (" + "species_group INTEGER," + "species_label CHAR(50)," + "diam_group INTEGER," + "diam_class CHAR(15))"; m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL); foreach (ProcessorScenarioItem.SpcGroupItem objSpcGroup in ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oSpcGroupItem_Collection) { foreach (ProcessorScenarioItem.TreeDiamGroupsItem objDiamGroup in ReferenceProcessorScenarioForm.m_oProcessorScenarioItem.m_oTreeDiamGroupsItem_Collection) { // INITIALIZE RECORDS IN WORK TABLE m_oAdo.m_strSQL = "INSERT INTO spcgrp_dbhgrp (species_group,species_label, diam_group, diam_class) " + "VALUES (" + objSpcGroup.SpeciesGroup + ",'" + objSpcGroup.SpeciesGroupLabel + "'," + objDiamGroup.DiamGroup + ",'" + objDiamGroup.DiamClass + "')"; m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL); // //INSERT SCENARIO RECORDS // m_oAdo.m_strSQL = "INSERT INTO scenario_tree_species_diam_dollar_values (scenario_id,species_group,diam_group) " + "VALUES ('" + ScenarioId.Trim() + "'," + objSpcGroup.SpeciesGroup + "," + objDiamGroup.DiamGroup + ")"; m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL); } } // //UPDATE SCENARIO RECORDS WITH MERCH AND CHIP VALUES // for (x = 0; x <= this.uc_processor_scenario_spc_dbh_group_value_collection1.Count - 1; x++) { strValues = ""; strSpcGrp = uc_processor_scenario_spc_dbh_group_value_collection1.Item(x).SpeciesGroup.Trim(); strDbhGrp = uc_processor_scenario_spc_dbh_group_value_collection1.Item(x).DbhGroup.Trim(); strWoodBin = uc_processor_scenario_spc_dbh_group_value_collection1.Item(x).GetWoodBin(); strMerchValue = uc_processor_scenario_spc_dbh_group_value_collection1.Item(x).CubicFootDollarValue.Trim(); strMerchValue = strMerchValue.Replace("$", ""); strChipValue = this.txtChipValue.Text.Trim(); strChipValue = strChipValue.Replace("$", ""); m_oAdo.m_strSQL = "UPDATE scenario_tree_species_diam_dollar_values a " + "INNER JOIN spcgrp_dbhgrp b " + "ON a.species_group=b.species_group AND " + "a.diam_group=b.diam_group " + "SET a.merch_value=" + strMerchValue + "," + "a.chip_value=" + strChipValue + ", " + "a.wood_bin='" + strWoodBin.Trim() + "' " + "WHERE TRIM(a.scenario_id)='" + ScenarioId.Trim() + "' AND " + "TRIM(b.species_label)='" + strSpcGrp + "' AND " + "TRIM(b.diam_class)='" + strDbhGrp + "'"; m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL); uc_processor_scenario_spc_dbh_group_value_collection1.Item(x).SaveValues(); } this.m_strChipValueSave = this.txtChipValue.Text; }
/// <summary> /// Delete all plot records and the plot's tree and condition records /// </summary> private void DeleteAllPlotRecords() { string strMsg = "All condition and tree records for the plots deleted will also be deleted. \n Are you sure you want to remove all plot data from the project?"; DialogResult result = MessageBox.Show(strMsg, "Delete All Plot Data", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question); switch (result) { case DialogResult.Yes: break; case DialogResult.No: return; case DialogResult.Cancel: return; } frmMain.g_oFrmMain.ActivateStandByAnimation(frmMain.g_oFrmMain.WindowState, this.Left, this.Height, this.Width, this.Top); //instantiate the ado data access class FIA_Biosum_Manager.ado_data_access p_ado = new ado_data_access(); //instantiate the datasource class FIA_Biosum_Manager.Datasource p_datasource = new Datasource(((frmDialog)this.ParentForm).m_frmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim()); string strSQL = ""; /******************************************************************** **have the data source class create an mdb file in the user's temp **directory, create links to the plot,tree, and cond table and **return the name of the mdb path and file ********************************************************************/ string strMDBFile = p_datasource.CreateMDBAndTableDataSourceLinks(); //have the ado data access class return the connection string string strConn = p_ado.getMDBConnString(strMDBFile, "", ""); //get the location in the array for each of the table type information int intTree = p_datasource.getValidTableNameRow("TREE"); int intPlot = p_datasource.getValidTableNameRow("PLOT"); int intCond = p_datasource.getValidTableNameRow("CONDITION"); int intPpsa = p_datasource.getValidTableNameRow("POPULATION PLOT STRATUM ASSIGNMENT"); int intTreeRegionalBiomass = p_datasource.getValidTableNameRow("TREE REGIONAL BIOMASS"); int intPopEval = p_datasource.getValidTableNameRow("POPULATION EVALUATION"); int intPopStratum = p_datasource.getValidTableNameRow("POPULATION STRATUM"); int intPopEstUnit = p_datasource.getValidTableNameRow("POPULATION ESTIMATION UNIT"); int intSiteTree = p_datasource.getValidTableNameRow("SITE TREE"); //check to see if we found all the table information if (intTree >= 0 && intPlot >= 0 && intCond >= 0) { //check if we have tree regional biomass records if (p_datasource.m_strDataSource[intTreeRegionalBiomass, RECORDCOUNT].Trim() != "0") { //delete the tree regional records that are related to a plot strSQL = "DELETE FROM " + p_datasource.m_strDataSource[intTreeRegionalBiomass, TABLE]; // + " tr " + // "WHERE EXISTS (SELECT * FROM " + p_datasource.m_strDataSource[intCond,TABLE] + " c, " + // p_datasource.m_strDataSource[intPlot,TABLE] + " p, " + // p_datasource.m_strDataSource[intTree,TABLE] + " t " + // "WHERE c.biosum_plot_id=p.biosum_plot_id AND " + // "t.biosum_cond_id=c.biosum_cond_id AND " + // "tr.tre_cn = t.cn);"; p_ado.SqlNonQuery(strConn, strSQL); } //check if we have site tree records if (p_datasource.m_strDataSource[intSiteTree, RECORDCOUNT].Trim() != "0") { strSQL = "DELETE FROM " + p_datasource.m_strDataSource[intSiteTree, TABLE]; p_ado.SqlNonQuery(strConn, strSQL); } //check if we have tree records if (p_datasource.m_strDataSource[intTree, RECORDCOUNT].Trim() != "0") { //delete the tree records that are related to a plot strSQL = "DELETE FROM " + p_datasource.m_strDataSource[intTree, TABLE]; // + " t " + // "WHERE EXISTS (SELECT * FROM " + p_datasource.m_strDataSource[intCond,TABLE] + " c " + // " INNER JOIN " + p_datasource.m_strDataSource[intPlot,TABLE] + " p " + // "ON c.biosum_plot_id = p.biosum_plot_id " + // " WHERE t.biosum_cond_id = c.biosum_cond_id);"; p_ado.SqlNonQuery(strConn, strSQL); } //check if we have ppsa records if (p_ado.m_intError == 0 && p_datasource.m_strDataSource[intPpsa, RECORDCOUNT].Trim() != "0") { //delete the ppsa records strSQL = "DELETE FROM " + p_datasource.m_strDataSource[intPpsa, TABLE]; p_ado.SqlNonQuery(strConn, strSQL); } //check if we have cond records if (p_ado.m_intError == 0 && p_datasource.m_strDataSource[intCond, RECORDCOUNT].Trim() != "0") { //delete the cond records that are related to a plot strSQL = "DELETE FROM " + p_datasource.m_strDataSource[intCond, TABLE]; // + " c " + // "WHERE EXISTS (SELECT * FROM " + p_datasource.m_strDataSource[intPlot,TABLE] + " p " + // " WHERE c.biosum_plot_id = p.biosum_plot_id);"; p_ado.SqlNonQuery(strConn, strSQL); } //check if we have ploteval records if (p_ado.m_intError == 0 && p_datasource.m_strDataSource[intPopEval, RECORDCOUNT].Trim() != "0") { //delete all the ploteval records strSQL = "DELETE FROM " + p_datasource.m_strDataSource[intPopEval, TABLE]; p_ado.SqlNonQuery(strConn, strSQL); } //check if we have pop est unit records if (p_ado.m_intError == 0 && p_datasource.m_strDataSource[intPopEstUnit, RECORDCOUNT].Trim() != "0") { //delete all the ploteval records strSQL = "DELETE FROM " + p_datasource.m_strDataSource[intPopEstUnit, TABLE]; p_ado.SqlNonQuery(strConn, strSQL); } //check if we have pop est unit records if (p_ado.m_intError == 0 && p_datasource.m_strDataSource[intPopStratum, RECORDCOUNT].Trim() != "0") { //delete all the ploteval records strSQL = "DELETE FROM " + p_datasource.m_strDataSource[intPopStratum, TABLE]; p_ado.SqlNonQuery(strConn, strSQL); } //check if we have plot records if (p_ado.m_intError == 0 && p_datasource.m_strDataSource[intPlot, RECORDCOUNT].Trim() != "0") { //delete all the plot records strSQL = "DELETE FROM " + p_datasource.m_strDataSource[intPlot, TABLE]; p_ado.SqlNonQuery(strConn, strSQL); } frmMain.g_oFrmMain.DeactivateStandByAnimation(); MessageBox.Show("Done", "DELETE ALL PLOT DATA", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.None); } else { frmMain.g_oFrmMain.DeactivateStandByAnimation(); strSQL = "Delete Failed - Could not locate these designated tables:\n"; if (intPlot == -1) { strSQL += "Plot table\n"; } if (intCond == -1) { strSQL += "Cond table\n"; } if (intTree == -1) { strSQL += "Tree table\n"; } MessageBox.Show(strSQL, "DELETE ALL PLOT DATA", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Exclamation); } }
private void SetConditionLookupAndIDStrings() { string[] condCNs = m_strCondCNs.Split(','); setBiosumCondCNs = new HashSet <string>(); setBiosumCondIds = new HashSet <string>(); setBiosumPlotIds = new HashSet <string>(); setPlotCNs = new HashSet <string>(); setTreeCNs = new HashSet <string>(); if (m_intError == 0 && !GetBooleanValue(m_frmTherm, "AbortProcess")) { SetThermValue(m_frmTherm.progressBar1, "Value", 20); //Create table of condition CNs to join Plot/Cond/Tree m_ado.SqlNonQuery(m_connTempMDBFile, "CREATE TABLE conds_to_delete (cn TEXT(34))"); m_ado.AddPrimaryKey(m_connTempMDBFile, "conds_to_delete", "conds_to_delete_pk", "cn"); foreach (string condCN in condCNs) { m_ado.SqlNonQuery(m_connTempMDBFile, "INSERT INTO conds_to_delete (cn) VALUES (" + condCN + ");"); } m_ado.CreateDataSet(m_connTempMDBFile, string.Format( "SELECT c.cn, c.biosum_cond_id, t.cn " + "FROM (({0} c INNER JOIN conds_to_delete ON c.cn=conds_to_delete.cn) " + "INNER JOIN {1} p ON c.biosum_plot_id=p.biosum_plot_id) " + "LEFT JOIN {2} t on c.biosum_cond_id=t.biosum_cond_id;", m_strCondTable, m_strPlotTable, m_strTreeTable), "identifiers"); foreach (DataRow row in m_ado.m_DataSet.Tables["identifiers"].Rows) { setBiosumCondCNs.Add(String.Format("'{0}'", row[0])); setBiosumCondIds.Add(String.Format("'{0}'", row[1])); setTreeCNs.Add(String.Format("'{0}'", row[2])); } m_ado.CreateDataSet(m_connTempMDBFile, String.Format( "SELECT allconds.biosum_plot_id, allconds.cn " + //, allconds.cntConds, someconds.cntConds " + "FROM (SELECT p.biosum_plot_id, p.cn, count(*) as cntConds FROM ({0} c INNER JOIN conds_to_delete ON c.cn=conds_to_delete.cn) INNER JOIN {1} p ON c.biosum_plot_id = p.biosum_plot_id WHERE c.cn IN ({2}) GROUP BY p.biosum_plot_id, p.cn) someconds " + "RIGHT JOIN (SELECT p.biosum_plot_id, p.cn, count(*) as cntConds FROM {0} c INNER JOIN {1} p ON c.biosum_plot_id = p.biosum_plot_id GROUP BY p.biosum_plot_id, p.cn) allconds " + "ON allconds.biosum_plot_id=someconds.biosum_plot_id WHERE allconds.cntConds=someconds.cntConds", m_strCondTable, m_strPlotTable, m_strCondCNs), "plots_with_all_conds_deleted"); foreach (DataRow row in m_ado.m_DataSet.Tables["plots_with_all_conds_deleted"].Rows) { setBiosumPlotIds.Add(String.Format("'{0}'", row[0])); setPlotCNs.Add(String.Format("'{0}'", row[1])); } m_intError = m_ado.m_intError; } m_dictIdentityColumnsToValues = new Dictionary <string, HashSet <string> > { { "biosum_cond_id", setBiosumCondIds }, { "StandID", setBiosumCondIds }, { "Stand_ID", setBiosumCondIds }, { "biosum_plot_id", setBiosumPlotIds }, { "cnd_cn", setBiosumCondCNs }, { "plt_cn", setPlotCNs }, { "tre_cn", setTreeCNs }, }; }
public void SaveScenarioProperties() { bool bOptimizer; string strDesc = ""; string strSQL = ""; System.Text.StringBuilder strFullPath; m_intError = 0; //validate the input // //Optimization id // if (this.txtScenarioId.Text.Length == 0) { MessageBox.Show("Enter A Unique Optimization scenario Id"); this.txtScenarioId.Focus(); m_intError = -1; return; } // //check for duplicate scenario id // System.Data.OleDb.OleDbConnection oConn = new System.Data.OleDb.OleDbConnection(); string strProjDir = frmMain.g_oFrmMain.getProjectDirectory(); string strScenarioDir = strProjDir + "\\" + ScenarioType + "\\db"; string strFile = "scenario_" + ScenarioType + "_rule_definitions.mdb"; strFullPath = new System.Text.StringBuilder(strScenarioDir); strFullPath.Append("\\"); strFullPath.Append(strFile); ado_data_access oAdo = new ado_data_access(); string strConn = oAdo.getMDBConnString(strFullPath.ToString(), "admin", ""); oAdo.SqlQueryReader(strConn, "select scenario_id from scenario"); if (oAdo.m_OleDbDataReader.HasRows) { while (oAdo.m_OleDbDataReader.Read()) { if (oAdo.m_OleDbDataReader["scenario_id"] != System.DBNull.Value) { if (this.txtScenarioId.Text.Trim().ToUpper() == Convert.ToString(oAdo.m_OleDbDataReader["scenario_id"]).Trim().ToUpper()) { this.m_intError = -1; MessageBox.Show("Cannot have a duplicate Optimization scenario id"); oAdo.m_OleDbDataReader.Close(); oAdo.m_OleDbDataReader = null; oAdo = null; this.txtScenarioId.Focus(); return; } } } } else { } oAdo.m_OleDbDataReader.Close(); oAdo.m_OleDbDataReader = null; // //create the scenario path if it does not exist and //copy the scenario_results.mdb to it // try { if (!System.IO.Directory.Exists(this.txtScenarioPath.Text)) { System.IO.Directory.CreateDirectory(this.txtScenarioPath.Text); System.IO.Directory.CreateDirectory(this.txtScenarioPath.Text.ToString() + "\\db"); //copy default processor scenario_results database to the new project directory if (this.ScenarioType == "processor") { dao_data_access oDao = new dao_data_access(); string strDestFile = this.txtScenarioPath.Text + "\\" + Tables.ProcessorScenarioRun.DefaultHarvestCostsTableDbFile; oDao.CreateMDB(strDestFile); oDao.m_DaoWorkspace.Close(); oDao = null; string strScenarioResultsConn = oAdo.getMDBConnString(strDestFile, "", ""); System.Data.OleDb.OleDbConnection OleDbScenarioResultsConn = new System.Data.OleDb.OleDbConnection(); oAdo.OpenConnection(strScenarioResultsConn, ref OleDbScenarioResultsConn); frmMain.g_oTables.m_oProcessor.CreateHarvestCostsTable( oAdo, OleDbScenarioResultsConn, Tables.ProcessorScenarioRun.DefaultHarvestCostsTableName); frmMain.g_oTables.m_oProcessor.CreateTreeVolValSpeciesDiamGroupsTable( oAdo, OleDbScenarioResultsConn, Tables.ProcessorScenarioRun.DefaultTreeVolValSpeciesDiamGroupsTableName); OleDbScenarioResultsConn.Close(); OleDbScenarioResultsConn.Dispose(); } } } catch { MessageBox.Show("Error Creating Folder"); m_intError = -1; return; } // //copy the project data source values to the scenario data source // string strProjDBDir = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\db"; string strProjFile = "project.mdb"; StringBuilder strProjFullPath = new StringBuilder(strProjDBDir); strProjFullPath.Append("\\"); strProjFullPath.Append(strProjFile); string strProjConn = oAdo.getMDBConnString(strProjFullPath.ToString(), "admin", ""); System.Data.OleDb.OleDbConnection p_OleDbProjConn = new System.Data.OleDb.OleDbConnection(); oAdo.OpenConnection(strProjConn, ref p_OleDbProjConn); string strScenarioDBDir = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\" + ScenarioType + "\\db"; string strScenarioFile = "scenario_" + ScenarioType + "_rule_definitions.mdb"; StringBuilder strScenarioFullPath = new StringBuilder(strScenarioDBDir); strScenarioFullPath.Append("\\"); strScenarioFullPath.Append(strScenarioFile); string strScenarioConn = oAdo.getMDBConnString(strScenarioFullPath.ToString(), "admin", ""); oAdo.OpenConnection(strScenarioConn); if (oAdo.m_intError == 0) { if (this.txtDescription.Text.Trim().Length > 0) { strDesc = oAdo.FixString(this.txtDescription.Text.Trim(), "'", "''"); } strSQL = "INSERT INTO scenario (scenario_id,description,Path,File) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," + "'" + strDesc + "'," + "'" + this.txtScenarioPath.Text.Trim() + "','scenario_" + ScenarioType + "_rule_definitions.mdb');"; oAdo.SqlNonQuery(oAdo.m_OleDbConnection, strSQL); oAdo.SqlQueryReader(p_OleDbProjConn, "select * from datasource"); if (oAdo.m_intError == 0) { try { while (oAdo.m_OleDbDataReader.Read()) { bOptimizer = false; switch (oAdo.m_OleDbDataReader["table_type"].ToString().Trim().ToUpper()) { case "PLOT": bOptimizer = true; break; case "CONDITION": bOptimizer = true; break; //case "FIRE AND FUEL EFFECTS": // bCore = true; // break; //case "HARVEST COSTS": // bCore = true; // break; case "ADDITIONAL HARVEST COSTS": bOptimizer = true; break; case "TREATMENT PRESCRIPTIONS": bOptimizer = true; break; //case "TREE VOLUMES AND VALUES BY SPECIES AND DIAMETER GROUPS": // bCore = true; // break; case "TRAVEL TIMES": bOptimizer = true; break; case "PROCESSING SITES": bOptimizer = true; break; //case "TREE SPECIES AND DIAMETER GROUPS DOLLAR VALUES": // bCore = true; // break; case "PLOT AND CONDITION RECORD AUDIT": if (ScenarioType == "optimizer") { bOptimizer = true; } break; case "PLOT, CONDITION AND TREATMENT RECORD AUDIT": if (ScenarioType == "optimizer") { bOptimizer = true; } break; case "TREE": if (ScenarioType == "processor") { bOptimizer = true; } break; case "HARVEST METHODS": if (ScenarioType == "processor") { bOptimizer = true; } break; case "TREATMENT PACKAGES": bOptimizer = true; break; //case "FVS TREE LIST FOR PROCESSOR": // if (ScenarioType=="processor") bCore=true; // break; case "TREE SPECIES": if (ScenarioType == "processor") { bOptimizer = true; } break; case "TREATMENT PRESCRIPTIONS HARVEST COST COLUMNS": if (ScenarioType == "processor") { bOptimizer = true; } break; case "FIA TREE SPECIES REFERENCE": if (ScenarioType == "processor") { bOptimizer = true; } break; default: break; } if (bOptimizer == true) { strSQL = "INSERT INTO scenario_datasource (scenario_id,table_type,Path,file,table_name) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," + "'" + oAdo.m_OleDbDataReader["table_type"].ToString().Trim() + "'," + "'" + oAdo.m_OleDbDataReader["path"].ToString().Trim() + "'," + "'" + oAdo.m_OleDbDataReader["file"].ToString().Trim() + "'," + "'" + oAdo.m_OleDbDataReader["table_name"].ToString().Trim() + "');"; oAdo.SqlNonQuery(oAdo.m_OleDbConnection, strSQL); } } } catch (Exception caught) { m_intError = -1; m_strError = caught.Message; MessageBox.Show(strError); } oAdo.m_OleDbDataReader.Close(); oAdo.m_OleDbDataReader = null; oAdo.m_OleDbCommand = null; p_OleDbProjConn.Close(); p_OleDbProjConn = null; } if (ScenarioType.Trim().ToUpper() == "OPTIMIZER") { string strTemp = oAdo.FixString("SELECT @@PlotTable@@.* FROM @@PlotTable@@ WHERE @@PlotTable@@.plot_accessible_yn='Y'", "'", "''"); strSQL = "INSERT INTO scenario_plot_filter (scenario_id,sql_command,current_yn) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," + "'" + strTemp + "'," + "'Y');"; oAdo.SqlNonQuery(oAdo.m_OleDbConnection, strSQL); strTemp = oAdo.FixString("SELECT @@CondTable@@.* FROM @@CondTable@@", "'", "''"); strSQL = "INSERT INTO scenario_cond_filter (scenario_id,sql_command,current_yn) VALUES " + "('" + this.txtScenarioId.Text.Trim() + "'," + "'" + strTemp + "'," + "'Y');"; oAdo.SqlNonQuery(oAdo.m_OleDbConnection, strSQL); } } oAdo.m_OleDbConnection.Close(); oAdo.m_OleDbConnection = null; oAdo = null; }
public int savevalues() { int x = 0; //const int LANDOWNER_FORESTSERVICE = 10; //const int LANDOWNER_OTHERFEDERAL = 20; //const int LANDOWNER_STATELOCAL = 30; //const int LANDOWNER_PRIVATE = 40; //string str=""; string strSQL = ""; string strConn = ""; ado_data_access p_ado = new ado_data_access(); string strScenarioId = ((frmOptimizerScenario)this.ParentForm).uc_scenario1.txtScenarioId.Text.Trim().ToLower(); string strScenarioMDB = ((frmMain)this.ParentForm.ParentForm).frmProject.uc_project1.m_strProjectDirectory + "\\" + Tables.OptimizerScenarioRuleDefinitions.DefaultScenarioTableDbFile; this.m_OleDbConnectionScenario = new System.Data.OleDb.OleDbConnection(); strConn = p_ado.getMDBConnString(strScenarioMDB, "admin", ""); //strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strScenarioMDB + ";User Id=admin;Password=;"; p_ado.OpenConnection(strConn, ref this.m_OleDbConnectionScenario); if (p_ado.m_intError != 0) { x = p_ado.m_intError; p_ado = null; return(x); } //delete all records from the scenario wind speed class table strSQL = "DELETE FROM scenario_land_owner_groups WHERE " + " scenario_id = '" + strScenarioId + "';"; p_ado.SqlNonQuery(this.m_OleDbConnectionScenario, strSQL); if (p_ado.m_intError < 0) { this.m_OleDbConnectionScenario.Close(); x = p_ado.m_intError; p_ado = null; return(x); } if (this.chkOwnGrp10.Checked == true) { strSQL = "INSERT INTO scenario_land_owner_groups (scenario_id,owngrpcd)" + " VALUES ('" + strScenarioId + "',10);"; p_ado.SqlNonQuery(this.m_OleDbConnectionScenario, strSQL); } if (this.chkOwnGrp20.Checked == true) { strSQL = "INSERT INTO scenario_land_owner_groups (scenario_id,owngrpcd)" + " VALUES ('" + strScenarioId + "',20);"; p_ado.SqlNonQuery(this.m_OleDbConnectionScenario, strSQL); } if (this.chkOwnGrp30.Checked == true) { strSQL = "INSERT INTO scenario_land_owner_groups (scenario_id,owngrpcd)" + " VALUES ('" + strScenarioId + "',30);"; p_ado.SqlNonQuery(this.m_OleDbConnectionScenario, strSQL); } if (this.chkOwnGrp40.Checked == true) { strSQL = "INSERT INTO scenario_land_owner_groups (scenario_id,owngrpcd)" + " VALUES ('" + strScenarioId + "',40);"; p_ado.SqlNonQuery(this.m_OleDbConnectionScenario, strSQL); } this.m_OleDbConnectionScenario.Close(); p_ado = null; return(0); }
private void ExecuteDeleteOnTables(string strDbPathFile, string[] tables = null, string[] exceptions = null) { using (var conn = new OleDbConnection(m_ado.getMDBConnString(strDbPathFile, "", ""))) { conn.Open(); string[] strTables = tables; if (tables == null || tables.Length == 0) { strTables = m_ado.getTableNamesOfSpecificTypes(conn).Where(s => !(s.Contains("~") || s.Contains(" "))).ToArray(); //In case none of the tables are valid if (strTables.Length == 1 && strTables[0] == "") { return; } } string column; foreach (string table in strTables) { if (exceptions != null && exceptions.Contains(table)) { continue; } column = null; foreach (string col in new string[] { "rxpackage" }) { if (m_ado.ColumnExist(conn, table, col)) { column = col; break; } } if (!String.IsNullOrEmpty(column)) { var strTempIndex = column + "_delete_idx"; if (!m_dao.IndexExists(strDbPathFile, table, strTempIndex)) { m_ado.AddIndex(conn, table, strTempIndex, column); } if (frmMain.g_bDebug) { frmMain.g_oUtils.WriteText(m_strDebugFile, Checked(chkDeletesDisabled) ? "\r\nCounting records to delete from " + strDbPathFile + " " + table + " using " + column + "\r\n" : "\r\nDeleting from " + strDbPathFile + " " + table + " using " + column + "\r\n"); } int deletedRecords = BuildAndExecuteDeleteSQLStmts(conn, table, column); if (deletedRecords > 0) { AddDeletedCountToDictionary(strDbPathFile, table, deletedRecords); } m_ado.SqlNonQuery(conn, String.Format("DROP INDEX {0} ON {1}", strTempIndex, table)); } } } if (Checked(chkCompactMDB)) { m_dao.CompactMDB(strDbPathFile); } }
public void savevalues() { m_intError = 0; m_strError = ""; string strValues = ""; string strFields = "scenario_id," + "EscalatorOperatingCosts_Cycle2," + "EscalatorOperatingCosts_Cycle3," + "EscalatorOperatingCosts_Cycle4," + "EscalatorMerchWoodRevenue_Cycle2," + "EscalatorMerchWoodRevenue_Cycle3," + "EscalatorMerchWoodRevenue_Cycle4," + "EscalatorEnergyWoodRevenue_Cycle2," + "EscalatorEnergyWoodRevenue_Cycle3," + "EscalatorEnergyWoodRevenue_Cycle4"; try { SQLite.ADO.DataMgr oDataMgr = null; // //DELETE THE CURRENT SCENARIO RECORDS // if (!ReferenceProcessorScenarioForm.m_bUsingSqlite) { m_oAdo.m_strSQL = "DELETE FROM scenario_cost_revenue_escalators " + "WHERE TRIM(scenario_id)='" + this.ScenarioId.Trim() + "'"; m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL); } else { oDataMgr = new SQLite.ADO.DataMgr(); string strScenarioDB = frmMain.g_oFrmMain.frmProject.uc_project1.txtRootDirectory.Text.Trim() + "\\processor\\" + Tables.ProcessorScenarioRuleDefinitions.DefaultSqliteDbFile; oDataMgr.OpenConnection(oDataMgr.GetConnectionString(strScenarioDB)); if (oDataMgr.m_intError != 0) { m_intError = oDataMgr.m_intError; m_strError = oDataMgr.m_strError; oDataMgr = null; return; } oDataMgr.m_strSQL = "DELETE FROM scenario_cost_revenue_escalators " + "WHERE TRIM(scenario_id)='" + this.ScenarioId.Trim() + "'"; oDataMgr.SqlNonQuery(oDataMgr.m_Connection, oDataMgr.m_strSQL); } // //scenario id // strValues = "'" + ScenarioId.Trim() + "',"; // //Operating Cost Cycle1 // strValues = strValues + this.uc_processor_scenario_escalators_value1.Cycle1.Trim() + ","; // //Operating Cost Cycle2 // strValues = strValues + this.uc_processor_scenario_escalators_value1.Cycle2.Trim() + ","; // //Operating Cost Cycle3 // strValues = strValues + this.uc_processor_scenario_escalators_value1.Cycle3.Trim() + ","; // //Merch Wood Revenue Cycle1 // strValues = strValues + this.uc_processor_scenario_escalators_value2.Cycle1.Trim() + ","; // //Merch Wood Revenue Cycle2 // strValues = strValues + this.uc_processor_scenario_escalators_value2.Cycle2.Trim() + ","; // //Merch Wood Revenue Cycle3 // strValues = strValues + this.uc_processor_scenario_escalators_value2.Cycle3.Trim() + ","; // //Energy Wood Revenue Cycle1 // strValues = strValues + this.uc_processor_scenario_escalators_value3.Cycle1.Trim() + ","; // //Energy Wood Revenue Cycle2 // strValues = strValues + this.uc_processor_scenario_escalators_value3.Cycle2.Trim() + ","; // //Energy Wood Revenue Cycle3 // strValues = strValues + this.uc_processor_scenario_escalators_value3.Cycle3.Trim(); if (!ReferenceProcessorScenarioForm.m_bUsingSqlite) { m_oAdo.m_strSQL = Queries.GetInsertSQL(strFields, strValues, "scenario_cost_revenue_escalators"); m_oAdo.SqlNonQuery(m_oAdo.m_OleDbConnection, m_oAdo.m_strSQL); } else { oDataMgr.m_strSQL = Queries.GetInsertSQL(strFields, strValues, "scenario_cost_revenue_escalators"); oDataMgr.SqlNonQuery(oDataMgr.m_Connection, oDataMgr.m_strSQL); m_intError = oDataMgr.m_intError; oDataMgr.CloseConnection(oDataMgr.m_Connection); oDataMgr = null; } this.uc_processor_scenario_escalators_value1.SaveValues(); this.uc_processor_scenario_escalators_value2.SaveValues(); this.uc_processor_scenario_escalators_value3.SaveValues(); } catch (Exception e) { m_intError = -1; m_strError = e.Message; } }