Esempio n. 1
0
 public virtual DbCommand CreateCommand(string commandText, FilterParam[] parms)
 {
     var cmd = this.DbProvider.DbProviderFactory.CreateCommand();
     cmd.CommandText = commandText;
     if (parms != null)
     {
         foreach (var parm in parms)
         {
             DbParameter p = this.DbProvider.DbProviderFactory.CreateParameter();
             p.ParameterName = this.DbProvider.ParamPrefix + parm.Name;
             p.Value = parm.Value;
             cmd.Parameters.Add(p);
         }
     }
     return cmd;
 }
        /// <summary>
        /// SQL2005/SQL2008的分页方案,你也可以改成存储过程的方式
        /// </summary>
        /// <param name="view"></param>
        /// <param name="where"> </param>
        /// <param name="sortname"></param>
        /// <param name="sortorder"></param>
        /// <param name="pagenumber"></param>
        /// <param name="pagesize"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public override DataTable GetGridRows(string view, string where, string sortname, string sortorder, int? pagenumber, int? pagesize, FilterParam[] parms)
        {
            string commandText = "";

            bool pagable = pagenumber.HasValue && pagesize.HasValue;
            bool sortable = !sortname.IsNullOrEmpty() && !sortorder.IsNullOrEmpty();

            if (pagable)
            {
                if (!sortable)
                {
                    throw new Exception("必须指定一个排序条件");
                }
                var orderby = string.Concat("ORDER BY ", sortname, " ", sortorder.EqualsTo("asc") ? "ASC" : "DESC");
                var orderbyREV = string.Concat("ORDER BY ", sortname, " ", sortorder.EqualsTo("asc") ? "DESC" : "ASC");
                var sql = @"SELECT * FROM (SELECT {TOP} FROM {VIEW} WHERE {WHERE}) AS tmptableinner WHERE rowId between {N} and {M}";
                sql = sql.Replace("{N}", ((pagenumber - 1) * pagesize + 1).ToString());
                sql = sql.Replace("{M}", (pagenumber * pagesize).ToString());
                sql = sql.Replace("{VIEW}", view);
                sql = sql.Replace("{TOP}", string.Concat("row_number() over (", orderby, ") as rowId,*"));
                sql = sql.Replace("{WHERE}", where.IsNullOrEmpty() ? "1=1" : where);

                commandText = sql;
            }
            else
            {
                commandText = "SELECT * FROM {VIEW} WHERE {WHERE} ";
                if (sortable)
                {
                    commandText += string.Concat(" ORDER BY ", sortname, " ", sortorder.EqualsTo("asc") ? "ASC" : "DESC");
                }
                commandText = commandText.Replace("{WHERE}", where.IsNullOrEmpty() ? "1=1" : where);
                commandText = commandText.Replace("{VIEW}", view);
            }

            //创建command
            var cmd = this.CreateCommand(commandText, parms);
            //使用liger.Data内置的数据适配器预处理command
            this.DbProvider.PrepareCommand(cmd);
            //执行command
            var ds = this.DbContext.ExecuteDataSet(cmd);
            if (ds == null || ds.Tables.Count == 0)
                return null;
            return ds.Tables[0];
        }
Esempio n. 3
0
 private string CreateFilterParam(object value,string type)
 {
     string paramName = "p" + ++paramCounter;
     object val = value;
     if (type.EqualsTo("int", true) || type.EqualsTo("digits", true))
         val = DataHelper.ConvertValue(typeof(int), val);
     else if (type.EqualsTo("float", true) || type.EqualsTo("number", true))
         val = DataHelper.ConvertValue(typeof(decimal),val);
     FilterParam param = new FilterParam(paramName, val);
     this.Parms.Add(param);
     return paramName;
 }
Esempio n. 4
0
 public virtual int GetGridTotal(string view, string where, FilterParam[] parms)
 {
     string commandText = "SELECT count(*) FROM [{0}] WHERE {1}".FormatWith(view, where.IsNullOrEmpty() ? "1=1" : where);
     //创建command
     var cmd = this.CreateCommand(commandText, parms);
     //使用liger.Data内置的数据适配器预处理command
     this.DbProvider.PrepareCommand(cmd);
     //执行command
     return (int)DbContext.ExecuteScalar(cmd);
 }
Esempio n. 5
0
        /// <summary>
        /// 默认的 N-M行    分页方案: 
        /// 1,根据条件和排序 检索出全部前M行数据 
        /// 2,对这M行数据反转(排序反转) 
        /// 3,对反转完的数据,取前 M-N+1 行
        /// 4,取完数据以后,再次反转
        /// 如:select * from (select top [M-N+1] * from (select top [M] * from 表 [order by id desc]) [order by id asc]) [order by id desc]
        /// </summary>
        /// <param name="view"></param>
        /// <param name="where"></param>
        /// <param name="sortname"></param>
        /// <param name="sortorder"></param>
        /// <param name="pagenumber"></param>
        /// <param name="pagesize"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        public virtual DataTable GetGridRows(string view, string where, string sortname, string sortorder, int? pagenumber, int? pagesize, FilterParam[] parms)
        {
            string commandText = "";
            bool pagable = pagenumber.HasValue && pagesize.HasValue;
            bool sortable = !sortname.IsNullOrEmpty() && !sortorder.IsNullOrEmpty();
            if (pagable)
            {
                string sql = "SELECT TOP {2} * FROM [{0}] WHERE {1}".FormatWith(view, where.IsNullOrEmpty() ? "1=1" : where, (pagenumber.Value * pagesize.Value).ToString());

                commandText = " SELECT * FROM (SELECT TOP {PAGESIZE} * FROM ({OLDSQL} {ORDER}) AS tmptableinner {REVORDER}) AS tmptableouter {ORDER} ";
                commandText = commandText.Replace("{PAGESIZE}", pagesize.Value.ToString());
                if (sortable)
                {
                    commandText = commandText.Replace("{ORDER}", string.Concat("ORDER BY " ,sortname," ", sortorder.EqualsTo("asc") ? "ASC" : "DESC"));
                    commandText = commandText.Replace("{REVORDER}", string.Concat("ORDER BY ", sortname, " ", sortorder.EqualsTo("asc") ? "DESC" : "ASC"));
                }
                else
                {
                    throw new Exception("必须指定一个排序条件");
                }
                commandText = commandText.Replace("{OLDSQL}", sql);
            }
            else
            {
                commandText = "SELECT * FROM [{VIEW}] WHERE {WHERE} ";
                if (sortable)
                {
                    commandText += string.Concat(" ORDER BY ", sortname, " ", sortorder.EqualsTo("asc") ? "ASC" : "DESC");
                }
                commandText = commandText.Replace("{WHERE}", where.IsNullOrEmpty() ? "1=1" : where);
                commandText = commandText.Replace("{VIEW}", view);
            }
            //创建command
            var cmd = this.CreateCommand(commandText, parms);
            //使用liger.Data内置的数据适配器预处理command
            this.DbProvider.PrepareCommand(cmd);
            //执行command
            var ds = this.DbContext.ExecuteDataSet(cmd);
            if (ds == null || ds.Tables.Count == 0)
                return null;
            return ds.Tables[0];
        }
Esempio n. 6
0
 public virtual GridData GetGridData(string view, string where, string sortname, string sortorder, int? pagenumber, int? pagesize, FilterParam[] parms)
 {
     var rows = GetGridRows(view, where, sortname, sortorder, pagenumber, pagesize, parms);
     var total = GetGridTotal(view, where, parms);
     return new GridData()
     {
         Rows = rows,
         Total = total
     };
 }