使用sql分页显示

 select * from (
                          select ROW_NUMBER () over(order by  主键字段) as RowNumber,* from   表

                      ) as TAB
                     where TAB.RowNumber>1 and TAB.RowNumber<=2 

分页  TAB.RowNumber>
                     (@CurPageNum - 1)* @PageSize AND TAB.RowNumber< =@CurPageNum * @PageSize

--当前页（第几页）

--每行显示行数

 

 

 

public void GetData()
         {
             dateTimePicker1.Format = DateTimePickerFormat.Custom;
             dateTimePicker1.CustomFormat = "yyyy-MM-dd";
             dateTimePicker2.Format = DateTimePickerFormat.Custom;
             dateTimePicker2.CustomFormat = "yyyy-MM-dd";
             if (dateTimePicker2.Value <= dateTimePicker1.Value)
             {
                 MessageBox.Show("起始日期应该小于终止日期！");
             }
             else
             {
                 string conditions = "";//sql拼的条件
                 if (txtPatientID.Text.Trim() != "")
                 {
                     conditions += " and PatientID like '%" + txtPatientID.Text.Trim() + "%'";
                 }
                 if (textBox2.Text.Trim() != "")
                 {
                     conditions += "and PTN_NAME = '" + textBox2.Text.Trim() + "'";
                 }
                 conditions += " and (Birthday between " + "'" + dateTimePicker1.Text.Trim() +
                  "'" + " and " + "'" + dateTimePicker2.Text.Trim() + "'" + ")";
                 string sql = @"SELECT PatientID,PTN_NAME,PY,SEX,Birthday,CertificateID,Address,Telephone,"+
                     "othernumber1,othernumber2,othernumber3 FROM(SELECT ROW_NUMBER() OVER(Order BY PatientID ASC )"+
                     " AS RowNumber,* FROM RIS_PATIENT where 1=1"+conditions+" ) AS TAB WHERE TAB.RowNumber>" +
                     (this.CurPageNum - 1) * PageSize + " AND TAB.RowNumber< =" + this.CurPageNum * this.PageSize + "";//分页
               
                 DataTable dt = dbHeleper.getTable(sql);
                 dgvData.DataSource = dt;

                 #region\\显示查询到的行数
                 string tjsql = "select count(PatientID)  as counts from RIS_PATIENT where 1=1";
                 if (txtPatientID.Text.Trim() != "")
                 {
                     tjsql += " and PatientID like '%" + txtPatientID.Text.Trim() + "%'";
                 }
                 if (textBox2.Text.Trim() != "")
                 {
                     tjsql += "and PTN_NAME = '" + textBox2.Text.Trim() + "'";
                 }
                 tjsql += " and (Birthday between " + "'" + dateTimePicker1.Text.Trim() +
                     "'" + " and " + "'" + dateTimePicker2.Text.Trim() + "'" + ")";
                 DataTable dt2 = dbHeleper.getTable(tjsql);
                 if (dt2 != null && dt2.Rows.Count > 0)
                 {
                     lblCount.Text = "已查询到数据" + dt2.Rows[0][0].ToString() + "条";
                     TotalNum= long.Parse(dt2.Rows[0][0].ToString());
                 }
                 #endregion

                 if (this.CurPageNum > this.TotalNum / this.PageSize)
                     button2.Enabled = false;
                 else
                     button2.Enabled = true;
                 if (this.CurPageNum == 1)
                     button3.Enabled = false;
                 else
                     button3.Enabled = true;
                 if (this.TotalNum % this.PageSize != 0)
                 {
                     this.TotalPage = this.TotalNum / this.PageSize + 1;
                 }
                 else
                     this.TotalPage = this.TotalNum / this.PageSize;
                 label5.Text = "当前页为" + this.CurPageNum + "/" + this.TotalPage;
                 textBox1.Text = CurPageNum.ToString();
             }
         }

 

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

存储过程

USE [ris_lc]
GO
/****** Object:  StoredProcedure [dbo].[FenYeFor_RIS_PATIENT]    Script Date: 08/01/2013 15:38:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[FenYeFor_RIS_PATIENT]
 @CurPageNum int,--当前查询的页
 @PageSize int--每页的数据行数
as
SELECT PatientID,PTN_NAME,PY,SEX,Birthday,CertificateID,Address,Telephone,
                    othernumber1,othernumber2,othernumber3 FROM(SELECT ROW_NUMBER() OVER(Order BY PatientID ASC )
                     AS RowNumber,* FROM RIS_PATIENT where 1=1) AS TAB WHERE TAB.RowNumber>
                     (@CurPageNum - 1) * @PageSize AND TAB.RowNumber< =@CurPageNum * @PageSize
               

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

建表

USE [ris_lc]
GO

/****** Object:  Table [dbo].[RIS_PATIENT]    Script Date: 08/01/2013 15:40:54 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[RIS_PATIENT](
 [PatientID] [varchar](16) NOT NULL,
 [PTN_NAME] [varchar](128) NOT NULL,
 [PY] [varchar](128) NOT NULL,
 [SEX] [varchar](32) NOT NULL,
 [Birthday] [datetime] NULL
) ON [PRIMARY]
SET ANSI_PADDING OFF
ALTER TABLE [dbo].[RIS_PATIENT] ADD [CertificateID] [varchar](50) NULL
ALTER TABLE [dbo].[RIS_PATIENT] ADD [Address] [varchar](100) NULL
ALTER TABLE [dbo].[RIS_PATIENT] ADD [Telephone] [varchar](50) NULL
ALTER TABLE [dbo].[RIS_PATIENT] ADD [othernumber1] [varchar](50) NULL
ALTER TABLE [dbo].[RIS_PATIENT] ADD [othernumber2] [varchar](50) NULL
ALTER TABLE [dbo].[RIS_PATIENT] ADD [othernumber3] [varchar](50) NULL
/****** Object:  Index [PK_RIS_PATIENT]    Script Date: 08/01/2013 15:40:54 ******/
ALTER TABLE [dbo].[RIS_PATIENT] ADD  CONSTRAINT [PK_RIS_PATIENT] PRIMARY KEY CLUSTERED
(
 [PatientID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

 

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