sql语句分页_及linq分页方法步骤在注释里_生成数据库分页及sql语句.doc

以下为燃气项目的首页通知分页方法Homepage.cs：1、 调用DAL为linq语法2、 Linq最后生成sql语句，与sql分页同性质#region 分页事件 private void btnprevious_Click(object sender, EventArgs e) string strtip=; pageoperate(intcurrentpage - 1, Convert.ToInt32(treeView1.SelectedNode.Name), out strtip); /如果有错误提示 if(strtip.Length0) else intcurrentpage = intcurrentpage - 1; setpageviewinfo(); private void btngopage_Click(object sender, EventArgs e) int intinputpagenum = 0; if (int.TryParse(txtpagenum.Text, out intinputpagenum) string strtip = ; pageoperate(intinputpagenum, Convert.ToInt32(treeView1.SelectedNode.Name), out strtip); /如果有错误提示 if (strtip.Length 0) else intcurrentpage = intinputpagenum; else MessageBox.Show(请输入数字); setpageviewinfo(); private void btnnext_Click(object sender, EventArgs e) string strtip = ; pageoperate(intcurrentpage + 1, Convert.ToInt32(treeView1.SelectedNode.Name), out strtip); /如果有错误提示 if (strtip.Length 0) else intcurrentpage = intcurrentpage + 1; setpageviewinfo(); #endregion #region 分页辅助 / / 当前页数 / int intcurrentpage=1; / / 每页行数 / int intperrownum=8; / / 总页数 / int inttotalnum=0; / / 分页逻辑处理 参数 / 1、首先规定页面传入参数（页数），逻辑出入参数（记录数） / 2、查出所有记录，计算出最大页数 / 3、判断当前页数是否查出范围 / 4、在完全符合的情况下，再编写处理逻辑，如第几条记录开始，第几条记录结束（共几条记录） / / / / public void pageoperate(int intcurrentnum, int intnoticetypeid,out string strtip) /错误提示 strtip = ; /总记录数 int inttotalrecord = NoticeManage_NoticeBLL.GetCountHomePageByType(intnoticetypeid); /根据总记录数算出总页数 inttotalnum = (inttotalrecord / intperrownum) + (inttotalrecord % intperrownum) 0 ? 1 : 0); /页数开始范围正常 if (intcurrentnum = 1) /页数结束范围正常 if (intcurrentnum = inttotalnum) #region 计算记录的索引 /计算跳转的记录数（即记录的索引） int intskipnum = 0; /开始的记录索引 int intbeginrecord = (intcurrentnum - 1) * intperrownum; #endregion IList NoticeManage_Noticelist = NoticeManage_NoticeBLL.GetHomePageListByTypeAndPaging(intnoticetypeid, intbeginrecord, intperrownum, out inttotalrecord); bindgrid(NoticeManage_Noticelist); inttotalnum = (inttotalrecord / intperrownum) + (inttotalrecord % intperrownum) 0 ? 1 : 0); lbltotalpage.Text = inttotalnum.ToString(); else strtip = 超出范围，最多第 + inttotalnum.ToString() + 页; MessageBox.Show(超出范围，最多第 + inttotalnum.ToString() + 页); else strtip = 超出范围，最少第1页; MessageBox.Show(超出范围，最少第1页); / / 设置分页控件显示 / public void setpageviewinfo() / / 当前页数 / lblcurrentpage.Text = intcurrentpage.ToString(); txtpagenum.Text = intcurrentpage.ToString(); / / 总页数 / lbltotalpage.Text = inttotalnum.ToString(); btnprevious.Enabled = intcurrentpage = 1 ? false : true; btnnext.Enabled = intcurrentpage = inttotalnum ? false : true; #endregionDAL方法 linq语法/ / 分页的通知查询 / / / public static IList GetHomePageListByTypeAndPaging(int intnoticetypeid, int intskipnum, int intpernum, out int inttotalrecord) inttotalrecord = 0; IList obj = null; using (GasManageModelDataContext DBway = new GasManageModelDataContext(ConnectCommon.getstrconnect() obj = (from i in DBway.NoticeManage_Notice where (intnoticetypeid = 0 | i.noticetype = intnoticetypeid) & i.isview = true orderby i.id descending select i).Skip(intskipnum).Take(intpernum).ToList(); inttotalrecord = DBway.NoticeManage_Notice.Count(i = (intnoticetypeid = 0 | i.noticetype = intnoticetypeid) & i.isview = true); return obj; / / 统计通知的个数 / / / public static int GetCountHomePageByType(int intnoticetypeid) using (GasManageModelDataContext DBway = new GasManageModelDataContext(ConnectCommon.getstrconnect() return DBway.NoticeManage_Notice.Count(i = (intnoticetypeid = 0 | i.noticetype = intnoticetypeid) & i.isview = true); return 0; 最后绑定datagridviewint intnoticetypeid为分类编号，是个条件，没有用*Linq生成的Sqlserver语句exec sp_executesql NSELECT t1.id, t1.createtime, t1.title, t1.filename, t1.ftpfilepath, t1.operateuser, t1.format, t1.noticetype, t1.isviewFROM ( SELECT ROW_NUMBER() OVER (ORDER BY t0.id DESC) AS ROW_NUMBER, t0.id, t0.createtime, t0.title, t0.filename, t0.ftpfilepath, t0.operateuser, t0.format, t0.noticetype, t0.isview FROM dbo.NoticeManage_Notice AS t0 WHERE t0.isview = p0 ) AS t1WHERE t1.ROW_NUMBER BETWEEN p1 + 1 AND p1 + p2ORDER BY t1.ROW_NUMBER,Np0 varchar(8000),p1 int,p2 int,p0=true,p1=8,p2=8注：ROW_NUMBER()为Sql 2005 提供的新函数，sql2000只能使用top,count,group by数据库表结构USE gasmanageGO/* Object: Table dbo.NoticeManage_Notice Script Date: 06/02/2015 13:53:27 */SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE dbo.NoticeManage_Notice(id int IDENTITY(1,1) NOT NULL,createtime datetime NULL,title varchar(50) NULL,filename varchar(500) NULL,ftpfilepath varchar(50) NULL,operateuser int NULL,format varchar(50) NULL,noticetype int NULL,isview varchar(50) NULL, CONSTRAINT PK_NoticeManage_Notice PRIMARY KEY CLUSTERED (id ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY) ON PRIMARYGOSET ANSI_PADDING OFFGOsql语句分页示例*exec sp_executesql NSELEC