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