(C#)DataGrid实现自定义分页，鼠标移至变色，删除确认、可编辑，可删除

效果预览：   好了，让我们开始奇异的Datagrid之旅吧：

 

先在数据库中定义存储过程，轻易实现百万级数据分页：

//@PageSize：分页大小，PageIndex：页号，@PageCount：总页数，@recordCount：记录数

CREATE PROCEDURE GetCustomDataPage @pageSize int, @pageIndex int, @pageCount int output, @recordCount int output AS

declare @SQL varchar(1000)

select @recordCount=count(*) from products

set @pageCount=ceiling(@recordCount*1.0/@pageSize)

if @pageIndex = 0 or @pageCount<=1

 set @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'");          }        }