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