/// <summary> /// 新闻公告查询 /// </summary> /// <param name="lqc">新闻查询条件</param> /// <param name="pageindex">第几页,从1开始</param> /// <param name="pagesize">每页多少条</param> /// <param name="page">输出参数(总页数)</param> /// <returns>新闻信息</returns> public TradeNewsInfo GetTradeNewsInfoWithPage(NewsLqc lqc, int pageindex, int pagesize, ref int page) { TradeNewsInfo tradeNewsInfo = new TradeNewsInfo(); SqlConnection sqlconn = null; SqlCommand sqlcmd = null; SqlDataReader sqldr = null; tradeNewsInfo.TradeNewsInfoList = new List<TradeNews>(); try { TradeUser TdUser = new TradeUser(); #region 判断登陆标识是否过期 if (!ComFunction.ExistUserLoginID(lqc.LoginID, ref TdUser)) { tradeNewsInfo.Result = false; tradeNewsInfo.ReturnCode = ResCode.UL003; tradeNewsInfo.Desc = ResCode.UL003Desc; return tradeNewsInfo; } #endregion string newsType = string.Empty; if (lqc.NType != NewsType.ALL) { newsType = string.Format(" and NewsType={0}", (int)lqc.NType); } string status = string.Empty; if (UserType.NormalType == TdUser.UType) //客户端只返回启用的新闻 { status = string.Format(" and Status=1"); } string sqlcondition = string.Format("where PubTime>='{0}' and PubTime<='{1}' {2} {3}", lqc.StartTime.ToString("yyyy-MM-dd"), lqc.EndTime.ToString("yyyy-MM-dd"), newsType, status); sqlconn = new SqlConnection(ComFunction.SqlConnectionString); sqlconn.Open(); sqlcmd = sqlconn.CreateCommand(); sqlcmd.CommandType = CommandType.StoredProcedure; sqlcmd.CommandText = "GetRecordFromPage"; sqlcmd.Parameters.Add("@selectlist", SqlDbType.VarChar);//选择字段列表 sqlcmd.Parameters.Add("@SubSelectList", SqlDbType.VarChar); //内部子查询字段列表 sqlcmd.Parameters.Add("@TableSource", SqlDbType.VarChar); //表名或视图表 sqlcmd.Parameters.Add("@TableOrder", SqlDbType.VarChar); //排序后的表名称 即子查询结果集的别名 sqlcmd.Parameters.Add("@SearchCondition", SqlDbType.VarChar); //查询条件 sqlcmd.Parameters.Add("@OrderExpression", SqlDbType.VarChar); //排序 表达式 sqlcmd.Parameters.Add("@PageIndex", SqlDbType.Int); sqlcmd.Parameters.Add("@PageSize", SqlDbType.Int); sqlcmd.Parameters.Add("@PageCount", SqlDbType.Int); sqlcmd.Parameters["@SubSelectList"].Value = "ID,NewsTitle,NewsContent,PubPerson,PubTime,Status,NewsType,OverView "; sqlcmd.Parameters["@selectlist"].Value = "ID,NewsTitle,NewsContent,PubPerson,PubTime,Status,NewsType,OverView "; sqlcmd.Parameters["@TableSource"].Value = "Trade_News"; sqlcmd.Parameters["@TableOrder"].Value = "a";//取L_Trade_Order的别名 sqlcmd.Parameters["@SearchCondition"].Value = sqlcondition; sqlcmd.Parameters["@OrderExpression"].Value = "order by PubTime desc"; sqlcmd.Parameters["@PageIndex"].Value = pageindex; sqlcmd.Parameters["@PageSize"].Value = pagesize; sqlcmd.Parameters["@PageCount"].Direction = ParameterDirection.Output; sqldr = sqlcmd.ExecuteReader(); while (sqldr.Read()) { TradeNews TdNews = new TradeNews(); TdNews.ID = sqldr["ID"].ToString(); TdNews.NewsTitle = sqldr["NewsTitle"].ToString(); TdNews.NewsContent = sqldr["NewsContent"].ToString(); TdNews.PubPerson = sqldr["PubPerson"].ToString(); TdNews.PubTime = Convert.ToDateTime(sqldr["PubTime"]); TdNews.Status = (NewsStatus)sqldr["Status"]; TdNews.NType = (NewsType)sqldr["NewsType"]; TdNews.OverView = System.DBNull.Value != sqldr["OverView"] ? sqldr["OverView"].ToString() : string.Empty; tradeNewsInfo.TradeNewsInfoList.Add(TdNews); } sqlconn.Close(); page = Convert.ToInt32(sqlcmd.Parameters["@PageCount"].Value); tradeNewsInfo.Result = true; tradeNewsInfo.Desc = "查询成功"; } catch (Exception ex) { ComFunction.WriteErr(ex); if (null != tradeNewsInfo.TradeNewsInfoList && tradeNewsInfo.TradeNewsInfoList.Count > 0) { tradeNewsInfo.TradeNewsInfoList.Clear(); } tradeNewsInfo.Result = false; tradeNewsInfo.Desc = "查询失败"; } finally { if (null != sqlconn) { sqlconn.Close(); } if (null != sqldr) { sqldr.Close(); } } return tradeNewsInfo; }
/// <summary> /// 新闻公告查询 /// </summary> /// <param name="LoginId">登陆标识</param> /// <param name="starttime">开始时间</param> /// <param name="endtime">结束时间</param> /// <param name="NType">新闻类型</param> /// <returns>新闻信息</returns> public TradeNewsInfo GetTradeNewsInfo(string LoginId, DateTime starttime, DateTime endtime, NewsType NType) { TradeNewsInfo tradeNewsInfo = new TradeNewsInfo(); try { TradeUser TdUser = new TradeUser(); #region 判断登陆标识是否过期 if (!ComFunction.ExistUserLoginID(LoginId, ref TdUser)) { tradeNewsInfo.Result = false; tradeNewsInfo.ReturnCode = ResCode.UL003; tradeNewsInfo.Desc = ResCode.UL003Desc; return tradeNewsInfo; } #endregion string newsType = string.Empty; if(NType!= NewsType.ALL) { newsType = string.Format(" and NewsType={0}",(int)NType); } string status = string.Empty; if (UserType.NormalType == TdUser.UType) //客户端只返回启用的新闻 { status = string.Format(" and Status=1"); } string sql = string.Format("select * from Trade_News where PubTime>='{0}' and PubTime<='{1}' {2} {3}", starttime.ToString("yyyy-MM-dd"), endtime.ToString("yyyy-MM-dd"), newsType, status); tradeNewsInfo.TradeNewsInfoList = ComFunction.GetTradeNewsInfo(sql); tradeNewsInfo.Result = true; tradeNewsInfo.Desc = "查询成功"; } catch (Exception ex) { ComFunction.WriteErr(ex); tradeNewsInfo.Result = false; tradeNewsInfo.Desc = "查询失败"; } return tradeNewsInfo; }