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