private bool _sqlIsDirty; // SQL needs to be regenerated
#endregion Members
//----------------------------------------------------------------
#region Constructor
/// <summary>
/// Initializes a new instance of a <see cref="QueryBuilder"/>.
/// </summary>
/// <param name="schema"><see cref="OleDbSchema"/> used by the query builder.</param>
public QueryBuilder(OleDbSchema schema)
{
_sql = null;
_schema = schema;
_queryFields = new QueryFieldCollection();
_queryFields.ListChanged += _queryFields_ListChanged;
}
// build the SELECT clause
private string BuildSelectClause()
{
var sb = new StringBuilder();
foreach (QueryField field in QueryFields)
{
if (field.Output)
{
// add separator
if (sb.Length > 0)
{
sb.Append(",\r\n\t");
}
// add field expression ("table.column" or "colexpr")
string item = field.GetFullName(GroupBy);
sb.Append(item);
// add alias (use brackets to contain spaces, reserved words, etc)
if (!string.IsNullOrEmpty(field.Alias))
{
sb.AppendFormat(" AS {0}", OleDbSchema.BracketName(field.Alias));
}
}
}
return(sb.ToString());
}
/// <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));
}
public static Type GetAltaxoColumnType(Type oledbType)
{
if (OleDbSchema.IsNumeric(oledbType))
{
return(typeof(Altaxo.Data.DoubleColumn));
}
else if (OleDbSchema.IsDateTime(oledbType))
{
return(typeof(Altaxo.Data.DateTimeColumn));
}
else
{
return(typeof(Altaxo.Data.TextColumn));
}
}
/// <summary>
/// Gets a <see cref="OleDbSchema"/> that contains the schema for a
/// given connection string.
/// </summary>
/// <param name="connString">OleDb connection string used to
/// initialize the new <see cref="OleDbSchema"/>.</param>
/// <returns>
/// A new <see cref="OleDbSchema"/> containing the schema for the
/// given <paramref name="connString"/> or null if the connection
/// string is invalid.
/// </returns>
public static OleDbSchema GetSchema(string connString)
{
// trivial test
connString = OleDbConnString.TranslateConnectionString(connString);
if (string.IsNullOrEmpty(connString) ||
connString.IndexOf("Provider=", StringComparison.OrdinalIgnoreCase) < 0)
{
return(null);
}
// connString looks OK, try getting the schema
try
{
var ds = new OleDbSchema
{
ConnectionString = connString
};
return(ds);
}
catch
{
return(null);
}
}
// build the FROM clause
private 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
var dt1 = qTables[index];
var dt2 = 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
var sb = new StringBuilder();
for (int i = 0; i < qTables.Count - 1; i++)
{
var 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());
}