示例#1
0
        public List <T> GetResult(List <T> t, GetReportDataParams param)
        {
            var count = t.Count();

            if (param.PageSize > count)
            {
                return(t);
            }
            else
            {
                var tResult = t.Skip((param.PageIndex - 1) * param.PageSize).Take(param.PageSize).ToList();

                return(tResult);
            }
        }
示例#2
0
        /// <summary>
        /// 前台需要点击排序的时候调用
        /// </summary>
        /// <param name="reportName"> XML对?应?|的??名?称? </param>
        /// <param name="where"> 查??询??条??件t </param>
        /// <param name="pageSize"> 页?3面?打???小? </param>
        /// <param name="pageIndex"> 页?3面?索??引?y </param>
        /// <param name="order"></param>
        /// <param name="systemID"> 系??统?3ID</param>
        /// <param name="totalCount"></param>
        /// <returns></returns>
        public static DataSet GetReportData(string reportName, GetReportDataParams param,
                                            int systemID, out int totalCount, bool optimize = false)
        {
            if (param.Where != null)
            {
                foreach (var item in param.Where)
                {
                    item.Value = item.Value.Trim();
                }
            }
            DataSet ds;

            ds = MSSqlHelper.GetReportData(reportName, param, systemID, out totalCount);

            return(ds);
        }
示例#3
0
文件: MSSqlHelper.cs 项目: yuabd/Hope
        public static DataSet GetReportData(string reportName, GetReportDataParams param, int systemID, out int totalCount, bool optimize = false)
        {
            int    pageSize  = param.PageSize;
            int    pageIndex = param.PageIndex;
            string order     = param.Order;

            List <KeyValue> where = param.Where;
            totalCount            = 0;
            if (pageIndex < 1)             //不能出现索引页小于1的情况,否则查询语句报错
            {
                return(new DataSet());
            }
            if (where == null)
            {
                where = new List <KeyValue>();
            }

            string reportPath = CReport.GetReportPath(systemID);             //报表路径

            if (string.IsNullOrEmpty(reportPath))
            {
                return(new DataSet());
            }
            string sqlCount = "";
            string sql      = CReport.GetSql(reportPath, reportName, where, 1);        //获取要查询的SQL语句 及其 参数

            if (string.IsNullOrEmpty(sql))
            {
                return(new DataSet());
            }
            else
            {
                sql = sql.Trim();
            }
            string conString = CReport.GetConnection();             //获取SQL连接串

            if (string.IsNullOrEmpty(conString))
            {
                return(new DataSet());
            }
            string rowOrder = "";

            if (!string.IsNullOrEmpty(order))
            {
                rowOrder = "order by " + order + "";
            }
            else
            {
                rowOrder = "order by (select 0)";
            }
            int start = pageSize * (pageIndex - 1) + 1;
            int end   = pageSize * pageIndex;


            var    matchs   = Regex.Matches(sql, @"\s+order\s+", RegexOptions.IgnoreCase);        //检查语句中是否含有order by
            string strCount = sql;

            if (matchs.Count > 1)
            {
                strCount = sql.Substring(0, matchs[matchs.Count - 1].Index);
                if (string.IsNullOrEmpty(order))
                {
                    rowOrder = sql.Substring(matchs[matchs.Count - 1].Index);
                }
            }
            else if (matchs.Count == 1)
            {
                strCount = sql.Substring(0, matchs[0].Index);
                if (string.IsNullOrEmpty(order))
                {
                    rowOrder = sql.Substring(matchs[0].Index);
                }
            }
            int firstSelectIndex = Regex.Matches(strCount, @"select", RegexOptions.IgnoreCase)[0].Index + 7;

            sql = strCount.Insert(firstSelectIndex, " Row_Number() OVER ({0}) row ,");
            sql = string.Format(sql, rowOrder);
            sql = string.Format("select * from ( {0} ) item WHERE item.row BETWEEN {1} AND {2}", sql, start, end);

            if (string.IsNullOrEmpty(sqlCount))
            {
                sqlCount = "select count(0) from (" + strCount + ") item ";
            }


            sql = sql + ";" + sqlCount;
            var Rundate = DateTime.Now;
            int RunTime = 0;

            using (SqlConnection conn = new SqlConnection(conString))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                //where 替换
                foreach (var data in where)
                {
                    cmd.Parameters.Add(new SqlParameter("@" + data.Key, data.Value));
                }
                DataSet ds = new DataSet();

                SqlDataAdapter adp = new SqlDataAdapter(cmd);

                adp.Fill(ds);
                if (ds.Tables.Count > 1)
                {
                    totalCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
                }
                else
                {
                    totalCount = 0;
                }

                conn.Close();
                RunTime = (int)DateTime.Now.Subtract(Rundate).TotalMilliseconds;

                return(ds);
            }
        }