Exemplo n.º 1
0
        public override UpdateAction Where(TableFilter filter)
        {
            if (this.UpdateColumns.Count == 0)
            {
                throw new Exception("no update columns");
            }

            #region update columns
            string columnStr = string.Empty;
            foreach (var col in this.UpdateColumns)
            {
                var p = new DBParam(col.Key, col.Value);
                ParamCollection.Add(p);
                columnStr += string.Format(" {0} ={1},", col.Key, p.ParamName);
            }
            columnStr = columnStr.TrimEnd(',');
            #endregion

            #region where
            string filterCondition = string.Empty, where = string.Empty;
            if (filter != null)
            {
                filterCondition = filter.Build(base.ParamCollection);
                if (!string.IsNullOrWhiteSpace(filterCondition))
                {
                    where = string.Format(" where {0} ", filterCondition);
                }
            }
            #endregion

            base.MainSql = string.Format("update {0} set {1} {2}", base.TableCode, columnStr, where);
            return(this);
        }
Exemplo n.º 2
0
        public override DeleteAction Where(TableFilter filter)
        {
            string filterCondition = string.Empty, where = string.Empty;

            if (filter != null)
            {
                filterCondition = filter.Build(base.ParamCollection);
                if (!string.IsNullOrWhiteSpace(filterCondition))
                {
                    where = string.Format(" where {0} ", filterCondition);
                }
            }

            base.MainSql = string.Format("delete from {0} {1}", base.TableCode, where);
            return(this);
        }
Exemplo n.º 3
0
        //public QueryAction LeftJoin(string table)
        //{
        //    this.joinTables.Add(new JoinTable(table, EJoinType.Left));
        //    return this;
        //}
        //public QueryAction RightJoin(string table)
        //{
        //    this.joinTables.Add(new JoinTable(table, EJoinType.Right));
        //    return this;
        //}
        //public QueryAction InnerJoin(string table)
        //{
        //    this.joinTables.Add(new JoinTable(table, EJoinType.Inner));
        //    return this;
        //}
        public override QueryAction Where(TableFilter filter)
        {
            if (filter != null)
            {
                filter.MainTable = base.TableCode;
            }

            string joinCondition = string.Empty, filterCondition = string.Empty, tmpField = string.Empty, where = string.Empty;

            if (string.IsNullOrWhiteSpace(this.fixField))
            {
                throw new Exception("no field");
            }

            #region tables
            joinCondition = base.TableCode;
            foreach (var table in joinTables)
            {
                var tmpJoin = string.Empty;
                foreach (var item in table.Conditions)
                {
                    tmpJoin = string.Format("{0}={1}", item.Key.IndexOf('.') == -1 ? (base.TableCode + "." + item.Key) : item.Key,
                                            item.Value.IndexOf('.') == -1 ? (table.Table + "." + item.Value) : item.Value);
                }
                joinCondition += string.Format(" {0} join {1} on {2} ", table.JoinType.ToString(), table.Table, tmpJoin);
            }
            #endregion

            #region field
            foreach (var col in this.fixField.Split(','))
            {
                if (col.IndexOf(".") == -1)
                {
                    tmpField += string.Format("{0}.{1},", base.TableCode, col);
                }
                else if (col.IndexOf(".") > -1 && col.IndexOf("*") > -1)
                {
                    tmpField += string.Format("{0},", col);
                }
                else
                {
                    var tmpArray = col.Split('.');
                    if (string.Compare(tmpArray[0], base.TableCode, true) == 0)
                    {
                        tmpField += string.Format("{0}.{1} as {1},", tmpArray[0], tmpArray[1]);
                    }
                    else
                    {
                        tmpField += string.Format("{0}.{1} as {0}{1},", tmpArray[0], tmpArray[1]);
                    }
                }
            }
            #endregion

            #region where
            if (filter != null)
            {
                filterCondition = filter.Build(base.ParamCollection);
                where           = string.Format(" {0} ", filterCondition);
            }
            else
            {
                where = " 1=1 ";
            }
            #endregion

            string sql = string.Empty;
            #region paging
            if (this.pageIndex > 0 && this.pageSize > 0)
            {
                if (string.IsNullOrWhiteSpace(this.sortField))
                {
                    this.sortField = string.Format("{0}.{1} asc", base.TableCode, this.defaultField);
                }
                var start = (this.pageIndex - 1) * this.pageSize + 1;
                var end   = this.pageSize * this.pageIndex;
                sql = base.DbContext.GetPagingFormat(new QueryModel()
                {
                    Fields    = tmpField.TrimEnd(','),
                    Tables    = joinCondition,
                    Where     = where,
                    SortField = this.sortField,
                    Start     = start,
                    End       = end
                });
                base.SecondarySql = string.Format("select count(1) from {0}  where {1}", joinCondition, where);
            }
            else
            {
                sql = string.Format("select {0} from {1} where {2} {3}", tmpField.TrimEnd(','), joinCondition, where,
                                    !string.IsNullOrWhiteSpace(this.sortField) ? ("order by " + this.sortField) : string.Empty);
            }
            #endregion
            base.MainSql = sql;
            return(this);
        }