예제 #1
0
        /// <summary>
        /// Get output part of SCD sql command
        /// </summary>
        /// <param name="tempTableName">SCD temporary table name</param>
        /// <returns>Output part of SCD sql command</returns>
        private string GetOutputPart(string tempTableName)
        {
            string result = TEMPLATE_OUTPUT;

            ScdColumnList scdListBKs        = new ScdColumnList();
            ScdColumnList scdListAttributes = new ScdColumnList();

            foreach (string scdTableName in _scdList.Keys)
            {
                scdListBKs.AddList(_scdList[scdTableName].BkList);
                scdListAttributes.AddList(_scdList[scdTableName].AttributeList);
            }

            result = result.Replace("<tempTableName_scd>", tempTableName);
            result = result.Replace("<attribute_select>", SCDHelper.GetSqlAttributeListDoubled(scdListAttributes.ScdColumns, "src.", "DELETED.", "", "", 2));
            result = result.Replace("<vaild_from_select>", SCDHelper.GetSqlValidFrom(_scdList, false, "src.", 2));
            result = result.Replace("<attribute_insert>", SCDHelper.GetSqlAttributeListDoubled(scdListAttributes.ScdColumns, "", "",
                                                                                               SCDConfiguration.POSTFIX_COLUMN_QUELLE, SCDConfiguration.POSTFIX_COLUMN_DWH_VOR_UPDATE, 2));
            result = result.Replace("<vaild_from_insert>", SCDHelper.GetSqlValidFrom(_scdList, true, "", 2));
            result = result.Replace("<bk_output>", SCDHelper.GetSqlBkList(scdListBKs.ScdColumns, "INSERTED.", 2));
            result = result.Replace("<bk_into>", SCDHelper.GetSqlBkList(scdListBKs.ScdColumns, _prefixFK, "", 2));
            result = result.Replace("<FK_ID>", _prefixFK + "ID");

            return(result);
        }
예제 #2
0
        /// <summary>
        /// Get sql coomand for creating SCD temporary table
        /// </summary>
        /// <param name="tempTableName">SCD temporary table name</param>
        /// <param name="enableIndexOnSCD">Disable SCD temporary table nonclusterd index?</param>
        /// <returns>Sql coomand for creating SCD temporary table</returns>
        private string GetCreateTempTable(string tempTableName, bool enableIndexOnSCD)
        {
            string result          = TEMPLATE_CREATE_TEMP_TABLE.Replace("<tempTableName_scd>", tempTableName);
            string indexBk         = TEMPLATE_CREATE_CLUSTERD_INDEX_ON_TEMP_TABLE.Replace("<tempTableName_scd>", tempTableName);
            string indexAttributes = "";

            ScdColumnList scdListBKs        = new ScdColumnList();
            ScdColumnList scdListAttributes = new ScdColumnList();

            foreach (string scdTableName in _scdList.Keys)
            {
                scdListBKs.AddList(_scdList[scdTableName].BkList);
                scdListAttributes.AddList(_scdList[scdTableName].AttributeList);
            }

            result = result.Replace("<bk>", SCDHelper.GetSqlBkListWithDataType(scdListBKs.ScdColumns, _prefixFK, "", 2));
            result = result.Replace("<attributes>", SCDHelper.GetSqlAttributeListWithDataType(scdListAttributes.ScdColumns, SCDConfiguration.POSTFIX_COLUMN_QUELLE, SCDConfiguration.POSTFIX_COLUMN_DWH_VOR_UPDATE, 2));
            result = result.Replace("<valid_from>", SCDHelper.GetSqlValidFromWithDataType(_scdList, 2));
            result = result.Replace("<FK_ID>", _prefixFK + "ID");

            indexBk = indexBk.Replace("<index_columns>", "  " +
                                      SCDHelper.GetSqlBkList(scdListBKs.ScdColumns, _prefixFK, "", 2).Substring(3)); //Substring(3):  Komma an 3. Stelle entfernen

            if (enableIndexOnSCD)
            {
                foreach (SCDColumn scdColumn in scdListAttributes.ScdColumns)
                {
                    string index = TEMPLATE_CREATE_NONCLUSTERD_INDEX_ON_TEMP_TABLE
                                   .Replace("<tempTableName_scd>", tempTableName)  //.TrimStart("[".ToCharArray()).TrimEnd("]".ToCharArray()))
                                   .Replace("<index_name>", scdColumn.ColumnName);

                    List <SCDColumn> scdColumns = new List <SCDColumn>();
                    scdColumns.Add(scdColumn);

                    string columns = SCDHelper.GetSqlAttributeListDoubled(scdColumns, "", "", SCDConfiguration.POSTFIX_COLUMN_QUELLE, SCDConfiguration.POSTFIX_COLUMN_DWH_VOR_UPDATE, 2);
                    columns          = "  " + columns.Substring(3); // Komma an 3. Stelle entfernen
                    index            = index.Replace("<index_columns>", columns);
                    indexAttributes += index + Environment.NewLine + Environment.NewLine;
                }
            }

            return(result + Environment.NewLine + Environment.NewLine + indexBk + Environment.NewLine + Environment.NewLine + indexAttributes);
        }