示例#1
0
        /// <summary>
        /// 列表
        /// </summary>
        /// <param name="setting"></param>
        /// <returns></returns>
        public static IPageOfList<MemberInfo> List(SearchSetting setting)
        {
            FastPaging fp = new FastPaging();
            fp.PageIndex = setting.PageIndex;
            fp.PageSize = setting.PageSize;
            fp.Ascending = false;
            fp.TableName = "Members";
            fp.TableReName = "p";
            fp.PrimaryKey = "ID";
            fp.QueryFields = "p.*";
            fp.OverOrderBy = " CreateDateTime DESC";
            fp.WithOptions = " WITH(NOLOCK)";

            IList<MemberInfo> list = new List<MemberInfo>();
            DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    list.Add(GetByRow(dr));
                }
            }

            int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL())); ;
            return new PageOfList<MemberInfo>(list, setting.PageIndex, setting.PageSize, count);
        }
示例#2
0
        /// <summary>
        /// 反馈列表
        /// </summary>
        /// <param name="setting"></param>
        /// <returns></returns>
        public static IPageOfList<FeedbackInfo> List(SearchSetting setting)
        {
            FastPaging fp = new FastPaging();
            fp.PageIndex = setting.PageIndex;
            fp.PageSize = setting.PageSize;
            fp.Ascending = false;
            fp.TableName = "Feedback";
            fp.TableReName = "p";
            fp.PrimaryKey = "ID";
            fp.QueryFields = "p.*";
            fp.OverOrderBy = "CreateDateTime DESC";
            
            fp.Condition = " IsDeleted = 0";

            IList<FeedbackInfo> list = new List<FeedbackInfo>();
            DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    list.Add(new FeedbackInfo(){
                        Id = dr.Field<int>("Id"),
                        Title = dr.Field<string>("Title"),
                        Content = dr.Field<string>("Content"),
                        Contact = dr.Field<string>("Contact"),
                        Email = dr.Field<string>("Email"),
                        Phone = dr.Field<string>("Phone"),
                        Address = dr.Field<string>("Address"),
                        Fax = dr.Field<string>("Fax"),
                        CreateDateTime = dr.Field<DateTime>("CreateDateTime")
                    });
                }
            }

            int count = Convert.ToInt32(Goodspeed.Library.Data.SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL())); ;
            return new PageOfList<FeedbackInfo>(list, setting.PageIndex, setting.PageSize, count);
        }
示例#3
0
        /// <summary>
        /// 产品列表
        /// </summary>
        /// <param name="setting"></param>
        /// <returns></returns>
        public static IPageOfList<ProductInfo> List(ProductSearchSetting setting) {
            FastPaging fp = new FastPaging();
            fp.PageIndex = setting.PageIndex;
            fp.PageSize = setting.PageSize;
            fp.Ascending = false;
            fp.TableName = "Products";
            fp.TableReName = "p";
            fp.PrimaryKey = "ID";
            fp.QueryFields = "p.*";
            fp.OverOrderBy = " Sort ASC,CreateDateTime ASC";
            StringBuilder sbCondition = new StringBuilder();
            sbCondition.Append("    1 = 1 ");
            if (setting.CategoryId > 0)
            {
                sbCondition.Append(@"   AND EXISTS(
		                            SELECT * FROM Categories AS AC WITH(NOLOCK) 
		                            WHERE (AC.ID = @CID OR CHARINDEX(','+CAST(@CID AS NVARCHAR(MAX))+',',','+AC.ParentIdList+',') >0)
		                            AND p.CategoryId = AC.ID
                                )");
            }
            if (!setting.ShowDeleted)
            {
                sbCondition.Append("    AND IsDeleted = 0 /*获取未删除的*/");
            }
            SqlParameter[] parms = { 
                                    new SqlParameter("CID",SqlDbType.Int),
                                   };
            parms[0].Value = setting.CategoryId;
            

            fp.Condition = sbCondition.ToString();
            IList<ProductInfo> list = new List<ProductInfo>();
            ProductInfo model = null;
            DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005(),parms);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    model = GetByRow(dr);
                    if (model != null)
                    {
                        list.Add(model);
                    }
                }
            }
            int count = Convert.ToInt32(SQLPlus.ExecuteScalar(CommandType.Text,fp.BuildCountSQL(),parms));
            return new PageOfList<ProductInfo>(list, setting.PageIndex, setting.PageSize, count);
        }
示例#4
0
        /// <summary>
        /// 列表
        /// </summary>
        /// <param name="setting"></param>
        /// <returns></returns>
        public static IPageOfList<AttachmentDownloadLogInfo> ListLog(int attachId,SearchSetting setting)
        {
            FastPaging fp = new FastPaging();
            fp.PageIndex = setting.PageIndex;
            fp.PageSize = setting.PageSize;
            fp.Ascending = false;
            fp.TableName = "AttachmentDownloadLog";
            fp.TableReName = "p";
            fp.PrimaryKey = "ID";
            fp.QueryFields = "p.*,A.Title";
            fp.OverOrderBy = " LastDownloadDateTime DESC";
            fp.WithOptions = " WITH(NOLOCK)";
            fp.JoinSQL = " INNER JOIN Attachment AS A WITH(NOLOCK) ON A.Id = P.AttachId";

            if(attachId>0){
                fp.Condition = string.Format("    AttachId = {0}",attachId);
            }
            IList<AttachmentDownloadLogInfo> list = new List<AttachmentDownloadLogInfo>();
            DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005());
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    list.Add(new AttachmentDownloadLogInfo() { 
                        Id = dr.Field<int>("Id"),
                        UserId = dr.Field<int>("UserId"),
                        UserName = dr.Field<string>("UserName"),
                        DownloadCount = dr.Field<int>("DownloadCount"),
                        LastDownloadDateTime = dr.Field<DateTime>("LastDownloadDateTime"),
                        AttachTitle = dr.Field<string>("Title")
                    });
                }
            }

            int count = Convert.ToInt32(Goodspeed.Library.Data.SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL())); ;
            return new PageOfList<AttachmentDownloadLogInfo>(list, setting.PageIndex, setting.PageSize, count);
        }
