/// <summary>
/// Gets the mysql DB connection_ execute.
/// </summary>
private void GetMysqlDBConnection_Execute()
{
string connectionString = connectionString_MYSQL_Export + "Connection Timeout=180";
string errorMsg = string.Empty;
StringBuilder item;
try
{
if (queryToExecute != null && queryToExecute.Count > 0)
{
// Set the connection
this.factory = DbProviderFactories.GetFactory("MySql.Data.MySqlClient");
cn = Factory.CreateConnection();
cn.ConnectionString = connectionString;
isRunning = true;
foreach (string table in queryToExecute.Keys)
{
// Open con and Begin Trans
cn.Open();
trans = cn.BeginTransaction();
errorMsg = table;
// Append to Log
ListLog.Invoke((Action)(() => this.ListLog.AppendText(table + " Executing..." + Environment.NewLine)));
ListLog.Invoke((Action)(() => this.ListLog.Focus()));
queryToExecute.TryGetValue(table, out item);
cmd = this.CreateCommand(item.ToString(), CommandType.Text);
cmd.Transaction = trans;
cmd.ExecuteNonQuery();
if (progressBar1.Value < maxProgress)
{
progressBar1.Invoke((Action)(() => this.progressBar1.Value += 1));
}
// Append to Log
ListLog.Invoke((Action)(() => this.ListLog.AppendText(table + " Completed." + Environment.NewLine)));
ListLog.Invoke((Action)(() => this.ListLog.Focus()));
// Close con and Commit Trans
trans.Commit();
if (cn != null && cn.State == ConnectionState.Open)
{
cn.Close();
}
}
isRunning = false;
progressBar1.Invoke((Action)(() => this.progressBar1.Value = maxProgress));
RetrievingTables.Invoke((Action)(() => this.RetrievingTables.Visible = false));
ListLog.Invoke((Action)(() => this.ListLog.AppendText((queryToExecute.Count - 2) + " tables copied successfully." + Environment.NewLine)));
ListLog.Invoke((Action)(() => this.ListLog.Focus()));
}
}
catch (Exception ex)
{
hasErrors = true;
trans.Rollback();
this.ElapsedTime.Stop();
this.ElapsedTime.Enabled = false;
//CancelBtn.Invoke((Action)(() => this.CancelBtn.Visible = false));
progressBar1.Invoke((Action)(() => this.progressBar1.Value = 0));
RetrievingTables.Invoke((Action)(() => this.RetrievingTables.Visible = false));
// Append to Log
ListLog.Invoke((Action)(() => this.ListLog.AppendText("Execution aborted." + Environment.NewLine)));
ListLog.Invoke((Action)(() => this.ListLog.Focus()));
MessageBox.Show("Sorry, There was an error encountered, the error was on the " + errorMsg + " table." + Environment.NewLine + ex.Message);
}
}
/// <summary>
/// Gets the table data.
/// </summary>
/// <param name="tablesList">The tables list.</param>
private void GetTableData(Dictionary <string, string> tablesList, Dictionary <string, string> tables_fields, string connectionString)
{
int count = 0;
string fields, initialQuery;
StringBuilder sb = new StringBuilder();
try
{
if (tablesList != null && tablesList.Count > 0 && tables_fields != null && tables_fields.Count > 0)
{
isRunning = true;
ListLog.Invoke((Action)(() => this.ListLog.Clear()));
// Append to Log
ListLog.Invoke((Action)(() => this.ListLog.AppendText("Query generation in progress..." + Environment.NewLine)));
// Set the connection
this.factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
cn = Factory.CreateConnection();
cn.ConnectionString = connectionString;
cn.Open();
using (TransactionScope scope = new TransactionScope())
{
maxProgress = (tablesList.Keys.Count * 2) + 2;
progressBar1.Invoke((Action)(() => progressBar1.Maximum = maxProgress));
// Set foreign_key_checks = 0;
sb.Append("SET foreign_key_checks = 0;" + Environment.NewLine);
// Initialize the query list
queryToExecute = new Dictionary <string, StringBuilder>();
queryToExecute.Add("FK_Disable", sb);
foreach (string table in tablesList.Keys)
{
sb = new StringBuilder();
tables_fields.TryGetValue(table, out fields);
cmd = this.CreateCommand("SELECT " + fields + " FROM [" + table + "]", CommandType.Text);
rdr = cmd.ExecuteReader();
if (rdr != null)
{
tablesList.TryGetValue(table, out initialQuery);
// Append the delete script
sb.Append("SET SQL_SAFE_UPDATES = 0;" + Environment.NewLine);
sb.Append("DELETE FROM `" + table + "`;" + Environment.NewLine);
if (rdr.HasRows)
{
while (rdr.Read())
{
// Append the insert script
sb.Append(initialQuery);
sb.Append("(");
for (int i = 0; i < rdr.FieldCount; i++)
{
// Format the quote ' values.
if (rdr[i].ToString().Contains("'"))
{
string[] s = rdr[i].ToString().Split('\'');
sb.Append("'");
if (i + 1 == rdr.FieldCount)
{
sb.Append("" + s[0].ToString() + "''" + s[1].ToString() + "'");
}
else
{
sb.Append("" + s[0].ToString() + "''" + s[1].ToString() + "', ");
}
}
else
{
// Format the data according to the datatype.
if (rdr[i] == (object)System.DBNull.Value)
{
if (i + 1 == rdr.FieldCount)
{
sb.Append("NULL");
}
else
{
sb.Append("NULL, ");
}
}
else if (rdr[i] is long || rdr[i] is short)
{
if (i + 1 == rdr.FieldCount)
{
sb.Append(" " + rdr[i].ToString());
}
else
{
sb.Append(" " + rdr[i].ToString() + ", ");
}
}
else if (rdr[i] is bool)
{
if (i + 1 == rdr.FieldCount)
{
sb.Append(rdr[i].ToString().ToLower().Equals("false") ? 0 : 1);
}
else
{
sb.Append(rdr[i].ToString().ToLower().Equals("false") ? 0 + "," : 1 + ",");
}
}
else if (rdr[i] is DateTime)
{
sb.Append("'");
if (i + 1 == rdr.FieldCount)
{
sb.Append("" + Convert.ToDateTime(rdr[i]).ToString("yyyy/MM/dd HH:mm:ss") + "'");
}
else
{
sb.Append("" + Convert.ToDateTime(rdr[i]).ToString("yyyy/MM/dd HH:mm:ss") + "', ");
}
}
else
{
sb.Append("'");
if (i + 1 == rdr.FieldCount)
{
sb.Append("" + rdr[i].ToString() + "'");
}
else
{
sb.Append("" + rdr[i].ToString() + "', ");
}
}
}
}
sb.Append(");" + Environment.NewLine);
count++;
}
}
}
rdr.Close();
// Assign sb to the global variable
queryToExecute.Add(table, sb);
// Increment the progressbar.
progressBar1.Invoke((Action)(() => this.progressBar1.Value += 1));
}
}
isRunning = false;
// Append to Log
ListLog.Invoke((Action)(() => this.ListLog.AppendText("Query generation is completed." + Environment.NewLine + "Data exporting started..." + Environment.NewLine)));
}
// Set foreign_key_checks = 1;
sb = new StringBuilder();
sb.Append("SET foreign_key_checks = 1;" + Environment.NewLine);
queryToExecute.Add("FK_Enabled", sb);
}
catch (Exception ex)
{
hasErrors = true;
ListLog.Invoke((Action)(() => this.ListLog.AppendText("Error in Query generation." + Environment.NewLine)));
MessageBox.Show(ex.Message);
}
}