private void ClearETLTables()
{
MySqlDAL mySqlDAL = new MySqlDAL(mySqlConnString);
bool nonQueryExecuted = false;
string sqlStatement = "DELETE FROM migration.ETL_Results";
nonQueryExecuted = mySqlDAL.ExecuteNonQuery(sqlStatement);
if (nonQueryExecuted)
{
ListBoxInfo.Items.Add("ETL_Results table cleared.");
}
else
{
ListBoxInfo.Items.Add("Failed to clear table ETL_Results.");
}
nonQueryExecuted = false;
sqlStatement = "DELETE FROM etl_attributes";
nonQueryExecuted = mySqlDAL.ExecuteNonQuery(sqlStatement);
if (nonQueryExecuted)
{
ListBoxInfo.Items.Add("etl_attributes table cleared.");
}
else
{
ListBoxInfo.Items.Add("Failed to clear table etl_attributes.");
}
}
public void WriteReportEntry(string Summary, string Details, string Notes)
{
MySqlDAL mySqlDAL = new MySqlDAL();
mySqlDAL.ExecuteNonQuery("INSERT INTO ETL_Results (EventTime, ResultSummary, ResultDetail, Notes) " +
"VALUES ('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "', '" + Summary + "', '" + Details + "', '" + Notes + "')");
}
public MySqlDALTests()
{
string address = "220.165.9.44";
int port = 4311;
string account = "root";
string password = "******";
string dbName = "1010gps";
_dal = new MySqlDAL(address, dbName, account, password, port);
}
public bool Update(PointTO point)
{
try
{
MySqlDAL.Update(point);
return(true);
}
catch (Exception)
{
return(false);
}
}
public bool Create(int dimensions)
{
try
{
MySqlDAL.Delete();
IODAL.SaveDimensions(dimensions);
return(true);
}
catch (Exception)
{
return(false);
}
}
public void WriteReportDataEntry(string DataEntity, int SuccessfulRows, int ErrorRows, string Notes = "")
{
MySqlDAL mySqlDAL = new MySqlDAL();
if (ErrorRows == 0)
{
mySqlDAL.ExecuteNonQuery("INSERT INTO ETL_Results (EventTime, ResultSummary, ResultDetail, Notes) " +
"VALUES ('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "', '" + SuccessfulRows + " " + DataEntity + " rows copied.', '" + Notes + "')");
}
else
{
mySqlDAL.ExecuteNonQuery("INSERT INTO ETL_Results (EventTime, ResultSummary, ResultDetail, Notes) " +
"VALUES ('" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + "', '" + SuccessfulRows + " " + DataEntity + " rows copied.', '" + ErrorRows + " rows had errors.', '" + Notes + "')");
}
}
private bool TablesAreClear()
{
string selectStatement = "";
int tableCount = 0;
MySqlDAL mySqlDAL = new MySqlDAL(mySqlConnString);
foreach (string s in listOfMigrationTables)
{
selectStatement = "SELECT COUNT(*) FROM migration." + s;
tableCount = mySqlDAL.ExecuteScalar(selectStatement);
if (tableCount > 0)
{
ListBoxInfo.Items.Add("POSSIBLE Problem - Table " + s.ToString() + " has data.");
}
}
return(true);
}
private bool ClearTables()
{
string sqlStatement = "";
bool nonQueryExecuted = false;
bool allExecuted = true;
MySqlDAL mySqlDAL = new MySqlDAL(mySqlConnString);
foreach (string s in listOfMigrationTables)
{
sqlStatement = "DELETE FROM migration." + s;
nonQueryExecuted = mySqlDAL.ExecuteNonQuery(sqlStatement);
if (!nonQueryExecuted)
{
allExecuted = false;
ListBoxInfo.Items.Add("Failed to clear table " + s.ToString());
}
}
return(allExecuted);
}
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);
}
}
public AuthenticationService(MySqlDAL sqlDal, IHttpContextAccessor contextAccessor)
{
_sqlDal = sqlDal;
_request = contextAccessor.HttpContext.Request;
}
/// <summary>
/// 构造方法
/// </summary>
/// <param name="address">服务器地址</param>
/// <param name="dbName">数据库名称</param>
/// <param name="account">账号</param>
/// <param name="password">密码</param>
/// <param name="port">端口</param>
public MySqlBLL(string address, string dbName, string account, string password, int port)
{
_dal = new MySqlDAL(address, dbName, account, password, port);
}
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
}
public double SumRegion(PointTO point1, PointTO point2)
{
return(MySqlDAL.SumRegion(point1, point2));
}
public List <PointTO> GetValues()
{
return(MySqlDAL.GetAll());
}
