private DataTable BuildDataTableTemplate() { var result = RightTable.Clone(); foreach (var column in NonPrimaryColumns) { var columnType = result.Columns[column.Name].DataType; result.Columns.Remove(column.Name); var leftColumnName = ColumnNameBuilder.BuildColumName(LeftTableAlias, column.Name); var rightColumnName = ColumnNameBuilder.BuildColumName(RightTableAlias, column.Name); result.Columns.Add(leftColumnName, columnType); result.Columns.Add(rightColumnName, columnType); } foreach (var columnName in CompareColumnNames) { var column = AllColumns.FirstOrDefault(c => c.Name.Equals(columnName)); if (column != null) { var gapColumnName = ColumnNameBuilder.BuildGapColumnName(column.Name); var compareColumnName = ColumnNameBuilder.BuildCompareResultColumnName(column.Name); result.Columns.Add(gapColumnName, typeof(double)); result.Columns.Add(compareColumnName, typeof(double)); } } return(result); }
/// <summary> /// Helper to check the join /// </summary> public void CheckJoin() { Error = ""; Information = ""; try { if (LeftTable == null) { throw new Exception("Please select a Left table for the join"); } if (RightTable == null) { throw new Exception("Please select a Right table for the join"); } if (_joinType != JoinType.Cross && string.IsNullOrEmpty(Clause.Trim())) { throw new Exception("Please enter a SQL statement for the join"); } string CTE1 = "", name1 = "", CTE2 = "", name2 = ""; LeftTable.GetExecSQLName(ref CTE1, ref name1); RightTable.GetExecSQLName(ref CTE2, ref name2); string CTE = Helper.AddCTE(CTE1, CTE2); string sql = string.Format("{0}SELECT * FROM {1}\r\n", CTE, name1); if (_joinType != JoinType.Cross) { sql += string.Format("{0} {1} ON {2}\r\n", SQLJoinType, name2, Clause.Trim()); } else { sql += string.Format("{0} {1}\r\n", SQLJoinType, name2); } sql += "WHERE 0=1"; Error = Source.CheckSQL(sql, new List <MetaTable>() { LeftTable, RightTable }, null, false); if (!string.IsNullOrEmpty(Error)) { Information = "Error got when checking join. Please check the SQL:\r\n" + sql; } else { Information = "Join checked successfully."; } } catch (Exception ex) { Error = ex.Message; Information = "Error got when checking the join."; } Information = Helper.FormatMessage(Information); UpdateEditorAttributes(); }
/// <summary> /// Helper to check the join /// </summary> public void CheckJoin() { Error = ""; Information = ""; try { if (LeftTable == null) { throw new Exception("Please select a Left table for the join"); } if (RightTable == null) { throw new Exception("Please select a Right table for the join"); } if (_joinType != JoinType.Cross && string.IsNullOrEmpty(Clause.Trim())) { throw new Exception("Please enter a Statement for the join"); } if (Source.IsSQL) { string CTE1 = "", name1 = "", CTE2 = "", name2 = ""; LeftTable.GetExecSQLName(ref CTE1, ref name1); RightTable.GetExecSQLName(ref CTE2, ref name2); string CTE = Helper.AddCTE(CTE1, CTE2); string sql = string.Format("{0}SELECT * FROM {1}\r\n", CTE, name1); if (_joinType != JoinType.Cross) { sql += string.Format("{0} {1} ON {2}\r\n", SQLJoinType, name2, Clause.Trim()); } else { sql += string.Format("{0} {1}\r\n", SQLJoinType, name2); } sql += "WHERE 0=1"; Error = Source.CheckSQL(sql, new List <MetaTable>() { LeftTable, RightTable }, null, false); if (!string.IsNullOrEmpty(Error)) { Information = "Error got when checking join. Please check the SQL:\r\n" + sql; } else { Information = "Join checked successfully."; } } else { var linq = string.Format(@"@using System.Data @{{ DataTable aDataTable = new DataTable(); var query = from {0} in aDataTable.AsEnumerable() join {1} in aDataTable.AsEnumerable() on {2} select {0}; }}", LeftTable.AliasName, RightTable.AliasName, Clause); try { RazorHelper.Compile(linq, typeof(MetaJoin), Helper.NewGUID()); Information = "Join checked successfully."; } catch (Exception ex) { Error = ex.Message; Information = "Error got when checking join. Please check the LINQ:\r\n" + linq; } } } catch (Exception ex) { Error = ex.Message; Information = "Error got when checking the join."; } Information = Helper.FormatMessage(Information); }
protected override void Execute(CodeActivityContext context) { System.Data.DataTable LTable = LeftTable.Get(context).Select(string.Empty, LeftSort.Get(context)).CopyToDataTable(); System.Data.DataTable RTable = RightTable.Get(context).Select(string.Empty, RightSort.Get(context)).CopyToDataTable(); if (CheckRecordsCount.Get(context) && LTable.Rows.Count != RTable.Rows.Count) { throw new ArgumentException(Properties.Resources.Exception_Records_Count_Invalid); } System.Data.DataTable ret = new System.Data.DataTable(); var convDic = new Dictionary <DataColumn, String>(); // 左側テーブルは、列名を、そのまま使います foreach (DataColumn c in LTable.Columns) { convDic.Add(c, c.ColumnName); ret.Columns.Add(new DataColumn(c.ColumnName, c.DataType)); } // 右側テーブルは、列名が、重複したら、 _0 形式で、数値を足します foreach (DataColumn c in RTable.Columns) { var newColName = string.Empty; for (int i = -1; convDic.ContainsValue(newColName = (i < 0 ? c.ColumnName : string.Format("{0}_{1}", newColName, i.ToString().Trim()))); i++) { ; } convDic.Add(c, newColName); ret.Columns.Add(new DataColumn(newColName, c.DataType)); } int maxRecord = LTable.Rows.Count > RTable.Rows.Count ? LTable.Rows.Count : RTable.Rows.Count; for (int i = 0; i < maxRecord; i++) { DataRow row = ret.NewRow(); if (LTable.Rows.Count > i) { foreach (DataColumn c in LTable.Columns) { row[convDic[c]] = LTable.Rows[i][c]; } } if (RTable.Rows.Count > i) { foreach (DataColumn c in RTable.Columns) { row[convDic[c]] = RTable.Rows[i][c]; } } ret.Rows.Add(row); } Result.Set(context, ret); }