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