Exemplo n.º 1
0
        protected virtual SqlParameter CreateParamter_Orderby(FeedbackSearchEntity sp)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(" f.CreateTime DESC ");

            return(new SqlParameter("@OrderField", sb.ToString()));
        }
Exemplo n.º 2
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="sp"></param>
        /// <returns></returns>
        protected virtual SqlParameter CreateParameter_Table(FeedbackSearchEntity sp)
        {
            StringBuilder sbtable = new StringBuilder();

            sbtable.Append(@"dbo.M_User_Feedback AS f
                LEFT JOIN dbo.M_User AS u ON u.UserID = f.UserID");

            return(new SqlParameter("@TableName", sbtable.ToString()));
        }
Exemplo n.º 3
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="sp"></param>
        /// <returns></returns>
        protected virtual SqlParameter CreateParameter_Fileds(FeedbackSearchEntity sp)
        {
            StringBuilder sbfileds = new StringBuilder();

            sbfileds.Append(@"f.*
                ,CONVERT(varchar(30), f.CreateTime, 20) as FormatCreateTime
                ,ISNULL(u.LoginName,'未知用户') AS UserName");
            return(new SqlParameter("@Fields", sbfileds.ToString()));
        }
Exemplo n.º 4
0
        /// <summary>
        /// 核心方法。查询条件转换成sql参数
        /// </summary>
        /// <param name="sp"></param>
        /// <returns></returns>
        protected virtual List <SqlParameter> ParseToSqlParameters(FeedbackSearchEntity sp)
        {
            List <SqlParameter> _parameters = new List <SqlParameter>();

            _parameters.Add(CreateParameter_Table(sp));
            _parameters.Add(CreateParameter_Fileds(sp));
            _parameters.Add(CreateParamter_Orderby(sp));
            _parameters.Add(CreateParameter_Where(sp));

            _parameters.Add(new SqlParameter("@pageSize", sp.PageSize));
            _parameters.Add(new SqlParameter("@pageIndex", sp.PageIndex));
            _parameters.Add(new SqlParameter()
            {
                ParameterName = "@Records", Value = 0, Direction = System.Data.ParameterDirection.Output
            });
            return(_parameters);
        }
Exemplo n.º 5
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="info"></param>
        /// <param name="totalcnt"></param>
        /// <returns></returns>
        public DataTable QueryData(FeedbackSearchEntity info, out int totalcnt)
        {
            try
            {
                SqlParameter[] prms = ParseToSqlParameters(info).ToArray();
                DataTable      dt   = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "sp_pager05", prms).Tables[0];
                totalcnt = int.Parse(prms[prms.Length - 1].Value.ToString());

                return(dt);
            }
            catch (Exception ex)
            {
                totalcnt = -1;
                LogUtil.WriteLog(ex);
                return(null);
            }
        }
Exemplo n.º 6
0
        //,CONVERT(varchar(30), a.ActivityStartTime, 20) as ActivityStartTime
        //,CONVERT(varchar(30), a.ActivityEndTime, 20) as ActivityEndTime

        /// <summary>
        ///
        /// </summary>
        /// <param name="sp"></param>
        /// <returns></returns>
        protected virtual SqlParameter CreateParameter_Where(FeedbackSearchEntity sp)
        {
            StringBuilder sbwhere = new StringBuilder(" 1=1 ");

            if (!string.IsNullOrEmpty(sp.StartTime) && !string.IsNullOrEmpty(sp.EndTime))
            {
                sbwhere.AppendFormat(" AND f.CreateTime BETWEEN '{0} 00:00:00' AND '{1} 23:59:59' "
                                     , sp.StartTime, sp.EndTime);
            }
            else if (string.IsNullOrEmpty(sp.StartTime) && !string.IsNullOrEmpty(sp.EndTime))
            {
                sbwhere.AppendFormat(" AND f.CreateTime < '{0} 23:59:59' "
                                     , sp.EndTime);
            }
            else if (!string.IsNullOrEmpty(sp.StartTime) && string.IsNullOrEmpty(sp.EndTime))
            {
                sbwhere.AppendFormat(" AND f.CreateTime > '{0} 00:00:00' "
                                     , sp.StartTime);
            }

            return(new SqlParameter("@sqlWhere", sbwhere.ToString()));
        }
Exemplo n.º 7
0
 /// <summary>
 /// 使用缓存查询信息
 /// </summary>
 /// <param name="info">查询主体</param>
 /// <param name="totalcnt">返回条目数</param>
 /// <returns></returns>
 public DataTable QueryData(FeedbackSearchEntity info, out int totalcnt)
 {
     return(dao.QueryData(info, out totalcnt));
 }