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