ObjectDataSource + Sql Server 2005 + GridView的自定义分页
1.数据库结构:
CREATE TABLE [dbo].[test](
[id] [int] IDENTITY(100,1) NOT NULL,
[str] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[val] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
2.添加数据
declare @i int
set @i = 1
while @i < 100
begin
insert into test values(@i,'values'+cast(@i as varchar))
set @i = @i + 1
end
3.书写存储过程
ALTER procedure [dbo].[Pagination]
(
@startRowIndex int, --开始数据行号
@PageSize int --每页显示的数据条数
)
AS
declare @TotalRecord int --总的记录数
select @TotalRecord = count(*) from test;
declare @TotalCount int
SELECT @TotalCount = @TotalRecord
Begin
select * from
(
select id,str,val,row_Number() over (order by ID) as rowIndex from test
) t
where t.rowIndex > @startRowIndex and t.rowIndex < @startRowIndex+@PageSize+1
End
return @TotalCount
public class DBManager
{
private int totalCount = 0;
/// <summary>
/// 自定义分页
/// </summary>
/// <param name="startRowIndex">提取的第一行在结果有的序号(从第几行开始取数据)</param>
/// <param name="maximumRows">每页显多少行(每次取多少行)</param>
/// <param name="totalCount">总记录数</param>
/// <returns>强类型的结果集合</returns>
[DataObjectMethod(DataObjectMethodType.Select)]
public List<TestObj> PageSelect(int startRowIndex, int maximumRows)
{
string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["testConnectionString"].ConnectionString;
SqlConnection sqlConn = new SqlConnection(strConn);
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.Connection = sqlConn;
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "Pagination";
SqlParameter startRow = sqlCmd.Parameters.Add("@startRowIndex", SqlDbType.Int);
SqlParameter pageSize = sqlCmd.Parameters.Add("@PageSize", SqlDbType.Int);
SqlParameter Parrtvalue = sqlCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
startRow.Direction = ParameterDirection.Input;
pageSize.Direction = ParameterDirection.Input;
Parrtvalue.Direction = ParameterDirection.ReturnValue;
startRow.Value = startRowIndex;
pageSize.Value = maximumRows;
List<TestObj> list = new List<TestObj>();
TestObj temp = null;
try
{
sqlConn.Open();
DataSet ds = new DataSet();
SqlDataAdapter sqlda = new SqlDataAdapter(sqlCmd);
sqlda.Fill(ds);
this.totalCount = (int)Parrtvalue.Value;
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
temp = new TestObj();
temp.ID = (int)dr["id"];
temp.Str = dr["str"].ToString();
temp.Val = dr["val"].ToString();
list.Add(temp);
}
}
}
}
catch (Exception ex)
{
throw new Exception("error", ex);
}
finally
{
if (sqlConn.State == ConnectionState.Open)
{
sqlConn.Close();
}
}
return list;
}
public int getTotalCount()
{
return this.totalCount;
}
}
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Use ObjectDataSource and Sql Server 2005 Pagination</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False"
DataSourceID="ObjectDataSource1" PageSize="5" Width="70%">
<Columns>
<asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" />
<asp:BoundField DataField="Val" HeaderText="Val" SortExpression="Val" />
<asp:BoundField DataField="Str" HeaderText="Str" SortExpression="Str" />
</Columns>
</asp:GridView>
<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" OldValuesParameterFormatString="original_{0}"
SelectMethod="PageSelect" TypeName="DBManager" SelectCountMethod="getTotalCount" EnablePaging="True">
</asp:ObjectDataSource>
</div>
</form>
</body>
</html>
CREATE TABLE [dbo].[test]( [id] [int] IDENTITY(100,1) NOT NULL, [str] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL, [val] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL) ON [PRIMARY]2.添加数据
declare @i intset @i = 1while @i < 100begin insert into test values(@i,'values'+cast(@i as varchar)) set @i = @i + 1end3.书写存储过程
ALTER procedure [dbo].[Pagination]( @startRowIndex int, --开始数据行号 @PageSize int --每页显示的数据条数)ASdeclare @TotalRecord int --总的记录数select @TotalRecord = count(*) from test;declare @TotalCount intSELECT @TotalCount = @TotalRecord Begin select * from ( select id,str,val,row_Number() over (order by ID) as rowIndex from test ) t where t.rowIndex > @startRowIndex and t.rowIndex < @startRowIndex+@PageSize+1 Endreturn @TotalCount4.书写分页程序
public class DBManager{
private int totalCount = 0; /// <summary>
/// 自定义分页 /// </summary> /// <param name="startRowIndex">提取的第一行在结果有的序号(从第几行开始取数据)</param> /// <param name="maximumRows">每页显多少行(每次取多少行)</param> /// <param name="totalCount">总记录数</param> /// <returns>强类型的结果集合</returns> [DataObjectMethod(DataObjectMethodType.Select)] public List<TestObj> PageSelect(int startRowIndex, int maximumRows) { string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["testConnectionString"].ConnectionString; SqlConnection sqlConn = new SqlConnection(strConn); SqlCommand sqlCmd = new SqlCommand(); sqlCmd.Connection = sqlConn; sqlCmd.CommandType = CommandType.StoredProcedure; sqlCmd.CommandText = "Pagination"; SqlParameter startRow = sqlCmd.Parameters.Add("@startRowIndex", SqlDbType.Int); SqlParameter pageSize = sqlCmd.Parameters.Add("@PageSize", SqlDbType.Int); SqlParameter Parrtvalue = sqlCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int); startRow.Direction = ParameterDirection.Input; pageSize.Direction = ParameterDirection.Input; Parrtvalue.Direction = ParameterDirection.ReturnValue; startRow.Value = startRowIndex; pageSize.Value = maximumRows; List<TestObj> list = new List<TestObj>(); TestObj temp = null; try { sqlConn.Open(); DataSet ds = new DataSet(); SqlDataAdapter sqlda = new SqlDataAdapter(sqlCmd); sqlda.Fill(ds); this.totalCount = (int)Parrtvalue.Value; if (ds != null) { if (ds.Tables[0].Rows.Count > 0) { foreach (DataRow dr in ds.Tables[0].Rows) { temp = new TestObj(); temp.ID = (int)dr["id"]; temp.Str = dr["str"].ToString(); temp.Val = dr["val"].ToString(); list.Add(temp); } } } } catch (Exception ex) { throw new Exception("error", ex); } finally { if (sqlConn.State == ConnectionState.Open) { sqlConn.Close(); } } return list; } public int getTotalCount() { return this.totalCount; }}
实体类省去
5.web代码:
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head runat="server"> <title>Use ObjectDataSource and Sql Server 2005 Pagination</title></head><body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AutoGenerateColumns="False" DataSourceID="ObjectDataSource1" PageSize="5" Width="70%"> <Columns> <asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" /> <asp:BoundField DataField="Val" HeaderText="Val" SortExpression="Val" /> <asp:BoundField DataField="Str" HeaderText="Str" SortExpression="Str" /> </Columns> </asp:GridView> <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" OldValuesParameterFormatString="original_{0}" SelectMethod="PageSelect" TypeName="DBManager" SelectCountMethod="getTotalCount" EnablePaging="True"> </asp:ObjectDataSource> </div> </form></body></html>注意:
ObjectDataSource关于自定义分页几个属性.
AllowPaging属性：当要实现自定义分页时必须把AllowPaging值设置为true。
SelectMethod属性：指向获取数据的方法名称，本例设置为PageSelect。其所指向的方法的参数名与ObjectDataSource的StartRowIndexParameterName、MaximumRowsParameterName、SortParameterName相对应（注意：参数不分先后，但参数名要一一对应相同，否则出错）
StartRowIndexParameterName属性：当前页的第一条数据在查询数据中的位置，它的值与SelectMethod所指向的方法的相应参数，默认值为startRowIndex。
MaximumRowsParameterName属性：当前页面的记录行数，它的值与SelectMethod所指向的方法的相应参数，默认值为maximumRows.
SelectCountMethod指获取记录总数的方法,本例为getTotalCount
本例主要结何SQL Server 2005的新函数row_Number() over (order by xx)来实现分页的!
