/// <summary>
/// Appends a new INNER JOIN between this table an the specified table matching between this tables parentfield and the other tables child field
/// </summary>
/// <param name="table"></param>
/// <param name="parentField"></param>
/// <param name="childField"></param>
/// <returns></returns>
public DBJoin InnerJoin(DBTable table, string parentfield, string childfield)
{
DBField parent = DBField.Field(this.Catalog, this.Owner, this.Name, parentfield);
DBField child = DBField.Field(table.Catalog, table.Owner, table.Name, childfield);
return(InnerJoin(table, parent, child));
}
public DBTableIndex AddColumn(string name, Order order)
{
DBOrder orderitem = DBOrder.OrderBy(order, DBField.Field(name));
ColumnOrders.Add(orderitem);
return(this);
}
/// <summary>
/// Adds 2 columns to the index with the ordering as specified
/// </summary>
/// <param name="column1"></param>
/// <param name="order1"></param>
/// <param name="column2"></param>
/// <param name="order2"></param>
/// <returns></returns>
public DBCreateIndexQuery Columns(string column1, Order order1, string column2, Order order2)
{
this.ColumnOrders.Add(DBOrder.OrderBy(order1, DBField.Field(column1)));
this.ColumnOrders.Add(DBOrder.OrderBy(order2, DBField.Field(column2)));
return(this);
}
/// <summary>
/// Adds an INNER JOIN from the results of this sub query to a second table
/// where this tables parent field matched the child field
/// </summary>
/// <param name="table"></param>
/// <param name="parentfield"></param>
/// <param name="childfield"></param>
/// <returns></returns>
public DBJoin InnerJoin(string table, string parentfield, string childfield)
{
DBTable tbl = DBTable.Table(table);
DBField parent = DBField.Field(this.Alias, parentfield);
DBField child = DBField.Field(table, parentfield);
return(InnerJoin(tbl, parent, child));
}
/// <summary>
/// Adds the columns to the index with their default ordering
/// </summary>
/// <param name="columnlist"></param>
/// <returns></returns>
public DBCreateIndexQuery Columns(params string[] columnlist)
{
if (null != columnlist)
{
foreach (string col in columnlist)
{
this.ColumnOrders.Add(DBOrder.OrderBy(Order.Default, DBField.Field(col)));
}
}
return(this);
}
/// <summary>
/// Creates the first where clause in this DBDeleteQuery using the fields and a sub select that is evaluated at execution time
/// </summary>
/// <param name="field">The field which will be compared to the specified values</param>
/// <param name="select">The sub select to be evaluated and compared</param>
/// <returns>Itself so further statements can be chained</returns>
public DBDeleteQuery WhereIn(string field, DBSelectQuery select)
{
DBField fld = DBField.Field(field);
DBSubQuery subsel = DBSubQuery.SubSelect(select);
DBComparison compare = DBComparison.In(fld, subsel);
DBFilterSet fs = DBFilterSet.Where(compare);
this._where = fs;
this._last = fs;
return(this);
}
public static DBSelectSet SelectFields(params string[] fields)
{
DBSelectSet set = new DBSelectSet();
if (fields != null)
{
foreach (string fld in fields)
{
DBField fref = DBField.Field(fld);
set.Results.Add(fref);
set.Last = fref;
}
}
return(set);
}
/// <summary>
/// Creates the first where clause in this DBDeleteQuery using the fields and a series of constant values
/// </summary>
/// <param name="field">The field which will be compared to the psecified values</param>
/// <param name="values">The set of values to limit the deletion to</param>
/// <returns>Itself so further statements can be chained</returns>
public DBDeleteQuery WhereIn(string field, params object[] values)
{
DBField fld = DBField.Field(field);
List <DBClause> items = new List <DBClause>();
if (values != null && values.Length > 0)
{
foreach (object val in values)
{
items.Add(DBConst.Const(val));
}
}
DBComparison compare = DBComparison.In(fld, items.ToArray());
DBFilterSet fs = DBFilterSet.Where(compare);
this._where = fs;
this._last = fs;
return(this);
}
public DBSelectSet And(string field)
{
DBField fld = DBField.Field(field);
return(this.And(fld));
}
public DBFilterSet Or(string owner, string table, string field, Compare op, DBClause right)
{
return(Or(DBField.Field(owner, table, field), op, right));
}
public DBFilterSet Or(string table, string field, Compare op, DBClause clause)
{
return(Or(DBField.Field(table, field), op, clause));
}
//
// Statement factory methods
//
#region public DBFilterSet And(DBClause combine) + 4 overloads
public DBFilterSet And(string field, Compare op, DBClause clause)
{
return(And(DBField.Field(field), op, clause));
}
internal static DBGroupBySet GroupBy(string owner, string table, string field)
{
DBField fld = DBField.Field(owner, table, field);
return(GroupBy(fld));
}
//
// static factory methods
//
#region public static DBFunction IsNull(string field, DBClause otherwise) + 3 overloads
/// <summary>
/// Creates a new IsNull function reference to be executed on the database server - ISNULL(field, otherwise)
/// </summary>
/// <param name="field"></param>
/// <param name="otherwise"></param>
/// <returns></returns>
public static DBFunction IsNull(string field, DBClause otherwise)
{
return(IsNull(DBField.Field(field), otherwise));
}
public static DBSelectSet Select(string field)
{
DBField fref = DBField.Field(field);
return(Select(fref));
}
internal static DBOrderSet OrderBy(string field, Order order)
{
DBField fld = DBField.Field(field);
return(OrderBy(fld, order));
}
//
// public methods
//
#region public DBInsertQuery Field(string field) + 4 overloads
/// <summary>
/// Specify explictly one of the schema.table.columns to be set when performing this insert
/// </summary>
/// <param name="owner">The table schema owner</param>
/// <param name="table">The name of the table</param>
/// <param name="field">The name of the column</param>
/// <returns>Iteslf so statements can be chained</returns>
public DBInsertQuery Field(string owner, string table, string field)
{
DBField fld = DBField.Field(owner, table, field);
return(Field(fld));
}
/// <summary>
/// Creates the first where clause by using the specified comparison between the specified owner.table.field and the clause
/// </summary>
/// <param name="fieldOwner">The schema owner of the table</param>
/// <param name="fieldTable">The table the field belongs to</param>
/// <param name="fieldName">The field to compare against</param>
/// <param name="op">The comparison operator</param>
/// <param name="value">The value clause</param>
/// <returns>Itself so further statements can be combined</returns>
public DBDeleteQuery WhereField(string fieldOwner, string fieldTable, string fieldName, Compare op, DBClause value)
{
DBField fld = DBField.Field(fieldOwner, fieldTable, fieldName);
return(Where(fld, op, value));
}
/// <summary>
/// Creates the first where clause by using the specified comparison between the specified field and the clause
/// </summary>
/// <param name="field">The field to compare against</param>
/// <param name="op">The comparison operator</param>
/// <param name="value">The value clause</param>
/// <returns>Itself so further statements can be combined</returns>
public DBDeleteQuery WhereField(string field, Compare op, DBClause value)
{
DBField fld = DBField.Field(field);
return(Where(fld, op, value));
}
/// <summary>
/// Creates a new IsNull function reference to be executed on the database server - ISNULL(schema.table.field, otherwise)
/// </summary>
/// <param name="owner"></param>
/// <param name="table"></param>
/// <param name="field"></param>
/// <param name="otherwise"></param>
/// <returns></returns>
public static DBFunction IsNull(string owner, string table, string field, DBClause otherwise)
{
return(IsNull(DBField.Field(owner, table, field), otherwise));
}
public static DBSelectSet SelectAggregate(AggregateFunction funct, string table, string field)
{
DBField fref = DBField.Field(table, field);
return(SelectAggregate(funct, fref));
}
/// <summary>
/// Adds a column to the index with the ordering specified
/// </summary>
/// <param name="column"></param>
/// <param name="order"></param>
/// <returns></returns>
public DBCreateIndexQuery Columns(string column, Order order)
{
this.ColumnOrders.Add(DBOrder.OrderBy(order, DBField.Field(column)));
return(this);
}
internal static DBGroupBySet GroupBy(string field)
{
DBField fld = DBField.Field(field);
return(GroupBy(fld));
}
public static DBSelectSet Select(string owner, string table, string field)
{
DBField fref = DBField.Field(owner, table, field);
return(Select(fref));
}
public DBUpdateQuery AndSet(string field, DBClause toValue)
{
DBField fld = DBField.Field(field);
return(this.AndSet(fld, toValue));
}
/// <summary>
/// Specify the name of one of the columns to be set when performing this insert
/// </summary>
/// <param name="field">The name of the column</param>
/// <returns>Itself so statements can be chained</returns>
public DBInsertQuery Field(string field)
{
DBField fld = DBField.Field(field);
return(Field(fld));
}
/// <summary>
/// Creates a new Count('field') aggregation
/// </summary>
/// <param name="field"></param>
/// <returns></returns>
public static DBAggregate Count(string field)
{
return(Aggregate(AggregateFunction.Count, DBField.Field(field)));
}
protected virtual DBClause DoRead(string element, XmlReader reader, XmlReaderContext context)
{
DBClause c = null;
switch (element)
{
case (XmlHelper.Select):
c = DBSelectQuery.Select();
break;
case (XmlHelper.Delete):
c = DBDeleteQuery.Delete();
break;
case (XmlHelper.Update):
c = DBUpdateQuery.Update();
break;
case (XmlHelper.Insert):
c = DBInsertQuery.InsertInto();
break;
case (XmlHelper.Script):
c = DBQuery.Script();
break;
case (XmlHelper.Use):
c = DBUseQuery.Use();
break;
case (XmlHelper.Table):
c = DBTable.Table();
break;
case (XmlHelper.Fields):
c = DBSelectSet.Select();
break;
case (XmlHelper.AField):
c = DBField.Field();
break;
case (XmlHelper.AllFields):
c = DBField.AllFields();
break;
case (XmlHelper.From):
c = DBTableSet.From();
break;
case (XmlHelper.Where):
c = DBFilterSet.Where();
break;
case (XmlHelper.Group):
c = DBGroupBySet.GroupBy();
break;
case (XmlHelper.Order):
c = DBOrderSet.OrderBy();
break;
case (XmlHelper.Assignments):
c = DBAssignSet.Assign();
break;
case (XmlHelper.Values):
c = DBValueSet.Values();
break;
case (XmlHelper.Join):
c = DBJoin.Join();
break;
case (XmlHelper.Function):
c = DBFunction.Function();
break;
case (XmlHelper.Constant):
c = DBConst.Null();
break;
case (XmlHelper.Top):
c = DBTop.Top();
break;
case (XmlHelper.UnaryOp):
c = DBComparison.Not();
break;
case (XmlHelper.Compare):
c = DBComparison.Compare();
break;
case (XmlHelper.Between):
c = DBComparison.Between();
break;
case (XmlHelper.Parameter):
//parameter is a special case.
//we add them to akeyed colection if they are not already registered
//then at the end we set the values at the end
string name = reader.GetAttribute(XmlHelper.Name);
DBParam aparam;
if (context.Parameters.TryGetParameter(name, out aparam))
{
c = aparam;
}
else
{
aparam = DBParam.Param();
aparam.Name = name;
context.Parameters.Add(aparam);
c = aparam;
}
break;
case (XmlHelper.OrderBy):
c = DBOrder.OrderBy();
break;
case (XmlHelper.Calculation):
c = DBCalc.Calculate();
break;
case (XmlHelper.Aggregate):
c = DBAggregate.Aggregate();
break;
case (XmlHelper.ValueGroup):
c = DBValueGroup.Empty();
break;
case (XmlHelper.BooleanOperator):
c = DBBooleanOp.Compare();
break;
case (XmlHelper.Assign):
c = DBAssign.Assign();
break;
case (XmlHelper.InnerSelect):
c = DBSubQuery.SubSelect();
break;
case (XmlHelper.Multiple):
c = DBMultiComparisonRef.Many();
break;
case (XmlHelper.QueryOptionSet):
c = new DBQueryHintOptionSet();
break;
case (XmlHelper.QueryOption):
c = DBQueryHintOption.QueryOption();
break;
case (XmlHelper.CreateSproc):
c = DBCreateProcedureQuery.CreateProcedure();
break;
case (XmlHelper.Declare):
c = DBDeclaration.Declare();
break;
case (XmlHelper.CreateTable):
c = DBCreateTableQuery.Table();
break;
case (XmlHelper.ColumnDefinition):
c = DBColumn.Column();
break;
case (XmlHelper.PrimaryKey):
c = DBPrimaryKey.PrimaryKey();
break;
case (XmlHelper.ForeignKey):
c = DBForeignKey.ForeignKey();
break;
case (XmlHelper.CreateIndex):
c = DBCreateIndexQuery.Index();
break;
case (XmlHelper.CreateView):
c = DBCreateViewQuery.CreateView();
break;
case (XmlHelper.CreateSequence):
c = DBCreateSequenceQuery.Sequence();
break;
case (XmlHelper.DropTable):
c = DBDropTableQuery.DropTable();
break;
case (XmlHelper.DropIndex):
c = DBDropIndexQuery.DropIndex();
break;
case (XmlHelper.DropView):
c = DBDropViewQuery.DropView();
break;
case (XmlHelper.DropSproc):
c = DBDropProcedureQuery.DropProcedure();
break;
case (XmlHelper.DropSequence):
c = DBDropSequenceQuery.DropSequence();
break;
default:
throw XmlHelper.CreateException("The XML data could not be deserialized because the element {1} was not recognised. {0}", reader, null, element);
}
if (c != null)
{
c.ReadXml(reader, context);
}
return(c);
}
public DBTableSet InnerJoin(string table, string parentfield, Compare op, string childfield)
{
DBTable tref = DBTable.Table(table);
return(this.InnerJoin(tref, DBField.Field(parentfield), op, DBField.Field(childfield)));
}
