示例#1
0
 protected virtual string BuildSelectText()
 {
     return("SELECT "
            + SelectModifier + " "
            + GetFieldList()
            + " FROM " + SqlTable
            + (SqlConditionals.Count == 0 ? ""
             : " WHERE " + SqlConditionals.Join(" AND "))
            + (OrderBys.Count == 0 ? ""
             : " ORDER BY " + OrderBys.Join(",")));
 }
示例#2
0
        protected virtual TCmd BuildDelete()
        {
            TCmd cmd = new TCmd();

            cmd.CommandText = "DELETE FROM " + SqlTable
                              + (SqlConditionals.Count == 0 ? ""
                    : " WHERE " + SqlConditionals.Select(sc => sc.Replace("@", "@" + ParamUniqueKey)).Join(" AND "));
            SetParameters(cmd);

            return(cmd);
        }
示例#3
0
        protected virtual TCmd BuildUpdate()
        {
            if (SqlSets.Count == 0)
            {
                return(null);
            }

            TCmd cmd = new TCmd();

            cmd.CommandText = "UPDATE " + SqlTable
                              + " SET "
                              + SqlSets.Where(kvp => !PreservedFields.Contains(kvp.Key))
                              .Select(kvp => QuoteName(kvp.Key) + " = @" + ParamUniqueKey + "S_" + kvp.Key)
                              .Join(", ")
                              + (SqlConditionals.Count == 0 ? ""
                    : " WHERE " + SqlConditionals.Select(sc => sc.Replace("@", "@" + ParamUniqueKey)).Join(" AND "));
            SetParameters(cmd);
            SetUpdateParameters(cmd, true);

            return(cmd);
        }
示例#4
0
        protected virtual TCmd BuildPagedSelect(int skip, int take, string idField, Type idType,
                                                string sortField, Type sortType)
        {
            var subBuilder = new QueryBuilder <TConn, TCmd, TParam, TReader>(ConnectionString);

            subBuilder.SqlFields       = this.SqlFields.ToList();
            subBuilder.SqlTable        = this.SqlTable;
            subBuilder.SqlConditionals = this.SqlConditionals.ToList();
            subBuilder.OrderBys        = this.OrderBys.ToList();

            string wherePart = (SqlConditionals.Count == 0 ? ""
                    : " WHERE " + SqlConditionals.Join(" AND "));

            if (skip == 0)
            {
                subBuilder.SelectModifier = "TOP " + take.ToString();
                var selCmd = subBuilder.BuildSelect();
                selCmd.CommandText += string.Format("; SELECT Count(*) FROM {0}{1};", subBuilder.SqlTable, wherePart);
                SetParameters(selCmd);
                return(selCmd);
            }

            string text;

            if (string.IsNullOrEmpty(sortField))
            {
                text = string.Format(@"DECLARE @firstId {0}, @skip int = {1}, @take int = {2};
                SET ROWCOUNT @skip;
                SELECT @firstId = {3} FROM {4}{6} ORDER BY {3};

                -- Now, set the row count to MaximumRows and get
                -- all records >= @first_id
                SET ROWCOUNT @take;

                SELECT @firstId;

                SELECT {5}
                FROM {4}
                WHERE {3} >= @firstId {7}
                ORDER BY {3};

                SET ROWCOUNT 0;

                SELECT Count(*) FROM {4}{6};", DbTypeName(idType), skip + 1, take,
                                     idField, subBuilder.SqlTable, GetFieldList(),
                                     wherePart, wherePart.Replace(" WHERE ", " AND "));
            }
            else
            {
                text = string.Format(@"DECLARE @firstValue {0}, @firstId {1}, @skip int = {2}, @take int = {3};
                SET ROWCOUNT @skip;
                SELECT @firstValue = {4}, @firstId = {5} FROM {6}{8} ORDER BY {4}, {5};

                -- Now, set the row count to MaximumRows and get
                -- all records >= @first_id
                SET ROWCOUNT @take;

                SELECT @firstValue, @firstId;

                SELECT {7}
                FROM {6}
                WHERE {5} >= @firstId AND {4} >= @firstValue{9}
                ORDER BY {4}, {5};

                SET ROWCOUNT 0;

                SELECT Count(*) FROM {6}{8};", DbTypeName(sortType), DbTypeName(idType), skip + 1, take, sortField,
                                     idField, subBuilder.SqlTable, GetFieldList(),
                                     wherePart, wherePart.Replace(" WHERE ", " AND "));
            }

            TCmd cmd = new TCmd();

            cmd.CommandText = text;
            SetParameters(cmd);

            return(cmd);
        }
示例#5
0
 protected virtual string BuildCountText()
 {
     return("SELECT Count(*)  FROM " + SqlTable
            + (SqlConditionals.Count == 0 ? ""
             : " WHERE " + SqlConditionals.Join(" AND ")));
 }