/// <summary>
/// Gets the full field name, including the parent table name and brackets.
/// </summary>
/// <returns>The full field name.</returns>
public string GetFullName()
{
// return table.column string
if (Column == "*" || _table.Columns.Contains(Column))
{
return(string.Format("{0}.{1}",
OleDbSchema.GetFullTableName(_table),
OleDbSchema.BracketName(Column)));
}
// column is not part of a table (e.g. expression)
return(OleDbSchema.BracketName(Column));
}
// build the FROM clause
string BuildFromClause()
{
// build list of tables in query
var tables = new List <DataTable>();
foreach (QueryField field in QueryFields)
{
string tableName = field.Table;
DataTable table = _schema.Tables[tableName];
if (table != null && !tables.Contains(table))
{
tables.Add(table);
}
}
// save table count so caller can check this
_tableCount = tables.Count;
// build list of joined tables so each table is related to the next one
var qTables = new List <DataTable>();
bool done = false;
while (qTables.Count < tables.Count && !done)
{
done = true;
foreach (DataTable dt in tables)
{
bool inserted = InsertRelatedTable(dt, qTables);
if (inserted)
{
done = false;
}
}
}
// build join list
var qJoins = new List <string>();
for (int index = 0; index < qTables.Count - 1; index++)
{
// get relation
DataTable dt1 = (DataTable)qTables[index];
DataTable dt2 = (DataTable)qTables[index + 1];
DataRelation dr = GetRelation(dt1, dt2);
// build join statement
qJoins.Add(string.Format("{0}.{1} = {2}.{3}",
OleDbSchema.GetFullTableName(dr.ParentTable),
dr.ParentColumns[0].ColumnName,
OleDbSchema.GetFullTableName(dr.ChildTable),
dr.ChildColumns[0].ColumnName));
}
// build from statement
StringBuilder sb = new StringBuilder();
for (int i = 0; i < qTables.Count - 1; i++)
{
DataTable dt = qTables[i] as DataTable;
if (sb.Length > 0)
{
sb.Append("\r\n\t");
}
sb.AppendFormat("({0} INNER JOIN", OleDbSchema.GetFullTableName(dt));
}
sb.AppendFormat(" {0}", OleDbSchema.GetFullTableName(qTables[qTables.Count - 1]));
for (int i = qJoins.Count - 1; i >= 0; i--)
{
string join = qJoins[i] as string;
sb.AppendFormat("\r\n\tON {0})", join);
}
// not all tables joined? probably not what the user wants...
_missingJoins = qTables.Count < tables.Count;
// add tables that couldn't be joined
if (_missingJoins)
{
foreach (DataTable dt in tables)
{
if (!qTables.Contains(dt))
{
sb.AppendFormat(", {0}", OleDbSchema.GetFullTableName(dt));
qTables.Add(dt);
}
}
}
// done
return(sb.ToString());
}
