---<PS:本人菜鸟，大手子还请高台贵手>

以下是我今天在做分页时所遇到的一个分页显示问题，使用拼写SQL的方式写的，同类型可参考哦~

-----------------------------------------------------------------------------------------------------------

---------------------------------------------Model-Page----------------------------------------------------

-----------------------------------------------------------------------------------------------------------
public class page<T>
{
public int PageIndex { get; set; }
public int PageSzie { get; set; }
public int DbCount { get; set; }
public List<T> Dblist { get; set; }

}

----------------------------------------------------------------------------------------------------

---------------------------------------------DAL----------------------------------------------------

----------------------------------------------------------------------------------------------------
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=CMS;Integrated Security=True");//数据库连接字符串

/// <summary>
/// 查询总数
/// </summary>
/// <returns></returns>
public int SelectCount()
{
con.Open();

string sql = "SELECT COUNT(*) FROM AdvertNews";
SqlCommand com = new SqlCommand(sql, con);
int Count = Convert.ToInt32(com.ExecuteScalar());

con.Close();
return Count;
}

/// <summary>
/// 广告新闻 分页显示
/// </summary>
/// <param name="PageIndex"></param>
/// <param name="PageSize"></param>
/// <returns></returns>
public List<AdvertNews> Adv_Show(int PageIndex, int PageSize)
{
con.Open();

string sql = "";
sql += "SELECT * FROM (";
sql += "SELECT ROW_NUMBER() OVER(ORDER BY AdvertNews_Id)id,AdvertNews_Id,AdvertNews_Title,AdvertNews_Content,AdvertNews_Writer,AdvertNews_Date,AdvertNews_State,AdvertNews_Url FROM AdvertNews";
sql += ")T WHERE T.id >=" + ((PageIndex - 1) * PageSize + 1);
sql += "AND T.id <=" + PageIndex * PageSize;
SqlCommand com = new SqlCommand(sql, con);

SqlDataReader reader = com.ExecuteReader();

List<AdvertNews> list = new List<AdvertNews>();

while (reader.Read())
{
AdvertNews a = new AdvertNews();
a.AdvertNews_Id = Convert.ToInt32(reader["AdvertNews_Id"]);
a.AdvertNews_Title = reader["AdvertNews_Title"].ToString();
a.AdvertNews_Content = reader["AdvertNews_Content"].ToString();
a.AdvertNews_Writer = reader["AdvertNews_Writer"].ToString();
a.AdvertNews_Date = Convert.ToDateTime(reader["AdvertNews_Date"]);
a.AdvertNews_State = Convert.ToInt32(reader["AdvertNews_State"]);
a.AdvertNews_Url = reader["AdvertNews_Url"].ToString();

list.Add(a);
}
con.Close();

return list;
}

----------------------------------------------------------------------------------------------------

---------------------------------------------BLL----------------------------------------------------

----------------------------------------------------------------------------------------------------

AdvertNews_Dal dal = new AdvertNews_Dal();

public page<AdvertNews> Adv_Show(int PageIndex, int PageSize)
{
page<AdvertNews> p = new page<AdvertNews>();
p.PageIndex = PageIndex;
p.PageSzie = PageSize;
p.DbCount = dal.SelectCount();
p.Dblist = dal.Adv_Show(PageIndex, PageSize);

return p;
}

----------------------------------------------------------------------------------------------------

---------------------------------------------Controller---------------------------------------------

----------------------------------------------------------------------------------------------------

AdvertNews_Bll adv_bll = new AdvertNews_Bll();
public ActionResult Adv_Show()
{
return View(adv_bll.Adv_Show(1,8));
}

public ActionResult Adv_Second_Show(int id)
{
return View("Adv_Show", adv_bll.Adv_Show(id, 8));
}

----------------------------------------------------------------------------------------------

---------------------------------------------前台---------------------------------------------

----------------------------------------------------------------------------------------------

<table>
<tr>
<td>新闻标号</td>
<td>新闻标题</td>
</tr>
@foreach (var item in Model.Dblist)
{
<tr>
<td>@item.AdvertNews_Id</td>
<td>@item.AdvertNews_Title</td>
</tr>
}
</table>

<div style="float:right;">
<a href="/AdvertNews/Adv_Show">首页</a>
@if (Model.PageIndex > 1)
{
<a href="/AdvertNews/Adv_Second_Show/@(Model.PageIndex-1)">上一页</a>
}
@if (Model.DbCount % Model.PageSzie == 0)
{
if (Model.PageIndex < Model.DbCount / Model.PageSzie)
{
<a href="/AdvertNews/Adv_Second_Show/@(Model.PageIndex+1)">下一页</a>
}
}
else
{
if (Model.PageIndex < Model.DbCount / Model.PageSzie + 1)
{
<a href="/AdvertNews/Adv_Second_Show/@(Model.PageIndex+1)">下一页</a>
}
}
@if (Model.DbCount % Model.PageSzie == 0)
{
<a href="/AdvertNews/Adv_Second_Show/@(Model.DbCount/Model.PageSzie)">尾页</a>
}
else
{
<a href="/AdvertNews/Adv_Second_Show/@(Model.DbCount/Model.PageSzie+1)">尾页</a>
}
</div>
<div style="float:left;">
@{
var page = 0;
if (Model.DbCount % Model.PageSzie == 0)
{
page = Model.DbCount / Model.PageSzie;
}
else
{
page = Model.DbCount / Model.PageSzie + 1;
}
<span>
数据总条数：@Model.DbCount &nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;
总页数为:@page &nbsp;&nbsp;&nbsp;&nbsp;|&nbsp;&nbsp;&nbsp;&nbsp;
当前页：@Model.PageIndex
</span>
}
</div>