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 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); }
private void GenerateDwSchema() { try { AppendLogLine("Creating a DataSourceView ..."); //Create the connection string. string conxString = "Data Source=" + DwDbServerName + "; Initial Catalog=" + DwDbName + "; Integrated Security=True;"; string conxStringOle = "Provider=sqloledb; Data Source=" + DwDbServerName + "; Initial Catalog=" + DwDbName + "; Integrated Security=SSPI;"; //Create the SqlConnection. _CbConnection = new SqlConnection(conxString); _CBOleConnection = new OleDbConnection(conxStringOle); _CbDataSet = new DataSet(); foreach (DsDwTableMap_M aDwTableMap in DsDwMap.DsDwTableMapList) { FillDataSet(aDwTableMap); } if (DsDwMap.DwTableMapTopologicalSortList != null) { for (int i = DsDwMap.DwTopologicalLevels - 1; i >= 1; i--) { ICollection <DsDwTableMap_M> dsDwTableMaps = DsDwMap.DwTableMapTopologicalSortList[i]; foreach (DsDwTableMap_M aChildTable in dsDwTableMaps) { foreach (DsDwColumnMap_M aChildFkColumn in aChildTable.DsDwColumnMapList.Where(cm => cm.Include && cm.Transformation == DsDwColumnTransformation.ForeignKey)) { DsDwColumnMap_M aParentColumnPK = aChildFkColumn.DwForeignKeyReferencedTableMap.DsDwColumnMapList.FirstOrDefault(cm => cm.Transformation == DsDwColumnTransformation.SurrogateKey); AddDataTableRelation(aParentColumnPK.Parent.DwTableName, aParentColumnPK.DwColumn.Name, aChildTable.DwTableName, aChildFkColumn.DwColumn.Name); } } } } } catch (Exception ex) { AppendLogLine("Error in creating a dataSourceView - GenerateDwSchema. Error Message -> " + ex.Message); _CbDataSet = null; throw; } }
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(); } } }
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; } } } }
public void FindForeignKeyTableMatch(DsDwColumnMap_M aDsDwColumnMap, DsDwForeignKey_M aDsDwForeignKey) { if (aDsDwColumnMap != null && aDsDwForeignKey != null) { aDsDwColumnMap.DwForeignKeyReferencedTableList.Clear(); aDsDwColumnMap.DwForeignKeyReferencedTable = null; foreach (DsDwTableMap_M aDsDwTableMap in this.DsDwTableMapList) { if ((aDsDwTableMap != null) && (aDsDwTableMap != aDsDwColumnMap.Parent)) { if (aDsDwTableMap.DsSchemaName.ToLower() == aDsDwForeignKey.ReferencedTableSchema.ToLower() && aDsDwTableMap.DsTableName.ToLower() == aDsDwForeignKey.ReferencedTable.ToLower() && aDsDwTableMap.DsIsTable) { //found table reference - no need to check for column - we assume FK point to primary keys AddUniqueLocalForeignKeyTableReference(aDsDwColumnMap, aDsDwTableMap); } if (!aDsDwTableMap.DsIsTable && aDsDwTableMap.DsDwColumnMapList != null) //search in related meta data of the view { foreach (DsDwColumnMap_M dsDwColumnMapLocal in aDsDwTableMap.DsDwColumnMapList) { //we assume FK point to primary keys if (dsDwColumnMapLocal.DsReferencedColumn != null && dsDwColumnMapLocal.DsReferencedTable != null && dsDwColumnMapLocal.DsReferencedColumn.InPrimaryKey && dsDwColumnMapLocal.DsReferencedTable.SchemaName.ToLower() == aDsDwForeignKey.ReferencedTableSchema.ToLower() && dsDwColumnMapLocal.DsReferencedTable.TableName.ToLower() == aDsDwForeignKey.ReferencedTable.ToLower()) { //found view reference AddUniqueLocalForeignKeyTableReference(aDsDwColumnMap, aDsDwTableMap); } } } } } } }
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"); }
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); }