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