//Implements Join 2 Data Tables logic //INNER vs. LEFT OUTER join logic is driven by matchingRowsOnly flag //Disambiguation logic is driven by joinMatchType public static DataTable JoinTables( DataSet ds, DataTable dtLeft, DataTable dtRight, List<string> joinLeftColumnList, List<string> joinRightColumnList, bool matchingRowsOnly, JoinDisambiguationTypeEnum joinMatchType ) { DataTable left_dt, right_dt, joined_dt; //Make sure both tables are in the DataSet otherwise can't use Relationships left_dt = dtLeft; bool detach_left_dt = false; if (!ds.Tables.Contains(left_dt.TableName)) { if (left_dt.DataSet != null) left_dt = dtLeft.Copy(); ds.Tables.Add(left_dt); detach_left_dt = true; } right_dt = dtRight; bool detach_right_dt = false; if (!ds.Tables.Contains(right_dt.TableName)) { if (right_dt.DataSet != null) right_dt = dtRight.Copy(); ds.Tables.Add(right_dt); detach_right_dt = true; } //create a relationship between the two tables List<DataColumn> left_join_col = new List<DataColumn>(); foreach (string col_name in joinLeftColumnList) { string use_col_name = col_name; if (use_col_name.IndexOf(".") > -1) use_col_name = GetFieldFromDelimString(use_col_name, '.', -1); left_join_col.Add(left_dt.Columns[use_col_name]); } List<DataColumn> right_join_col = new List<DataColumn>(); foreach (string col_name in joinRightColumnList) { string use_col_name = col_name; if (use_col_name.IndexOf(".") > -1) use_col_name = GetFieldFromDelimString(use_col_name, '.', -1); right_join_col.Add(right_dt.Columns[use_col_name]); } DataRelation joinRelation = new DataRelation("_JOIN_RELATION_", left_join_col.ToArray(), right_join_col.ToArray(), false); ds.Relations.Add(joinRelation); //build the detached temporary table to contain ALL columns from both joined tables List<string> join_table_col_names = GetTableColumnNames(left_dt, "."); join_table_col_names.AddRange(GetTableColumnNames(right_dt, ".")); //foreach (string right_join_col_nm in joinRightColumnList) // join_table_col_names.Remove(right_join_col_nm); //no need repeating columns we join on joined_dt = BuildOutputTable(ds, join_table_col_names); joined_dt.Columns.Add("JoinMatch", System.Type.GetType("System.Boolean")); joined_dt.TableName = "_" + left_dt.TableName + "_TO_" + right_dt.TableName + (matchingRowsOnly ? "_INNER_" : "_LEFT_") + "JOIN_RESULT_"; List<DataRow> matched_left_rows = new List<DataRow>(); List<DataRow> matched_right_rows = new List<DataRow>(); foreach (DataRow dr_left in left_dt.Rows) { //Get the related rows from the "right" table DataRow[] drs = dr_left.GetChildRows("_JOIN_RELATION_"); if (drs.GetLength(0) > 0) { bool leftRowMatched = false; bool rightRowMatched = false; foreach (DataRow dr_right in drs) { leftRowMatched = matched_left_rows.Contains(dr_left); rightRowMatched = matched_right_rows.Contains(dr_right); //Add new row if // a) STANDARD disambiguation mode // b) SOFT disambiguation mode and this Left row has not been matched yet // d) SOFT_FIRST_RIGHT disambiguation mode and this is first Right row has not been added // d) SOFT_MOVING_RIGHT disambiguation mode and Right row has not been added bool addNewRow = false; switch (joinMatchType) { case JoinDisambiguationTypeEnum.STANDARD: addNewRow = true; if (!leftRowMatched) matched_left_rows.Add(dr_left); if (!rightRowMatched) matched_right_rows.Add(dr_right); break; case JoinDisambiguationTypeEnum.SOFT_FIRST_RIGHT: addNewRow = (!leftRowMatched); if (!leftRowMatched) matched_left_rows.Add(dr_left); if (!rightRowMatched) matched_right_rows.Add(dr_right); break; case JoinDisambiguationTypeEnum.SOFT_MOVING_RIGHT: addNewRow= (!leftRowMatched && !rightRowMatched); if (addNewRow) { if (!leftRowMatched) matched_left_rows.Add(dr_left); if (!rightRowMatched) matched_right_rows.Add(dr_right); } break; default: break; } if (addNewRow) { bool rowAdded = false; DataRow dr_dest = joined_dt.NewRow(); dr_dest["JoinMatch"] = true; CopyDataRowValues(dr_left, dr_dest, true); CopyDataRowValues(dr_right, dr_dest, true); joined_dt.Rows.Add(dr_dest); rowAdded = true; if (!rowAdded) { joined_dt.Rows.Add(dr_dest); rowAdded = true; } } } //Check if Left row is still left unmatched that happens in SOFT_MOVING_RIGHT mode //when left side has more matching rows than right //OUTSTANDING: Is "matchingRowsOnly" mode really applicable here??? if (!matched_left_rows.Contains(dr_left) && !matchingRowsOnly) { DataRow dr_dest = joined_dt.NewRow(); dr_dest["JoinMatch"] = false; CopyDataRowValues(dr_left, dr_dest, true); joined_dt.Rows.Add(dr_dest); } } else { if (!matchingRowsOnly) { //No matching rows, just copy from left table DataRow dr_dest = joined_dt.NewRow(); dr_dest["JoinMatch"] = false; CopyDataRowValues(dr_left, dr_dest, true); joined_dt.Rows.Add(dr_dest); } } } //delete the temporary relationship we created above ds.Relations.Remove("_JOIN_RELATION_"); if (detach_left_dt) ds.Tables.Remove(left_dt); if (detach_right_dt) ds.Tables.Remove(right_dt); // return joined_dt; }
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; }