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