存储过程:

 

GetProductsCount1:

GetProductsByPage:

复制代码
CREATE PROCEDURE GetProductsByPage
@PageNumber
int
,
@PageSize
int

AS
declare @sql nvarchar(
4000
)
set @sql ='select top '+ Convert(varchar, @PageSize) +' * from test where id not in (select top '+ Convert(varchar, (@PageNumber -1) * @PageSize) +' id from test)'

exec sp_executesql @sql
GO
复制代码

前台代码:

复制代码
view plaincopy to clipboardprint?
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default2.aspx.cs" Inherits="Default2"%>
<%@ Register Assembly="AspNetPager" Namespace="Wuqi.Webdiyer" TagPrefix="webdiyer"%>

<%@ Register Assembly="System.Web.Extensions, Version=1.0.61025.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"

Namespace
="System.Web.UI" TagPrefix="asp"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN""http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

<title>无标题页</title>

<mce:style type="text/css"><!--

.lbtnstyle{
font
-size: 12px; color: black; text-
decoration: none;color:red;
}
.anpager .cpb {background:#1F3A87 none repeat scroll
00;border:1px solid #CCCCCC;color:#FFFFFF;font-weight:bold;margin:5px 4px 00;padding:4px 5px 0;font-
size:12px}
.anpager a {background:#FFFFFF none repeat scroll
00;border:1px solid #CCCCCC;color:#1F3A87;margin:5px 4px 00;padding:4px 5px 0;text-decoration:none;font-
size:12px}
.anpager a:hover{background:#1F3A87 none repeat scroll
00
;border:1px solid #1F3A87;color:#FFFFFF;}

--></mce:style><style type="text/css" mce_bogus="1">
.lbtnstyle{
font
-size: 12px; color: black; text-
decoration: none;color:red;
}
.anpager .cpb {background:#1F3A87 none repeat scroll
00;border:1px solid #CCCCCC;color:#FFFFFF;font-weight:bold;margin:5px 4px 00;padding:4px 5px 0;font-
size:12px}
.anpager a {background:#FFFFFF none repeat scroll
00;border:1px solid #CCCCCC;color:#1F3A87;margin:5px 4px 00;padding:4px 5px 0;text-decoration:none;font-
size:12px}
.anpager a:hover{background:#1F3A87 none repeat scroll
00
;border:1px solid #1F3A87;color:#FFFFFF;}
</style>

</head>

<body>

<form id="form1" runat="server">

<div>

<asp:ScriptManager ID="ScriptManager1" runat="server">

</asp:ScriptManager>

<asp:UpdatePanel ID="UpdatePanel1" runat="server">

<ContentTemplate>

<table>

<tr>

<td width="150">

<asp:TextBox ID="txtname" runat="server"></asp:TextBox>

</td>

<td width="300">

<asp:Button ID="btninsert" runat="server" Text="添加行"/>

</td>

</tr>

<tr>

<td colspan="2">

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" Width="450px" CellPadding="4" ForeColor="#333333" GridLines="None" style="font-size:12px" mce_style="font-size:12px" OnRowDataBound="GridView1_RowDataBound1">

<Columns>

<asp:TemplateField>

<HeaderTemplate>

ID
</HeaderTemplate>

<EditItemTemplate><asp:TextBox ID="txtid" runat="server" Text='<%#Eval("id") %>' Enabled="false"></asp:TextBox></EditItemTemplate>

<ItemTemplate><asp:Label ID="labid" runat="server" Text='<%# Eval("id") %>'></asp:Label></ItemTemplate>

<ControlStyle Width="170px"/>

</asp:TemplateField>

<asp:TemplateField>

<HeaderTemplate>

名称
</HeaderTemplate>

<EditItemTemplate><asp:TextBox ID="txtname" runat="server" Text='<%#Eval("name") %>'></asp:TextBox></EditItemTemplate>

<ItemTemplate><asp:Label ID="labname" runat="server" Text='<%# Eval("name") %>'></asp:Label></ItemTemplate>

<ControlStyle Width="170px"/>

</asp:TemplateField>

<asp:TemplateField>

<EditItemTemplate>

<asp:LinkButton ID="lbtnupdt" runat="server" CommandName="Update" Text="更新"class="lbtnstyle" OnClientClick="return confirm('确定更新?');"></asp:LinkButton>

<asp:LinkButton ID="lbtncan" runat="server" CommandName="Cancel" Text="取消"class="lbtnstyle"></asp:LinkButton>

</EditItemTemplate>

<ItemTemplate>

<asp:LinkButton ID="lbtnedit" CommandName="Edit" runat="server" Text="编辑"class="lbtnstyle"></asp:LinkButton>

<asp:LinkButton ID="lbtndel" CommandName="Delete" runat="server" Text="删除"class="lbtnstyle" OnClientClick="return confirm('确定删除?');"></asp:LinkButton>

</ItemTemplate>

</asp:TemplateField>

</Columns>

<FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White"/>

<RowStyle BackColor="#EFF3FB"/>

<PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center"/>

<SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333"/>

<HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White"/>

<EditRowStyle BackColor="#2461BF"/>

<AlternatingRowStyle BackColor="White"/>

</asp:GridView>

</td>

</tr>

<tr>

<td colspan="2" align="right">

<webdiyer:AspNetPager ID="AspNetPager1" runat="server" CssClass="anpager" CurrentPageButtonClass="cpb" FirstPageText="首页" LastPageText="尾页" NextPageText="下一页" PrevPageText="上一页">

</webdiyer:AspNetPager>

</td>

</tr>

</table>

</ContentTemplate>

</asp:UpdatePanel>

</div>

</form>

</body>

</html>
复制代码


后台代码:

复制代码
private SqlConnection con;
int currentPageNumber;//当前页号

int pageSize =10;//每页显示记录条数
protectedvoid Page_Load(object sender, EventArgs e)
{
if (!
IsPostBack)
{
currentPageNumber
=1
;
ViewState[
"currentPageNumber"] =
currentPageNumber;
getbind();
}
//注册事件

GridView1.RowDeleting +=new GridViewDeleteEventHandler(GridView1_RowDeleting);
GridView1.RowEditing
+=new
GridViewEditEventHandler(GridView1_RowEditing);
GridView1.RowUpdating
+=new
GridViewUpdateEventHandler(GridView1_RowUpdating);
GridView1.RowCancelingEdit
+=new
GridViewCancelEditEventHandler(GridView1_RowCancelingEdit);
btninsert.Click
+=new
EventHandler(btninsert_Click);
AspNetPager1.PageChanged
+=new
EventHandler(AspNetPager1_PageChanged);

}
//分页事件

void AspNetPager1_PageChanged(object sender, EventArgs e)
{
currentPageNumber
=
AspNetPager1.CurrentPageIndex;
ViewState[
"currentPageNumber"] =
currentPageNumber;
getbind();
}
//添加一行数据事件

void btninsert_Click(object sender, EventArgs e)
{
string sql ="insert into test (name)values('"+ txtname.Text +"')"
;
con
=new SqlConnection("Server=.;DataBase=JobWanted Apartment;Uid=sa;pwd="
);
SqlCommand cmd
=new
SqlCommand(sql, con);
con.Open();
cmd.ExecuteNonQuery();
getbind();
}
//取消GridView编辑

void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
{

GridView1.EditIndex
=-1
;
getbind();
}
//GridView修改事件

void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{
string sql ="update test set name='"+ (((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtname")).Text) +"' where id='"+Convert.ToInt32(((TextBox)GridView1.Rows[e.RowIndex].FindControl("txtid")).Text)+"' "
;
con
=new SqlConnection("Server=.;DataBase=JobWanted Apartment;Uid=sa;pwd="
);
SqlCommand cmd
=new
SqlCommand(sql, con);
con.Open();
cmd.ExecuteNonQuery();
GridView1.EditIndex
=-1
;
getbind();
}
//GridView编辑事件

void GridView1_RowEditing(object sender, GridViewEditEventArgs e)
{
GridView1.EditIndex
=
e.NewEditIndex;
getbind();
}
//GridView删除行事件

void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)
{
string sql ="delete from test where id='"+ Convert.ToInt32(((Label)GridView1.Rows[e.RowIndex].FindControl("labid")).Text) +"'"
;
con
=new SqlConnection("Server=.;DataBase=JobWanted Apartment;Uid=sa;pwd="
);
SqlCommand cmd
=new
SqlCommand(sql, con);
con.Open();
cmd.ExecuteNonQuery();
getbind();
}
//GridView数据绑定

privatevoid getbind()
{
currentPageNumber
=Convert.ToInt32( ViewState["currentPageNumber"
]);
con
=new SqlConnection("Server=.;DataBase=JobWanted Apartment;Uid=sa;pwd="
);
SqlCommand cmd
=new SqlCommand("GetProductsCount1"
, con);
cmd.CommandType
=
CommandType.StoredProcedure;
SqlCommand cmd1
=new SqlCommand("GetProductsByPage"
, con);
cmd1.CommandType
=
CommandType.StoredProcedure;
cmd1.Parameters.Add(
"@PageNumber", SqlDbType.Int, 4).Value =
currentPageNumber;
cmd1.Parameters.Add(
"@PageSize", SqlDbType.Int, 4).Value =
pageSize;
con.Open();
DataTable dt
=new
DataTable();
SqlDataAdapter adapter
=new
SqlDataAdapter(cmd1);
adapter.Fill(dt);
//AspNetPager1.PageSize = 10;//设置每页显示的行数

AspNetPager1.RecordCount = (int)cmd.ExecuteScalar();//得出总的记录条数
AspNetPager1.CurrentPageIndex = currentPageNumber;
GridView1.DataSource
=
dt;
GridView1.DataBind();
}
//GridView高亮行显示

protectedvoid GridView1_RowDataBound1(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType ==
DataControlRowType.DataRow)
{
e.Row.Attributes.Add(
"onmouseover", "c=this.style.backgroundColor,this.style.backgroundColor='#C7DEF3'"
);
e.Row.Attributes.Add(
"onmouseout", "this.style.backgroundColor=c"
);
}
}
复制代码


转载:http://blog.csdn.net/weng2961389/archive/2010/03/04/5346063.aspx

 

CREATE PROCEDURE GetProductsCount1 AS
select count(
*) from test
GO

 

posted on 2013-07-08 17:38  努力实现目标  阅读(330)  评论(0编辑  收藏  举报