/// <summary>
/// Gets the table size, row count etc. from the Database
/// </summary>
/// <param name="SQLDwConfig"></param>
public void RefreshTableListFromDB(MySettingsEnvironments SQLDwConfig)
{
string connstr = SQLDwConfig.GetConnectionString();
string sql;
string strFolderApplication = UtilGeneral.GetApplicationFolder();
string strFileTableList = strFolderApplication + "\\" + Constants.FOLDER_CONNECTION_TYPE + "\\" + SQLDwConfig.ConnectionType + "\\" + Constants.FILE_TABLE_LIST;
sql = System.IO.File.ReadAllText(strFileTableList);
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);
try
{
comm.CommandTimeout = 120;
SqlDataReader rdr = comm.ExecuteReader();
TableList.Clear();
TableList.Load(rdr);
rdr = null;
}
catch (Exception ex)
{
throw new System.Exception(ex.Message);
}
finally
{
conn.Close();
}
}
private void LoadConfiguration()
{
try
{
string strConfigFile = null;
if (cmbConfigFile.Items.Count > 1)
{
strConfigFile = cmbConfigFile.SelectedItem.ToString();
}
if (strConfigFile != NEW_CONFIG_FILE)
{
//Set the Current Configuration in Settings file for future persistence
new MySettingsUserConfig().SetCurrentConfiguration(MySettingsUserConfig.KEY_CONFIG_NAME, strConfigFile);
//Change the Application Configuration
MySettingsEnvironments config = new MySettingsEnvironments(strConfigFile);
frmParent.UpdateConfig(config);
this.Close();
}
}
catch (Exception ex)
{
UtilGeneral.ShowError(ex.Message);
}
}
private void SelectConfiguration()
{
string strConfig = null;
if (cmbConfigFile.Items.Count != 0)
{
strConfig = cmbConfigFile.SelectedItem.ToString();
}
MySettingsEnvironments CurrentConfiguration;
CurrentConfiguration = new MySettingsEnvironments(strConfig);
CurrentConfiguration.LoadConfigurationFile();
txtServerName.Text = CurrentConfiguration.ServerName;
txtDatabaseName.Text = CurrentConfiguration.DatabaseName;
txtUserName.Text = CurrentConfiguration.UserName;
txtPassword.Text = CurrentConfiguration.Password;
for (int i = 0; i < cmbConnectionType.Items.Count; i++)
{
if (cmbConnectionType.Items[i].ToString() == CurrentConfiguration.ConnectionType)
{
cmbConnectionType.SelectedIndex = i;
break;
}
}
for (int i = 0; i < cmbAuthentication.Items.Count; i++)
{
if (cmbAuthentication.Items[i].ToString() == CurrentConfiguration.AuthenticationType)
{
cmbAuthentication.SelectedIndex = i;
break;
}
}
txtBCPOutputFolder.Text = CurrentConfiguration.BCPOutputFolder;
txtAzStorageAccount.Text = CurrentConfiguration.AzStorageAccount;
txtAzContainer.Text = CurrentConfiguration.AzContainer;
txtAzSASToken.Text = CurrentConfiguration.AzSASToken;
txtAzStorageKey.Text = CurrentConfiguration.AzStorageKey;
txt7Zip.Text = CurrentConfiguration.File7Zip;
txtAZCopy.Text = CurrentConfiguration.FileAZCopy;
if (CurrentConfiguration.BCPOutputFormat == Constants.BCP_OUT_FORMAT_UTF8)
{
optBCPUTF8.Checked = true;
}
else
{
optBCPUnicode.Checked = true;
}
txtConfigName.Text = GetConfigName();
txtConfigName.Enabled = false;
}
/// <summary>
/// Creates a table list and refreshes values from database
/// </summary>
/// <param name="SQLDwConfig">ApplicationConfiguration to get the database connection</param>
public MyTableList(MySettingsEnvironments SQLDwConfig)
{
TableList = new DataTable();
UpdateTableName();
AddColumns();
RefreshTableListFromDB(SQLDwConfig);
}
private void SaveConfig()
{
string strConnectionType, strServerName, strDatabaseName, strAuthentication, strUserName, strPassword;
string strBCPOutputFolder, strBCPOutputFormat;
string strAzStorageAccount, strAzContainer, strAzSASToken, strAzStorageKey;
string strFile7Zip, strFileAZCopy;
string strConfigName;
if (cmbConfigFile.SelectedItem.ToString() == NEW_CONFIG_FILE)
{
strConfigName = txtConfigName.Text;
}
else
{
strConfigName = cmbConfigFile.SelectedItem.ToString();
strConfigName = strConfigName.ToString().Replace(".Config", "");
}
strConnectionType = cmbConnectionType.SelectedItem.ToString();
strServerName = txtServerName.Text;
strDatabaseName = txtDatabaseName.Text;
strAuthentication = cmbAuthentication.SelectedItem.ToString();
strUserName = txtUserName.Text;
strPassword = txtPassword.Text;
strBCPOutputFolder = txtBCPOutputFolder.Text;
strAzStorageAccount = txtAzStorageAccount.Text;
strAzContainer = txtAzContainer.Text;
strAzSASToken = txtAzSASToken.Text;
strAzStorageKey = txtAzStorageKey.Text;
strFile7Zip = txt7Zip.Text;
strFileAZCopy = txtAZCopy.Text;
if (optBCPUTF8.Checked)
{
strBCPOutputFormat = Constants.BCP_OUT_FORMAT_UTF8;
}
else
{
strBCPOutputFormat = Constants.BCP_OUT_FORMAT_Unicode;
}
MySettingsEnvironments cfg = new MySettingsEnvironments();
cfg.SaveConfiguration(strConfigName, strConnectionType, strServerName, strDatabaseName, strAuthentication, strUserName, strPassword,
strBCPOutputFolder, strBCPOutputFormat,
strAzStorageAccount, strAzContainer, strAzSASToken, strAzStorageKey,
strFile7Zip, strFileAZCopy);
cmbConfigFile.Items.Add(strConfigName + ".Config");
LoadInitialData(strConfigName);
}
/// <summary>
/// Initial routine for the form
/// </summary>
public frmMain()
{
InitializeComponent();
this.Top = 0;
this.Left = 0;
this.Width = Screen.PrimaryScreen.WorkingArea.Width;
this.Height = Screen.PrimaryScreen.WorkingArea.Height;
tlpMain.Width = this.Width;
dgvTables.Width = (int)(tlpMain.Width * 0.9);
tlpMain.Height = this.Height - (sbrMain.Height * 5);
dgvTables.Height = (int)(tlpMain.Height * 0.9);
tlpRight.Height = (int)(tlpMain.Height * 0.9);
this.Show();
try
{
CurrentConfigFileName = UtilGeneral.GetConfigValue(MySettingsUserConfig.KEY_CONFIG_NAME);
if (CurrentConfigFileName == "")
{
ShowConfigForm();
}
else
{
SQLDwConfig = new MySettingsEnvironments(CurrentConfigFileName);
if (!SQLDwConfig.ConfigFileExists())
{
UtilGeneral.ShowMessage("Configuration File do not exist. Please create a Configuration file to continue");
ShowConfigForm();
}
else
{
LoadData();
}
}
if (dgvTables.ColumnCount > 0)
{
dgvTables.Columns[MyTableList.TABLE_NAME].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dgvTables.Columns[MyTableList.INDEX_TYPE].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dgvTables.Columns[MyTableList.BCP_SPLIT_VALUE_TYPE].Width = 30;
}
}
catch (Exception ex)
{
UtilGeneral.ShowError(ex.Message);
}
}
public frmMigrateToSQLDW(MyTableList TableList, frmMain ParentForm, MySettingsEnvironments Config)
{
InitializeComponent();
frmParent = ParentForm;
this.dtTable = TableList;
this.SQLDwConfig = Config;
this.btnMigrate.Enabled = false;
lblCaption.Text = "";
SQLDwConnFolder = Properties.Settings.Default.ApplicationFolder + "\\" + Constants.SUB_FOLDER_SQLDW_CONN;
dgvMigrate.Columns.Clear();
dgvMigrate.Columns.Add(DGV_COL_STEPS, DGV_COL_STEPS);
DataGridViewCheckBoxColumn chk = new DataGridViewCheckBoxColumn();
dgvMigrate.Columns.Add(chk);
chk.HeaderText = DGV_COL_SELECT;
chk.Name = DGV_COL_SELECT;
gbxMigrationOption.Visible = false;
gbxMigrationOption.Width = gbxConnection.Width;
gbxMigrationOption.Height = gbxConnection.Height;
gbxMigrationOption.Left = gbxConnection.Left;
gbxMigrationOption.Top = gbxConnection.Top;
dgvMigrate.Width = gbxMigrationOption.Width - 30;
dgvMigrate.Height = gbxMigrationOption.Height - 30;
dgvMigrate.Rows.Add(Constants.MIGRATE_STEP_BCP_DATA);
dgvMigrate.Rows.Add(Constants.MIGRATE_STEP_UTF8_CONVERT);
dgvMigrate.Rows.Add(Constants.MIGRATE_STEP_COMPRESS_FILES);
dgvMigrate.Rows.Add(Constants.MIGRATE_STEP_BLOB_UPLOAD);
dgvMigrate.Rows.Add(Constants.MIGRATE_STEP_AZURE_ENV_PREPARE);
dgvMigrate.Rows.Add(Constants.MIGRATE_STEP_EXTERNAL_TABLE);
dgvMigrate.Rows.Add(Constants.MIGRATE_STEP_DATA_WAREHOUSE_TABLE);
dgvMigrate.Rows.Add(Constants.MIGRATE_STEP_INSERT);
dgvMigrate.Columns[DGV_COL_STEPS].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells;
dgvMigrate.Columns[DGV_COL_STEPS].ReadOnly = true;
foreach (DataGridViewRow row in dgvMigrate.Rows)
{
row.Cells[DGV_COL_SELECT].Value = true;
}
/*
* btnPrevious.Enabled = false;
* btnNext.Enabled = false;
* btnMigrate.Enabled = false;
*/
LoadInitialData();
}
public frmTableDetails(frmMain ParentForm, DataGridViewRow SelectedRow, MySettingsEnvironments CurrentConfig)
{
InitializeComponent();
SQLDwConfig = CurrentConfig;
this.frmParent = ParentForm;
this.TableRow = SelectedRow;
string strSchema = TableRow.Cells[MyTableList.SCHEMA_NAME].Value.ToString();
string strTable = TableRow.Cells[MyTableList.TABLE_NAME].Value.ToString();
firstload = true;
LoadDefaultValues(strSchema, strTable);
firstload = false;
}
/// <summary>
/// Creates a new ColumnList instance based on Schema and Table Name.
/// Uses the current configuration data for more details
/// </summary>
/// <param name="strSchemaName"></param>
/// <param name="strTableName"></param>
/// <param name="SQLDwConfig">The application configuration is passed to get all details related to currently selected configuration</param>
internal MyColumnList(string strSchemaName, string strTableName, MySettingsEnvironments SQLDwConfig)
{
ColumnList = new DataTable();
ColumnList.Columns.Add(COLUMN_NAME);
ColumnList.Columns.Add(DATA_TYPE);
ColumnList.Columns.Add(LENGTH, Type.GetType("System.Int32"));
ColumnList.Columns.Add(PRECISION, Type.GetType("System.Int32"));
ColumnList.Columns.Add(SCALE, Type.GetType("System.Int32"));
ColumnList.Columns.Add(IS_NULLABLE, Type.GetType("System.Int32"));
ColumnList.Columns.Add(ORDER, Type.GetType("System.Int32"));
ColumnList.Columns.Add(PKCOLUMN, Type.GetType("System.Int32"));
ColumnList.Columns.Add(PKDESCENDING, Type.GetType("System.Int32"));
// Get the connection string from the current selected configuration
string connstr = SQLDwConfig.GetConnectionString();
string strFolderApplication = UtilGeneral.GetApplicationFolder();
string strFileTableList = strFolderApplication + "\\" + Constants.FOLDER_CONNECTION_TYPE + "\\" + SQLDwConfig.ConnectionType + "\\" + Constants.FILE_COLUMN_LIST;
string sql = System.IO.File.ReadAllText(strFileTableList);
SqlConnection conn = new SqlConnection(connstr);
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);
comm.Parameters.Add(new SqlParameter("@SchemaName", SqlDbType.VarChar));
comm.Parameters["@SchemaName"].Value = strSchemaName;
comm.Parameters.Add(new SqlParameter("@TableName", SqlDbType.VarChar));
comm.Parameters["@TableName"].Value = strTableName;
try
{
SqlDataReader rdr = comm.ExecuteReader();
ColumnList.Load(rdr);
}
catch (Exception ex)
{
throw new System.Exception(ex.ToString());
}
finally
{
conn.Close();
}
}
/// <summary>
/// This routine updates a new configuration file from Configuation file and loads new data for it
/// </summary>
/// <param name="NewConfig"></param>
public void UpdateConfig(MySettingsEnvironments NewConfig)
{
CurrentConfigFileName = new MySettingsUserConfig().GetCurrentConfigurationName(MySettingsUserConfig.KEY_CONFIG_NAME);
SQLDwConfig = NewConfig;
LoadData();
}
/// <summary>
/// Returns the distinct values for any column from the database table
/// This is needed on the BCP Split screen to get the distinct list of values for splitting the BCP file
/// </summary>
/// <param name="SchemaName"></param>
/// <param name="TableName"></param>
/// <param name="ColumnName"></param>
/// <param name="AppConfig"></param>
/// <returns></returns>
public static DataTable GetColumnValues(string SchemaName, string TableName, string ColumnName, MySettingsEnvironments AppConfig)
{
DataTable dt = new DataTable();
string connstr = AppConfig.GetConnectionString();
SqlConnection conn = new SqlConnection(connstr);
string sql = "SELECT DISTINCT [" + ColumnName + "] Col FROM [" + SchemaName + "].[" + TableName + "] ORDER BY Col";
try
{
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);
comm.CommandTimeout = 0;
SqlDataReader rdr = comm.ExecuteReader();
dt.Load(rdr);
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
/// <summary>
/// Get Distinct list of Date values from database based for any Date format.
/// This is needed on the BCP Split screen to get the distinct list of values for splitting the BCP file
/// </summary>
/// <param name="SchemaName"></param>
/// <param name="TableName"></param>
/// <param name="ColumnName"></param>
/// <param name="DataType"></param>
/// <param name="TimePeriodType"></param>
/// <param name="AppConfig"></param>
/// <returns></returns>
public static DataTable GetColumnValuesWithDateFormat(string SchemaName, string TableName, string ColumnName, string DataType, int TimePeriodType, MySettingsEnvironments AppConfig)
{
DataTable dt = new DataTable();
string connstr = AppConfig.GetConnectionString();
SqlConnection conn = new SqlConnection(connstr);
string strColumnFormatted = null;
TableName = UtilGeneral.GetQuotedString(TableName);
string sql = "WITH AllDates AS ( ";
sql += "SELECT DISTINCT " + ColumnName + " FROM " + SchemaName + "." + TableName + " ";
sql += ")";
sql += "SELECT DISTINCT ";
strColumnFormatted = GetDateSQLString(ColumnName, DataType, TimePeriodType);
//In case there are invalid values, then use that value instead of formatting it
if (DataType == Constants.DATA_TYPE_INT)
{
strColumnFormatted = "CASE WHEN LEN(" + ColumnName + ") = 8 THEN " + strColumnFormatted + " ELSE CONVERT(VARCHAR, " + ColumnName + ") END ";
}
strColumnFormatted = strColumnFormatted + " " + ColumnName;
sql += strColumnFormatted;
sql += " FROM AllDates ORDER BY " + ColumnName;
try
{
conn.Open();
SqlCommand comm = new SqlCommand(sql, conn);
comm.CommandTimeout = 0;
SqlDataReader rdr = comm.ExecuteReader();
dt.Load(rdr);
return(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
public frmTableSplit(frmTableDetails ParentForm, string strSchema, string strTable, string strBCPCol, string strBCPValues, string strBCPValueType, MySettingsEnvironments CurrentConfig)
{
InitializeComponent();
this.frmParent = ParentForm;
SQLDwConfig = CurrentConfig;
SchemaName = strSchema;
TableName = strTable;
//Load Initial Values
lblName.Text = SchemaName + "." + TableName;
optSplitColumn.Checked = true;
optTPYear.Checked = true;
optValueTable.Checked = true;
dtpStart.Value = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);
dtpEnd.Value = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.DaysInMonth(DateTime.Now.Year, DateTime.Now.Month));
txtBCPValueType.Text = strBCPValueType;
//Load Column List
MyColumnList col = new MyColumnList(SchemaName, TableName, SQLDwConfig);
colList = col.GetColumnsList();
cmbColumns.Items.Clear();
foreach (DataRow row in colList.Rows)
{
cmbColumns.Items.Add(row[MyColumnList.COLUMN_NAME]);
}
strBCPCol = strBCPCol.Replace("[", "");
strBCPCol = strBCPCol.Replace("]", "");
if (strBCPCol != "")
{
cmbColumns.SelectedItem = strBCPCol;
}
else
{
cmbColumns.SelectedIndex = 0;
}
if (strBCPValueType == Constants.BCP_SPLIT_TYPE_VALUE)
{
optValueSelect.Checked = false;
}
else
{
optSplitTimePeriod.Checked = true;
optValueSelect.Checked = true;
}
switch (strBCPValueType)
{
case Constants.BCP_SPLIT_TYPE_TIME_YEAR:
optTPYear.Checked = true;
break;
case Constants.BCP_SPLIT_TYPE_TIME_MONTH:
optTPMonth.Checked = true;
break;
case Constants.BCP_SPLIT_TYPE_TIME_DATE:
case Constants.BCP_SPLIT_TYPE_TIME_INT_DATE:
optTPDay.Checked = true;
break;
default:
optSplitColumn.Checked = true;
break;
}
lstValues.Items.Clear();
if (strBCPValues != "")
{
string[] BCPVals = strBCPValues.Split(',');
foreach (string val in BCPVals)
{
lstValues.Items.Add(val);
}
if (strBCPValueType == Constants.BCP_SPLIT_TYPE_TIME_MONTH)
{
lstValues.Items.RemoveAt(0);
lstValues.Items.RemoveAt(lstValues.Items.Count - 1);
}
}
}
