/// <summary>
///
/// </summary>
private void prepareStatement()
{
foreach (TableField tf in DataSchema.Fields)
{
if (tf.FieldAlias != "")
{
select.Field(tf.Table, tf.Field).As(tf.FieldAlias);
}
else
{
select.Field(tf.Table, tf.Field).As(tf.Field);
}
}
if (DataSchema.Joins.Count == 0)
{
select.From(DataSchema.Table);
}
int index = 0;
foreach (Join j in DataSchema.Joins)
{
if (index == 0)
{
select.InnerJoin(j.TableName.Trim()).As(j.TableName.Trim()).On(j.TableName.Trim(), j.JoinField.Trim(), Compare.Equals, j.JoinTable.Trim(), j.JoinField.Trim());
}
else
{
select.And(j.TableName.Trim(), j.Field.Trim(), Compare.Equals, j.JoinTable.Trim(), j.JoinField.Trim());
}
}
index = 0;
foreach (WhereStatement ws in DataSchema.Wheres)
{
if (index == 0)
{
index = 1;
select.WhereField(ws.TableName.Trim(), ws.FieldName.Trim(), GetCompare(ws.Operator), DBConst.String(ws.FieldValue.Trim()));
}
else
{
select.AndWhere(ws.TableName.Trim(), ws.FieldName.Trim(), GetCompare(ws.Operator), DBConst.String(ws.FieldValue.Trim()));
}
}
}
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 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;
}