public void AddJoin(JoinTypeEnum type, TableBase table, Column columnLeft, Column columnRight)
{
this.JoinIndexX++;
table.JoinIndex = this.JoinIndexX;
Provider.AddJoin(type, table.TableName, table.GetTableNameShortcut(), columnLeft.DbTable.GetTableNameShortcut(),
columnLeft.DbName, columnRight.DbTable.GetTableNameShortcut(), columnRight.DbName);
}
public LiteJoinTable(LiteQuery query, string _tableName, string _tableAlias, JoinTypeEnum joinType, LiteFilter _filter)
{
this.joinTables = new List <LiteJoinTable>();
this.query = query;
this.tableName = _tableName;
this.tableAlias = _tableAlias;
this.filter = _filter;
}
public JoinAttribute(JoinTypeEnum joinType, params string[] columns)
{
JoinType = joinType;
ColumnsFrom = new string[columns.Length];
ColumnsTo = new string[columns.Length];
for (var i = 0; i < columns.Length; i++)
{
var column = columns[i].Split(':');
ColumnsFrom[i] = column[0];
ColumnsTo[i] = column[1];
}
}
public override void AddJoin(JoinTypeEnum _joinType, String _joinTableName, string _joinTableNameShortcut, String _tableNameLeft, String _columnNameLeft, String _tableNameRight, String _columnNameRight)
{
switch (_joinType)
{
case JoinTypeEnum.InnerJoin:
_sb_join.Append(" INNER JOIN ");
break;
case JoinTypeEnum.LeftJoin:
_sb_join.Append(" LEFT JOIN ");
break;
case JoinTypeEnum.RightJoin:
_sb_join.Append(" RIGHT JOIN ");
break;
}
_sb_join.Append(_joinTableName + " " + _joinTableNameShortcut);
_sb_join.Append(" ON ");
_sb_join.Append("[" + _tableNameLeft + "].[" + _columnNameLeft + "]");
_sb_join.Append(" = ");
_sb_join.Append("[" + _tableNameRight + "].[" + _columnNameRight + "]");
}
public LiteJoinTable(LiteQuery query, string _tableName, string _tableAlias, JoinTypeEnum joinType) : this(query, _tableName, _tableAlias, joinType, null)
{
}
public abstract void AddJoin(JoinTypeEnum _joinType, String _joinTableName, String _joinTableNameShortcut, String _tableNameLeft, String _columnNameLeft, String _tableNameRight, String _columnNameRight);
private void PropertyInitialize()
{
_ds = new DataSet();
_joinType = JoinTypeEnum.INNER;
_joinDisambiguationType = JoinDisambiguationTypeEnum.STANDARD;
_leftTable = String.Empty;
_rightTable = String.Empty;
_outputTableName = string.Empty;
_leftJoinColumns = new List<string>();
_rightJoinColumns = new List<string>();
_outputColumns = new List<string>();
}
// Join two data tables together by providing interface to underlying main functionality method
// Adds ability to do "right" and "full" join types
// Add ability to define Output Data Table using specific fields formulas from Joined Table
// Output Column List members are expected to follow notation "ColumnName=SourceTable.SourceColumn" or
// in case of formula "ColumnName=(expression <i.e. ISNULL(SourceTable1.SourceColumn1, SourceTable2.SourceColumn1)>, optional data type)
public static DataTable JoinTables(
DataSet ds,
DataTable dtLeft,
DataTable dtRight,
List<string> joinLeftColumnList,
List<string> joinRightColumnList,
List<string> outColumnList,
JoinTypeEnum joinType,
JoinDisambiguationTypeEnum joinMatchType
)
{
if (dtRight.TableName == "")
dtRight.TableName = "__RIGHTTABLE__";
if (dtLeft.TableName == "")
dtLeft.TableName = "__LEFTABLE__";
DataTable output_dt = null;
//what kind of join is this?
switch (joinType)
{
case JoinTypeEnum.INNER:
output_dt = JoinTables(ds, dtLeft, dtRight, joinLeftColumnList, joinRightColumnList, true, joinMatchType);
break;
case JoinTypeEnum.LEFT:
output_dt = JoinTables(ds, dtLeft, dtRight, joinLeftColumnList, joinRightColumnList, false, joinMatchType);
break;
case JoinTypeEnum.RIGHT:
//Flip the arguments and do left join
output_dt = JoinTables(ds, dtRight, dtLeft, joinRightColumnList, joinLeftColumnList, false, joinMatchType);
break;
case JoinTypeEnum.FULL:
output_dt = JoinTables(ds, dtLeft, dtRight, joinLeftColumnList, joinRightColumnList, false, joinMatchType);
DataTable temp_dt_right = JoinTables(ds, dtRight, dtLeft, joinRightColumnList, joinLeftColumnList, false, joinMatchType);
DataRow[] drs = temp_dt_right.Select("JoinMatch = false");
foreach (DataRow dr in drs)
{
DataRow dr_out = output_dt.NewRow();
CopyDataRowValues(dr, dr_out, false);
output_dt.Rows.Add(dr_out);
}
break;
Default:
throw new ApplicationException("SQL syntax error: Unknown Join type '" + joinType + "'");
}
output_dt.Columns.Remove("JoinMatch");
//build the detached output table with limited specified columns returned if outColumnList is specified
//otherwise return full joined result
if (outColumnList.Count > 0)
{
DataTable specified_output_dt = new DataTable();
specified_output_dt.TableName = output_dt.TableName;
//First separate Output Column Names from their underlying source column names and formulas
//to apply formulas and build an output table
SortedList<string, string> outColumnSourceList = new SortedList<string, string>();
foreach (string outColumnName in outColumnList)
{
if (outColumnName.IndexOf("=") > -1)
{
string outColName = GetFieldFromDelimString(outColumnName, '=', 0);
string outColSourceName = GetFieldFromDelimString(outColumnName, '=', 1);
if (outColSourceName.IndexOf(".") > -1)
outColSourceName = MakeColumnName(GetFieldFromDelimString(outColSourceName, '.', -2),
GetFieldFromDelimString(outColSourceName, '.', -1));
if (!outColumnSourceList.ContainsKey(outColName))
outColumnSourceList.Add(outColName, outColSourceName);
if (!output_dt.Columns.Contains(outColName) && !output_dt.Columns.Contains(outColSourceName))
{
try
{
//Take entire expression after first "=" separating column name from expression
outColSourceName = GetFieldFromDelimString(outColumnName, '=', 1, 999);
//Add formula column to Joined Table
foreach (DataColumn joinTableColumn in output_dt.Columns)
{
string joinTableColName = joinTableColumn.ColumnName;
string joinTableColSrcTblName = string.Empty;
string joinTableColSrcName = string.Empty;
if (joinTableColName.Length > dtLeft.TableName.Length)
{
if (joinTableColName.Substring(0, dtLeft.TableName.Length) == dtLeft.TableName)
{
joinTableColSrcTblName = dtLeft.TableName;
joinTableColSrcName = joinTableColName.Substring(dtLeft.TableName.Length + 1,
joinTableColName.Length - dtLeft.TableName.Length - 1);
}
}
if (joinTableColName.Length > dtRight.TableName.Length)
{
if (joinTableColName.Substring(0, dtRight.TableName.Length) == dtRight.TableName)
{
joinTableColSrcTblName = dtRight.TableName;
joinTableColSrcName = joinTableColName.Substring(dtRight.TableName.Length + 1,
joinTableColName.Length - dtRight.TableName.Length - 1);
}
}
if (joinTableColSrcTblName.Length > 0 && joinTableColSrcName.Length > 0)
{
outColSourceName =
outColSourceName.Replace(joinTableColSrcTblName + "." + joinTableColSrcName,
joinTableColName);
}
}
output_dt.Columns.Add(new DataColumn(outColName));
output_dt.Columns[outColName].Expression = outColSourceName;
}
catch (Exception exc)
{
throw new ApplicationException(string.Format("Error '{0}' occured while creating expression ({1}) column {2}",
exc.Message, outColSourceName, outColName));
}
finally {}
}
if (!specified_output_dt.Columns.Contains(outColName)
&&
(output_dt.Columns.Contains(outColName)
|| output_dt.Columns.Contains(outColSourceName)
)
)
{
//Add column from Joined Table to Output Table
DataColumn c, c_in;
if (output_dt.Columns.Contains(outColSourceName))
c_in = output_dt.Columns[outColSourceName];
else
c_in = output_dt.Columns[outColName];
c = new DataColumn(outColName);
c.DataType = c_in.DataType;
c.MaxLength = c_in.MaxLength;
specified_output_dt.Columns.Add(c);
}
}
}
//Copy values from Joined Table to Specified Output table
foreach (DataRow dr in output_dt.Rows)
{
DataRow dr_out = specified_output_dt.NewRow();
//CopyDataRowValues(dr, dr_out, false);
foreach (string outColName in outColumnSourceList.Keys)
{
if (specified_output_dt.Columns.Contains(outColName) )
{
string colSourceName = outColumnSourceList[outColName];
//Formula - names in both tables will be the same
if (!output_dt.Columns.Contains(colSourceName) && output_dt.Columns.Contains(outColName))
colSourceName = outColName;
if (output_dt.Columns.Contains(colSourceName))
{
dr_out[outColName] = dr[colSourceName];
}
}
}
specified_output_dt.Rows.Add(dr_out);
}
output_dt = specified_output_dt;
}
return output_dt;
}
public void AddJoin(JoinTypeEnum type, TableBase table, Column columnLeft, Column columnRight, bool loadJoinTableData)
{
AddJoin(type, table, columnLeft, columnRight);
AddTableToLoadData(table);
}
/// <summary>
/// 多表联合查询
/// </summary>
/// <param name="mainType">关联主表</param>
/// <param name="fromType">关联从表</param>
/// <param name="mainField">主表关联字段</param>
/// <param name="fromField">从表关联字段</param>
/// <param name="joInType">连接类型</param>
/// <param name="isMultiTable">是否返回多表对象</param>
/// <param name="comparisonOperator">判断运算符</param>
/// <param name="joinFields">多字段对应的集合</param>
/// <returns></returns>
public virtual Sql MultiTableQuery(Type mainType, Type fromType, string mainField, string fromField, JoinTypeEnum joInType = JoinTypeEnum.Join, bool isMultiTable = false, ComparisonOperatorEnum comparisonOperator = ComparisonOperatorEnum.Equal, List <JoinField> joinFields = null)
{
_multiTables.Add(new JoinTable
{
MainType = mainType,
FromType = fromType,
MainField = mainField,
FromField = fromField,
JoInType = joInType,
ComparisonOperator = comparisonOperator,
JoinFields = joinFields
});
if (_multiTables.Count <= 0)
{
return(this);
}
Clear();
Select();
var startMainType = _multiTables[0].MainType;
var startMainTableName = startMainType.Name;
var startMainProperties = startMainType.GetProperties();
foreach (var p in startMainProperties)
{
var asName = p.Name;
if (isMultiTable)
{
asName = $"{startMainTableName}_{p.Name}";
}
AddTf(startMainTableName, p.Name).AddBs("AS").AddBs(asName).Comma();
}
foreach (var multiTable in _multiTables)
{
var fromTableName = multiTable.FromType.Name;
var fromProperties = multiTable.FromType.GetProperties();
foreach (var p in fromProperties)
{
var asName = p.Name;
if (isMultiTable)
{
asName = $"{fromTableName}_{p.Name}";
}
AddTf(fromTableName, p.Name).AddBs("AS").AddBs(asName).Comma();
}
}
SqlText.Remove(SqlText.Length - 1, 1);
From().Add(startMainTableName);
foreach (var multiTable in _multiTables)
{
switch (multiTable.JoInType)
{
case JoinTypeEnum.Join:
Join();
break;
case JoinTypeEnum.InnerJoin:
InnerJoin();
break;
case JoinTypeEnum.LeftJoin:
LeftJoin();
break;
case JoinTypeEnum.RightJoin:
RightJoin();
break;
default:
Join();
break;
}
var fromTableName = multiTable.FromType.Name;
Add(fromTableName).On();
if (multiTable.JoinFields == null || multiTable.JoinFields.Count == 0)
{
AddTf(multiTable.MainType.Name, multiTable.MainField);
switch (multiTable.ComparisonOperator)
{
case ComparisonOperatorEnum.Equal:
Equal();
break;
case ComparisonOperatorEnum.EqualNot:
EqualNot();
break;
default:
Equal();
break;
}
AddTf(fromTableName, multiTable.FromField);
}
else
{
foreach (var f in multiTable.JoinFields)
{
switch (f.LogicalOperator)
{
case LogicalOperatorEnum.And:
And();
break;
case LogicalOperatorEnum.Or:
Or();
break;
}
}
}
}
return(this);
}
