public bool BuildDictionaries()
{
// SCB TODO: Remove all this if not using.
try
{
MySqlDAL mySqlDAL = new MySqlDAL("");
Dictionaries dicts = new Dictionaries();
MySqlDataReader dr = mySqlDAL.ExecuteDataReader("SELECT id, name, description FROM ref_migration_item_type");
if (dr != null && dr.HasRows)
{
while (dr.Read())
{
dicts.AddToItemTypesDictionary(dr.GetInt32(0), dr[1].ToString());
}
}
mySqlDAL.Close();
dr = mySqlDAL.ExecuteDataReader("SELECT id, arrest_type FROM ref_migration_arrest_types");
if (dr != null && dr.HasRows)
{
while (dr.Read())
{
dicts.AddToArrestTypesDictionary(dr.GetInt32(0), dr[1].ToString());
}
}
mySqlDAL.Close();
logging.WriteEvent("BuildDictionaries completed.");
return(true);
}
catch (System.Exception ex)
{
logging.WriteEvent("Error in BuildDictionaries: " + ex.Message);
return(false);
}
}
private void btnRUNMigration_Click(object sender, EventArgs e)
{
#region Initilize
ListBoxInfo.Items.Add("========== CONVERSION COMMENCED ==========");
logging.WriteEvent("========== CONVERSION COMMENCED ==========");
logging.WriteReportEntry("========== CONVERSION COMMENCED ==========", "========== CONVERSION COMMENCED ==========", "");
BuildTableList();
#endregion
#region Sanity Check
if (FormSetupSeemsCompleted() == false)
{ // Routine will alert user to any problems, so don't need to do here too.
return;
}
if (TablesAreClear() == false)
{
DialogResult response = MessageBox.Show("Existing data found. Continue with data conversion?", "Attention!", MessageBoxButtons.YesNo);
if (response != DialogResult.Yes)
{
ListBoxInfo.Items.Add("Existing data found, user aborted.");
return;
}
ListBoxInfo.Items.Add("Existing data found, user continued.");
}
#endregion
#region Data Access Connections
logging.WriteEvent("Oracle Connection String used:" + oracleConnString);
logging.WriteEvent("MySql Connection String used:" + mySqlConnString);
OracleDAL oracleDAL = null;
MySqlDAL mySqlDAL = null;
try
{
oracleDAL = new OracleDAL(oracleConnString);
}
catch (Exception) // ex
{
// SCB TODO: Revisit Oracle connection once we have DB, and put the MessageBox back:
//ListBoxInfo.Items.Add("Error creating Oracle DAL. Error: " + ex.Message);
//logging.WriteEvent("Error creating Oracle DAL. Error: " + ex.Message);
//MessageBox.Show("Error creating Oracle DAL. Error: " + ex.Message);
//return;
}
try
{
mySqlDAL = new MySqlDAL(mySqlConnString);
}
catch (Exception ex)
{
ListBoxInfo.Items.Add("Error creating MySql DAL. Error: " + ex.Message);
logging.WriteEvent("Error creating MySql DAL. Error: " + ex.Message);
MessageBox.Show("Error creating MySql DAL. Error: " + ex.Message);
return;
}
ListBoxInfo.Items.Add("Connections to Databases successful.");
#endregion
// At this point, form completeness has been checked as best as can be AND the DB connections have been successful,
// procees with the ETL process.
ETL_Proccessing etlProcessing = new ETL_Proccessing();
#region Populate lookups
ListBoxInfo.Items.Add("Building Master Codes DataView for lookups...");
if (!etlProcessing.BuildMasterCodeDataView(txtDataDictionaryFilePath.Text, mySqlDAL))
{
logging.WriteEvent("Could not build MasterCode view. Check that the Excel file exists and that it is not opened.");
ListBoxInfo.Items.Add("Could not build MasterCode view. Check that the Excel file exists and that it is not opened.");
return;
}
ListBoxInfo.Items.Add("Building Reference DataViews for lookups...");
if (!etlProcessing.BuildReferenceDataViews(mySqlDAL))
{
logging.WriteEvent("Could not build Reference Table views.");
ListBoxInfo.Items.Add("Could not build Reference Table views.");
return;
}
listOfAgencies.Clear();
if (ChkLBAgencies.CheckedItems.Count != 0)
{
for (int x = 0; x <= ChkLBAgencies.CheckedItems.Count - 1; x++)
{
listOfAgencies.Add(ChkLBAgencies.CheckedItems[x].ToString());
}
}
#endregion
#region ETL Processes
if (chkAttributes.Checked)
{
ListBoxInfo.Items.Add("Processing Attributes...");
ListBoxInfo.Refresh();
if (!etlProcessing.ETL_10_Atrributes(oracleDAL, mySqlDAL, listOfAgencies, out iInsertedRows, out iErroredRows))
{
DialogResult response = MessageBox.Show("An error occurred during Attribute data loading. Continue?", "Attention", MessageBoxButtons.YesNo);
if (response == DialogResult.No)
{
logging.WriteEvent("Error occurred during Attribute data loading, user aborted.");
return;
}
}
if (iErroredRows == 0)
{
ListBoxInfo.Items.Add("Attributes completed. " + iInsertedRows + " rows inserted.");
}
else
{
ListBoxInfo.Items.Add("Attributes completed. " + iInsertedRows + " rows inserted, " + iErroredRows + " rows had errors.");
}
}
if (chkOffenseCodes.Checked)
{
ListBoxInfo.Items.Add("Processing Offense Codes...");
ListBoxInfo.Refresh();
if (!etlProcessing.ETL_20_OffenseCodes(oracleDAL, mySqlDAL, listOfAgencies, txtOffenseExcelFile.Text, out iInsertedRows, out iErroredRows))
{
DialogResult response = MessageBox.Show("An error occurred during Offense Codes data loading. Continue?", "Attention", MessageBoxButtons.YesNo);
if (response == DialogResult.No)
{
logging.WriteEvent("Error occurred during Offense Codes data loading, user aborted.");
return;
}
}
if (iErroredRows == 0)
{
ListBoxInfo.Items.Add("Offense Codes completed. " + iInsertedRows + " rows inserted.");
}
else
{
ListBoxInfo.Items.Add("Offense Codes completed. " + iInsertedRows + " rows inserted, " + iErroredRows + " rows had errors.");
}
}
if (chkUsers.Checked)
{
ListBoxInfo.Items.Add("Processing Users...");
// SCB TODO: Do the same with the other routines (out counts)? I think so, given time.
if (!etlProcessing.ETL_40_Users(oracleDAL, mySqlDAL, out iInsertedRows, out iErroredRows))
{
// SCB TODO: Do the same with the other routines (MessageBox)? Errors are logged to file but not readily appearant.
DialogResult response = MessageBox.Show("An error occurred during User data loading. Continue?", "Attention", MessageBoxButtons.YesNo);
if (response == DialogResult.No)
{
logging.WriteEvent("Error occurred during User data loading, user aborted.");
return;
}
}
if (iErroredRows == 0)
{
ListBoxInfo.Items.Add("Users completed. " + iInsertedRows + " rows inserted.");
}
else
{
ListBoxInfo.Items.Add("Users completed. " + iInsertedRows + " rows inserted, " + iErroredRows + " rows had errors.");
}
}
if (chkLocations.Checked)
{
ListBoxInfo.Items.Add("Processing Locations...");
etlProcessing.ETL_50_Locations(oracleDAL, mySqlDAL);
}
if (chkNames.Checked)
{
ListBoxInfo.Items.Add("Processing Names...");
etlProcessing.ETL_60_Names(oracleDAL, mySqlDAL);
ListBoxInfo.Items.Add("Processing Name Report Links...");
etlProcessing.ETL_60_Name_Report_Links(oracleDAL, mySqlDAL);
}
if (chkReports.Checked)
{
ListBoxInfo.Items.Add("Processing Reports...");
etlProcessing.ETL_70_Reports(oracleDAL, mySqlDAL);
ListBoxInfo.Items.Add("Processing Reports_Arrests...");
etlProcessing.ETL_70_1_Reports_Arrests(oracleDAL, mySqlDAL);
ListBoxInfo.Items.Add("Processing Reports_Charges...");
etlProcessing.ETL_70_2_Reports_Charges(oracleDAL, mySqlDAL);
ListBoxInfo.Items.Add("Processing Reports_Offenses...");
etlProcessing.ETL_70_3_Reports_Offenses(oracleDAL, mySqlDAL);
ListBoxInfo.Items.Add("Processing Reports_FieldContacts...");
etlProcessing.ETL_70_4_Reports_FieldContacts(oracleDAL, mySqlDAL);
ListBoxInfo.Items.Add("Processing Reports_MissingPersons...");
etlProcessing.ETL_70_5_Reports_MissingPersons(oracleDAL, mySqlDAL);
ListBoxInfo.Items.Add("Processing Reports_Impounds...");
etlProcessing.ETL_70_6_Reports_Impounds(oracleDAL, mySqlDAL);
ListBoxInfo.Items.Add("Processing Reports_AdditionalInfo...");
etlProcessing.ETL_70_7_Reports_Additional_Information(oracleDAL, mySqlDAL);
ListBoxInfo.Items.Add("Processing Reports_CitationCharges...");
etlProcessing.ETL_70_8_Reports_Citation_Charges(oracleDAL, mySqlDAL);
ListBoxInfo.Items.Add("Processing Reports_TrafficCrashes...");
etlProcessing.ETL_70_9_Reports_Traffic_Crash(oracleDAL, mySqlDAL);
}
if (chkItems.Checked)
{
ListBoxInfo.Items.Add("Processing Items...");
etlProcessing.ETL_80_Items(oracleDAL, mySqlDAL);
}
if (chkEvidence.Checked)
{
ListBoxInfo.Items.Add("Processing Evidence Items...");
etlProcessing.ETL_90_1_Evidence_Items(oracleDAL, mySqlDAL);
ListBoxInfo.Items.Add("Processing Evidence Chain Items...");
etlProcessing.ETL_90_2_Evidence_Chain(oracleDAL, mySqlDAL);
}
if (chkCases.Checked)
{
ListBoxInfo.Items.Add("Processing Cases...");
etlProcessing.ETL_110_Cases(oracleDAL, mySqlDAL);
ListBoxInfo.Items.Add("Processing Case Notes...");
etlProcessing.ETL_110_Case_Notes(oracleDAL, mySqlDAL);
}
if (chkLegacyAttachments.Checked)
{
ListBoxInfo.Items.Add("Processing Attachments...");
etlProcessing.ETL_120_Attachments(oracleDAL, mySqlDAL);
}
// Etc...
try
{
oracleDAL.CloseConnection();
}
catch (Exception)
{
}
mySqlDAL.Close();
ListBoxInfo.Items.Add("========== CONVERSION COMPLETED ==========");
logging.WriteEvent("========== CONVERSION COMPLETED ==========");
logging.WriteReportEntry("========== CONVERSION COMPLETED ==========", "========== CONVERSION COMPLETED ==========", "");
#endregion
}
