private void CascReadBtn_Click(object sender, EventArgs e)
{
frmSQLFilter frm = new frmSQLFilter();
frm.ShowDialog(this);
if (!con.Connected())
{
con.Connection();
}
if (con.Connected())
{
tableColumn = getTableColumns(ConfigurationManager.AppSettings["Table"]);
MySqlCommand query = new MySqlCommand(@"SELECT " + ConfigurationManager.AppSettings["Path"] + "," + ConfigurationManager.AppSettings["FileDataID"] + " FROM " +
ConfigurationManager.AppSettings["Table"] + " WHERE " + ConfigurationManager.AppSettings["Path"] + " LIKE '%" + SQLFilter + "%' ORDER BY " +
ConfigurationManager.AppSettings["FileDataID"], con.TheConnection);
MySqlDataReader reader = query.ExecuteReader();
dt = new DataTable();
dt.Columns.Add(ConfigurationManager.AppSettings["Path"]);
dt.Columns.Add(ConfigurationManager.AppSettings["FileDataID"]);
if (reader.HasRows)
{
while (reader.Read())
{
dt.Rows.Add(new Object[] { reader.GetString(0), reader.GetString(1) });
}
}
reader.Close();
dataGridView1.DataSource = dt;
}
}
private string getTableColumns(string tableName)
{
if (con.Connected())
{
sb = new StringBuilder();
MySqlCommand cmd = new MySqlCommand(@"SELECT column_name as 'Column Name'
FROM information_schema.columns
WHERE table_name = '" + tableName + "'", con.TheConnection);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
sb.Append(reader["Column Name"].ToString());
sb.Append(',');
}
reader.Close();
tableColumns = sb.ToString().Substring(0, sb.ToString().LastIndexOf(','));
return(tableColumns);
}
else
{
MessageBox.Show("No Active Connection");
con.Disconnect();
return(null);
}
}
private void ConnectionBtn_Click(object sender, EventArgs e)
{
con = new DBInstance(serverTxt.Text, databaseTxt.Text, usernameTxt.Text, passwordTxt.Text);
if (con.Connected())
{
lblWaiting.Text = "Connecting to " + con.ConnectionString().Substring(con.ConnectionString().IndexOf('=') + 1).Substring(0, con.ConnectionString().IndexOf(';') - (con.ConnectionString().IndexOf('=')) - 1);
con.Disconnect();
Connection();
}
else
{
con.Connection();
lblWaiting.Text = "Connecting to " + con.ConnectionString().Substring(con.ConnectionString().IndexOf('=') + 1).Substring(0, con.ConnectionString().IndexOf(';') - (con.ConnectionString().IndexOf('=')) - 1);
con.Disconnect();
Connection();
}
}
private void SQL_Script_Generation()
{
if (!con.Connected())
{
con.Connection();
}
if (con.Connected())
{
timer = new Stopwatch();
timer.Start();
sb = new StringBuilder();
if (File.Exists(pathFile))
{
File.Delete(pathFile);
}
File.AppendAllText(pathFile, "##Script " + TableName + " du " + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss\n\n"));
try
{
MySqlCommand cmd = new MySqlCommand(@"SELECT column_name as 'Column Name'
FROM information_schema.columns
WHERE table_name = '" + TableName + "'", con.TheConnection);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
sb.Append(reader["Column Name"].ToString());
sb.Append(',');
}
tableColumns = sb.ToString().Substring(0, sb.ToString().LastIndexOf(','));
tableColumnsArray = tableColumns.Split(',');
sb.Clear();
reader.Close();
cmd = new MySqlCommand("SELECT " + tableColumns + " FROM " + TableName, con.TheConnection);
reader = cmd.ExecuteReader();
dt = new DataTable();
dt.Load(reader);
nbRows = dt.Rows.Count;
reader.Close();
cmd = new MySqlCommand("SELECT " + tableColumns + " FROM " + TableName, con.TheConnection);
reader = cmd.ExecuteReader();
while (reader.Read())
{
for (int i = 0; i < tableColumnsArray.Length; i++)
{
sb.Append(reader[tableColumnsArray[i]] + ",");
}
queryResult = sb.ToString().Substring(0, sb.ToString().LastIndexOf(','));
csvQuery = "INSERT INTO `" + TableName + "` (" + tableColumns + ") VALUES (" + queryResult + ");\n";
File.AppendAllText(pathFile, csvQuery);
sb.Clear();
counter++;
if (timer.ElapsedMilliseconds % 2 == 0)
{
backgroundWorker1.ReportProgress(counter * 100 / nbRows);
}
}
reader.Close();
}
catch (Exception e)
{
MessageBox.Show(e.Message);
}
}
else
{
MessageBox.Show("No Active Connection", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}