private void button1_Click(object sender, EventArgs e)
{
try
{
Con_UsersDB.Close();
Con_UsersDB.Open();
SQLiteDataAdapter SqlDA = new SQLiteDataAdapter();
if (comboBox1.SelectedIndex == 0)
{
SqlDA = new SQLiteDataAdapter("SELECT * FROM Users WHERE Usertype = 'Bruger' ORDER BY Username ASC", Con_UsersDB);
} //if you selected "Bruger" in the usertype drop down menu, then all users with the usertype "Bruger" will be selected and ordered by ascending usernames
if (comboBox1.SelectedIndex == 1)
{
SqlDA = new SQLiteDataAdapter("SELECT * FROM Users WHERE Usertype = 'Superbruger' ORDER BY Username ASC", Con_UsersDB);
}
if (comboBox1.SelectedIndex == 2)
{
SqlDA = new SQLiteDataAdapter("SELECT * FROM Users WHERE Usertype = 'Administrator' ORDER BY Username ASC", Con_UsersDB);
}
DataTable DT = new DataTable();
SqlDA.Fill(DT);
dataGridView1.DataSource = DT;
Con_UsersDB.Close();
}
catch (Exception exc) { MessageBox.Show(exc.Message); }
}
protected void FillCalendarDateDropbox(DateTime kalenderDatum, ComboBox cbCalendarDates)
{
string sqlQuery = (BU == Utility.BU.ILVB ? SQLQueries.SQL_CALENDAR_DATES_ILH : SQLQueries.SQL_CALENDAR_DATES_ILSB).Replace("<period>", Period.ToString());
DataSet ds = SqlDA.GetQueryDataSet(Utility.GetConnectionString(this.ENV, this.BU, Period, false), sqlQuery, false);
cbCalendarDates.DataSource = ds.Tables[0];
cbCalendarDates.DisplayMember = "Kalenderdatum";
// Set selected item
foreach (DataRowView item in cbCalendarDates.Items)
{
if (DateTime.Parse(item[0].ToString()) == kalenderDatum)
{
cbCalendarDates.SelectedItem = item;
break;
}
}
}
private void button1_Click(object sender, EventArgs e)
{
try
{
Con_fødselsrate_2017.Close();
Con_fødselsrate_2017.Open();
SQLiteDataAdapter SqlDA = new SQLiteDataAdapter();
if (comboBox1.SelectedIndex == 0)
{
SqlDA = new SQLiteDataAdapter("SELECT *, RANK() OVER (ORDER BY Fødselsrate DESC) AS Rang FROM Africa", Con_fødselsrate_2017);
}
if (comboBox1.SelectedIndex == 1)
{
SqlDA = new SQLiteDataAdapter("SELECT *, RANK() OVER (ORDER BY Fødselsrate DESC) AS Rang FROM Asien", Con_fødselsrate_2017);
}
if (comboBox1.SelectedIndex == 2)
{
SqlDA = new SQLiteDataAdapter("SELECT *, RANK() OVER (ORDER BY Fødselsrate DESC) AS Rang FROM Europa", Con_fødselsrate_2017);
}
if (comboBox1.SelectedIndex == 3)
{
SqlDA = new SQLiteDataAdapter("SELECT *, RANK() OVER (ORDER BY Fødselsrate DESC) AS Rang FROM Mellemamerika", Con_fødselsrate_2017);
}
if (comboBox1.SelectedIndex == 4)
{
SqlDA = new SQLiteDataAdapter("SELECT *, RANK() OVER (ORDER BY Fødselsrate DESC) AS Rang FROM Nordamerika", Con_fødselsrate_2017);
}
if (comboBox1.SelectedIndex == 5)
{
SqlDA = new SQLiteDataAdapter("SELECT *, RANK() OVER (ORDER BY Fødselsrate DESC) AS Rang FROM Oceanien", Con_fødselsrate_2017);
}
if (comboBox1.SelectedIndex == 6)
{
SqlDA = new SQLiteDataAdapter("SELECT *, RANK() OVER (ORDER BY Fødselsrate DESC) AS Rang FROM Sydamerika", Con_fødselsrate_2017);
}
DataTable DT = new DataTable();
SqlDA.Fill(DT);
dataGridView1.DataSource = DT;
Con_fødselsrate_2017.Close();
}
catch (Exception exc) { MessageBox.Show(exc.Message); }
}
private void button1_Click(object sender, EventArgs e)
{
try
{
Con.Close(); //closes the connection to the database(for some reason, a non-skippable error occurs when i generate two consecutive errors, and the only fix i could find, is to close the connection before opening it)
Con.Open(); //opens the connection to the database
SQLiteDataAdapter SqlDA = new SQLiteDataAdapter();
if (comboBox1.SelectedIndex == 0)
{
SqlDA = new SQLiteDataAdapter("SELECT *, RANK() OVER (ORDER BY Fødselsrate DESC) AS Rang FROM Africa", Con);
} //if you selected "Africa" in the drop down menu, then all the entries in the "Africa" table will be selected, and ranked by descending birthrates
if (comboBox1.SelectedIndex == 1)
{
SqlDA = new SQLiteDataAdapter("SELECT *, RANK() OVER (ORDER BY Fødselsrate DESC) AS Rang FROM Asien", Con);
}
if (comboBox1.SelectedIndex == 2)
{
SqlDA = new SQLiteDataAdapter("SELECT *, RANK() OVER (ORDER BY Fødselsrate DESC) AS Rang FROM Europa", Con);
}
if (comboBox1.SelectedIndex == 3)
{
SqlDA = new SQLiteDataAdapter("SELECT *, RANK() OVER (ORDER BY Fødselsrate DESC) AS Rang FROM Mellemamerika", Con);
}
if (comboBox1.SelectedIndex == 4)
{
SqlDA = new SQLiteDataAdapter("SELECT *, RANK() OVER (ORDER BY Fødselsrate DESC) AS Rang FROM Nordamerika", Con);
}
if (comboBox1.SelectedIndex == 5)
{
SqlDA = new SQLiteDataAdapter("SELECT *, RANK() OVER (ORDER BY Fødselsrate DESC) AS Rang FROM Oceanien", Con);
}
if (comboBox1.SelectedIndex == 6)
{
SqlDA = new SQLiteDataAdapter("SELECT *, RANK() OVER (ORDER BY Fødselsrate DESC) AS Rang FROM Sydamerika", Con);
}
DataTable DT = new DataTable();
SqlDA.Fill(DT);
dataGridView1.DataSource = DT; //shows the data in the "DT" datatable
Con.Close();
}
catch (Exception exc) { MessageBox.Show(exc.Message); }
}
public TroublehsootingForm(Utility.ENV env, Utility.BU bu, Utility.PERIOD period, DateTime kalenderDatum, int ILRunID, Int64 ssisRunID)
{
InitializeComponent();
Init(env, bu, period, ILRunID, ssisRunID, kalenderDatum);
// Get Failure Details
string cs = Utility.GetConnectionString(ENV, BU, Period, false);
DataSet dsFailureDetails = SqlDA.GetQueryDataSet(cs, SQLQueries.SQL_RUN_FAILURES.Replace("<RunID>", ILRunID.ToString()), false);
dgvFailureDetails.DataSource = dsFailureDetails.Tables[0];
dgvFailureDetails.Refresh();
// Get SSIS Errors
DataSet dsFSSISErrors = SqlDA.GetQueryDataSet(cs, SQLQueries.SQL_SSIS_ERRORS.Replace("<RunID>", SsisRunID.ToString()), false);
dgvSSISErrors.DataSource = dsFSSISErrors.Tables[0];
dgvSSISErrors.Refresh();
}
public override void Refresh()
{
string cs = Utility.GetConnectionString(ENV, BU, Period, false);
string query = SQLQueries.SQL_LATEST_SSIS_MESSAGES.Replace("<RunID>", SsisRunID.ToString());
//if (_sortAsc == false)
// query += " order by LogId desc";
try
{
DataSet dsSSISLog = SqlDA.GetQueryDataSet(cs, query, false);
dgvSSISLog.DataSource = dsSSISLog.Tables[0];
dgvSSISLog.Refresh();
// Show last refresh in toolstripstatuslabel
toolStripStatusLabel1.Text = "Last Refreshed: " + DateTime.Now.ToString();
}
catch (Exception e)
{
throw e;
}
}
protected void GenerateUpdateScript(bool executeScript)
{
string query = GenerateUpdateQuery();
if (query.Length > 0)
{
if (executeScript)
{
string cs = Utility.GetConnectionString(ENV, BU, Period);
try
{
int rc = SqlDA.ExecuteSQLCommand(cs, query);
if (rc >= 1)
{
MessageBox.Show("Calendar updated!");
}
else
{
MessageBox.Show("Failed to update calendar");
}
RefreshData(); // Refresh form data
}
catch (Exception ex)
{
ShowMessage(ex.Message);
}
}
else
{
QueryOutputForm queryForm = new QueryOutputForm(query.ToString());
queryForm.Show();
}
}
else
{
MessageBox.Show("Nothing to update!");
}
}
protected void AbortRun()
{
if (ENV == Utility.ENV.LOCAL)
{
if (SsisRunID > 0)
{
try
{
string envMessage = BU.ToString() + " - " + ENV.ToString() + " - " + Period.ToString();
if (MessageBox.Show("Abort run " + envMessage + " ?", "Abort Run", MessageBoxButtons.YesNo) == DialogResult.Yes)
{
string cs = Utility.GetConnectionString(ENV, BU, Period);
string abortRunQuery = SQLQueries.SQL_ABORT_RUN.Replace("<RunID>", this.SsisRunID.ToString());
int rc = SqlDA.ExecuteSQLCommand(cs, abortRunQuery);
string message = "Run Aborted, " + envMessage;
_toolStripStatusLabel.Text = DateTime.Now.ToString() + ": " + message;
MessageBox.Show(message);
this.Close();
}
}
catch (Exception ex)
{
ShowMessage(ex.Message);
}
}
else
{
_toolStripStatusLabel.Text = DateTime.Now.ToString() + ": No valid SSIS Run ID available";
}
}
else
{
_toolStripStatusLabel.Text = DateTime.Now.ToString() + ": Function only available on local machine";
}
}
private void BtnQuery_Click(object sender, EventArgs e)
{
DataTable dataTable = SqlDA.Select(sqlstr, sql);
gridView.DataSource = dataTable;
}
public override int RefreshData()
{
string cs;
int rc = 0;
cs = Utility.GetConnectionString(ENV, BU, Period, false);
if (cs != "")
{
string detailsQuery = (BU == Utility.BU.ILVB ? SQLQueries.SQL_RUN_DETAILS_ILH : SQLQueries.SQL_RUN_DETAILS_ILSB);
// Replace DAG with MAAND if this is a MAAND process
if (Period == Utility.PERIOD.MAAND)
{
detailsQuery = detailsQuery.Replace("_DAG", "_MAAND");
}
// Replace <kalenderdatum> with real date
detailsQuery = detailsQuery.Replace("<Kalenderdatum>", CalendarDate.Year + "-" + CalendarDate.Month + "-" + CalendarDate.Day);
DataSet ds = SqlDA.GetQueryDataSet(cs, detailsQuery, false);
if (ds.Tables[0].Rows.Count > 0)
{
// Load details list
LoadDetailsList(ds.Tables[0]);
//lblPeilDatum.Text = Convert.ToDateTime(ds.Tables[0].Rows[0]["PeilDatum"]).ToString("dd-MM-yyyy");
//cbLegenStaging.Checked = (ds.Tables[0].Rows[0]["LEGEN_STG"].ToString() == "J");
//cbLaadStaging.Checked = (ds.Tables[0].Rows[0]["LAAD_STG"].ToString() == "J");
//cbLaadCloseBO.Checked = (ds.Tables[0].Rows[0]["LAAD_CLOSE"].ToString() == "P");
//cbVoorbereidenRun.Checked = (ds.Tables[0].Rows[0]["VOORBEREIDEN_RUN"].ToString() == "J");
//cbArchiefbestandenZippen.Checked = (ds.Tables[0].Rows[0]["ARCHIEFBESTANDEN_ZIPPEN"].ToString() == "J");
//cbLaadHouseFP.Checked = (ds.Tables[0].Rows[0]["LAAD_HOUSE_FP"].ToString() == "P");
//cbLaadCBS.Checked = (ds.Tables[0].Rows[0]["LAAD_CBS"].ToString() == "P");
//cbLaadCloseCM.Checked = (ds.Tables[0].Rows[0]["LAAD_CLOSE_CM"].ToString() == "P");
//cbLaadDaybreak.Checked = (ds.Tables[0].Rows[0]["LAAD_DAYBREAK"].ToString() == "P");
//cbLaadHomes.Checked = (ds.Tables[0].Rows[0]["LAAD_HOMES"].ToString() == "J");
//cbLaadMidas.Checked = (ds.Tables[0].Rows[0]["LAAD_MIDAS"].ToString() == "P");
//cbLaadMidasCK.Checked = (ds.Tables[0].Rows[0]["LAAD_MIDAS_CK"].ToString() == "P");
//cbLaadHouse.Checked = (ds.Tables[0].Rows[0]["LAAD_HOUSE"].ToString() == "P");
//cbLaadMDS.Checked = (ds.Tables[0].Rows[0]["LAAD_MDS"].ToString() == "J");
//cbLaadQuion.Checked = (ds.Tables[0].Rows[0]["LAAD_QUION"].ToString() == "P");
//cbLaadSAPAchterstandPolisData.Checked = (ds.Tables[0].Rows[0]["LAAD_SAPBW_ACHTERSTAND_POLISDATA"].ToString() == "P");
//cbLaadSAPKlantData.Checked = (ds.Tables[0].Rows[0]["LAAD_SAPBW_KLANTDATA"].ToString() == "P");
//cbLaadSAPPolisData.Checked = (ds.Tables[0].Rows[0]["LAAD_SAPBW_POLISDATA"].ToString() == "P");
//cbLaadSAPICM.Checked = (ds.Tables[0].Rows[0]["LAAD_SAP_ICM"].ToString() == "P");
//cbLaadDDS.Checked = (ds.Tables[0].Rows[0]["LAAD_DDS"].ToString() == "P");
//cbLaadDDSDWH.Checked = (ds.Tables[0].Rows[0]["LAAD_DDS_DWH"].ToString() == "P");
//cbMaakCF.Checked = (ds.Tables[0].Rows[0]["MAAK_CF"].ToString() == "J");
//dtpClosePeilDatum.Text = ds.Tables[0].Rows[0]["CLOSE_Peildatum"].ToString();
//dtpCloseCMPeilDatum.Text = ds.Tables[0].Rows[0]["CLOSE_CM_Peildatum"].ToString();
//dtpDaybreakPeilDatum.Text = ds.Tables[0].Rows[0]["DAYBREAK_Verwacht_Peildatum"].ToString();
//dtpHomesPeilDatum.Text = ds.Tables[0].Rows[0]["HOMES_Peildatum"].ToString();
//dtpMidasPeilDatum.Text = ds.Tables[0].Rows[0]["MIDAS_Peildatum"].ToString();
//dtpMidasCKPeilDatum.Text = ds.Tables[0].Rows[0]["MIDAS_CK_Verwacht_Peildatum"].ToString();
//dtpHousePeilDatum.Text = ds.Tables[0].Rows[0]["HOUSE_Peildatum"].ToString();
//dtpQuionPeilDatum.Text = ds.Tables[0].Rows[0]["QUION_Peildatum"].ToString();
//dtpSAPAchterstandPeilDatum.Text = ds.Tables[0].Rows[0]["SAPBW_Achterstand_PolisData_Peildatum"].ToString();
//dtpSAPKlantPeilDatum.Text = ds.Tables[0].Rows[0]["SAPBW_KlantData_Peildatum"].ToString();
//dtpSAPPolisPeilDatum.Text = ds.Tables[0].Rows[0]["SAPBW_PolisData_Peildatum"].ToString();
//dtpSAPICMPeilDatum.Text = ds.Tables[0].Rows[0]["SAP_ICM_Verwacht_Peildatum"].ToString();
//lblCFDistributieLijst.Text = ds.Tables[0].Rows[0]["CFDistributielijst"].ToString();
//lblSoortRun.Text = ds.Tables[0].Rows[0]["SoortRun"].ToString();
//cbLaadDDS.Checked = (ds.Tables[0].Rows[0]["LAAD_DDS"].ToString() == "J");
//cbLaadDDSDWH.Checked = (ds.Tables[0].Rows[0]["LAAD_DDS_DWH"].ToString() == "J");
}
}
else
{
rc = -1;
}
return(rc);
}
