public void _06_FullComplexInnerSelectXmlTest()
{
DBSelectQuery orderValue = DBQuery.Select()
.Sum(DBField.Field("UnitPrice") * DBField.Field("Quantity") * (DBConst.Const(1.0) - DBField.Field("Discount"))).As("OrderValue")
.And(DBFunction.IsNull("OrderID", DBConst.Const("No ID")))
.Field("O", "customerID")
.From("Orders").As("O")
.InnerJoin("Order Details").As("OD")
.On("OD", "OrderID", Compare.Equals, "O", "OrderID")
.GroupBy("O", "CustomerID");
DBQuery q = DBQuery.SelectTopN(10)
.Field("CUST", "CompanyName").As("Company Name")
.Field("CUST", "CustomerID").As("Customer ID")
.And(DBFunction.IsNull("CUST", "ContactName", DBConst.Const("No Contact"))).As("Contact")
.Sum("ORD", "OrderValue").As("Total Value")
.From("Customers").As("CUST")
.InnerJoin(orderValue).As("ORD")
.On("ORD", "CustomerID", Compare.Equals, "CUST", "CustomerID")
.WhereField("ORD", "OrderValue", Compare.GreaterThanEqual, DBConst.Const(500.0))
.GroupBy("CUST", "CompanyName").And("CUST", "CustomerID")
.OrderBy("Total Value", Order.Descending);
q = SerializeAndDeserialzeQueryToMatch(q, "Complex Inner Select Test");
}
/// <summary>
/// Reads each inner Xml element and returns true if it was a known xml element
/// </summary>
/// <param name="reader"></param>
/// <param name="context"></param>
/// <returns></returns>
protected override bool ReadAnInnerElement(System.Xml.XmlReader reader, XmlReaderContext context)
{
bool b;
if (this.IsElementMatch(XmlHelper.Into, reader, context) && !reader.IsEmptyElement && reader.Read())
{
this._into = (DBTable)this.ReadNextInnerClause(XmlHelper.Into, reader, context);
b = true;
}
else if (this.IsElementMatch(XmlHelper.Fields, reader, context))
{
this._fields = (DBSelectSet)context.Factory.Read(XmlHelper.Fields, reader, context);
b = true;
}
else if (this.IsElementMatch(XmlHelper.Values, reader, context))
{
this._values = (DBValueSet)context.Factory.Read(XmlHelper.Values, reader, context);
b = true;
}
else if (this.IsElementMatch(XmlHelper.Select, reader, context))
{
this._innerselect = (DBSelectQuery)context.Factory.Read(XmlHelper.Select, reader, context);
this._innerselect.IsInnerQuery = true;
b = true;
}
else
{
b = base.ReadAnInnerElement(reader, context);
}
return(b);
}
public void Test_03_ScriptOfSelectCustomersWithOrdersOver500()
{
DBSelectQuery orderValue = DBQuery.Select()
.Sum(DBField.Field("UnitPrice") * DBField.Field("Quantity") * (DBConst.Const(1.0) - DBField.Field("Discount"))).As("OrderValue")
.And(DBFunction.IsNull("OrderID", DBConst.Const("No ID")))
.Field("O", "customerID")
.From("Orders").As("O")
.InnerJoin("Order Details").As("OD")
.On("OD", "OrderID", Compare.Equals, "O", "OrderID")
.GroupBy("O", "CustomerID");
DBQuery q = DBQuery.SelectTopN(10)
.Field("CUST", "CompanyName").As("Company Name")
.Field("CUST", "CustomerID").As("Customer ID")
.And(DBFunction.IsNull("CUST", "ContactName", DBConst.Const("No Contact"))).As("Contact")
.Sum("ORD", "OrderValue").As("Total Value")
.From("Customers").As("CUST")
.InnerJoin(orderValue).As("ORD")
.On("ORD", "CustomerID", Compare.Equals, "CUST", "CustomerID")
.WhereField("ORD", "OrderValue", Compare.GreaterThanEqual, DBConst.Const(500.0))
.GroupBy("CUST", "CompanyName").And("CUST", "CustomerID")
.OrderBy("Total Value", Order.Descending);
string sql = this.OutputSql(q, "SelectCustomersWithOrdersOver500", true);
string xml = this.OutputXML(q, "SelectCustomersWithOrdersOver500");
ValidateXml(xml, sql, "SelectCustomersWithOrdersOver500");
}
/// <summary>
/// Begins a new SELECT [catalog].[owner].[table].* statment
/// </summary>
/// <param name="table">The name (or alias) of the table</param>
/// <param name="owner">The schema owner of the table</param>
/// <param name="catalog">The catalog or database of the table</param>
/// <returns>A new DBSelectQuery to support statement chaining</returns>
public static DBSelectQuery SelectAll(string catalog, string owner, string table)
{
DBSelectQuery q = new DBSelectQuery();
q.SelectSet = DBSelectSet.SelectAll(catalog, owner, table);
q.Last = q.SelectSet;
return(q);
}
/// <summary>
/// Begins a new SELECT [table].* statment
/// </summary>
/// <param name="table">The name (or alias) of the table</param>
/// <returns>A new DBSelectQuery to support statement chaining</returns>
public static DBSelectQuery SelectAll(string table)
{
DBSelectQuery q = new DBSelectQuery();
q.SelectSet = DBSelectSet.SelectAll(table);
q.Last = q.SelectSet;
return(q);
}
/// <summary>
/// Begins a new SELECT statement with a COUNT([table].[field]) as the first result
/// </summary>
/// <param name="field">The name of the field to count</param>
/// <param name="table">The name of the table (or alias) containing the field to count</param>
/// <returns>A new DBSelectQuery to support statement chaining</returns>
public static DBSelectQuery SelectCount(string table, string field)
{
DBSelectSet sel = DBSelectSet.SelectCount(table, field);
DBSelectQuery q = new DBSelectQuery();
q.SelectSet = sel;
return(q);
}
/// <summary>
/// Begins a new SELECT statement with the clause as the first value returned.
/// </summary>
/// <param name="clause">Any valid SQL clause (DBConst, DBParam, etc.) </param>
/// <returns>A new DBSelectQuery to support statement chaining</returns>
public static DBSelectQuery Select(DBClause clause)
{
DBSelectQuery q = new DBSelectQuery();
q.SelectSet = DBSelectSet.Select(clause);
q.Last = q.SelectSet;
return(q);
}
//
// DBSelectQuery factory methods
//
#region public static DBQuery SelectCount() + 3 overloads
/// <summary>
/// Begins a new SELECT statement with a COUNT(*) as the first result
/// </summary>
/// <returns>A new DBSelectQuery to support statement chaining</returns>
public static DBSelectQuery SelectCount()
{
DBSelectSet sel = DBSelectSet.SelectCount();
DBSelectQuery q = new DBSelectQuery();
q.SelectSet = sel;
q.Last = sel;
return(q);
}
/// <summary>
/// Begins a new SELECT statement with a set of fields as the result
/// </summary>
/// <param name="fields">The name of the fields to select</param>
/// <returns>A new DBSelectQuery to support statement chaining</returns>
public static DBSelectQuery SelectFields(params string[] fields)
{
DBSelectSet sel = DBSelectSet.SelectFields(fields);
DBSelectQuery query = new DBSelectQuery();
query.SelectSet = sel;
query.Last = sel;
return(query);
}
/// <summary>
/// Begins a new SELECT statement with the clauses as the first values returned
/// </summary>
/// <param name="clauses">Any valid clause (DBConst, DBParam, etc)</param>
/// <returns>A new DBSelectQuery to support statement chaining</returns>
public static DBSelectQuery Select(params DBClause[] clauses)
{
DBSelectQuery q = new DBSelectQuery();
q.SelectSet = DBSelectSet.Select();
foreach (DBClause c in clauses)
{
q.SelectSet.And(c);
}
q.Last = q.SelectSet;
return(q);
}
/// <summary>
/// Appends a select statement to the Insert query which will be evaluated
/// at runtime and actioned on the database.
/// </summary>
/// <param name="select">The select query which returns the results to be inserted.</param>
/// <returns>Itself so statements can be chained</returns>
public DBInsertQuery Select(DBSelectQuery select)
{
if (_values != null)
{
throw new ArgumentException("Cannot assign from values if the insert statement has an existing select statement");
}
_innerselect = select;
_innerselect.IsInnerQuery = true;
_last = select;
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 void Test_01_SelectOneCategoryTest()
{
int id = 1;
DBSelectQuery select = DBQuery.SelectAll()
.From("Categories").As("C")
.WhereField("CategoryID", Compare.Equals, DBParam.ParamWithDelegate(DbType.Int32, delegate { return(id); }))
.GroupBy("CategoryName")
.OrderBy("ProductCount", Order.Descending);
this.OutputSql(select, " SELECT ONE CATEGORY");
this.OutputXML(select, " SELECT ONE CATEGORY");
}
public void Test_02_SelectProductsInCategoriesTest()
{
DBSelectQuery select = DBQuery.Select()
.Count("ProductID").As("ProductCount")
.Field("CategoryName")
.From("Categories").As("C")
.InnerJoin("Products").As("P")
.On("C", "CategoryID", Compare.Equals, "P", "CategoryID")
.GroupBy("CategoryName")
.OrderBy("ProductCount", Order.Descending);
this.OutputSql(select, " # PRODUCTS IN CATEGORIES");
this.OutputXML(select, " # PRODUCTS IN CATEGORIES");
}
public void Test_04_InsertScript()
{
byte[] imgdata = this.GetLocalImage("bomb.gif");
DBParam cName = DBParam.ParamWithValue("name", DbType.String, (object)"newType_1");
DBParam cDesc = DBParam.ParamWithValue("desc", DbType.String, (object)"newDescrption_1");
DBParam cPic = DBParam.ParamWithValue("pic", DbType.Binary, (object)imgdata);
DBInsertQuery ins = DBQuery.InsertInto("Categories")
.Fields("CategoryName", "Description", "Picture")
.Values(cName, cDesc, cPic);
DBSelectQuery sel = DBQuery.Select(DBFunction.LastID());
DBScript script = DBQuery.Begin(ins)
.Then(sel)
.End();
this.OutputSql(script, "Insert and Return last ID script");
this.OutputXML(script, "Insert and Return last ID script");
}
/// <summary>
/// returns the Statement to Create the custom view
/// </summary>
/// <returns></returns>
private static DBCreateViewQuery GetCustomViewQuery()
{
DBSelectQuery sel = GetViewSelect();
return(DBQuery.Create.View(ViewName).As(sel));
}
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 void GetDatatable(Tech.QScript.Syntax.SQuery sQuery)
{
DBSelectQuery select = DBQuery.Select();
foreach (Syntax.Query.Table t in sQuery.tables)
{
foreach (Syntax.Query.TableField tf in t.Fields)
{
select.Field(t.TableName, tf.FieldName).As(tf.FieldName);
}
foreach (Syntax.Query.Where where in sQuery.Conditions)
{
if (where.Type == Syntax.Query.ConditionType.relation)
{
if (where.JoinType == Syntax.Query.JoinType.And)
{
select.And(where.lefttable, where.LeftField, Data.Compare.Equals, where.RightTable, where.RightField);
}
else if (where.JoinType == Syntax.Query.JoinType.or)
{
DBTable lft = DBTable.Table(where.lefttable);
DBTable rgt = DBTable.Table(where.RightTable);
DBField lff = DBField.Field(where.lefttable, where.LeftField);
DBField rff = DBField.Field(where.RightTable, where.RightField);
select.OrWhere(lff, Data.Compare.Equals, rff);
// DBComparison joined = DBComparison.Compare(DBClause);
//select.InnerJoin(where.lefttable).As (where.lefttable)
// .On(where.lefttable,where.LeftField,Data.Compare.Equals,where.RightTable,where.RightField)
//select.OrWhere(where.lefttable,where.LeftField,Data.Compare.Equals, )
//select.OrWhere (where.lefttable, where.LeftField, Data.Compare.Equals, where.RightTable, where.RightField);
}
else if (where.JoinType == Syntax.Query.JoinType.None)
{
DBField lff = DBField.Field(where.lefttable, where.LeftField);
DBField rff = DBField.Field(where.RightTable, where.RightField);
DBComparison d = DBComparison.Equal(lff, rff);
select.WhereNone(d);
}
}
else if (where.Type == Syntax.Query.ConditionType.filter)
{
if (where.JoinType == Syntax.Query.JoinType.And)
{
select.WhereField(where.lefttable, where.LeftField, Data.Compare.Equals, DBConst.String(where.Value));
}
else if (where.JoinType == Syntax.Query.JoinType.or)
{
DBField lff = DBField.Field(where.lefttable, where.LeftField);
DBField rff = DBField.Field(where.RightTable, where.RightField);
DBComparison d = DBComparison.Equal(lff, DBConst.String(where.Value));
select.OrWhere(d);
}
else if (where.JoinType == Syntax.Query.JoinType.None)
{
DBField lff = DBField.Field(where.lefttable, where.LeftField);
DBField rff = DBField.Field(where.RightTable, where.RightField);
DBComparison d = DBComparison.Equal(lff, DBConst.String(where.Value));
select.OrWhereNone(d);
}
}
}
}
var data = Database.GetDatatable(select);
query = select.ToSQLString(Database);
dataTable
= (System.Data.DataTable)data;
}
public void GetDatatable(Tech.QScript.Syntax.SQuery sQuery)
{
DBSelectQuery select = DBQuery.Select();
bool isAdded = false;
foreach (Syntax.Query.Table t in sQuery.tables)
{
foreach (Syntax.Query.TableField tf in t.Fields)
{
if (t.TableAlias != "")
{
select.Field(t.TableAlias.Trim(), tf.FieldName.Trim()).As(tf.FieldName.Trim());
}
else
{
select.Field(t.TableName.Trim(), tf.FieldName.Trim()).As(tf.FieldName.Trim());
}
}
}
foreach (Syntax.Query.Table t in sQuery.tables)
{
if (t.Relations.Count == 0)
{
if (isAdded == false)
{
select.From(t.TableName.Trim()).As(t.TableAlias.Trim());
isAdded = true;
}
}
isAdded = false;
int indx = 0;
foreach (Syntax.Query.Relation r in t.Relations)
{
if (indx == 0)
{
select.InnerJoin(t.TableName.Trim()).As(t.TableAlias.Trim()).On(r.lefttable.Trim(), r.LeftField.Trim(), GetCompare(r.Operator), r.RightTable.Trim(), r.RightField.Trim());
}
else if (t.Relations.Count > 1)
{
select.And(r.lefttable.Trim(), r.LeftField.Trim(), GetCompare(r.Operator), r.RightTable.Trim(), r.RightField.Trim());
}
indx = indx + 1;
}
}
//foreach (Syntax.Query.Table t in sQuery.from.Tables )
//{
// select.From(t.TableName ).As(t.TableName );
//}
int index = 0;
foreach (Syntax.Query.Where where in sQuery.Conditions)
{
if (where.Type == Syntax.Query.ConditionType.filter)
{
if (where.JoinType == Syntax.Query.JoinType.And)
{
if (index == 0)
{
select.WhereField(where.lefttable.Trim(), where.LeftField.Trim(), GetCompare(where.Operator), DBConst.String(getValue(where.Operator, where.Value.Trim())));
}
else
{
select.AndWhere(where.lefttable.Trim(), where.LeftField.Trim(), GetCompare(where.Operator), DBConst.String(getValue(where.Operator, where.Value.Trim())));
}
}
else if (where.JoinType == Syntax.Query.JoinType.or)
{
DBField lff = DBField.Field(where.lefttable, where.LeftField.Trim());
DBField rff = DBField.Field(where.RightTable, where.RightField.Trim());
DBComparison d = DBComparison.Equal(lff, DBConst.String(where.Value.Trim()));
select.OrWhere(d);
}
else if (where.JoinType == Syntax.Query.JoinType.None)
{
DBField lff = DBField.Field(where.lefttable, where.LeftField.Trim());
DBField rff = DBField.Field(where.RightTable, where.RightField.Trim());
DBComparison d = DBComparison.Equal(lff, DBConst.String(where.Value.Trim()));
select.OrWhereNone(d);
}
index = index + 1;
}
}
var data = Database.GetDatatable(select);
query = select.ToSQLString(Database);
dataTable
= (System.Data.DataTable)data;
}
public Schema(DataSchema ds) : base()
{
select = DBQuery.Select();
DataSchema = ds;
}
/// <summary>
/// Begins a new SELECT statement. Add fields and tables by using the methods on the instance returned
/// </summary>
/// <returns>A new DBSelectQuery to support statement chaining</returns>
public static DBSelectQuery Select()
{
DBSelectQuery q = new DBSelectQuery();
return(q);
}
/// <summary>
/// Begins a new SELECT DISTINCT statement. Add fields and tables by using the methods on the instance returned
/// </summary>
/// <returns>A new DBSelectQuery to support statement chaining</returns>
public static DBSelectQuery SelectDistinct()
{
DBSelectQuery q = DBQuery.Select();
return(q.Distinct());
}
/// <summary>
/// Begins a new SELECT statement with a limit of [count] items returned
/// </summary>
/// <param name="count">The total number of items to return</param>
/// <returns>A new DBSelectQuery to support statement chaining</returns>
public static DBSelectQuery SelectTopN(int count)
{
DBSelectQuery q = DBQuery.Select();
return(q.TopN(count));
}