private bool CopyRoutine()
{
try
{
const string csvFileName = "proc.csv";
string csvFullName = Path.Combine(DatabaseController.ProgramDataDirectory(), csvFileName);
if (File.Exists(csvFullName))
{
File.Delete(csvFullName);
}
var conn = DatabaseController.GenericConnection(null);
var paramCsvFile = new SqlParameter("@csv_file", csvFullName);
var queryBuilder = new StringBuilder();
queryBuilder.AppendLine("SELECT * FROM mysql.proc WHERE db = @source_db");
queryBuilder.AppendLine("INTO");
queryBuilder.AppendLine("OUTFILE @csv_file");
queryBuilder.AppendLine("FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n';");
var params1 = new SqlParameter[2];
params1[0] = new SqlParameter("@source_db", _viewModel.SourceDatabase);
params1[1] = paramCsvFile;
DatabaseController.ExecuteSelectQuery(conn, queryBuilder.ToString(), params1);
queryBuilder = new StringBuilder();
queryBuilder.AppendLine("LOAD DATA INFILE @csv_file");
queryBuilder.AppendLine("INTO TABLE mysql.proc FIELDS");
queryBuilder.AppendLine("TERMINATED BY ';' LINES TERMINATED BY '\r\n' SET db = @target_db;");
var params2 = new SqlParameter[2];
params2[0] = new SqlParameter("@target_db", _viewModel.TargetDatabase);
params2[1] = paramCsvFile;
DatabaseController.ExecuteSelectQuery(conn, queryBuilder.ToString(), params2);
if (File.Exists(csvFullName))
{
File.Delete(csvFullName);
}
return(true);
}
catch (Exception exception)
{
MessageWindow.ShowAlertMessage(exception.Message);
}
return(false);
}
private void CopyDatabase()
{
try
{
var connection = DatabaseController.GenericConnection(null);
var paramSourceDB = new SqlParameter("@source_db", _viewModel.SourceDatabase);
var queryTableNames = new StringBuilder();
queryTableNames.AppendLine("SELECT table_name FROM information_schema.tables");
queryTableNames.AppendLine("WHERE table_schema = @source_db");
DataTable tableNames = DatabaseController.ExecuteSelectQuery(connection,
queryTableNames.ToString(),
new[] { paramSourceDB });
int processed = 0;
int total = tableNames.Rows.Count;
foreach (DataRow dataRow in tableNames.Rows)
{
string tableName = dataRow["table_name"].ToString();
var paramTableName = new SqlParameter("@table_name", tableName);
// check if base table
// SELECT table_type FROM information_schema.tables where table_schema = '$old_news' and table_name = '$table'"
var queryTableType = new StringBuilder();
queryTableType.AppendLine("SELECT table_type FROM information_schema.tables");
queryTableType.AppendLine("WHERE table_schema = @source_db AND table_name = @table_name");
var tableTypeParams = new List <SqlParameter> {
paramSourceDB, paramTableName
};
DataTable tableTypes = DatabaseController.ExecuteSelectQuery(connection,
queryTableType.ToString(),
tableTypeParams.ToArray());
foreach (DataRow type in tableTypes.Rows)
{
string tableType = type["table_type"].ToString();
if (tableType == "BASE TABLE")
{
// CREATE TABLE targetDB.@tableName LIKE sourceDB.@tableName;
// INSERT INTO targetDB.@tableName SELECT * FROM sourceDB.@tableName;
var executeCreateTable = new StringBuilder();
executeCreateTable.AppendLine(string.Format("CREATE TABLE `{0}`.`{2}` LIKE `{1}`.`{2}`",
_viewModel.TargetDatabase,
_viewModel.SourceDatabase,
tableName));
DatabaseController.ExecuteNonQuery(connection, executeCreateTable.ToString());
// do not copy vouchers
switch (tableName)
{
case "or":
break;
case "jv":
break;
case "cv":
break;
default:
var executeCopyData = new StringBuilder();
executeCopyData.AppendLine(string.Format("INSERT INTO `{0}`.`{1}`",
_viewModel.TargetDatabase, tableName));
executeCopyData.AppendLine(string.Format("SELECT * FROM `{0}`.`{1}`",
_viewModel.SourceDatabase,
tableName));
DatabaseController.ExecuteInsertQuery(connection, executeCopyData.ToString());
break;
}
}
}
processed++;
_viewModel.Progress = Convert.ToInt32((processed * 100m) / total);
_worker.ReportProgress(_viewModel.Progress);
}
}
catch (Exception exception)
{
MessageWindow.ShowAlertMessage(exception.Message);
_worker.CancelAsync();
}
}