Exemplo n.º 1
0
 private void AddExistingForeignKeysTable(Table aTable, DsDwTableMap_M aDsDwTableMap)
 {
     foreach (ForeignKey aForeignKey in aTable.ForeignKeys)
     {
         aDsDwTableMap.DsDwForeignKeyList.Add(new DsDwForeignKey_M(aForeignKey));
     }
 }
        //MATCHED AND

        private string GenerateScd1MatchedAnd(DsDwTableMap_M aDsDwTableMap)
        {
            string aResultString = String.Empty;

            foreach (DsDwColumnMap_M aDsDwColumnMap in aDsDwTableMap.DsDwColumnMapList.Where(cm => cm.Include &&
                                                                                             (cm.Transformation == DsDwColumnTransformation.Scd1 ||
                                                                                              cm.Transformation == DsDwColumnTransformation.ForeignKey)))
            {
                if (aDsDwColumnMap.Transformation == DsDwColumnTransformation.Scd1)
                {
                    aResultString = aResultString +
                                    string.Format(
                        "    ([source].[{0}] <> [target].[{1}] OR{2}    ([source].[{0}] IS NULL AND [target].[{1}] IS NOT NULL) OR{2}    ([source].[{0}] IS NOT NULL AND [target].[{1}] IS NULL)) OR{2}",
                        aDsDwColumnMap.DsColumn.Name, aDsDwColumnMap.DwColumn.Name, Environment.NewLine);
                }
                else
                {
                    //ForeignKey
                    aResultString = aResultString +
                                    string.Format(
                        "    ([source].[{0}] <> [target].[{1}] OR{2}    ([source].[{0}] IS NULL AND [target].[{1}] IS NOT NULL) OR{2}    ([source].[{0}] IS NOT NULL AND [target].[{1}] IS NULL)) OR{2}",
                        aDsDwColumnMap.DwColumn.Name, aDsDwColumnMap.DwColumn.Name, Environment.NewLine);
                }
            }
            aResultString = RemoveAtEnd(aResultString, " OR" + Environment.NewLine);
            return(aResultString);
        }
        private string GenerateScd1SourceSelect(DsDwTableMap_M aDsDwTableMap)
        {
            string aResultString = String.Empty;

            //add normal columns
            foreach (DsDwColumnMap_M aDsDwColumnMap in aDsDwTableMap.DsDwColumnMapList.Where(cm => cm.Include &&
                                                                                             (cm.Transformation == DsDwColumnTransformation.Scd1 ||
                                                                                              cm.Transformation == DsDwColumnTransformation.BusinesKey)))
            {
                aResultString = aResultString + string.Format("        [{0}],", aDsDwColumnMap.DsColumn.Name) + Environment.NewLine;
            }

            DsDwTableMap_M  aDsDwTableMapPrimaryKey;
            DsDwColumnMap_M aDsDwColumnMapReferencedSurogatePrimaryKey;
            DsDwColumnMap_M aDsDwColumnMapReferencedPrimaryKey;

            //Add foreign keys
            foreach (DsDwColumnMap_M aDsDwColumnMapForeignKey in aDsDwTableMap.DsDwColumnMapList.Where(cm => cm.Include &&
                                                                                                       cm.Transformation == DsDwColumnTransformation.ForeignKey))
            {
                aDsDwTableMapPrimaryKey = aDsDwColumnMapForeignKey.DwForeignKeyReferencedTableMap;
                if (aDsDwTableMapPrimaryKey != null)
                {
                    aDsDwColumnMapReferencedSurogatePrimaryKey = aDsDwTableMapPrimaryKey.DsDwColumnMapList.FirstOrDefault(
                        cm => cm.Include && cm.Transformation == DsDwColumnTransformation.SurrogateKey);

                    //for views
                    aDsDwColumnMapReferencedPrimaryKey = aDsDwTableMapPrimaryKey.DsDwColumnMapList.FirstOrDefault(
                        cm => cm.Include && cm.DsReferencedColumn != null && cm.DsReferencedColumn.InPrimaryKey);
                    if (aDsDwColumnMapReferencedPrimaryKey == null)
                    {
                        aDsDwColumnMapReferencedPrimaryKey = aDsDwTableMapPrimaryKey.DsDwColumnMapList.FirstOrDefault(
                            cm => cm.Include && cm.DsColumn != null && cm.DsColumn.InPrimaryKey);
                    }



                    if (aDsDwColumnMapReferencedSurogatePrimaryKey != null && aDsDwColumnMapReferencedPrimaryKey != null)
                    {
                        aResultString = aResultString +
                                        string.Format(
                            "        (SELECT [{0}] FROM [{1}].[{2}].[{3}] WHERE [{1}].[{2}].[{3}].[{4}] = [ST].[{5}]) AS [{6}],",
                            aDsDwColumnMapReferencedSurogatePrimaryKey.DwColumn.Name, //0
                            DwDbName,                                                 //1
                            aDsDwTableMapPrimaryKey.DwSchemaName,                     //2
                            aDsDwTableMapPrimaryKey.DwTableName,                      //3
                            aDsDwColumnMapReferencedPrimaryKey.DwColumn.Name,         //4
                            aDsDwColumnMapForeignKey.DsColumn.Name,                   //5
                            aDsDwColumnMapForeignKey.DwColumn.Name                    //6
                            ) + Environment.NewLine;
                    }
                }
            }
            aResultString = RemoveAtEnd(aResultString, "," + Environment.NewLine);

            return(aResultString);
        }
        private string GenerateScd1Target(DsDwTableMap_M aDsDwTableMap)
        {
            string aResultString = String.Empty;

            aResultString = string.Format("[{0}].[{1}].[{2}]",
                                          DwDbName,
                                          aDsDwTableMap.DwSchemaName,
                                          aDsDwTableMap.DwTableName);
            return(aResultString);
        }
        private string GenerateScd1Insert(DsDwTableMap_M aDsDwTableMap)
        {
            string aResultString = "(" + Environment.NewLine;

            foreach (DsDwColumnMap_M aDsDwColumnMap in aDsDwTableMap.DsDwColumnMapList.Where(cm => cm.Include &&
                                                                                             (cm.Transformation == DsDwColumnTransformation.Scd1 ||
                                                                                              cm.Transformation == DsDwColumnTransformation.ForeignKey ||
                                                                                              cm.Transformation == DsDwColumnTransformation.BusinesKey)))
            {
                aResultString = aResultString + string.Format("    [{0}],{1}", aDsDwColumnMap.DwColumn.Name, Environment.NewLine);
            }
            DsDwColumnMap_M aDsDwColumnMapIsDeleted = aDsDwTableMap.DsDwColumnMapList.FirstOrDefault(cm => cm.Include && cm.Transformation == DsDwColumnTransformation.IsDeleted);

            if (aDsDwColumnMapIsDeleted != null)
            {
                aResultString = aResultString + string.Format("    [{0}],{1}", aDsDwColumnMapIsDeleted.DwColumn.Name, Environment.NewLine);
            }
            aResultString = RemoveAtEnd(aResultString, "," + Environment.NewLine);
            aResultString = aResultString + string.Format("{0}){0}VALUES{0}({0}", Environment.NewLine);
            foreach (DsDwColumnMap_M aDsDwColumnMap in aDsDwTableMap.DsDwColumnMapList.Where(cm => cm.Include &&
                                                                                             (cm.Transformation == DsDwColumnTransformation.Scd1 ||
                                                                                              cm.Transformation == DsDwColumnTransformation.ForeignKey ||
                                                                                              cm.Transformation == DsDwColumnTransformation.BusinesKey)))
            {
                if (aDsDwColumnMap.Transformation == DsDwColumnTransformation.ForeignKey)
                {
                    aResultString = aResultString +
                                    string.Format("    [source].[{0}],{1}", aDsDwColumnMap.DwColumn.Name,
                                                  Environment.NewLine);
                }
                else
                {
                    aResultString = aResultString +
                                    string.Format("    [source].[{0}],{1}", aDsDwColumnMap.DsColumn.Name,
                                                  Environment.NewLine);
                }
            }
            if (aDsDwColumnMapIsDeleted != null)
            {
                aResultString = aResultString + "    0," + Environment.NewLine;
            }
            aResultString = RemoveAtEnd(aResultString, "," + Environment.NewLine);
            aResultString = aResultString + Environment.NewLine + ")";
            if (aDsDwColumnMapIsDeleted != null)
            {
                aResultString = aResultString + Environment.NewLine + string.Format(@"WHEN NOT MATCHED BY SOURCE AND
	([{0}] = 0 OR [{0}] IS NULL)
THEN UPDATE
SET
	[{0}] = 1
", aDsDwColumnMapIsDeleted.DwColumn.Name);
            }
            aResultString = aResultString + ";" + Environment.NewLine;
            return(aResultString);
        }
        private string GenerateScd1Source(DsDwTableMap_M aDsDwTableMap)
        {
            string aResultString = String.Empty;

            aResultString = string.Format("[{0}].[{1}].[{2}].[{3}]",
                                          aDsDwTableMap.DsServerName,
                                          aDsDwTableMap.DsDatabaseName,
                                          aDsDwTableMap.DsSchemaName,
                                          aDsDwTableMap.DsTableName);
            return(aResultString);
        }
        public string GenerateScd1(DsDwTableMap_M aDsDwTableMap)
        {
            string aResultString = String.Empty;

            if (aDsDwTableMap != null)
            {
                if ((aDsDwTableMap.DsDwColumnMapList.Count(cm => cm.Include && cm.Transformation == DsDwColumnTransformation.Scd1) > 0) &&
                    (aDsDwTableMap.DsDwColumnMapList.Count(cm => cm.Include && cm.Transformation == DsDwColumnTransformation.BusinesKey) == 1))
                {
                    aResultString = string.Format(@"

/******************************************************************************************************
*  Level {7} {2} -> {0}                   
*******************************************************************************************************/


MERGE {0} as [target]
USING
(
    SELECT
{1}
    FROM {2} AS [st]
) as [source]
ON
(
    {3}
)
WHEN MATCHED AND
(
{4}
)
THEN UPDATE
SET
{5}
WHEN NOT MATCHED BY TARGET
THEN INSERT
{6}",

                                                  GenerateScd1Target(aDsDwTableMap),           //0
                                                  GenerateScd1SourceSelect(aDsDwTableMap),     //1
                                                  GenerateScd1Source(aDsDwTableMap),           //2
                                                  GenerateScd1BusinessKeyMatch(aDsDwTableMap), //3
                                                  GenerateScd1MatchedAnd(aDsDwTableMap),       //4
                                                  GenerateScd1UpdateSet(aDsDwTableMap),        //5
                                                  GenerateScd1Insert(aDsDwTableMap),           //6
                                                  aDsDwTableMap.Level.ToString("D3")           //7
                                                  );
                }
            }
            return(aResultString);
        }
        private string GenerateScd1BusinessKeyMatch(DsDwTableMap_M aDsDwTableMap)
        {
            string          aResultString             = String.Empty;
            DsDwColumnMap_M aDsDwColumnMapBusinessKey =
                aDsDwTableMap.DsDwColumnMapList.FirstOrDefault(
                    cm => cm.Include && cm.Transformation == DsDwColumnTransformation.BusinesKey);

            if (aDsDwColumnMapBusinessKey != null)
            {
                aResultString = string.Format("    [source].[{0}] = [target].[{1}]",
                                              aDsDwColumnMapBusinessKey.DsColumn.Name,
                                              aDsDwColumnMapBusinessKey.DwColumn.Name);
            }
            return(aResultString);
        }
Exemplo n.º 9
0
        private void DwGenerateForeignKey(DsDwColumnMap_M aDsDwColumnMap)
        {
            if (aDsDwColumnMap != null && aDsDwColumnMap.Parent != null &&
                aDsDwColumnMap.DwForeignKeyReferencedTableMap != null &&
                !string.IsNullOrWhiteSpace(aDsDwColumnMap.DwForeignKeyReferencedTableMap.DwTableName))
            {
                Table tbea;
                tbea =
                    DwDb.Tables.Cast <Table>()
                    .FirstOrDefault(tb => tb.Name.ToLower() == aDsDwColumnMap.Parent.DwTableName.ToLower() &&
                                    tb.Schema.ToLower() == aDsDwColumnMap.Parent.DwSchemaName.ToLower());

                DsDwTableMap_M aDwFkReferencedTableMap = aDsDwColumnMap.DwForeignKeyReferencedTableMap;
                //DsDwMap.DsDwTableMapList.FirstOrDefault(
                //    tm =>
                //        tm.DwTableName.ToLower() == aDsDwColumnMap.DwForeignKeyReferencedTableMap.DwTableName.ToLower() &&
                //        tm.DwSchemaName.ToLower() == aDsDwColumnMap.DwForeignKeyReferencedTableMap.DwSchemaName.ToLower());

                DsDwColumnMap_M aDsDwPrimaryKeyColumnMap = null;
                if (aDwFkReferencedTableMap != null)
                {
                    aDsDwPrimaryKeyColumnMap =
                        aDwFkReferencedTableMap.DsDwColumnMapList.FirstOrDefault(
                            cm => cm.Transformation == DsDwColumnTransformation.SurrogateKey);
                }
                if (tbea != null && aDsDwPrimaryKeyColumnMap != null)
                {
                    //Define a Foreign Key object variable by supplying the EmployeeDepartmentHistory as the parent table and the foreign key name in the constructor.
                    ForeignKey fk;
                    fk = new ForeignKey(tbea, string.Format("FK_{0}_{1}", aDsDwColumnMap.Parent.DwTableName, aDsDwColumnMap.DwForeignKeyReferencedTableMap.DwTableName));
                    //Add BusinessEntityID as the foreign key column.
                    ForeignKeyColumn fkc;
                    fkc = new ForeignKeyColumn(fk, aDsDwColumnMap.DwColumn.Name, aDsDwPrimaryKeyColumnMap.DwColumn.Name);
                    fk.Columns.Add(fkc);
                    //Set the referenced table and schema.
                    fk.ReferencedTable       = aDsDwColumnMap.DwForeignKeyReferencedTableMap.DwTableName;
                    fk.ReferencedTableSchema = aDsDwColumnMap.DwForeignKeyReferencedTableMap.DwSchemaName;
                    //Create the foreign key on the instance of SQL Server.
                    fk.Create();
                }
            }
        }
Exemplo n.º 10
0
 private void FillDataSet(DsDwTableMap_M aDsDwTableMap)
 {
     try
     {
         string           strCommand = "Select * from " + aDsDwTableMap.DwSchemaTableName + " WHERE 1=0";
         OleDbDataAdapter objEmpData = new OleDbDataAdapter(strCommand, _CBOleConnection);
         DataTable[]      dataTables = objEmpData.FillSchema(_CbDataSet, SchemaType.Mapped, aDsDwTableMap.DwTableName);
         DataTable        dataTable  = dataTables[0];
         dataTable.ExtendedProperties.Add("TableType", "Table");
         dataTable.ExtendedProperties.Add("DbSchemaName", aDsDwTableMap.DsSchemaName);
         dataTable.ExtendedProperties.Add("DbTableName", aDsDwTableMap.DwTableName);
         dataTable.ExtendedProperties.Add("FriendlyName", aDsDwTableMap.DwTableName);
         dataTable  = null;
         dataTables = null;
         objEmpData = null;
     }
     catch (Exception ex)
     {
         AppendLogLine("Error in Creating a DataSourceView - FillDataSet. Error Message -> " + ex.Message);
         throw;
     }
 }
        private string GenerateScd1UpdateSet(DsDwTableMap_M aDsDwTableMap)
        {
            string aResultString = String.Empty;

            foreach (DsDwColumnMap_M aDsDwColumnMap in aDsDwTableMap.DsDwColumnMapList.Where(cm => cm.Include &&
                                                                                             (cm.Transformation == DsDwColumnTransformation.Scd1 ||
                                                                                              cm.Transformation == DsDwColumnTransformation.ForeignKey)))
            {
                if (aDsDwColumnMap.Transformation == DsDwColumnTransformation.Scd1)
                {
                    aResultString = aResultString +
                                    string.Format("    [target].[{0}] = [source].[{1}],{2}",
                                                  aDsDwColumnMap.DwColumn.Name, aDsDwColumnMap.DsColumn.Name, Environment.NewLine);
                }
                else
                {
                    aResultString = aResultString +
                                    string.Format("    [target].[{0}] = [source].[{1}],{2}",
                                                  aDsDwColumnMap.DwColumn.Name, aDsDwColumnMap.DwColumn.Name, Environment.NewLine);
                }
            }
            aResultString = RemoveAtEnd(aResultString, "," + Environment.NewLine);
            return(aResultString);
        }
Exemplo n.º 12
0
 public void AddUniqueLocalForeignKeyTableReference(DsDwColumnMap_M aDsDwColumnMap, DsDwTableMap_M aDsDwTableMap)
 {
     if (aDsDwColumnMap != null)
     {
         if (!aDsDwColumnMap.DwForeignKeyReferencedTableList.Any(fk =>
                                                                 fk.TableName.ToLower() == aDsDwTableMap.DsTableName.ToLower() &&
                                                                 fk.SchemaName.ToLower() == aDsDwTableMap.DsSchemaName.ToLower()))
         {
             aDsDwColumnMap.DwForeignKeyReferencedTableList.Add(
                 new DsDwTableReference_M
             {
                 TableName  = aDsDwTableMap.DsTableName,
                 SchemaName = aDsDwTableMap.DsSchemaName
             });
             if (aDsDwColumnMap.DwForeignKeyReferencedTableList.Count == 1)
             {
                 aDsDwColumnMap.DwForeignKeyReferencedTable    = aDsDwColumnMap.DwForeignKeyReferencedTableList[0];
                 aDsDwColumnMap.DwForeignKeyReferencedTableMap = aDsDwTableMap;
             }
         }
     }
 }
Exemplo n.º 13
0
        private void FillColumnAndForeignKeyInfoView(ObservableCollection <ColumnInfo> aParsedColumnList, DsDwTableMap_M aDsDwTableMap)
        {
            if (aDsDwTableMap != null && aParsedColumnList != null && aParsedColumnList.Count > 0)
            {
                Table           aTable;
                ForeignKey      aForeignKey;
                DsDwColumnMap_M aDsDwColumnMap;
                foreach (ColumnInfo aColumnInfo in aParsedColumnList)
                {
                    if (string.IsNullOrWhiteSpace(aColumnInfo.TableSchema))
                    {
                        aColumnInfo.TableSchema = _DsDb.DefaultSchema.ToString();
                    }
                    aDsDwColumnMap =
                        aDsDwTableMap.DsDwColumnMapList.FirstOrDefault(
                            cm => cm.DsColumn.Name.ToLower() == aColumnInfo.Alias.ToLower());
                    if (aDsDwColumnMap != null)
                    {
                        aDsDwColumnMap.DsReferencedTable = new DsDwTableReference_M
                        {
                            TableName    = aColumnInfo.TableName,
                            SchemaName   = aColumnInfo.TableSchema,
                            DatabaseName = aColumnInfo.TableDatabase,
                            ServerName   = aColumnInfo.TableServer
                        };

                        aTable = _DsDb.Tables.Cast <Table>().FirstOrDefault(t =>
                                                                            t.Name.ToLower() == aColumnInfo.TableName.ToLower() &&
                                                                            t.Schema.ToLower() == aColumnInfo.TableSchema.ToLower());
                        if (aTable != null)
                        {
                            aDsDwColumnMap.DsReferencedColumn = new DsDwColumn_M(
                                aTable.Columns.Cast <Column>().
                                FirstOrDefault(c => c.Name.ToLower() == aColumnInfo.TableColumnName.ToLower()));
                            if (aDsDwColumnMap.DsReferencedColumn.IsForeignKey)
                            {
                                aForeignKey =
                                    aTable.ForeignKeys.Cast <ForeignKey>()
                                    .FirstOrDefault(
                                        fk =>
                                        fk.Columns.Cast <ForeignKeyColumn>()
                                        .Any(c => c.Name.ToLower() == aDsDwColumnMap.DsReferencedColumn.Name.ToLower()));
                                if (aForeignKey != null)
                                {
                                    aDsDwTableMap.DsDwForeignKeyList.Add(new DsDwForeignKey_M(aForeignKey));
                                }
                            }
                        }
                    }
                }
            }
        }
Exemplo n.º 14
0
        public void InitializeDsDwMap()
        {
            if (DwRebuildDsDwTableMap)
            {
                AppendLogLine("Rebuilding table map...");
                DsDwMapChanged = true;
                DsDwMap        = null;
            }
            if (DsDwMap == null)
            {
                DsDwMap = new DsDwMap_M();
            }
            else
            {
                SycDwTableViewMapList();
            }
            foreach (TableView_M aTableView in DwTableViewList)
            {
                if ((aTableView != null) && (!DsDwMap.DsDwTableMapList.Any(tm => tm.DsObjectId == aTableView.ObjectId)))
                {
                    DsDwMapChanged = true;
                    AppendLogLine(string.Format("Processing table/view: {0}...", aTableView.Name));
                    DsDwTableMap_M aDsDwTableMap = new DsDwTableMap_M
                    {
                        DsIsTable      = aTableView.IsTable,
                        DsTableName    = aTableView.Name,
                        DsSchemaName   = aTableView.Schema,
                        DwTableName    = aTableView.Name,
                        DwSchemaName   = aTableView.Schema,
                        DsRowCount     = aTableView.RowCount,
                        DsObjectId     = aTableView.ObjectId,
                        DsDatabaseName = DsDbName,
                        DsServerName   = DsDbServerName
                    };
                    DsDwMap.DsDwTableMapList.Add(aDsDwTableMap);

                    TableViewBase aTableViewBase;
                    if (aTableView.IsTable)
                    {
                        aTableViewBase = _DsDb.Tables.Cast <Table>().FirstOrDefault(t => t.ID == aTableView.ObjectId);
                    }
                    else
                    {
                        aTableViewBase =
                            _DsDb.Views.Cast <Microsoft.SqlServer.Management.Smo.View>()
                            .FirstOrDefault(t => t.ID == aTableView.ObjectId);
                    }

                    if (aTableViewBase != null)
                    {
                        foreach (Column aColumn in aTableViewBase.Columns)
                        {
                            DsDwColumnMap_M aDsDwColumnMap = new DsDwColumnMap_M
                            {
                                DsColumn = new DsDwColumn_M(aColumn),
                                DwColumn = new DsDwColumn_M(aColumn),
                                Include  = true
                            };

                            if (aColumn.InPrimaryKey)
                            {
                                aDsDwColumnMap.Transformation = DsDwColumnTransformation.BusinesKey;
                                aDsDwTableMap.DsDwColumnMapList.Insert(0, aDsDwColumnMap);
                            }
                            else
                            {
                                aDsDwColumnMap.Transformation = DsDwColumnTransformation.Scd1;
                                aDsDwTableMap.DsDwColumnMapList.Add(aDsDwColumnMap);
                            }
                        }
                        if (aTableView.IsTable)
                        {
                            AddExistingForeignKeysTable((Table)aTableViewBase, aDsDwTableMap);
                        }

                        if (!aTableView.IsTable) //this is a view
                        {
                            aTableViewBase =
                                _DsDb.Views.Cast <Microsoft.SqlServer.Management.Smo.View>()
                                .FirstOrDefault(t => t.ID == aTableView.ObjectId);

                            ParseViewSql aParseViewSql = new ParseViewSql();

                            Microsoft.SqlServer.Management.Smo.View aView =
                                (aTableViewBase as Microsoft.SqlServer.Management.Smo.View);

                            if (aParseViewSql.ParseView(aView.TextHeader + aView.TextBody))
                            {
                                FillColumnAndForeignKeyInfoView(aParseViewSql.ColumnList, aDsDwTableMap);
                            }
                        }
                        AddDwSupportColumns(aDsDwTableMap);
                    }
                }
                else
                {
                    //Update DbName and ServerName
                    DsDwTableMap_M aUpdateDsDwTableMap = DsDwMap.DsDwTableMapList.FirstOrDefault(tm => tm.DsObjectId == aTableView.ObjectId);
                    if (aUpdateDsDwTableMap != null)
                    {
                        aUpdateDsDwTableMap.DsDatabaseName = DsDbName;
                        aUpdateDsDwTableMap.DsServerName   = DsDbServerName;
                    }
                }
            }
            if (DsDwMapChanged || true)
            {
                AppendLogLine(string.Format("Always rebuilding foreign key links."));
                DsDwMap.ConfigureForeignKeyTransformations();
            }
            else
            {
                AppendLogLine(string.Format("Map not changed, not rebuilding foreign key links."));
            }
            DsDwMapChanged = false;
            NotifyPropertyChanged("DsDwMap");
        }
Exemplo n.º 15
0
        private void DwGenerateTable(DsDwTableMap_M aDsDwTableMap)
        {
            if (aDsDwTableMap != null && aDsDwTableMap.DsDwColumnMapList != null)
            {
                Table aSMOTable = new Table(DwDb, aDsDwTableMap.DwTableName, aDsDwTableMap.DwSchemaName);
                //Add columns to the table
                foreach (DsDwColumnMap_M aDsDwColumnMap in aDsDwTableMap.DsDwColumnMapList)
                {
                    if (aDsDwColumnMap != null && aDsDwColumnMap.Include)
                    {
                        Column aNewColumn;
                        switch (aDsDwColumnMap.Transformation)
                        {
                        case DsDwColumnTransformation.SurrogateKey:
                            aNewColumn                   = new Column(aSMOTable, aDsDwColumnMap.DwColumn.Name, aDsDwColumnMap.DwColumn.DataType);
                            aNewColumn.Nullable          = false;
                            aNewColumn.Identity          = true;
                            aNewColumn.IdentityIncrement = 1;
                            aNewColumn.IdentitySeed      = 1;
                            aSMOTable.Columns.Add(aNewColumn);
                            break;

                        case DsDwColumnTransformation.BusinesKey:
                            aNewColumn           = new Column(aSMOTable, aDsDwColumnMap.DwColumn.Name, aDsDwColumnMap.DwColumn.DataType);
                            aNewColumn.Nullable  = false;
                            aNewColumn.Collation = aDsDwColumnMap.DwColumn.Collation;
                            aSMOTable.Columns.Add(aNewColumn);
                            break;

                        default:
                            aNewColumn           = new Column(aSMOTable, aDsDwColumnMap.DwColumn.Name, aDsDwColumnMap.DwColumn.DataType);
                            aNewColumn.Collation = aDsDwColumnMap.DwColumn.Collation;
                            aNewColumn.Nullable  = true;
                            aSMOTable.Columns.Add(aNewColumn);
                            break;
                        }
                    }
                }

                aSMOTable.Create();
                //Add indexes and primary key the table
                foreach (DsDwColumnMap_M aDsDwColumnMap in aDsDwTableMap.DsDwColumnMapList)
                {
                    if (aDsDwColumnMap != null && aDsDwColumnMap.Include)
                    {
                        switch (aDsDwColumnMap.Transformation)
                        {
                        case DsDwColumnTransformation.SurrogateKey:
                            Index         primaryKey    = new Index(aSMOTable, "PK_" + aDsDwTableMap.DwTableName);
                            IndexedColumn indexedColumn = new IndexedColumn(primaryKey, aDsDwColumnMap.DwColumn.Name);
                            primaryKey.IndexedColumns.Add(indexedColumn);
                            primaryKey.IndexKeyType = IndexKeyType.DriPrimaryKey;
                            primaryKey.Create();
                            break;

                        case DsDwColumnTransformation.BusinesKey:
                            // Define an Index object variable by providing the parent table and index name in the constructor.
                            Index idx;
                            idx = new Index(aSMOTable, "IX_" + aDsDwTableMap.DwTableName + "_" + aDsDwColumnMap.DwColumn.Name);
                            // Add indexed columns to the index.
                            IndexedColumn icol1;
                            icol1 = new IndexedColumn(idx, aDsDwColumnMap.DwColumn.Name, true);
                            idx.IndexedColumns.Add(icol1);
                            // Set the index properties.
                            idx.IndexKeyType = IndexKeyType.None;
                            idx.IsClustered  = false;
                            idx.FillFactor   = 70;
                            // Create the index on the instance of SQL Server.
                            idx.Create();
                            break;
                        }
                    }
                }
            }
        }
Exemplo n.º 16
0
        private void AddDwSupportColumns(DsDwTableMap_M aDsDwTableMap)
        {
            //Add Data warehouse support columns
            //[Description("Scd 2 is active")] Scd2IsActive,
            //[Description("Scd 2 date from")] Scd2DateFrom,
            //[Description("Scd 2 date to")] Scd2DateTo,
            //[Description("Surrogate key")] SurrogateKey,
            //[Description("Created date")] CreatedDate,
            //[Description("Deleted date")] DeletedDate,
            //[Description("Modified date")] ModifiedDate,
            //[Description("Is deleted")] IsDeleted

            DsDwColumnMap_M aDsDwColumnMap;

            aDsDwColumnMap = new DsDwColumnMap_M
            {
                DsColumn           = null,
                DsReferencedColumn = null,
                DwColumn           = new DsDwColumn_M {
                    Name = "Scd2IsActive", DataType = new DataType(SqlDataType.Bit)
                },
                Transformation = DsDwColumnTransformation.Scd2IsActive,
                Include        = false
            };
            aDsDwTableMap.DsDwColumnMapList.Add(aDsDwColumnMap);

            aDsDwColumnMap = new DsDwColumnMap_M
            {
                DsColumn           = null,
                DsReferencedColumn = null,
                DwColumn           = new DsDwColumn_M {
                    Name = "Scd2DateFrom", DataType = new DataType(SqlDataType.DateTime2)
                },
                Transformation = DsDwColumnTransformation.Scd2DateFrom,
                Include        = false
            };
            aDsDwTableMap.DsDwColumnMapList.Add(aDsDwColumnMap);

            aDsDwColumnMap = new DsDwColumnMap_M
            {
                DsColumn           = null,
                DsReferencedColumn = null,
                DwColumn           = new DsDwColumn_M {
                    Name = "Scd2DateTo", DataType = new DataType(SqlDataType.DateTime2)
                },
                Transformation = DsDwColumnTransformation.Scd2DateTo,
                Include        = false
            };
            aDsDwTableMap.DsDwColumnMapList.Add(aDsDwColumnMap);

            aDsDwColumnMap = new DsDwColumnMap_M
            {
                DsColumn           = null,
                DsReferencedColumn = null,
                DwColumn           = new DsDwColumn_M {
                    Name = aDsDwTableMap.DwTableName + "_Key", DataType = new DataType(SqlDataType.Int)
                },

                Transformation = DsDwColumnTransformation.SurrogateKey,
                Include        = true
            };
            aDsDwTableMap.DsDwColumnMapList.Insert(0, aDsDwColumnMap);

            aDsDwColumnMap = new DsDwColumnMap_M
            {
                DsColumn           = null,
                DsReferencedColumn = null,
                DwColumn           = new DsDwColumn_M {
                    Name = "CreatedDate", DataType = new DataType(SqlDataType.DateTime2)
                },
                Transformation = DsDwColumnTransformation.CreatedDate,
                Include        = true
            };
            aDsDwTableMap.DsDwColumnMapList.Add(aDsDwColumnMap);

            aDsDwColumnMap = new DsDwColumnMap_M
            {
                DsColumn           = null,
                DsReferencedColumn = null,
                DwColumn           = new DsDwColumn_M {
                    Name = "DeletedDate", DataType = new DataType(SqlDataType.DateTime2)
                },
                Transformation = DsDwColumnTransformation.DeletedDate,
                Include        = true
            };
            aDsDwTableMap.DsDwColumnMapList.Add(aDsDwColumnMap);

            aDsDwColumnMap = new DsDwColumnMap_M
            {
                DsColumn           = null,
                DsReferencedColumn = null,
                DwColumn           = new DsDwColumn_M {
                    Name = "ModifiedDate", DataType = new DataType(SqlDataType.DateTime2)
                },
                Transformation = DsDwColumnTransformation.ModifiedDate,
                Include        = true
            };
            aDsDwTableMap.DsDwColumnMapList.Add(aDsDwColumnMap);

            aDsDwColumnMap = new DsDwColumnMap_M
            {
                DsColumn           = null,
                DsReferencedColumn = null,
                DwColumn           = new DsDwColumn_M {
                    Name = "IsDeleted", DataType = new DataType(SqlDataType.Bit)
                },
                Transformation = DsDwColumnTransformation.IsDeleted,
                Include        = true
            };
            aDsDwTableMap.DsDwColumnMapList.Add(aDsDwColumnMap);
        }