コード例 #1
0
        /// <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()));
                }
            }
        }
コード例 #2
0
        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;
        }