/// <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();
}
public QueryTest()
{
PassedColumnNames = new List <string>();
PassedTableNames = new List <string>();
Column = Table.GetProperty(nameof(Right.RightId));
LeftTable = typeof(Left);
LeftColumn = LeftTable.GetProperty(nameof(Left.LeftId));
Query.ConfigureTo().NameColumnsWith(c => { PassedColumnNames.Add(c); return(c); })
.NameTablesWith(t => { PassedTableNames.Add(t); return(t); });
_sut = new Query("");
}
/// <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);
}
