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('卢妹琦',11,0,9,91)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('费洁兰',34,0,28,28)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('卞婉琴',77,0,63,47)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('蔡娣璐',57,0,47,83)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('池岚菁',81,0,66,66)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('方锦璧',4,0,4,4)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('赖勤琳',47,0,39,23)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('孔萍秋',28,0,23,58)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('丘莺莺',51,0,42,42)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('丁妍秀',93,0,77,77)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('廖蕊蓓',17,0,14,96)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('康姣玲',117,0,96,33)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('司徒凤艳',21,0,17,17)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('雷君茜',64,0,52,52)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('连兰纨',106,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('倪茜露',110,0,91,91)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('时娜芳',34,0,28,63)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('金梦雪',76,0,63,47)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('岑婷倩',57,0,47,82)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('凌彩珊',80,0,66,66)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('冯芬芝',4,0,3,38)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('鲁秋珠',47,0,38,22)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('潘玉妍',27,0,22,58)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('甘艳香',50,0,42,42)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('郭晶淑',93,0,77,14)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('钱珠桂',17,0,14,96)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('江芳华',40,0,33,33)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('梁荷晶',21,0,17,17)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('梅怡勤',63,0,52,87)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('白仪珍',106,0,87,71)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('韩惠仪',86,0,71,8)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('欧雁苑',110,0,90,90)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('盛淑娟',33,0,28,28)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('路媛凤',76,0,63,47)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('黄瑞蕊',57,0,47,82)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('骆巧惠',80,0,66,66)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('林佳梦',4,0,3,3)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('姬娥娥',46,0,38,22)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('任素嘉',27,0,22,57)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('石云凡',50,0,41,41)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('符馨颖',93,0,76,76)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('史纨菊',16,0,14,96)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('齐琼媛',116,0,96,33)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('胡青雁',20,0,17,17)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('罗叶蓉',63,0,52,52)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('强真丹',106,0,87,71)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('莫环岚',86,0,71,8)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('彭眉妹',109,0,90,90)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('李雅薇',33,0,27,27)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('苏静真',76,0,62,46)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('母瑶佳',56,0,46,82)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('柯翠英',79,0,66,66)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('皮嘉洁',3,0,3,3)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('穆珍娅',46,0,38,22)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('黎莲馨',26,0,22,57)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('马琦萍',69,0,57,41)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('宁婕云',92,0,76,76)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('姜华君',16,0,13,13)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('洪珊莉',116,0,95,32)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('吕慧婉',39,0,32,16)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('刘燕素',62,0,52,52)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('龙菁爱',105,0,87,87)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('蒲娅荣',86,0,71,8)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('邓莎雅',9,0,8,90)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('侯雪巧',33,0,27,27)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('柳蓉青',75,0,62,62)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('沈娟瑾',56,0,46,81)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('樊薇眉',99,0,81,65)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('常凡月',79,0,65,2)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('施春娜',45,0,38,38)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('傅秀姣',26,0,21,21)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('陈菊霞',69,0,57,41)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('车娴翠',49,0,41,76)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('郎璧贞',16,0,13,13)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('古琳莎',115,0,95,95)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('邱琴芬',39,0,32,16)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('葛美怡',19,0,16,51)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('何桂婵',62,0,51,86)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('成霞叶',85,0,70,70)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('简爱黛',9,0,7,89)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('戚璐婷',109,0,89,27)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('曹英锦',32,0,27,62)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('乐月玉',55,0,46,46)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('毕丹荷',98,0,81,65)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('程红静',79,0,65,2)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('秦贞慧',2,0,2,37)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('华露梅',26,0,21,21)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('孙梅娴',68,0,56,40)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('区香红',49,0,40,75)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('高瑾瑶',92,0,75,13)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('蒋莉美',115,0,95,95)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('贺芝琼',38,0,32,32)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('关黛燕',19,0,16,51)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('廉倩彩',62,0,51,86)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('孟婵环',104,0,86,70)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('陆亮乐',89,1,73,57)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('范乐海',70,1,57,93)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('华昌刚',36,1,30,30)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('龚坚松',17,1,14,14)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('费武世',59,1,49,33)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('蒲群庆',40,1,33,68)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('孙健厚',82,1,68,5)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('齐功祥',106,1,87,87)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('曹勇善',29,1,24,24)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('郎诚龙',10,1,8,43)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('白波义',53,1,43,79)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('罗子超',95,1,79,63)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('池飞山',119,1,98,98)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('沈文中',99,1,82,82)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('邓邦信',23,1,19,54)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('成茂辉',65,1,54,38)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('孟胜友',46,1,38,73)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('盛厚会',69,1,57,57)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('梅祥博',112,1,92,92)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('宁东峰',36,1,29,13)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('柯天进',16,1,13,49)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('江和承',39,1,32,32)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('岑震保',82,1,68,5)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('韩才良',6,1,5,87)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('龙清天',105,1,87,24)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('洪思邦',10,1,8,8)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('莫顺群',52,1,43,78)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('樊兴健',95,1,78,62)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('姬会全',75,1,62,97)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('邱伟富',99,1,81,81)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('金保永',22,1,18,18)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('时福先',65,1,54,38)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('秦岩亮',46,1,38,73)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('关峰敬',69,1,57,57)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('皮军广',112,1,92,92)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('强义壮',35,1,29,13)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('丁涛贵',16,1,13,48)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('苏裕绍',39,1,32,32)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('吕利和',82,1,67,4)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('黄俊杰',5,1,4,86)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('路元河',105,1,86,24)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('姜河元',9,1,8,8)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('车承岩',52,1,43,78)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('傅林志',95,1,78,62)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('蒋龙思',75,1,62,97)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('符平哲',98,1,81,81)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('倪善强',22,1,18,18)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('饶江平',65,1,53,37)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('简磊军',45,1,37,72)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('李中震',68,1,56,56)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('高明成',111,1,92,92)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('陈豪力',35,1,29,13)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('贺达国',15,1,13,48)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('葛星毅',58,1,48,32)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('雷强新',81,1,67,67)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('康全明',5,1,4,4)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('彭彬文',104,1,86,23)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('丘绍才',28,1,23,7)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('任敬飞',9,1,7,42)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('方广兴',94,1,78,78)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('鲁世茂',75,1,62,62)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('林贵武',117,1,97,81)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('史超康',98,1,81,18)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('黎博江',22,1,18,53)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('古刚生',45,1,37,37)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('马进有',87,1,72,56)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('骆心民',68,1,56,91)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('戚庆功',111,1,91,28)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('司徒彪裕',15,1,12,12)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('霍信达',58,1,48,32)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('钱毅安',38,1,32,67)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('区成彪',81,1,67,4)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('卢康发',104,1,86,86)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('甘安宁',28,1,23,7)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('柳发林',8,1,7,42)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('刘海利',51,1,42,77)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('廖辉顺',74,1,61,61)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('蔡宁磊',117,1,96,80)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('廉壮彬',97,1,80,18)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('孔浩仁',21,1,18,53)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('卞光诚',44,1,37,72)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('母力涛',87,1,72,56)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('程志福',68,1,56,91)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('穆先心',34,1,28,12)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('凌友勇',15,1,12,47)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('郭新光',38,1,31,31)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('胡民波',80,1,66,4)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('常富子',4,1,4,85)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('施生学',27,1,23,23)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('毕山浩',8,1,7,42)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('连振伟',51,1,42,77)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('欧国清',93,1,77,61)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('冯学俊',117,1,96,96)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('石有豪',97,1,80,80)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('何松星',21,1,17,52)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('乐哲昌',63,1,52,36)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('潘仁坚',44,1,36,71)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('赖永胜',67,1,55,91)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('梁良振',34,1,28,12)
insert into MyStudent(sName,sAge,sSex,sEnglish,sMath)values('侯杰东',57,1,47,31)
update MyStudent set sSex='女' where sSex='0'
update MyStudent set sSex='男' where sSex='1'