private void toFile() // This is the actual method of the thread
{
int startRow = 0;
string tempQuery = "";
int counter = 0;
string fileName = txtOriginDatabase.Text + ".sql";
string _separator = " ";
if (!chkMinifySQL.Checked)
{
_separator = Environment.NewLine;
}
//ToDo: convert SQL Server Database Collation to MySql Database Collation
//obtaining sql server collation for database:
//select collation_name from sys.databases where name='yourdbname'
File.AppendAllText(txtDestinationFolder.Text + "\\" + fileName, "/* Please check lower_case_table_names value is set to zero in 'my.cnf' file. to prevent errors due to table/view name casing */" + Environment.NewLine);
if (optOverwriteDatabase.Checked)
{
startRow = 2;
//delete mysql database
tempQuery = "drop database `" + txtOriginDatabase.Text + "`;" + _separator;
File.AppendAllText(txtDestinationFolder.Text + "\\" + fileName, tempQuery);
UpdateStatus("Success", 0, 0);
//create mysql database
tempQuery = "create database `" + txtOriginDatabase.Text + "`;" + _separator + "use `" + txtOriginDatabase.Text + "`;" + _separator;
File.AppendAllText(txtDestinationFolder.Text + "\\" + fileName, tempQuery);
UpdateStatus("Success", 0, 1);
}
if (optCreateDatabase.Checked)
{
startRow = 1;
//create mysql database
tempQuery = "create database `" + txtOriginDatabase.Text + "`;" + _separator + "use `" + txtOriginDatabase.Text + "`;" + _separator;
File.AppendAllText(txtDestinationFolder.Text + "\\" + fileName, tempQuery);
UpdateStatus("Success", 0, 0);
}
if (optAppendDatabase.Checked)
{
startRow = 0;
//use mysql database
tempQuery = "use `" + txtOriginDatabase.Text + "`;" + _separator;
File.AppendAllText(txtDestinationFolder.Text + "\\" + fileName, tempQuery);
UpdateStatus("Success", 0, 0);
}
conversionHelper conversion;
if (optWindowsAuth.Checked)
{
conversion = new conversionHelper(txtOriginServer.Text, txtOriginDatabase.Text);
}
else
{
conversion = new conversionHelper(txtOriginServer.Text, txtOriginDatabase.Text, txtOriginUserName.Text, txtOriginPassword.Text);
}
//create list of table names
List <string> tableNamesList = new List <string>();
for (int i = 0; i < dgvObjects.Rows.Count; i++)
{
if (dgvObjects.Rows[i].Cells[0].Value.ToString() == "TABLE")
{
tableNamesList.Add(dgvObjects.Rows[i].Cells[1].Value.ToString());
}
}
//migrate objects
for (int i = 0; i < dgvObjects.Rows.Count; i++)
{
if (chkSaveEachTableAsFile.Checked)
{
fileName = dgvObjects.Rows[i].Cells[1].Value.ToString() + ".sql"; File.AppendAllText(txtDestinationFolder.Text + "\\" + fileName, "/* Please check lower_case_table_names value is set to zero in 'my.cnf' file. to prevent errors due to table/view name casing */" + Environment.NewLine);
}
//create schema
if (Convert.ToBoolean(dgvObjects.Rows[i].Cells[2].Value))
{
tempQuery = conversion.convertSchema(dgvObjects.Rows[i].Cells[0].Value.ToString(), dgvObjects.Rows[i].Cells[1].Value.ToString(), tableNamesList, _separator);
//ToDo: support more objects?
//create schema
File.AppendAllText(txtDestinationFolder.Text + "\\" + fileName, tempQuery + _separator);
UpdateStatus("Success", 0, startRow);
startRow++;
}
//copy data
if (Convert.ToBoolean(dgvObjects.Rows[i].Cells[3].Value))
{
//ToDo: support more objects?
List <string> listaQuery = new List <string>();
listaQuery = conversion.copyData(dgvObjects.Rows[i].Cells[0].Value.ToString(), dgvObjects.Rows[i].Cells[1].Value.ToString(), startRow, this, _separator);
counter = 1;
//insert data
foreach (string query in listaQuery)
{
UpdateStatus("Writing data " + counter + " of " + listaQuery.Count, 3, startRow);
File.AppendAllText(txtDestinationFolder.Text + "\\" + fileName, query + _separator);
counter++;
}
UpdateStatus("Success", 0, startRow);
startRow++;
}
}
//create table primary key
for (int i = 0; i < dgvObjects.Rows.Count; i++)
{
if (Convert.ToBoolean(dgvObjects.Rows[i].Cells[2].Value))
{
if (chkSaveEachTableAsFile.Checked)
{
fileName = dgvObjects.Rows[i].Cells[1].Value.ToString() + ".sql";
}
UpdateStatus("Creating index " + (i + 1) + " of " + dgvObjects.Rows.Count, 3, startRow);
tempQuery = conversion.makeIndex(dgvObjects.Rows[i].Cells[1].Value.ToString(), _separator);
//insert data
File.AppendAllText(txtDestinationFolder.Text + "\\" + fileName, tempQuery);
}
}
//create identity columns
for (int i = 0; i < dgvObjects.Rows.Count; i++)
{
if (Convert.ToBoolean(dgvObjects.Rows[i].Cells[2].Value))
{
if (chkSaveEachTableAsFile.Checked)
{
fileName = dgvObjects.Rows[i].Cells[1].Value.ToString() + ".sql";
}
UpdateStatus("Creating identity columns " + (i + 1) + " of " + dgvObjects.Rows.Count, 3, startRow);
tempQuery = conversion.makeIdentity(dgvObjects.Rows[i].Cells[1].Value.ToString(), _separator);
//execute command
File.AppendAllText(txtDestinationFolder.Text + "\\" + fileName, tempQuery);
}
}
//create foreign key
for (int i = 0; i < dgvObjects.Rows.Count; i++)
{
if (Convert.ToBoolean(dgvObjects.Rows[i].Cells[2].Value))
{
if (chkSaveEachTableAsFile.Checked)
{
fileName = dgvObjects.Rows[i].Cells[1].Value.ToString() + ".sql";
}
UpdateStatus("Creating foreign key " + (i + 1) + " of " + dgvObjects.Rows.Count, 3, startRow);
tempQuery = conversion.makeForeignKey(dgvObjects.Rows[i].Cells[1].Value.ToString(), _separator);
File.AppendAllText(txtDestinationFolder.Text + "\\" + fileName, tempQuery);
}
}
//report successful end of tasks (code 99)
UpdateStatus("success", 99, 99);
}
private void toDatabase() // This is the actual method of the thread
{
int startRow = 0;
string tempQuery = "";
int counter = 0;
string _separator = " ";
//ToDo: convert SQL Server Database Collation to MySql Database Collation
//obtaining sql server collation for database:
//select collation_name from sys.databases where name='yourdbname'
if (optOverwriteDatabase.Checked)
{
startRow = 2;
//delete mysql database
UpdateStatus("Deleting...", 3, 0);
if (MySQLConn.ExecuteNonQuery("drop database `" + txtDestinationDatabase.Text + "`;"))
{
UpdateStatus("Success", 0, 0);
}
else
{
UpdateStatus(MySQLConn.getLastError(), 1, 0);
return;
}
//create mysql database
UpdateStatus("Creating...", 3, 1);
if (MySQLConn.ExecuteNonQuery("create database `" + txtDestinationDatabase.Text + "`; use `" + txtDestinationDatabase.Text + "`;"))
{
UpdateStatus("Success", 0, 1);
}
else
{
UpdateStatus(MySQLConn.getLastError(), 1, 1);
return;
}
}
if (optCreateDatabase.Checked)
{
startRow = 1;
//create mysql database
UpdateStatus("Creating...", 3, 0);
if (MySQLConn.ExecuteNonQuery("create database `" + txtDestinationDatabase.Text + "`; use `" + txtDestinationDatabase.Text + "`;"))
{
UpdateStatus("Success", 0, 0);
}
else
{
UpdateStatus(MySQLConn.getLastError(), 1, 0);
return;
}
}
if (optAppendDatabase.Checked)
{
startRow = 0;
//use mysql database
if (MySQLConn.ExecuteNonQuery("use `" + txtDestinationDatabase.Text + "`;"))
{
UpdateStatus("Success", 0, 0);
}
else
{
UpdateStatus(MySQLConn.getLastError(), 1, 0);
return;
}
}
conversionHelper conversion;
if (optWindowsAuth.Checked)
{
conversion = new conversionHelper(txtOriginServer.Text, txtOriginDatabase.Text);
}
else
{
conversion = new conversionHelper(txtOriginServer.Text, txtOriginDatabase.Text, txtOriginUserName.Text, txtOriginPassword.Text);
}
//create list of table names
List <string> tableNamesList = new List <string>();
for (int i = 0; i < dgvObjects.Rows.Count; i++)
{
if (dgvObjects.Rows[i].Cells[0].Value.ToString() == "TABLE")
{
tableNamesList.Add(dgvObjects.Rows[i].Cells[1].Value.ToString());
}
}
//migrate objects
for (int i = 0; i < dgvObjects.Rows.Count; i++)
{
//create schema
if (Convert.ToBoolean(dgvObjects.Rows[i].Cells[2].Value))
{
tempQuery = conversion.convertSchema(dgvObjects.Rows[i].Cells[0].Value.ToString(), dgvObjects.Rows[i].Cells[1].Value.ToString(), tableNamesList, _separator);
//ToDo: support more objects?
//create schema
if (!MySQLConn.ExecuteNonQuery(tempQuery))
{
UpdateStatus(MySQLConn.getLastError(), 1, startRow);
//return;
}
else
{
UpdateStatus("Success", 0, startRow);
}
startRow++;
}
//copy data
if (Convert.ToBoolean(dgvObjects.Rows[i].Cells[3].Value))
{
//ToDo: support more objects?
List <string> listaQuery = new List <string>();
listaQuery = conversion.copyData(dgvObjects.Rows[i].Cells[0].Value.ToString(), dgvObjects.Rows[i].Cells[1].Value.ToString(), startRow, this, _separator);
counter = 1;
//insert data
foreach (string query in listaQuery)
{
UpdateStatus("Writing data " + counter + " of " + listaQuery.Count, 3, startRow);
if (!MySQLConn.ExecuteNonQuery(tempQuery))
{
UpdateStatus(MySQLConn.getLastError(), 1, startRow);
//return;
}
else
{
UpdateStatus("Success", 0, startRow);
}
counter++;
}
//UpdateStatus("Success", 0, startRow);
startRow++;
}
}
//create table primary key
for (int i = 0; i < dgvObjects.Rows.Count; i++)
{
if (Convert.ToBoolean(dgvObjects.Rows[i].Cells[2].Value))
{
UpdateStatus("Creating index " + (i + 1) + " of " + dgvObjects.Rows.Count, 3, startRow);
tempQuery = conversion.makeIndex(dgvObjects.Rows[i].Cells[1].Value.ToString(), _separator);
//insert data
if (tempQuery.Trim() != "")
{
if (!MySQLConn.ExecuteNonQuery(tempQuery))
{
UpdateStatus(MySQLConn.getLastError(), 1, startRow);
//return;
}
}
}
}
//create identity columns
for (int i = 0; i < dgvObjects.Rows.Count; i++)
{
if (Convert.ToBoolean(dgvObjects.Rows[i].Cells[2].Value))
{
UpdateStatus("Creating identity columns " + (i + 1) + " of " + dgvObjects.Rows.Count, 3, startRow);
tempQuery = conversion.makeIdentity(dgvObjects.Rows[i].Cells[1].Value.ToString(), _separator);
//execute command
if (tempQuery.Trim() != "")
{
if (!MySQLConn.ExecuteNonQuery(tempQuery))
{
UpdateStatus(MySQLConn.getLastError(), 1, startRow);
//return;
}
}
}
}
//create foreign key
for (int i = 0; i < dgvObjects.Rows.Count; i++)
{
if (Convert.ToBoolean(dgvObjects.Rows[i].Cells[2].Value))
{
UpdateStatus("Creating foreign key " + (i + 1) + " of " + dgvObjects.Rows.Count, 3, startRow);
tempQuery = conversion.makeForeignKey(dgvObjects.Rows[i].Cells[1].Value.ToString(), _separator);
if (tempQuery.Trim() != "")
{
if (!MySQLConn.ExecuteNonQuery(tempQuery))
{
UpdateStatus(MySQLConn.getLastError(), 1, startRow);
//return;
}
}
}
}
//report successful end of tasks (code 99)
UpdateStatus("success", 99, 99);
}