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