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

数据库: SqlServer 2000, 数据源:系统自带的 Northwind

操作系统: Window XP 中文版

开发平台: .Net Framework 1.1, C# 语言

效果预览:

好了,让我们开始奇异的 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'");

}

}

Published At
Categories with Web编程
Tagged with
comments powered by Disqus