Asp.net使用存储过程实现DataGrid相关操作(调试通过)

本代码收集整理而成 已调试通过 ITFLY8
分页存储过程
//@PageSize：分页大小，PageIndex：页号，@PageCount：总页数

，@recordCount：记录数
drop procedure GetCustomDataPage
go
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
.aspx代码

<form id="Form1" method="post" runat="server">
   <FONT face="宋体">
    <asp:DataGrid

id="DataGrid1" style="Z-INDEX: 101; LEFT: 8px; POSITION:

absolute; TOP: 8px" runat="server"
     

AutoGenerateColumns="False" AllowPaging="True"

AllowCustomPaging="True">
     <Columns>
      

<asp:BoundColumn DataField="productID" ReadOnly="True"

HeaderText="productID"></asp:BoundColumn>
      

<asp:BoundColumn DataField="productName"

HeaderText="productName"></asp:BoundColumn>
      

<asp:BoundColumn DataField="unitPrice"

HeaderText="unitPrice"></asp:BoundColumn>
      

<asp:ButtonColumn Text="删除"

CommandName="Delete"></asp:ButtonColumn>
      

<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="

更新" CancelText="取消" EditText="编

辑"></asp:EditCommandColumn>
     </Columns>
     <PagerStyle

Mode="NumericPages"></PagerStyle>
    </asp:DataGrid></FONT>
  </form>

.aspx.cs代码

protected System.Web.UI.WebControls.DataGrid DataGrid1;
  private uint pageCount;  //总页数
  private uint recordCount;  //总记录数

 
  private void Page_Load(object sender,

System.EventArgs e)
  {
   // 在此处放置用户代码以初始化页面
   if(!Page.IsPostBack)
   {
    BindDataGrid();
   }
  }

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)

DataGrid1.PageSize,(uint)DataGrid1.CurrentPageIndex);
   DataGrid1.VirtualItemCount = (int)

recordCount;
   DataGrid1.DataSource = ds;
   DataGrid1.DataBind();
  }

  private void DataGrid1_PageIndexChanged

(object source,

System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
  {//分页事件
   DataGrid1.CurrentPageIndex =

e.NewPageIndex;
   BindDataGrid();
  }

  private void DataGrid1_EditCommand(object

source, System.Web.UI.WebControls.DataGridCommandEventArgs

e)
  {//编辑事件
   DataGrid1.EditItemIndex =

e.Item.ItemIndex;
   BindDataGrid();
  }

  private void DataGrid1_CancelCommand(object

source, System.Web.UI.WebControls.DataGridCommandEventArgs

e)
  {//取消事件
   DataGrid1.EditItemIndex = -1;
   BindDataGrid();
  }

  private void DataGrid1_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 =(e.Item.Cells[0].Text);
   //command.Parameters

["@productID"].Value = DataGrid1.DataKeys

[e.Item.ItemIndex];
   command.ExecuteNonQuery();
   conn.Close();
   DataGrid1.EditItemIndex = -1;
   BindDataGrid();
  }

  private void DataGrid1_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 =e.Item.Cells[0].Text;
   command.ExecuteNonQuery();
   BindDataGrid();
  }

  private void DataGrid1_ItemDataBound(object

sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
  {//实现删除确认及颜色交替显示功能
   if(e.Item.ItemType ==

ListItemType.Item ||e.Item.ItemType ==

ListItemType.AlternatingItem)
   {
    LinkButton btnDelete =

(LinkButton)(e.Item.Cells[3].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'");
   }  
  }