对特定表进行分页显示的存储过程（要求有两个参数一个是 每页显示的记录的条数（@pagesize），第二个是显示第几页（@pageIndex））

Sql Server 存储过程：

create proc usp_MyStudent_GetDateByPageIndex

@pageSize int,
@pageIndex int
as
begin
  select * from
  (select *,ROW_NUMBER() over(order by sId) as rowIndex
   from MyStudent) as tb1
  where tb1.rowIndex between (@pageSize*(@pageIndex-1))+1 and @pageSize*@pageIndex
end

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

app.configer:

<connectionStrings>
 <add name="MySchoolconStr" connectionString="Data Source=PC_THINK-THINK;Initial Catalog=MySchool;User ID=sa; Password=111111"/>
  </connectionStrings>

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

后台代码：

       string Constr = ConfigurationManager.ConnectionStrings["MySchoolconStr"].ConnectionString;

        DataSet ds = new DataSet();
        private void btnShow_Click(object sender, EventArgs e)
        {
           // DataTable table = new DataTable();
            ds.Clear();
            //dataGridView1.DataSource = table;
            using (SqlConnection conn = new SqlConnection(Constr))
            {
                conn.Open();
                string sp_name = "usp_MyStudent_GetDateByPageIndex";
                using (SqlCommand cmd = new SqlCommand(sp_name, conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = sp_name;
                    cmd.Parameters.Add(new SqlParameter("@pageSize", txtpageSize.Text));
                    cmd.Parameters.Add(new SqlParameter("@pageIndex", txtpageIndex.Text));
                    SqlDataAdapter data = new SqlDataAdapter(cmd);
                    data.Fill(ds);
                    dataGridView1.DataSource = ds.Tables[0];
                }
                
            }
        }

运行后：

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

create table MyStudent(sId int identity primary key,sName nvarchar(50),sAge int,sSex nchar(2),sEnglish float,sMath float)
select * from MyStudent

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

insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('范苑莲',41,0,34,18)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('霍颖瑞',64,0,53,53)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('饶荣娣',107,0,88,72)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('陆蓓春',87,0,72,9)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('