public SchemaMapper.SchemaMapping.SchemaMapper InitiateTestSchemaMapper(string schema, string table)
{
SchemaMapper.SchemaMapping.SchemaMapper smResult = new SchemaMapper.SchemaMapping.SchemaMapper();
smResult.TableName = table;
smResult.SchemaName = schema;
//Add variables
smResult.Variables.Add(new SchemaMapper.SchemaMapping.Variable("@Today", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
//Add Columns
SchemaMapper.SchemaMapping.SchemaMapper_Column smServerCol = new SchemaMapper.SchemaMapping.SchemaMapper_Column("Server_Name",
SchemaMapper.SchemaMapping.SchemaMapper_Column.ColumnDataType.Text);
SchemaMapper.SchemaMapping.SchemaMapper_Column smUserCol = new SchemaMapper.SchemaMapping.SchemaMapper_Column("User_Name",
SchemaMapper.SchemaMapping.SchemaMapper_Column.ColumnDataType.Text);
SchemaMapper.SchemaMapping.SchemaMapper_Column smPassCol = new SchemaMapper.SchemaMapping.SchemaMapper_Column("Password",
SchemaMapper.SchemaMapping.SchemaMapper_Column.ColumnDataType.Text);
//// Add column with Fixed Value
SchemaMapper.SchemaMapping.SchemaMapper_Column smFixedValueCol = new SchemaMapper.SchemaMapping.SchemaMapper_Column("AddedDate",
SchemaMapper.SchemaMapping.SchemaMapper_Column.ColumnDataType.Text,
"@Today");
//// Add Column with Expression
SchemaMapper.SchemaMapping.SchemaMapper_Column smExpressionCol = new SchemaMapper.SchemaMapping.SchemaMapper_Column("UserAndPassword",
SchemaMapper.SchemaMapping.SchemaMapper_Column.ColumnDataType.Text,
true,
"[User_Name] + '|' + [Password]");
smResult.Columns.Add(smServerCol);
smResult.Columns.Add(smUserCol);
smResult.Columns.Add(smPassCol);
smResult.Columns.Add(smFixedValueCol);
smResult.Columns.Add(smExpressionCol);
//Add all possible input Columns for each Column
smServerCol.MappedColumns.AddRange(new[] { "server", "server name", "servername", "Server", "Server Name", "ServerName" });
smUserCol.MappedColumns.AddRange(new[] { "UserName", "User", "login", "Login", "User name" });
smPassCol.MappedColumns.AddRange(new[] { "Password", "pass", "Pass", "password" });
//Added columns to ignore if found
//Sys_SheetName and Sys_ExtraFields is an auto generated column when reading Excel file
smResult.IgnoredColumns.AddRange(new[] { "Column1", "Sys_Sheetname", "Sys_ExtraFields", "Center Name" });
//Save Schema Mapper into xml
smResult.WriteToXml(Environment.CurrentDirectory + "\\SchemaMapper\\1.xml", true);
return(smResult);
}
public bool ChangeTableStructure(ref DataTable dt)
{
try
{
//Check if all columns are mapped
if (AreColumnsMapped(dt) == false)
{
return(false);
}
//Get columns Mapping for select Schema Mapper id
var lstColsMap = Columns.SelectMany(x =>
x.MappedColumns.Select(y =>
new
{
InputCol = y,
OutputCol = x.Name,
Type = x.DataType,
FixedValue = x.FixedValue,
IsExpression = x.IsExpression,
Expression = x.Expression
})).ToList();
//Store current columns name inside a list and assign temporery names to columns
List <TempColumn> lstTempColumns = new List <TempColumn>();
int intCounter = 0;
foreach (DataColumn dc in dt.Columns)
{
TempColumn tempCol = new TempColumn("TempColumn_" + intCounter.ToString(), dc.ColumnName);
lstTempColumns.Add(tempCol);
dc.ColumnName = "TempColumn_" + intCounter.ToString();
intCounter++;
}
//Get Column Mapped to many columns
var lstGroupByOutputCols = (from col in lstColsMap
join tmp in lstTempColumns
on col.InputCol equals tmp.originalCol
group tmp by col.OutputCol into grp
select new { Column = grp.Key, Inputs = grp.ToList() }).ToList();
List <String> lstColumnsWithMultipleInput = (from grp in lstGroupByOutputCols
where grp.Inputs.Count() > 1
select grp.Column).ToList();
//Get Columns for remove at the end
List <String> lstColsToRemove = (from tmp in lstTempColumns
where lstColsMap.Select(x => x.InputCol).ToList().Contains(tmp.originalCol) == false
select tmp.originalCol).ToList();
lstColsToRemove.AddRange(lstColumnsWithMultipleInput);
//Assign expresion for multiple mapped columns
foreach (var grp in lstGroupByOutputCols.Where(x => x.Inputs.Count > 1).ToList())
{
DataColumn dc = new DataColumn();
SchemaMapper_Column.ColumnDataType strType = lstColsMap.Where(x =>
x.OutputCol == grp.Column).Select(y => y.Type).First();
string expression = "";
switch (strType)
{
case SchemaMapper_Column.ColumnDataType.Boolean:
throw new Exception("Cannot map multiple columns into a column of type \"Boolean\"");
case SchemaMapper_Column.ColumnDataType.Date:
throw new Exception("Cannot map multiple columns into a column of type \"Date\"");
case SchemaMapper_Column.ColumnDataType.Text:
dc.DataType = System.Type.GetType("System.String");
dc.MaxLength = 255;
expression = String.Join(" + ", grp.Inputs.Select(x => "IIF(ISNULL([" + x.tempCol + "],'') = '','',ISNULL([" + x.tempCol + "],'') + '|')").ToArray());
break;
case SchemaMapper_Column.ColumnDataType.Number:
throw new Exception("Cannot map multiple columns into a column of type \"Number\"");
case SchemaMapper_Column.ColumnDataType.Memo:
dc.DataType = System.Type.GetType("System.String");
dc.MaxLength = 4000;
expression = String.Join(" + ", grp.Inputs.Select(x => "IIF(ISNULL([" + x.tempCol + "],'') = '','','" + x.originalCol + ":' + ISNULL([" + x.tempCol + "],'')+ '" + Environment.NewLine + "')").ToArray());
break;
}
dc.ColumnName = grp.Column;
dc.Expression = expression;
dt.Columns.Add(dc);
}
//Assign Default Values as expression
foreach (var col in Columns.Where(x => x.FixedValue != "" && !x.IsExpression))
{
DataColumn dc = new DataColumn();
dc.DataType = System.Type.GetType("System.String");
string strValue = col.FixedValue;
foreach (var variable in Variables.Where(x => x.Name.StartsWith(@"@")).ToList())
{
strValue = strValue.Replace(variable.Name, variable.Value);
}
switch (col.DataType)
{
case SchemaMapper_Column.ColumnDataType.Boolean:
dc.DataType = System.Type.GetType("System.Boolean");
dc.Expression = strValue;
break;
case SchemaMapper_Column.ColumnDataType.Date:
dc.DataType = System.Type.GetType("System.DateTime");
dc.Expression = "CONVERT('" + strValue + "',System.DateTime)";
break;
case SchemaMapper_Column.ColumnDataType.Text:
dc.DataType = System.Type.GetType("System.String");
dc.MaxLength = 255;
dc.Expression = ("'" + strValue + "'").Replace("''", "'");
break;
case SchemaMapper_Column.ColumnDataType.Number:
dc.DataType = System.Type.GetType("System.Int64");
dc.Expression = strValue;
break;
case SchemaMapper_Column.ColumnDataType.Memo:
dc.DataType = System.Type.GetType("System.String");
dc.MaxLength = 4000;
dc.Expression = ("'" + strValue + "'").Replace("''", "'");
break;
}
dc.ColumnName = col.Name;
dt.Columns.Add(dc);
}
//Get columns to be removed at the end
List <String> lstColsToRemoveWithTemp = (from rmv in lstColsToRemove
join tmp in lstTempColumns
on rmv equals tmp.originalCol
select tmp.tempCol).ToList();
//Map temp columns with output columns
var lstMapTempWithOutputCols = (from col in lstColsMap
join tmp in lstTempColumns
on col.InputCol equals tmp.originalCol
where lstColsToRemoveWithTemp.Contains(tmp.tempCol) == false
select new { tmp.tempCol, tmp.originalCol, col.OutputCol }).ToList();
//Assign column Expression
foreach (var col in Columns.Where(x => x.IsExpression == true &&
x.Expression != ""))
{
DataColumn dc = new DataColumn();
switch (col.DataType)
{
case SchemaMapper_Column.ColumnDataType.Boolean:
dc.DataType = System.Type.GetType("System.Boolean");
break;
case SchemaMapper_Column.ColumnDataType.Date:
dc.DataType = System.Type.GetType("System.DateTime");
break;
case SchemaMapper_Column.ColumnDataType.Text:
dc.DataType = System.Type.GetType("System.String");
dc.MaxLength = 255;
break;
case SchemaMapper_Column.ColumnDataType.Number:
dc.DataType = System.Type.GetType("System.Int64");
break;
case SchemaMapper_Column.ColumnDataType.Memo:
dc.DataType = System.Type.GetType("System.String");
dc.MaxLength = 4000;
break;
}
string expression = col.Expression;
//fix columns names inside expression
foreach (var tmp in lstMapTempWithOutputCols)
{
expression = expression.Replace("[" + tmp.OutputCol + "]", "[" + tmp.tempCol + "]");
}
foreach (var variable in Variables.Where(x => x.Name.StartsWith(@"@")).ToList())
{
expression = expression.Replace(variable.Name, "'" + variable.Value + "'");
}
dc.Expression = expression;
dc.ColumnName = col.Name;
dt.Columns.Add(dc);
}
//persist expressions
DataTable dtNew = dt.Clone();
foreach (DataColumn dtCol in dtNew.Columns)
{
dtCol.Expression = "";
}
dtNew.Merge(dt);
dt = dtNew;
dtNew.Dispose();
dtNew = null;
//Remove unsused Columns
foreach (string str in lstColsToRemoveWithTemp)
{
dt.Columns.Remove(str);
}
//Rename remaining Columns
foreach (var col in lstMapTempWithOutputCols)
{
DataColumn dc = dt.Columns[col.tempCol];
dc.ColumnName = col.OutputCol;
SchemaMapper_Column smCol = Columns.Where(y => y.Name == col.OutputCol).First();
switch (smCol.DataType)
{
case SchemaMapper_Column.ColumnDataType.Boolean:
dc.DataType = System.Type.GetType("System.Boolean");
break;
case SchemaMapper_Column.ColumnDataType.Date:
dc.DataType = System.Type.GetType("System.DateTime");
break;
case SchemaMapper_Column.ColumnDataType.Text:
dc.DataType = System.Type.GetType("System.String");
dc.MaxLength = 255;
break;
case SchemaMapper_Column.ColumnDataType.Number:
dc.DataType = System.Type.GetType("System.Int64");
break;
case SchemaMapper_Column.ColumnDataType.Memo:
dc.DataType = System.Type.GetType("System.String");
dc.MaxLength = 4000;
break;
}
}
return(true);
}
catch (Exception ex)
{
throw ex;
}
}
