这是一个简单的分页存储过程实例，返回总记录数表和查询得出的表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
create procedure [dbo].[goodfenye]
(@tablename varchar(200),//表名称
@pageindex int , //当前页数
@pagesize int, //每页显示的页数
@total int output, //总记录数
@search varchar(200) //查询条件
)as begin
declare @strsql varchar(2000)
declare @sqlwhere varchar (1000)
declare @sqltotal nvarchar (1000)
if @search!=‘‘
begin
set @sqlwhere = ‘1=1 and GoodName=‘+@search
end
else
begin
set @sqlwhere =‘ 1=1 ‘
end
set @strsql =‘select top ‘ + cast(@pagesize as varchar(20))+ ‘ * from ‘ +@tablename + ‘ where gid not in (select top ‘ + cast(@pagesize*(@pageindex-1) as varchar(20)) +‘ gid from ‘+@tablename +‘)‘
set @sqltotal=‘select count(1) from ‘+ @tablename
begin
exec sp_executesql @sqltotal,N‘@total int output‘,@total output //返回总页数
exec (@strsql) //返回
end
end
|
C#调用存储过程如下
public void databind() { int temp1 = Convert.ToInt32(lblCurrentPage.Text); DataSet ds = new System.Data.DataSet(); SqlCommand mycommand = new SqlCommand(); SqlDataAdapter asd = new SqlDataAdapter(); asd.SelectCommand = mycommand; asd.SelectCommand.Connection = conn; asd.SelectCommand.CommandText = "goodfenye"; asd.SelectCommand.CommandType = CommandType.StoredProcedure; conn.Open(); SqlParameter[] par = { new SqlParameter ("@tablename",SqlDbType.VarChar,255), new SqlParameter ("@pageindex",SqlDbType.Int), new SqlParameter ("@pagesize",SqlDbType.Int), new SqlParameter("@total",SqlDbType.Int), new SqlParameter ("@search",SqlDbType.VarChar,255) }; par[0].Value = "tabG_Goods"; par[1].Value = temp1; par[2].Value = 5; par[3].Direction = ParameterDirection.Output; par[4].Value = this.Textname.Text; foreach (SqlParameter pa in par) { asd.SelectCommand.Parameters.Add(pa); } asd.Fill(ds, "ss"); conn.Close(); this.Repeater1.DataSource = ds.Tables[1]; this.Repeater1.DataBind(); int count = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString()); this.labeltotal.Text = count.ToString(); int j = count / 5; //页数 int i = count % 5; if (j >= 1) { if (i > 0) { j = j + 1; } } else { j = 1; } lblPageCount.Text = j.ToString(); }
不足之处请大家指正，欢迎大家和我一起探讨.net，QQ:845750322。