datagrid|分页先在数据库中定义存储过程，轻易实现百万级数据分页：//PageSize：分页大小，PageIndex：页号，PageCount：总页数，recordCount：记录数CREATE PROCEDURE GetCustomDataPage pageSize int, pageIndex int, pageCount int output, recordCount int output ASdeclare SQL varchar(1000)select recordCount=count(*) from productsset pageCount=ceiling(recordCount*1.0/pageSize)if pageIndex = 0 or pageCount<=1set SQL='select top '+str(pageSize)+' productID,productName, unitPrice from products order by productID asc'else if pageIndex = pageCount -1 set SQL='select * from ( select top '+str(recordCount - pageSize * pageIndex)+' productID,productName, unitPrice from products order by productID desc) TempTable order by productID asc'
else set SQL='select top '+str(pageSize) +' * from ( select top '+str(recordCount - pageSize * pageIndex)+' productID,productName, unitPrice from products order by productID desc) TempTable order by productID asc' exec(SQL)GO好了，存储过程建好了，那么如何在.Net中使用呢？请看以下代码：
private uint pageCount; //总页数 private uint recordCount; //总记录数
private DataSet GetPageData(uint pageSize, uint pageIndex)
{
string strConn = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
SqlCommand command = new SqlCommand("GetCustomDataPage",conn); //第一个参数为存储过程名
command.CommandType = CommandType.StoredProcedure; //声明命令类型为存储过程
command.Parameters.Add("pageSize",SqlDbType.Int);
command.Parameters["pageSize"].Value = pageSize;
command.Parameters.Add("pageIndex",SqlDbType.Int);
command.Parameters["pageIndex"].Value = pageIndex;
command.Parameters.Add("pageCount",SqlDbType.Int);
command.Parameters["pageCount"].Value = pageCount;
command.Parameters["pageCount"].Direction = ParameterDirection.Output; //存储过程中的输出参数
command.Parameters.Add("recordCount",SqlDbType.Int);
command.Parameters["recordCount"].Value = recordCount;
command.Parameters["recordCount"].Direction = ParameterDirection.Output; //存储过程中的输出参数
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataSet ds = new DataSet();
adapter.Fill(ds);
//获得输出参数值
pageCount = Convert.ToUInt32(command.Parameters["pageCount"].Value);
recordCount = Convert.ToUInt32(command.Parameters["recordCount"].Value);
conn.Close();
return ds;
}
//绑定数据到DataGrid中
private void BindDataGrid()
{
DataSet ds = GetPageData((uint)dgProduct.PageSize,(uint)dgProduct.CurrentPageIndex);
dgProduct.VirtualItemCount = (int)recordCount;
dgProduct.DataSource = ds;
dgProduct.DataBind();
}
//页面加载时就绑定DataGrid
private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
BindDataGrid();
}
}
//用户翻页时事件处理
private void dgProduct_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
dgProduct.CurrentPageIndex = e.NewPageIndex;
BindDataGrid();
}
//用户单击编辑按纽时事件处理
private void dgProduct_EditCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
dgProduct.EditItemIndex = e.Item.ItemIndex;
BindDataGrid();
}
//用户单击取消按纽时事件处理
private void dgProduct_CancelCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
dgProduct.EditItemIndex = -1;
BindDataGrid();
}
//用户单击更新按纽时事件处理
private void dgProduct_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
string strConn = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
//string strSQL = "update from products set productName=productName, set unitPrice=unitPrice where productID=productID";
string strSQL = "update products set productName=productName where productID=productID";
SqlCommand command = new SqlCommand(strSQL,conn);
command.Parameters.Add("productName",SqlDbType.NVarChar,40);
command.Parameters["productName"].Value = ((TextBox)(e.Item.Cells[1].Controls[0])).Text.Trim();
//command.Parameters.Add("unitPrice",SqlDbType.Int);
//command.Parameters["unitPrice"].Value = Convert.ToInt32(((TextBox)(e.Item.Cells[2].Controls[0])).Text.Trim());
command.Parameters.Add("productID",SqlDbType.Int);
command.Parameters["productID"].Value = dgProduct.DataKeys[e.Item.ItemIndex];
command.ExecuteNonQuery();
conn.Close();
dgProduct.EditItemIndex = -1;
BindDataGrid();
}
//用户单击删除按纽时事件处理
private void dgProduct_DeleteCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
string strConn = System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"];
SqlConnection conn = new SqlConnection(strConn);
conn.Open();
SqlCommand command = new SqlCommand("DeleteProduct",conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("productID",SqlDbType.Int);
command.Parameters["productID"].Value = dgProduct.DataKeys[e.Item.ItemIndex];
command.ExecuteNonQuery();
BindDataGrid();
}
//实现删除确认及颜色交替显示功能
private void dgProduct_ItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
if(e.Item.ItemType == ListItemType.Item ||e.Item.ItemType == ListItemType.AlternatingItem)
{
Button btnDelete = (Button)(e.Item.Cells[4].Controls[0]);
btnDelete.Attributes.Add("onClick","JavaScript:return confirm('确定删除？')");
e.Item.Attributes.Add("onMouseOver","this.style.backgroundColor='#FFCC66'");
e.Item.Attributes.Add("onMouseOut","this.style.backgroundColor='#ffffff'");
} }
