Datalist控件使用存储过程来分页实现代码

body>
form id="form1" runat="server">
div>
asp:DataList ID="DataList1" runat="server"
onitemcommand="DataList1_ItemCommand"
oncancelcommand="DataList1_CancelCommand"
ondeletecommand="DataList1_DeleteCommand" oneditcommand="DataList1_EditCommand"
onupdatecommand="DataList1_UpdateCommand"
onitemdatabound="DataList1_ItemDataBound">
EditItemTemplate>
table style="width: 100%; height: 180px;">
tr>
td class="style4">
商品名：/td>
td class="style2">
asp:TextBox ID="txtProductName" runat="server"
Text='%# Eval("ProductName") %>'>/asp:TextBox>
/td>
/tr>
tr>
td class="style4">
规格：/td>
td class="style2">
asp:TextBox ID="txtProductStandard" runat="server"
Text='%# Eval("ProductStandard") %>'>/asp:TextBox>
/td>
/tr>
tr>
td class="style4">
包装率：/td>
td class="style2">
asp:TextBox ID="txtPackagingRatio" runat="server"
Text='%# Eval("PackagingRatio") %>'>/asp:TextBox>
/td>
/tr>
tr>
td class="style4">
商品条码：/td>
td class="style2">
asp:TextBox ID="txtArticleNum" runat="server" Text='%# Eval("ArticleNum") %>'>/asp:TextBox>
/td>
/tr>
tr>
td class="style4">
价格：/td>
td class="style2">
asp:TextBox ID="txtPrice" runat="server" Text='%# Eval("Price") %>'>/asp:TextBox>
/td>
/tr>
tr>
td class="style4">
asp:Button ID="btnUpdate" runat="server" CommandArgument='%# Eval("PId") %>'
CommandName="update" Height="21px" Text="更新" />
/td>
td class="style2">
asp:Button ID="btnCancel" runat="server" CommandName="cancel" Text="取消" />
/td>
/tr>
/table>
/EditItemTemplate>
ItemTemplate>
产品名:asp:Label ID="Label1" runat="server" Text='%# Eval("ProductName") %>'>/asp:Label>
br />
规格:asp:Label ID="Label2" runat="server" Text='%# Eval("ProductStandard") %>'>/asp:Label>
br />
包装率:asp:Label ID="Label3" runat="server" Text='%# Eval("PackagingRatio") %>'>/asp:Label>
br />
商品条码:asp:Label ID="Label4" runat="server" Text='%# Eval("ArticleNum") %>'>/asp:Label>
br />
超市价格:asp:Label ID="Label5" runat="server" Text='%# Eval("Price") %>'>/asp:Label>
br />
asp:Button ID="btnEdit" runat="server" Text="编辑" CommandName="Edit" />
nbsp;asp:Button ID="btnDelete" runat="server" Text="删除"
CommandArgument='%# Eval("PId") %>' CommandName="delete" />
br />
br />
asp:Button ID="Button1" runat="server" CommandArgument='%# Eval("PId") %>'
CommandName="Buy" Text="放入购物车" />
br />
/ItemTemplate>
/asp:DataList>
br />
br />
asp:Button ID="btnFirst" runat="server" onclick="btnFirst_Click"
Text="|lt;" />
nbsp;asp:Button ID="btnPrev" runat="server" onclick="btnPrev_Click" Text="lt;"
style="height: 21px" />
nbsp;asp:Button ID="btnNext" runat="server" onclick="btnNext_Click" Text="gt;" />
nbsp;asp:Button ID="btnLast" runat="server" onclick="btnLast_Click" Text="gt;|" />
nbsp;asp:Label ID="Label1" runat="server">/asp:Label>
asp:TextBox ID="txtPageNumber" runat="server" Height="26px" Width="43px">/asp:TextBox>
asp:CompareValidator ID="CompareValidator1" runat="server"
ControlToValidate="txtPageNumber" Display="Dynamic" ErrorMessage="必须为整数！"
ForeColor="#FF3300" Operator="DataTypeCheck" Type="Integer">/asp:CompareValidator>
asp:RangeValidator ID="RangeValidator1" runat="server"
ControlToValidate="txtPageNumber" Display="Dynamic" ErrorMessage="输入数据不合法！"
ForeColor="Red" MaximumValue="9" MinimumValue="1">/asp:RangeValidator>
asp:Button ID="btnGo" runat="server" onclick="btnGo_Click" Text="Go" />
br />
asp:HiddenField ID="HiddenField1" runat="server" />
asp:HiddenField ID="HiddenField2" runat="server" />
/div>
/form>
/body>

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
bindProduct(1);
}
}
private void bindProduct(int pageIndex)
{
string constr = ConfigurationManager.ConnectionStrings["studentConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
con.Open();
using (SqlCommand cmd = con.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_Product_Select_by_Page_rowNumber";
cmd.Parameters.AddWithValue("@pageSize", 3);
cmd.Parameters.Add("@pageCount", System.Data.DbType.Int32).Direction = ParameterDirection.Output;
cmd.Parameters.AddWithValue("@pageIndex", pageIndex);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
adapter.Fill(dt);
this.DataList1.DataSource = dt;
this.DataList1.DataBind();
int pageCount = Convert.ToInt32(cmd.Parameters["@pageCount"].Value);
this.HiddenField1.Value = pageCount.ToString();
this.HiddenField2.Value = pageIndex.ToString();
}
}
}
protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
{
if (e.CommandName == "Buy")
{
Response.Write(e.CommandArgument.ToString());
}
}
protected void DataList1_EditCommand(object source, DataListCommandEventArgs e)
{
this.DataList1.EditItemIndex = e.Item.ItemIndex;
this.bindProduct(1);
}
protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e)
{
string ProName = (e.Item.FindControl("txtProductName") as TextBox).Text;
string ProStandarde = (e.Item.FindControl("txtProductStandard") as TextBox).Text;
string ProPackaging = (e.Item.FindControl("txtPackagingRatio") as TextBox).Text;
string ProArtialeNum = (e.Item.FindControl("txtArticleNum") as TextBox).Text;
string ProPrice = (e.Item.FindControl("txtPrice") as TextBox).Text;
string sql = "update Product set ProductName=@ProductName,ProductStandard=@ProductStandard,PackagingRatio=@PackagingRatio,ArticleNum=@ArticleNum,Price=@Price where PId=@pid";
SqlParameter[] pms = new SqlParameter[]{
new SqlParameter("@ProductName",ProName),
new SqlParameter("@ProductStandard",ProStandarde),
new SqlParameter("@PackagingRatio",ProPackaging),
new SqlParameter("@ArticleNum",ProArtialeNum),
new SqlParameter("@Price",ProPrice),
new SqlParameter("@pid",e.CommandArgument)
};
SQLHelper.ExecuteNonQuery(sql, pms);
}
protected void DataList1_CancelCommand(object source, DataListCommandEventArgs e)
{
this.DataList1.EditItemIndex = -1;
this.bindProduct(1);
}
protected void DataList1_DeleteCommand(object source, DataListCommandEventArgs e)
{
string sql = "delete from Product where PId=@pid";
SqlParameter pms = new SqlParameter("@pid", e.CommandArgument);
SQLHelper.ExecuteNonQuery(sql, pms);
this.bindProduct(1);
}
protected void btnFirst_Click(object sender, EventArgs e)
{
this.bindProduct(1);
}
protected void btnPrev_Click(object sender, EventArgs e)
{
int index = Convert.ToInt32(this.HiddenField2.Value);
if (index > 1)
{
index--;
this.bindProduct(index);
}
}
protected void btnNext_Click(object sender, EventArgs e)
{
int index = Convert.ToInt32(this.HiddenField2.Value);
int pageCount = Convert.ToInt32(this.HiddenField1.Value);
if (indexpageCount)
{
index++;
this.bindProduct(index);
}
}
protected void btnLast_Click(object sender, EventArgs e)
{
this.bindProduct(Convert.ToInt32(this.HiddenField1.Value));
}
protected void btnGo_Click(object sender, EventArgs e)
{
if (Convert.ToInt32(txtPageNumber.Text) = Convert.ToInt32(HiddenField1.Value))
{
this.bindProduct(Convert.ToInt32(txtPageNumber.Text));
}
else
{
Response.Write("您输入的页数超出了总页数，如有需要请重新输入！");
}
}
protected void DataList1_ItemDataBound(object sender, DataListItemEventArgs e)
{
Label1.Text = "第" + (HiddenField2.Value).ToString() + "页，共" + HiddenField1.Value.ToString() + "页";
}

