/// <summary>
/// 获取文章列表
/// </summary>
/// <param name="page">第几页</param>
/// <param name="pageSize">每页数量</param>
/// <param name="sort">排序元</param>
/// <param name="order">desc或asc</param>
/// <returns></returns>
public listNewsWithPages GetNewsList(int page, int pageSize, string sort, string order)
{
listNewsWithPages lnp = new listNewsWithPages();
List<News> ns = new List<News>();
conn.Open();
if (page == 1)
{
cmd.CommandText = "select top " + pageSize +
" newsID,newsTitle,newsPostTime from newsTB order by " + sort + " " + order + " ";
}
else
{
cmd.CommandText = "select top " + pageSize +
" newsID,newsTitle,newsPostTime from newsTB where newsID not in ( select top " + (page - 1) * pageSize +
" newsID from newsTB order by " + sort + " " + order + " ) order by " + sort + " " + order + " ";
}
dr = cmd.ExecuteReader();
while (dr.Read())
{
News n = new News();
n.newsID =Convert.ToInt32( dr["newsID"]);
n.newsTitle = dr["newsTitle"].ToString();
n.newsContent = "";
n.newsPostTime = (DateTime)dr["newsPostTime"];
ns.Add(n);
}
dr.Close();
//查询页数
cmd.CommandText = "select count(newsID) from newsTB";
int tl = Convert.ToInt32(cmd.ExecuteScalar());
lnp.Total = tl;
conn.Close();
lnp.newsList = ns;
return lnp;
}
public int SaveNews(News nw)
{
conn.Open();
if (nw.newsID == 0)
{
cmd.CommandText = "insert into newsTB (newsTitle,newsPostTime,newsContent) values(@newsTitle,@newsPostTime,@newsContent)";
cmd.Parameters.AddWithValue("@newsTitle", nw.newsTitle);
cmd.Parameters.AddWithValue("@newsPostTime", nw.newsPostTime);
cmd.Parameters.AddWithValue("@newsContent", nw.newsContent);
cmd.ExecuteNonQuery();
cmd.CommandText = "SELECT @@Identity ";
nw.newsID = (int)cmd.ExecuteScalar();
}
else
{
cmd.CommandText = "update newsTB set newsTitle=@newsTitle,newsPostTime=@newsPostTime,newsContent=@newsContent where newsID=@newsID";
cmd.Parameters.AddWithValue("@newsTitle", nw.newsTitle);
cmd.Parameters.AddWithValue("@newsPostTime", nw.newsPostTime);
cmd.Parameters.AddWithValue("@newsContent", nw.newsContent);
cmd.Parameters.AddWithValue("@newsID", nw.newsID);
cmd.ExecuteNonQuery();
}
conn.Close();
return nw.newsID;
}
public News GetNewes(int id)
{
News n = new News();
conn.Open();
cmd.CommandText = "select * from newsTB where newsID="+id;
dr = cmd.ExecuteReader();
if (dr.Read())
{
n.newsID = Convert.ToInt32(dr["newsID"]);
n.newsContent = Convert.ToString(dr["newsContent"]);
n.newsPostTime = Convert.ToDateTime(dr["newsPostTime"]);
n.newsTitle = Convert.ToString(dr["newsTitle"]);
}
conn.Close();
return n;
}