Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2255037
  • 博文数量: 292
  • 博客积分: 10016
  • 博客等级: 中将
  • 技术积分: 3381
  • 用 户 组: 普通用户
  • 注册时间: 2005-11-06 11:26
文章分类

全部博文(292)

文章存档

2011年(1)

2010年(4)

2009年(3)

2008年(6)

2007年(47)

2006年(63)

2005年(168)

分类: C/C++

2006-04-08 13:37:32

数据库: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'");

          }

        }

阅读(2126) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~