// TODO: Create Command interface
public bool executeStatement(string strSql)
{
bool wasSuccessful = false;
// TODO: Think how to track multiple tables/TableContexts
// Note that the constructor will set up the table named
// in the SELECT statement in _table.
CommandParts updateParts = new CommandParts(_database, _table, strSql, CommandParts.COMMAND_TYPES.UPDATE);
if (MainClass.bDebug)
{
Console.WriteLine("SELECT: " + updateParts.strSelect);
Console.WriteLine("FROM: " + updateParts.strFrom);
if (!string.IsNullOrEmpty(updateParts.strInnerJoinKludge))
{
throw new Exception("Syntax error: INNER JOIN in an UPDATE statement is not supported: " + strSql);
}
Console.WriteLine("WHERE: " + updateParts.strWhere); // Note that WHEREs aren't applied to inner joined tables right now.
Console.WriteLine("ORDER BY: " + updateParts.strOrderBy);
}
_table = _database.getTableByName(updateParts.strTableName);
DataTable dtThrowAway = new DataTable();
WhereProcessor.ProcessRows(ref dtThrowAway, _table, updateParts);
return(wasSuccessful);
}
// TODO: Create Command interface
public DataTable executeStatement(string strSql)
{
DataTable dtReturn = new DataTable();
// TODO: Think how to track multiple tables/TableContexts
// Note that the constructor will set up the table named
// in the SELECT statement in _table.
CommandParts selectParts = new CommandParts(_database, _table, strSql, CommandParts.COMMAND_TYPES.SELECT);
if (MainClass.bDebug)
{
string strDebug = "SELECT: " + selectParts.strSelect + "\n";
strDebug += "FROM: " + selectParts.strFrom + "\n";
if (!string.IsNullOrEmpty(selectParts.strInnerJoinKludge))
{
strDebug += "INNER JOIN: " + selectParts.strInnerJoinKludge + "\n";
}
strDebug += "WHERE: " + selectParts.strWhere + "\n"; // Note that WHEREs aren't applied to inner joined tables right now.
strDebug += "ORDER BY: " + selectParts.strOrderBy + "\n";
SqlDbSharpLogger.LogMessage(strDebug, "SelectCommand executeStatement");
}
_table = _database.getTableByName(selectParts.strTableName);
Queue <TableContext> qAllTables = new Queue <TableContext>();
qAllTables.Enqueue(_table);
dtReturn = _initDataTable(selectParts);
WhereProcessor.ProcessRows(ref dtReturn, _table, selectParts);
//=====================================================================
// POST-PROCESS INNER JOINS
// (Joins are only in selects, so this isn't part of WhereProcessing.)
//
// To take account of joins, we basically need to create a SelectParts
// per inner join. So we need to create a WHERE from the table we
// just selected and then send those values down to a new _selectRows.
//=====================================================================
if (selectParts.strInnerJoinKludge.Length > 0)
{
if (selectParts.qInnerJoinFields.Count < 1)
{
selectParts.qInnerJoinFields.EnqueueIfNotContains("*"); // Kludge for "SELECT * FROM Table1 INNER JOIN..." or "SELECT test, * From...", etc
}
// TODO: Why aren't we just throwing in the whole selectParts again?
dtReturn = _processInnerJoin(qAllTables, dtReturn, selectParts.strInnerJoinKludge, selectParts.strTableName, selectParts.strOrderBy, selectParts.qInnerJoinFields);
// Now we need to make sure the order of the DataColumns reflects what we had
// in the original SQL. At least initially, column order wasn't guaranteed in
// _processInnerJoin, as it would add columns first for the "main" table and then
// for each "inner SELECT".
string strFromSelect = string.Join(", ", selectParts.qstrAllColumnNames.ToArray());
string strInTable = string.Join(", ", dtReturn.Columns.Cast <DataColumn>().Select(c => c.ColumnName).ToArray());
MainClass.logIt(string.Format(@"Select fields: {0}
Fields pushed into dtReturn: {1}", strFromSelect, strInTable));
try
{
string[] astrFromSelect = selectParts.qstrAllColumnNames.ToArray();
for (int i = 0; i < astrFromSelect.Length; i++)
{
dtReturn.Columns[astrFromSelect[i]].SetOrdinal(i);
}
// TODO: There are better ways to do this.
// TODO: Figure out if this handles all fuzzy name translations
// earlier in the SELECT process.
if (selectParts.lstrJoinONLYFields.Count() > 0)
{
foreach (string colName in selectParts.lstrJoinONLYFields)
{
dtReturn.Columns.Remove(colName);
}
}
}
catch (Exception e)
{
throw new SyntaxException("Problem reordering columns in inner join -- " + e.ToString());
}
}
//=====================================================================
// EO POST-PROCESS INNER JOINS
//=====================================================================
// strOrderBy has had all whitespace shortened to one space, so we can get away with the hardcoded 9.
if (null != selectParts.strOrderBy && selectParts.strOrderBy.Length > 9)
{
// ORDER BY needs to make sure it's not sorting on a fuzzy named column
// that may not have been explicitly selected in the SELECT.
string[] astrOrderByFields = selectParts.strOrderBy.Substring(9).Split(','); // Substring(9) to get rid of "ORDER BY " <<< But, ultimately, why not tokenize here too?
string strCleanedOrderBy = string.Empty;
foreach (string orderByClause in astrOrderByFields)
{
bool ascNotDesc = true;
string strOrderByClause = orderByClause.Trim();
string strField = orderByClause.Trim();
if (strField.Split().Length > 1)
{
strField = strOrderByClause.Substring(0, strOrderByClause.IndexOf(' ')).Trim();
string strAscDesc = strOrderByClause.Substring(strOrderByClause.IndexOf(' ')).Trim();
ascNotDesc = (-1 == strAscDesc.IndexOf("DESC", StringComparison.CurrentCultureIgnoreCase));
}
strOrderByClause += ","; // This is the default value if there's no fuzziness, and it needs the comma put back.
// TODO: Integrate fields prefixed by specific table names.
if (!dtReturn.Columns.Contains(strField))
{
// Check for fuzziness.
foreach (TableContext table in qAllTables)
{
if (!table.containsColumn(strField, false) && table.containsColumn(strField, true))
{
strOrderByClause = table.getRawColName(strField)
+ (ascNotDesc ? " ASC" : " DESC")
+ ",";
break;
}
}
}
strCleanedOrderBy += " " + strOrderByClause;
}
dtReturn.DefaultView.Sort = strCleanedOrderBy.Trim(',');
dtReturn = dtReturn.DefaultView.ToTable();
}
if (selectParts.dictRawNamesToASNames.Count > 0)
{
try
{
foreach (KeyValuePair <string, string> kvp in selectParts.dictRawNamesToASNames)
{
dtReturn.Columns[kvp.Key].ColumnName = kvp.Value;
}
}
catch (Exception e)
{
throw new SyntaxException("Illegal AS usage: " + e.ToString());
}
}
return(dtReturn);
}
