<appSettings>
<add key="ConnectionString" value="server=localhost;database=northwind;uid=sa;pwd=;"></add>
</appSettings>
public static readonly string CONN_STRING=ConfigurationSettings.AppSettings["ConnectionString"];
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<FONT face="宋体">
<TABLE id="Table1" style="WIDTH: 576px; HEIGHT: 304px" cellSpacing="1" cellPadding="1"
width="576" border="0">
<TR>
<TD style="HEIGHT: 207px">
<asp:DataGrid id="DataGrid1" runat="server" AutoGenerateColumns="False" Width="520px">
<Columns>
<asp:TemplateColumn HeaderText="产品序号">
<ItemTemplate>
<asp:Label ID=label3 Runat=server Text='<%# System.Convert.ToInt32(DataBinder.Eval(Container,"ItemIndex"))+1%>' />
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID=Label4 Runat=server Text='<%# System.Convert.ToInt32(DataBinder.Eval(Container,"ItemIndex"))+1%>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="产品ID">
<ItemTemplate>
<asp:Label ID=label5 Runat=server Text='<%# DataBinder.Eval(Container.DataItem,"ProductId")%>' />
</ItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="产品名称">
<ItemTemplate>
<asp:Label ID="Label6" Runat=server Text='<%# DataBinder.Eval(Container.DataItem,"ProductName")%>' />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="Textbox3" Runat=server Text='<%# DataBinder.Eval(Container.DataItem,"ProductName")%> '/>
</EditItemTemplate>
</asp:TemplateColumn>
<asp:TemplateColumn HeaderText="产品单价">
<ItemTemplate>
<asp:Label ID="Label7" Runat=server Text='<%# DataBinder.Eval(Container.DataItem,"UnitPrice")%> '/>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="Textbox4" Runat=server Text='<%# DataBinder.Eval(Container.DataItem,"UnitPrice")%>' />
</EditItemTemplate>
</asp:TemplateColumn>
<asp:EditCommandColumn ButtonType="LinkButton" UpdateText="更新" HeaderText="编辑" CancelText="取消" EditText="编辑"></asp:EditCommandColumn>
<asp:ButtonColumn Text="删除" HeaderText="删除" CommandName="Delete"></asp:ButtonColumn>
</Columns>
</asp:DataGrid></TD>
</TR>
<TR>
<TD>
<webdiyer:AspNetPager id="pager" runat="server" ShowInputBox="Always" ShowCustomInfoSection="Left"></webdiyer:AspNetPager></TD>
</TR>
<TR>
<TD>
<asp:Label id="Label1" runat="server">产品名称</asp:Label>
<asp:TextBox id="TextBox1" runat="server"></asp:TextBox><br>
<asp:Label id="Label2" runat="server">产品单价</asp:Label>
<asp:TextBox id="TextBox2" runat="server"></asp:TextBox><br>
<asp:Button id="Button1" runat="server" Text="Button"></asp:Button>
</TD>
</TR>
</TABLE>
</FONT>
</form>
</body>
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using ms.database;
namespace ms.web
{
/// <summary>
/// product 的摘要说明。
/// </summary>
public class product : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataGrid DataGrid1;
protected System.Web.UI.WebControls.Label Label1;
protected System.Web.UI.WebControls.Label Label2;
protected System.Web.UI.WebControls.TextBox TextBox1;
protected Wuqi.Webdiyer.AspNetPager pager;
protected System.Web.UI.WebControls.TextBox TextBox2;
protected System.Web.UI.WebControls.Button Button1;
protected System.Data.SqlClient.SqlCommand cmd;
private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
if(!IsPostBack)
{
BindData1();
BindData();
}
}
#region Web 窗体设计器生成的代码
override protected void OnInit(EventArgs e)
{
//
// CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}
/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.DataGrid1.CancelCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_CancelCommand);
this.DataGrid1.EditCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_EditCommand);
this.DataGrid1.UpdateCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_UpdateCommand);
this.DataGrid1.DeleteCommand += new System.Web.UI.WebControls.DataGridCommandEventHandler(this.DataGrid1_DeleteCommand);
this.DataGrid1.ItemDataBound += new System.Web.UI.WebControls.DataGridItemEventHandler(this.DataGrid1_ItemDataBound);
this.DataGrid1.SelectedIndexChanged += new System.EventHandler(this.DataGrid1_SelectedIndexChanged);
this.pager.PageChanged += new Wuqi.Webdiyer.PageChangedEventHandler(this.pager_PageChanged);
this.Button1.Click += new System.EventHandler(this.Button1_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
public void BindData1()
{
SqlConnection Conn=new SqlConnection(SQLHelper.CONN_STRING);
cmd=new SqlCommand("strsel",Conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add("@pageindex",1);
cmd.Parameters.Add("@pagesize",1);
cmd.Parameters.Add("@docount",true);
Conn.Open();
pager.RecordCount=(int)cmd.ExecuteScalar();
Conn.Close();
}
public void BindData()
{
SqlConnection Conn=new SqlConnection(SQLHelper.CONN_STRING);
cmd=new SqlCommand("strsel",Conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add("@pageindex",pager.CurrentPageIndex);
cmd.Parameters.Add("@pagesize",pager.PageCount);
cmd.Parameters.Add("@docount",false);
Conn.Open();
DataGrid1.DataSource=cmd.ExecuteReader();
DataGrid1.DataBind();
Conn.Close();
pager.CustomInfoText="记录总数：<font color=\"blue\"><b>"+pager.RecordCount.ToString()+"</b></font>";
pager.CustomInfoText+=" 总页数：<font color=\"blue\"><b>"+pager.PageCount.ToString()+"</b></font>";
pager.CustomInfoText+=" 当前页：<font color=\"red\"><b>"+pager.CurrentPageIndex.ToString()+"</b></font>";
}
private void Button1_Click(object sender, System.EventArgs e)
{
string strinsert="insert into products (productname,unitprice)values('"+TextBox1.Text+"',convert(money,'"+TextBox2.Text+"'))";
SqlConnection Conn=new SqlConnection(SQLHelper.CONN_STRING);
cmd=new SqlCommand(strinsert,Conn);
Conn.Open();
cmd.ExecuteScalar();
Conn.Close();
TextBox1.Text=null;
TextBox2.Text=null;
BindData();
}
private void pager_PageChanged(object src, Wuqi.Webdiyer.PageChangedEventArgs e)
{
pager.CurrentPageIndex=e.NewPageIndex;
BindData();
}
protected void DataGrid1_EditCommand(Object sender,DataGridCommandEventArgs e)
{
DataGrid1.EditItemIndex=e.Item.ItemIndex;
BindData();
}
protected void DataGrid1_CancelCommand(Object sender,DataGridCommandEventArgs e)
{
DataGrid1.EditItemIndex=-1;
BindData();
}
protected void DataGrid1_UpdateCommand(Object sender,System.Web.UI.WebControls.DataGridCommandEventArgs e)
{
int intid=System.Convert.ToInt32(((Label)e.Item.Cells[0].FindControl("Label5")).Text);
string strlm=((TextBox)e.Item.Cells[0].FindControl("TextBox3")).Text;
string intnum=((TextBox)e.Item.Cells[0].FindControl("TextBox4")).Text;
string strupdate="update products set productname='"+strlm+"',unitprice=convert(money,'"+intnum+"') where productid='"+intid+"'";
SqlConnection Conn=new SqlConnection(SQLHelper.CONN_STRING);
cmd=new SqlCommand(strupdate,Conn);
Conn.Open();
cmd.ExecuteNonQuery();
Conn.Close();
DataGrid1.EditItemIndex=-1;
BindData();
}
protected void DataGrid1_DeleteCommand(object sender, DataGridCommandEventArgs e)
{
int intid;
intid=System.Convert.ToInt32(((Label)e.Item.Cells[0].FindControl("Label5")).Text);
string strdel="delete from products where productid='"+intid+"'";
//pad.SQLExecute(strdel);
SqlConnection Conn=new SqlConnection(SQLHelper.CONN_STRING);
cmd=new SqlCommand(strdel,Conn);
Conn.Open();
cmd.ExecuteScalar();
Conn.Close();
DataGrid1.EditItemIndex=-1;
BindData();
}
private void DataGrid1_ItemDataBound(object sender,System.Web.UI.WebControls.DataGridItemEventArgs e)
{
if(e.Item.ItemIndex>=0)
{
e.Item.Cells[5].Attributes["onclick"]="return confirm('你确定要删除吗?');";
e.Item.Attributes["onMouseOver"] = "this.className='SelRow'" ;
e.Item.Attributes["onMouseOut"] = "this.className='Row0'";
}
}
private void DataGrid1_SelectedIndexChanged(object sender, System.EventArgs e)
{
}
}
}
ALTER PROCEDURE dbo.strsel
(@pagesize int,
@pageindex int,
@docount bit)
as
set nocount on
if(@docount=1)
select count(productid) from products
else
begin
declare @indextable table(id int identity(1,1),nid int)
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=(@pageindex-1)*@pagesize
set @PageUpperBound=@PageLowerBound+@pagesize
set rowcount @PageUpperBound
insert into @indextable(nid) select productid from products order by productid desc
select O.productid,O.productname,O.unitprice from products O,@indextable t where O.productid=t.nid
and t.id>@PageLowerBound and t.id<=@PageUpperBound order by t.id
end
set nocount off
家们网！
