private DataTable FillValuesFromDBDateFormatted()
{
string strColumnDataType = lblColType.Text;
switch (strColumnDataType)
{
case "int":
case "bigint":
break;
case "date":
case "datetime":
case "smalldatetime":
case "datetime2":
case "datetimeoffset":
break;
default:
throw new Exception("Only Date or Integer data types can be selected for Date");
}
string strColumnName = (string)cmbColumns.SelectedItem;
strColumnName = UtilGeneral.GetQuotedString(strColumnName);
int timeperiod = GetTimePeriodType();
DataTable dt = new DataTable();
dt = UtilDwQuery.GetColumnValuesWithDateFormat(SchemaName, TableName, strColumnName, strColumnDataType, timeperiod, SQLDwConfig);
return(dt);
}
private string GetColListForPrimaryKey()
{
string colstring = "";
foreach (DataRow col in ColumnList.Rows)
{
string colName;
colName = col[COLUMN_NAME].ToString();
colName = UtilGeneral.GetQuotedString(colName);
if (col[PKCOLUMN].ToString() == "1")
{
colstring += Constants.TAB + colName;
if (col[PKDESCENDING].ToString() == "1")
{
colstring += " DESC";
}
else
{
colstring += " ASC";
}
colstring += COLUMN_SEPERATOR + Environment.NewLine;
}
}
colstring = colstring.TrimEnd();
//Remove the last comma
if (colstring.EndsWith(COLUMN_SEPERATOR.Trim()))
{
colstring = colstring.Substring(0, colstring.Length - 1);
}
return(colstring);
}
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);
}
}
/// <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();
}
}
public void ShowConfigForm()
{
CurrentConfigFileName = UtilGeneral.GetConfigValue(MySettingsUserConfig.KEY_CONFIG_NAME);
frmConfig f = new frmConfig(CurrentConfigFileName, this);
f.ShowDialog();
}
public frmConfig(string strConfigName, frmMain ParentForm)
{
InitializeComponent();
string strSQLDWGeneratorMainFolder = Properties.Settings.Default.ApplicationFolder;
SQLDwConfigurationFolder = strSQLDWGeneratorMainFolder + "\\" + Constants.SUB_FOLDER_SETTINGS;
frmParent = ParentForm;
cmbAuthentication.Items.Clear();
cmbAuthentication.Items.Add(Constants.DB_AUTHENTICATION_INTEGRATED);
cmbAuthentication.Items.Add(Constants.DB_AUTHENTICATION_SQL);
cmbAuthentication.SelectedIndex = 0;
txtUserName.Text = "";
txtPassword.Text = "";
if (!Directory.Exists(SQLDwConfigurationFolder))
{
Directory.CreateDirectory(SQLDwConfigurationFolder);
}
DirectoryInfo d = new DirectoryInfo(SQLDwConfigurationFolder);
FileInfo[] Files = d.GetFiles("*.Config");
// Clear the main combo box and load the availble configuration files
cmbConfigFile.Items.Clear();
foreach (FileInfo file in Files)
{
cmbConfigFile.Items.Add(file.Name);
}
cmbConfigFile.Items.Add(NEW_CONFIG_FILE);
string strFolderApplication = UtilGeneral.GetApplicationFolder();
string strFolderConnectionType = strFolderApplication + "\\" + Constants.FOLDER_CONNECTION_TYPE;
string[] subdirectoryEntries = Directory.GetDirectories(strFolderConnectionType);
cmbConnectionType.Items.Clear();
foreach (string subdirectory in subdirectoryEntries)
{
DirectoryInfo di = new DirectoryInfo(subdirectory);
cmbConnectionType.Items.Add(di.Name);
}
for (int i = 0; i < cmbConnectionType.Items.Count; i++)
{
if (cmbConnectionType.Items[i].ToString() == "SQL Server")
{
cmbConnectionType.SelectedIndex = i;
break;
}
}
LoadInitialData(strConfigName);
}
/// <summary>
/// Get Column list for INSERT from External Table to DW table
/// </summary>
/// <returns></returns>
private string GetColListForINSERT()
{
string colString = "";
foreach (DataRow col in ColumnList.Rows)
{
string colName;
colName = col[MyColumnList.COLUMN_NAME].ToString();
colName = UtilGeneral.GetQuotedString(colName);
int length;
length = (int)col[MyColumnList.LENGTH];
switch (col[MyColumnList.DATA_TYPE].ToString())
{
case "varchar":
case "char":
case "nvarchar":
case "nchar":
colString += colName + COLUMN_SEPERATOR;
break;
case "xml":
colString += "";
break;
case "varbinary":
if (length == -1 || length > Constants.MAX_CHARACTER_NUMBER)
{
//If Varbinary(MAX) or VARBINARY(>8000) then ignore because SQL DW will not allow more than 8000 characters
colString += "";
}
else
{
colString += colName + COLUMN_SEPERATOR;
}
break;
default:
colString += colName + COLUMN_SEPERATOR;
break;
}
}
colString = colString.TrimEnd();
// Remove comma at the end
if (colString.EndsWith(COLUMN_SEPERATOR.Trim()))
{
colString = colString.Substring(0, colString.Length - 1);
}
return(colString);
}
private void btnLoadData_Click(object sender, EventArgs e)
{
try
{
LoadTableListFromDatabase();
}
catch (Exception ex)
{
UtilGeneral.ShowError(ex.Message);
}
}
private void btnDefault_Click(object sender, EventArgs e)
{
try
{
LoadDefaultValues();
}
catch (Exception ex)
{
UtilGeneral.ShowError(ex.Message);
}
}
private void btnConfig_Click(object sender, EventArgs e)
{
try
{
ShowConfigForm();
}
catch (Exception ex)
{
UtilGeneral.ShowError(ex.Message);
}
}
//Button Actions
private void btnSaveXML_Click(object sender, EventArgs e)
{
try
{
SaveListToXML();
}
catch (Exception ex)
{
UtilGeneral.ShowError(ex.Message);
}
}
/// <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);
}
}
private void btnGenerate_Click(object sender, EventArgs e)
{
try
{
GenerateScript();
}
catch (Exception ex)
{
UtilGeneral.ShowError(ex.Message);
}
}
private void btnReadXML_Click(object sender, EventArgs e)
{
try
{
ReadFromXML();
}
catch (Exception ex)
{
UtilGeneral.ShowError(ex.Message);
}
}
private void btnSave_Click(object sender, EventArgs e)
{
try
{
SaveUpdates();
this.Close();
}
catch (Exception ex)
{
UtilGeneral.ShowError(ex.Message);
}
}
/// <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();
}
}
//Form UI Actions
private void dgvTables_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
{
try
{
if (e.RowIndex != -1)
{
frmTableDetails f = new frmTableDetails(this, dgvTables.Rows[e.RowIndex], SQLDwConfig);
f.ShowDialog();
}
}
catch (Exception ex)
{
UtilGeneral.ShowError(ex.Message);
}
}
private void btnMigrate_Click(object sender, EventArgs e)
{
try
{
DataTable dtgv = GetDataTableFromGridView();
MyTableList dtTable = new MyTableList();
dtTable.ReplaceTableList(dtgv);
frmMigrateToSQLDW f = new frmMigrateToSQLDW(dtTable, this, SQLDwConfig);
f.ShowDialog();
}
catch (Exception ex)
{
UtilGeneral.ShowError(ex.Message);
}
}
private string GetColListForMerge(string MergeScriptType, string SourceAlias, string TargetAlias)
{
string colstring = "";
foreach (DataRow col in ColumnList.Rows)
{
string colName;
colName = col[COLUMN_NAME].ToString();
colName = UtilGeneral.GetQuotedString(colName);
switch (MergeScriptType)
{
case COL_SCRIPT_TYPE_MERGE_KEY_COL_JOIN:
if (col[PKCOLUMN].ToString() == "1")
{
colstring += Constants.TAB + SourceAlias + "." + colName + " = " + TargetAlias + "." + colName;
colstring += COLUMN_SEPERATOR + Environment.NewLine;
}
break;
case COL_SCRIPT_TYPE_MERGE_COL_UPDATE:
if (col[PKCOLUMN].ToString() != "1")
{
colstring += Constants.TAB + Constants.TAB + TargetAlias + "." + colName + " = " + SourceAlias + "." + colName;
colstring += COLUMN_SEPERATOR + Environment.NewLine;
}
break;
}
}
if (MergeScriptType == COL_SCRIPT_TYPE_MERGE_COL_UPDATE)
{
string strHasColumn = UtilGeneral.GetQuotedString(UtilGeneral.GetConfigValue(MySettingsUserConfig.KEY_HASH_COL_NAME));
colstring += Constants.TAB + Constants.TAB + Constants.MERGE_TARGET_ALIAS + "." + strHasColumn + " = " + Constants.MERGE_SOURCE_ALIAS + "." + strHasColumn;
colstring += COLUMN_SEPERATOR + Environment.NewLine;
}
colstring = colstring.TrimEnd();
//Remove the last comma
if (colstring.EndsWith(COLUMN_SEPERATOR.Trim()))
{
colstring = colstring.Substring(0, colstring.Length - 1);
}
return(colstring);
}
private void btnRemoveItem_Click(object sender, EventArgs e)
{
try
{
if (lstValues.Items.Count > 0)
{
while (lstValues.SelectedItems.Count > 0)
{
lstValues.Items.Remove(lstValues.SelectedItem);
}
}
}
catch (Exception ex)
{
UtilGeneral.ShowError(ex.Message);
}
}
public MySettingsUserConfig()
{
try
{
string appPath = UtilGeneral.GetApplicationFolder();
string configFileName = System.IO.Path.Combine(appPath, USER_SETTINGS_FILENAME);
ExeConfigurationFileMap configFileMap = new ExeConfigurationFileMap();
configFileMap.ExeConfigFilename = configFileName;
ConfigFile = ConfigurationManager.OpenMappedExeConfiguration(configFileMap, ConfigurationUserLevel.None);
}
catch (Exception ex)
{
throw ex;
}
}
/// <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();
}
}
// UI Actions
private void btnSave_Click(object sender, EventArgs e)
{
try
{
SaveConfig();
DialogResult result = MessageBox.Show("Configuration Saved. Do you want to load the Configuration now!!", "Save", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
if (result.Equals(DialogResult.OK))
{
LoadConfiguration();
}
}
catch (Exception ex)
{
UtilGeneral.ShowError(ex.Message);
}
}
//Anchor function to fill values in the list based on Selection
private void FillValues()
{
DataTable dtColValues = new DataTable();
try
{
// Chose how list will be populated
// (1) From Database (2) From Database but dates (3) From UI time selection
if (optValueTable.Checked)
{
if (optSplitColumn.Checked)
{
dtColValues = FillValuesFromDB();
}
else
{
dtColValues = FillValuesFromDBDateFormatted();
}
}
else
{
dtColValues = FillValuesTimeSeries();
}
// Fill the list box but do not allow if number of values are more than 500
lstValues.Items.Clear();
if (dtColValues.Rows.Count < 500)
{
foreach (DataRow row in dtColValues.Rows)
{
lstValues.Items.Add(row[0]);
}
}
else
{
UtilGeneral.ShowMessage("Too Many Values in the Column : " + dtColValues.Rows.Count);
}
}
catch (Exception ex)
{
UtilGeneral.ShowError(ex.ToString());
}
}
private void TestConnection()
{
string strConnString;
string strServer, strUser, strPassword, strDatabase, strConn = "";
strServer = txtAzServer.Text;
strUser = txtAzUser.Text;
strPassword = txtAzPassword.Text;
strDatabase = txtAzDatabase.Text;
strConn = strServer + "." + strDatabase;
strConnString = "Server=tcp:" + strServer + ";Database=" + strDatabase + ";Uid=" + strUser + ";Password="******"Connection successful");
MySettingsSQLDwConnList SQLDwList = new MySettingsSQLDwConnList(strConn);
//strPassword = UtilCryptor.Crypt(strPassword);
SQLDwList.SaveConnection(strServer, strDatabase, strUser, strPassword);
this.btnNext.Enabled = true;
this.AzConnString = strConnString;
}
catch
{
this.btnMigrate.Enabled = false;
this.AzConnString = "";
UtilGeneral.ShowError("Error connecting to database");
}
finally
{
conn.Close();
}
}
/// <summary>
/// Routine to save the selected values and return the result back to frmTableDetails
/// </summary>
private void SaveData()
{
if (lstValues.Items.Count == 0)
{
UtilGeneral.ShowMessage("No value selected. Please select the split values.");
return;
}
string colName = (string)cmbColumns.SelectedItem;
string colDataType = lblColType.Text;
string strDataType = "";
// Chose the data type
if (optSplitTimePeriod.Checked)
{
if (optTPYear.Checked)
{
if (colDataType == Constants.DATA_TYPE_INT)
{
strDataType = Constants.BCP_SPLIT_TYPE_TIME_INT_YEAR;
}
else
{
strDataType = Constants.BCP_SPLIT_TYPE_TIME_YEAR;
}
}
else if (optTPMonth.Checked)
{
if (colDataType == Constants.DATA_TYPE_INT)
{
strDataType = Constants.BCP_SPLIT_TYPE_TIME_INT_MONTH;
}
else
{
strDataType = Constants.BCP_SPLIT_TYPE_TIME_INT_MONTH;
}
}
else
{
if (colDataType == Constants.DATA_TYPE_INT)
{
strDataType = Constants.BCP_SPLIT_TYPE_TIME_INT_DATE;
}
else
{
strDataType = Constants.BCP_SPLIT_TYPE_TIME_DATE;
}
}
}
else
{
strDataType = Constants.BCP_SPLIT_TYPE_VALUE;
}
List <string> values = new List <string>();
if (optTPMonth.Checked)
{
// If month, then add the previous year as a failsafe to chose all data for this and prior period
int StartYear;
StartYear = Int32.Parse(lstValues.Items[0].ToString().Substring(0, 4)) - 1;
values.Add(StartYear.ToString());
}
for (int i = 0; i < lstValues.Items.Count; i++)
{
values.Add(lstValues.Items[i].ToString());
}
if (optTPMonth.Checked)
{
// If month, then add the next year as a failsafe to chose all data for this and later period
int EndYear;
EndYear = Int32.Parse(lstValues.Items[lstValues.Items.Count - 1].ToString().Substring(0, 4)) + 1;
values.Add(EndYear.ToString());
}
string result = String.Join(",", values);
// Update the BCP Data back to frmTableDetails
frmParent.UpdateBCPSelection(colName, result, strDataType);
this.Close();
}
/// <summary>
/// Get Column list string for Creating a Table
/// </summary>
/// <param name="TableType">Specify Table Type as DW Table or External Table</param>
/// <param name="ReplaceCRLF">Specify if you want to replace CRLF from text column. If so, the text columns on External Table will be created with double length</param>
/// <returns></returns>
private string GetColListForCREATETABLE(string TableType, string ReplaceCRLF)
{
string colstring = "";
foreach (DataRow col in ColumnList.Rows)
{
string datatype;
int length, precision, scale, isnullable, order;
string colName;
datatype = col[MyColumnList.DATA_TYPE].ToString();
length = (int)col[MyColumnList.LENGTH];
precision = (int)col[MyColumnList.PRECISION];
scale = (int)col[MyColumnList.SCALE];
isnullable = (int)col[MyColumnList.IS_NULLABLE];
order = (int)col[MyColumnList.ORDER];
colName = col[COLUMN_NAME].ToString();
int maxLength = 0;
if (datatype == "varchar" || datatype == "char" || datatype == "varbinary")
{
maxLength = Constants.MAX_CHARACTER_NUMBER;
}
else if (datatype == "nvarchar" || datatype == "nchar")
{
maxLength = Constants.MAX_CHARACTER_NUMBER / 2;
}
int colLength;
// If the length is MAX (-1) set the maximum length as 8000. If the column type is nvarchar or nchar then the max length is 4000
if (length == -1)
{
// If lenght is MAX, change the length to 8000
colLength = maxLength;
}
else
{
colLength = length;
}
if (TableType == Constants.TABLE_TYPE_EXTERNAL && datatype == "varbinary")
{
continue;
}
if (datatype == "xml")
{
continue;
}
colstring += Constants.TAB + UtilGeneral.GetQuotedString(colName) + " ";
switch (datatype)
{
case "varchar":
case "char":
case "nvarchar":
case "nchar":
case "binary":
case "varbinary":
if (TableType == Constants.TABLE_TYPE_STG || TableType == Constants.TABLE_TYPE_PERSISTENT)
{
if (length == -1)
{
colstring += datatype + "(MAX)";
}
else
{
colstring += datatype + "(" + colLength + ")";
}
}
else
{
//If ReplaceCRLF then increase the column length for External table as the BCP will out more characters <CRLF> for each CRLF
//These would however be replaced back in the INSERT script to main DWH table
if (ReplaceCRLF == Constants.YES && TableType == Constants.TABLE_TYPE_EXTERNAL)
{
colLength = colLength + Properties.Settings.Default.AddCharactersForCRLF;
}
if (colLength > maxLength)
{
colLength = maxLength;
}
colstring += datatype + "(" + colLength + ")";
}
break;
case "time":
case "datetimeoffset":
case "datetime2":
colstring += datatype + "(" + scale + ")";
break;
case "decimal":
case "numeric":
colstring += datatype + "(" + precision + "," + scale + ")";
break;
case "int":
case "bigint":
case "tinyint":
case "bit":
case "smallint":
case "smallmoney":
case "money":
case "float":
case "real":
case "date":
case "smalldatetime":
case "datetime":
case "image":
case "sysname":
colstring += datatype;
break;
case "uniqueidentifier":
// UniqueIdentifier columns are not supported in Exernal Table. Change this to varchar to allow storing the GuID data.
if (TableType == Constants.TABLE_TYPE_EXTERNAL)
{
colstring += "varchar(100)";
}
else
{
colstring += datatype;
}
break;
//SQL DW does not support geography and geometry. Change the column to varbinary(8000)
case "geometry":
case "geography":
if (TableType == Constants.TABLE_TYPE_EXTERNAL && TableType == Constants.TABLE_TYPE_DWH)
{
colstring += "varbinary(" + Constants.MAX_CHARACTER_NUMBER + ")";
}
else
{
colstring += datatype;
}
break;
}
if (isnullable == 0)
{
colstring += " NOT NULL";
}
else
{
colstring += " NULL";
}
colstring += COLUMN_SEPERATOR + Environment.NewLine;
}
colstring = colstring.TrimEnd();
//Remove the last comma
if (TableType != Constants.TABLE_TYPE_PERSISTENT)
{
if (colstring.EndsWith(COLUMN_SEPERATOR.Trim()))
{
colstring = colstring.Substring(0, colstring.Length - 1);
}
}
return(colstring);
}
/// <summary>
/// Get Column for SELECT SQL
/// </summary>
/// <param name="CRLFMode">Specify if CRLF is to be handled. Text Columns will have code to replace CRLF with [CRLF]</FR></param>
/// <returns></returns>
private string GetColListForSELECT(int CRLFMode, int DBMode, string SourceAlias)
{
string colString;
colString = "";
foreach (DataRow col in ColumnList.Rows)
{
string colName, dataType;
colName = col[MyColumnList.COLUMN_NAME].ToString();
dataType = col[MyColumnList.DATA_TYPE].ToString();
colName = UtilGeneral.GetQuotedString(colName);
int length;
length = (int)col[MyColumnList.LENGTH];
if (DBMode == DB_MODE_SQLDW)
{
switch (dataType)
{
case "varchar":
case "char":
case "nvarchar":
case "nchar":
switch (CRLFMode)
{
//While generating data for BCP replace CRLF with [CR][LF]
case MyColumnList.MODE_CRLF_REPLACE:
int colLength;
colLength = length + Properties.Settings.Default.AddCharactersForCRLF;
colString += "LEFT(REPLACE(REPLACE(" + colName + ", CHAR(13), '<CR>'), CHAR(10), '<LF>'), " + colLength + ") " + colName;
break;
//While generating SELECT for Insert from BLOB to Main revernt [CR][LF] with actual CRLF
case MyColumnList.MODE_CRLF_REVERT:
colString += "REPLACE(REPLACE(" + colName + ", '<CR>', CHAR(13)), '<LF>', CHAR(10))";
break;
default:
colString += colName;
break;
}
colString += COLUMN_SEPERATOR;
break;
case "xml":
// Ignore XML data columns.
colString += "";
break;
case "varbinary":
if (length == -1 || length > Constants.MAX_CHARACTER_NUMBER)
{
//If Varbinary(MAX) or VARBINARY(>8000) then ignore
colString += "";
}
else
{
// Ensure values are not more than max allowable length
colString += "LEFT(" + colName + ", " + Constants.MAX_CHARACTER_NUMBER + ")" + COLUMN_SEPERATOR;
}
break;
default:
colString += colName + COLUMN_SEPERATOR;
break;
}
}
else if (DBMode == DB_MODE_SQLDB)
{
if (SourceAlias == "")
{
colString += colName + COLUMN_SEPERATOR;
}
else
{
colString += SourceAlias + "." + colName + COLUMN_SEPERATOR;
}
}
}
colString = colString.TrimEnd();
// Remove comma at the end
if (colString.EndsWith(COLUMN_SEPERATOR.Trim()))
{
colString = colString.Substring(0, colString.Length - 1);
}
return(colString);
}
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();
}
}