/// <summary>
/// General Routing to generate Script based on the script type selected
/// </summary>
private void GenerateScript()
{
sbpMessage.Text = "Generating Files....";
DataTable dtgv = GetDataTableFromGridView();
MyTableList dtTable = new MyTableList();
dtTable.ReplaceTableList(dtgv);
string strScriptType;
strScriptType = cmbDataType.SelectedItem.ToString();
MyScriptWriter ScriptWriter = new MyScriptWriter(SQLDwConfig);
switch (strScriptType)
{
case Constants.SCRIPT_TYPE_EXTERNAL_TABLE_CREATE:
ScriptWriter.GenerateTableCreateExternal(dtTable, false, false);
break;
case Constants.SCRIPT_TYPE_DWH_TABLE_CREATE:
ScriptWriter.GenerateTableCreateDWH(dtTable, false, false);
break;
case Constants.SCRIPT_TYPE_EXTERNAL_TABLE_CREATE_DROP:
ScriptWriter.GenerateTableCreateExternal(dtTable, true, false);
break;
case Constants.SCRIPT_TYPE_DWH_TABLE_CREATE_DROP:
ScriptWriter.GenerateTableCreateDWH(dtTable, true, false);
break;
case Constants.SCRIPT_TYPE_BCP:
ScriptWriter.GenerateBCPScript(dtTable, false, false);
break;
case Constants.SCRIPT_TYPE_INSERT_FROM_EXT_TABLE:
ScriptWriter.GenerateInsertFromExternalTable(dtTable, false);
break;
case Constants.SCRIPT_TYPE_EXTERNAL_TABLE_DROP:
ScriptWriter.GenerateTableDrop(dtTable, Constants.TABLE_TYPE_EXTERNAL);
break;
case Constants.SCRIPT_TYPE_DWH_TABLE_DROP:
ScriptWriter.GenerateTableDrop(dtTable, Constants.TABLE_TYPE_DWH);
break;
case Constants.SCRIPT_TYPE_DWH_TABLE_TRUNCATE:
ScriptWriter.GenerateTableTruncate(dtTable);
break;
case Constants.SCRIPT_TYPE_AZURE:
ScriptWriter.GeneratePSFileUTF8(false);
ScriptWriter.GeneratePSFileZip(false);
ScriptWriter.GenerateAZCopyFile(false);
ScriptWriter.GenerateAzurePrepSQL(false);
break;
case Constants.SCRIPT_TYPE_FILE_HEADER:
ScriptWriter.GenerateFileHeaderWithColumnNames(dtTable);
break;
case Constants.SCRIPT_TYPE_SELECT_SQL:
ScriptWriter.GenerateTableSELECTFile(dtTable);
break;
case Constants.SCRIPT_TYPE_TABLE_REFACTOR:
ScriptWriter.GenerateTableRefactor(dtTable);
break;
case Constants.SCRIPT_TYPE_BCP_EMPTY_FILE:
ScriptWriter.GenerateBCPScript(dtTable, true, false);
break;
case Constants.SCRIPT_TYPE_SQL_ETL:
ScriptWriter.GenerateTableCreateSTG(dtTable, false, false);
ScriptWriter.GenerateTableCreatePersistent(dtTable, false, false);
ScriptWriter.GenerateMergeSQL(dtTable, false);
ScriptWriter.GenerateAlterTableForPSA(dtTable, false);
break;
case Constants.SCRIPT_TYPE_ALL:
//Genreate All Scripts
ScriptWriter.GenerateTableCreateExternal(dtTable, false, false);
ScriptWriter.GenerateTableCreateDWH(dtTable, false, false);
ScriptWriter.GenerateBCPScript(dtTable, false, false);
ScriptWriter.GenerateInsertFromExternalTable(dtTable, false);
ScriptWriter.GenerateTableDrop(dtTable, Constants.TABLE_TYPE_EXTERNAL);
ScriptWriter.GenerateTableDrop(dtTable, Constants.TABLE_TYPE_DWH);
ScriptWriter.GenerateTableTruncate(dtTable);
ScriptWriter.GeneratePSFileUTF8(false);
ScriptWriter.GeneratePSFileZip(false);
ScriptWriter.GenerateAZCopyFile(false);
ScriptWriter.GenerateAzurePrepSQL(false);
break;
}
sbpMessage.Text = "File(s) Generated";
}
private void MigrateData()
{
MyScriptWriter ScriptWriter = new MyScriptWriter(SQLDwConfig);
string strScript = "";
MyUtilityPowerShell MPS = new MyUtilityPowerShell();
MyUtilityCommandLine CLU = new MyUtilityCommandLine();
if (CheckMigrationStep(Constants.MIGRATE_STEP_BCP_DATA))
{
ShowProgressMessage("Generating BCP Files");
strScript = ScriptWriter.GenerateBCPScript(dtTable, false, true);
CLU.ExecuteScript(strScript);
}
if (CheckMigrationStep(Constants.MIGRATE_STEP_UTF8_CONVERT))
{
ShowProgressMessage("Converting Files to UTF8");
strScript = ScriptWriter.GeneratePSFileUTF8(true);
MPS.ExecuteScript(strScript);
}
if (CheckMigrationStep(Constants.MIGRATE_STEP_COMPRESS_FILES))
{
ShowProgressMessage("Compressing Files");
strScript = ScriptWriter.GeneratePSFileZip(true);
MPS.ExecuteScript(strScript);
}
if (CheckMigrationStep(Constants.MIGRATE_STEP_BLOB_UPLOAD))
{
ShowProgressMessage("Copying Files to BLOB");
strScript = ScriptWriter.GenerateAZCopyFile(true);
CLU.ExecuteScript(strScript);
}
SqlConnection conn;
conn = new SqlConnection(AzConnString);
try
{
string SQL = "";
conn.Open();
SqlCommand comm;
if (CheckMigrationStep(Constants.MIGRATE_STEP_AZURE_ENV_PREPARE))
{
ShowProgressMessage("Preparing Azure Environment");
SQL = ScriptWriter.GenerateAzurePrepSQL(true);
comm = new SqlCommand(SQL, conn);
comm.ExecuteNonQuery();
}
if (CheckMigrationStep(Constants.MIGRATE_STEP_EXTERNAL_TABLE))
{
ShowProgressMessage("Creating External Tables");
SQL = ScriptWriter.GenerateTableCreateExternal(dtTable, true, true);
comm = new SqlCommand(SQL, conn);
comm.CommandTimeout = 0;
comm.ExecuteNonQuery();
}
if (CheckMigrationStep(Constants.MIGRATE_STEP_DATA_WAREHOUSE_TABLE))
{
ShowProgressMessage("Creating Data Warehouse Tables");
SQL = ScriptWriter.GenerateTableCreateDWH(dtTable, true, true);
comm = new SqlCommand(SQL, conn);
comm.CommandTimeout = 0;
comm.ExecuteNonQuery();
}
if (CheckMigrationStep(Constants.MIGRATE_STEP_INSERT))
{
ShowProgressMessage("Inserting data");
SQL = ScriptWriter.GenerateInsertFromExternalTable(dtTable, true);
comm = new SqlCommand(SQL, conn);
comm.CommandTimeout = 0;
comm.ExecuteNonQuery();
}
ShowProgressMessage("Finished");
}
catch (Exception ex)
{
ShowProgressMessage("Error");
UtilGeneral.ShowError(ex.Message);
}
finally
{
conn.Close();
}
}
