/// <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); } }