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