CREATE PROCEDURE [dbo].[sp_Product_Select_by_Page_rowNumber]
@pageSize int, --每页记录数量
@pageCount int output, --总页数
@pageIndex int --当前页索引号
AS
BEGIN
declare @totalRecords int
select @totalRecords = count(PId) from Product
if(@totalRecords % @pageSize = 0)
set @pageCount = @totalRecords / @pageSize;
else
set @pageCount = @totalRecords / @pageSize +1;
with temp as (select row_number() over (order by PId) as id,* from Product)
select * from temp where id between (@pageIndex -1)*@pageSize +1 and @pageIndex * @pageSize
return @totalRecords
end
GO

connectionStrings>
add name="studentConnectionString" connectionString="Data Source=PC_THINK-THINK;Initial Catalog=student;Persist Security Info=True;User ID=sa;Password=111111"
providerName="System.Data.SqlClient" />
/connectionStrings>

public static String connStr = ConfigurationManager.ConnectionStrings["studentConnectionString"].ConnectionString;
public static int ExecuteNonQuery(string sql, params SqlParameter[] pms)
{
using (SqlConnection con = new SqlConnection(connStr))
{
using (SqlCommand cmd = new SqlCommand(sql, con))
{
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
con.Open();
return cmd.ExecuteNonQuery();
}
}
}
public static DataTable ExecuteDataTable(string sql, params SqlParameter[] pms)
{
DataTable dt = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(sql,connStr);
if (pms != null)
{
adapter.SelectCommand.Parameters.AddRange(pms);
}
adapter.Fill(dt);
return dt;
}