示例#5
0
        /// <summary>
        /// 列表
        /// </summary>
        /// <param name="setting"></param>
        /// <returns></returns>
        public static IPageOfList<AttachmentInfo> List(AttachmentSearchSetting setting)
        {
            FastPaging fp = new FastPaging();
            fp.PageIndex = setting.PageIndex;
            fp.PageSize = setting.PageSize;
            fp.Ascending = false;
            fp.TableName = "Attachment";
            fp.TableReName = "p";
            fp.PrimaryKey = "ID";
            fp.QueryFields = "p.*";
            fp.OverOrderBy = " Sort ASC,CreateDateTime DESC";
            fp.WithOptions = " WITH(NOLOCK)";

            StringBuilder sbCondition = new StringBuilder();
            sbCondition.Append("    IsDeleted = 0 ");
            if (setting.CategoryId > 0)
            {
                sbCondition.Append(@"   AND EXISTS(
		                            SELECT * FROM Categories AS AC WITH(NOLOCK) 
		                            WHERE (AC.ID = @CID OR CHARINDEX(','+CAST(@CID AS NVARCHAR(MAX))+',',','+AC.ParentIdList+',') >0)
		                            AND p.CategoryId = AC.ID
                                )");
            }
            fp.Condition = sbCondition.ToString();

            SqlParameter[] param = { 
                                    new SqlParameter("CID",SqlDbType.Int),
                                   };
            param[0].Value = setting.CategoryId;

            IList<AttachmentInfo> list = new List<AttachmentInfo>();
            DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005(),param);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    list.Add(Get(dr));
                }
            }

            int count = Convert.ToInt32(Goodspeed.Library.Data.SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL(),param)); ;
            return new PageOfList<AttachmentInfo>(list, setting.PageIndex, setting.PageSize, count);
        }
示例#6
0
        public static IPageOfList<ArticleInfo> List(ArticleSearchSetting setting)
        {
            SqlParameter[] parms = { 
                                    new SqlParameter("CID",SqlDbType.Int),
                                    new SqlParameter("Title",SqlDbType.NVarChar),
                                    new SqlParameter("PublishDate",SqlDbType.NVarChar)
                                   };
            parms[0].Value = setting.CategoryId;
            parms[1].Value = setting.Title;
            parms[2].Value = setting.PublishDate;

            FastPaging fp = new FastPaging();
            fp.PageIndex = setting.PageIndex;
            fp.PageSize = setting.PageSize;
            fp.Ascending = false;
            fp.TableName = "Articles";
            fp.TableReName = "p";
            fp.PrimaryKey = "ID";
            fp.QueryFields = "p.*";
            fp.OverOrderBy = "IsTop DESC,Sort ASC,PublishDateTime DESC";
            StringBuilder sbCondition = new StringBuilder();
            sbCondition.Append(@"EXISTS(
		                            SELECT * FROM Categories AS AC WITH(NOLOCK) 
		                            WHERE (AC.ID = @CID OR CHARINDEX(','+CAST(@CID AS NVARCHAR(MAX))+',',','+AC.ParentIdList+',') >0)
		                            AND p.CategoryId = AC.ID
                                )");
            if (!setting.ShowDeleted)
            {
                sbCondition.Append("    AND IsDeleted = 0 /*获取未删除的*/");
            }
            if (!string.IsNullOrEmpty(setting.Title))
            {
                //虚拟服务器,没有启用全文索引
                //sbCondition.Append("    AND CONTAINS(Title,@Title)  ");

                sbCondition.Append("    AND Title LIKE '%'+@Title+'%'  ");

            }
            if (Regex.IsMatch(setting.PublishDate, @"^\d{4}-\d{1,2}-\d{1,2}$", RegexOptions.IgnoreCase))
            {
                sbCondition.Append("    AND CONVERT(VARCHAR(10),PublishDateTime,120) = @PublishDate");
            }
            if(setting.IsOnlyShowPublished){
                //只显示发布的文章
                sbCondition.Append("    AND IsPublished = 1 ");
            }

            fp.Condition = sbCondition.ToString();
            IList<ArticleInfo> list = new List<ArticleInfo>();
            ArticleInfo model = null;
            DataTable dt = Goodspeed.Library.Data.SQLPlus.ExecuteDataTable(CommandType.Text, fp.Build2005(), parms);
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    model = Get(dr);
                    if (model != null)
                    {
                        list.Add(model);
                    }
                }
            }

            int count = Convert.ToInt32(Goodspeed.Library.Data.SQLPlus.ExecuteScalar(CommandType.Text, fp.BuildCountSQL(), parms)); ;
            return new PageOfList<ArticleInfo>(list, setting.PageIndex, setting.PageSize, count);
        }