ASP.NET对SQLSERVER数据库增改删操作

本文介绍了一个使用ASP.NET实现SQL Server数据库增删改查操作的例子,通过WebForm展示了如何利用C#代码与数据库交互,包括数据显示、分页、编辑及删除等功能。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

WebForm.aspx

<%@ Page language="c#" Codebehind="WebForm1.aspx.cs" AutoEventWireup="false" Inherits="StudyTest.SQLSERVER数据库增改删操作.WebForm1" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
 <HEAD>
  <title>WebForm1</title>
  <meta name="GENERATOR" Content="Microsoft Visual Studio .NET 7.1">
  <meta name="CODE_LANGUAGE" Content="C#">
  <meta name="vs_defaultClientScript" content="JavaScript">
  <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
 </HEAD>
 <body MS_POSITIONING="GridLayout">
  <form id="Form1" method="post" runat="server">
   <FONT face="宋体"></FONT><FONT face="宋体"></FONT>
   <br>
   <table cellSpacing="0" cellPadding="0" align="center" border="0">
    <tr>
     <td>对数据库进行操作(增加,修改,删除)
     </td>
    </tr>
   </table>
   <br>
   <br>
   <table cellSpacing="0" cellPadding="0" align="center" border="0">
    <tr>
     <td>姓名:
     </td>
     <td><asp:textbox id="txt_Name" runat="server"></asp:textbox></td>
    </tr>
    <tr>
     <td>性别:
     </td>
     <td><asp:textbox id="txt_Sex" runat="server"></asp:textbox></td>
    </tr>
    <tr>
     <td>地址:
     </td>
     <td><asp:textbox id="txt_Address" runat="server"></asp:textbox></td>
    </tr>
    <tr>
     <td>生日:
     </td>
     <td><asp:textbox id="txt_Birthday" runat="server"></asp:textbox></td>
    </tr>
    <tr>
     <td>电话:
     </td>
     <td><asp:textbox id="txt_Tel" runat="server"></asp:textbox></td>
    </tr>
    <tr align="center">
     <td></td>
     <td><asp:button id="txt_OK" runat="server" Text="确定"></asp:button>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<asp:button id="txt_Cancel" runat="server" Text="取消"></asp:button>
     </td>
     <td></td>
    </tr>
   </table>
   <br>
   <table cellSpacing="0" cellPadding="0" align="center" border="0">
    <tr>
     <td>
     
     <asp:datagrid id="DataGrid1" runat="server" HorizontalAlign="Center" AlternatingItemStyle-BackColor="#eeeeee"
       HeaderStyle-BackColor="#aaaadd" Font-Size="10pt" Font-Name="Verdana" CellPadding="3" BorderWidth="1px"
       BorderColor="Black" OnPageIndexChanged="MyDataGrid_Page" PagerStyle-HorizontalAlign="Right"
       PagerStyle-Mode="NumericPages" AllowPaging="True" Font-Names="Verdana" AutoGenerateColumns="False">

       <SelectedItemStyle Font-Bold="True" ForeColor="White" BackColor="#008A8C"></SelectedItemStyle>
       <AlternatingItemStyle BackColor="Gainsboro"></AlternatingItemStyle>
       <ItemStyle ForeColor="Black" BackColor="#EEEEEE"></ItemStyle>
       <HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#000084"></HeaderStyle>
       <FooterStyle ForeColor="Black" BackColor="#CCCCCC"></FooterStyle>
       <Columns>
        <asp:TemplateColumn>
         <HeaderTemplate>
          <FONT face="宋体">ID</FONT>
         </HeaderTemplate>
         <ItemTemplate>
          <asp:Label id="Label1" runat="server">
           <%#DataBinder.Eval(Container,"DataItem.ID")%>
          </asp:Label>
         </ItemTemplate>
        </asp:TemplateColumn>
        <asp:TemplateColumn>
         <HeaderTemplate>
          <FONT face="宋体">姓名</FONT>
         </HeaderTemplate>
         <ItemTemplate>
          <asp:Label id="Label2" runat="server">
         
  <%#DataBinder.Eval(Container,"DataItem.Name")%>
          </asp:Label>
         </ItemTemplate>
         <EditItemTemplate>
          <asp:TextBox Runat=server ID="Textbox1" Text='<%#DataBinder.Eval(Container,"DataItem.Name")%>'>
          </asp:TextBox>
         </EditItemTemplate>
        </asp:TemplateColumn>
        <asp:TemplateColumn>
         <HeaderTemplate>
          <FONT face="宋体">性别</FONT>
         </HeaderTemplate>
         <ItemTemplate>
          <asp:Label id="Label3" runat="server">
           <%#DataBinder.Eval(Container,"DataItem.Sex")%>
          </asp:Label>
         </ItemTemplate>
         <EditItemTemplate>
          <asp:TextBox Runat=server ID="Textbox2" Text='<%#DataBinder.Eval(Container,"DataItem.Sex")%>'>
          </asp:TextBox>
         </EditItemTemplate>
        </asp:TemplateColumn>
        <asp:TemplateColumn>
         <HeaderTemplate>
          <FONT face="宋体">地址</FONT>
         </HeaderTemplate>
         <ItemTemplate>
          <asp:Label id="Label4" runat="server">
           <%#DataBinder.Eval(Container,"DataItem.Address")%>
          </asp:Label>
         </ItemTemplate>
         <EditItemTemplate>
          <asp:TextBox Runat=server ID="Textbox3" Text='<%#DataBinder.Eval(Container,"DataItem.Address")%>'>
          </asp:TextBox>
         </EditItemTemplate>
        </asp:TemplateColumn>
        <asp:TemplateColumn>
         <HeaderTemplate>
          <FONT face="宋体">生日</FONT>
         </HeaderTemplate>
         <ItemTemplate>
          <asp:Label id="Label5" runat="server">
           <%#DataBinder.Eval(Container,"DataItem.Birthday")%>
          </asp:Label>
         </ItemTemplate>
         <EditItemTemplate>
          <asp:TextBox Runat=server ID="Textbox4" Text='<%#DataBinder.Eval(Container,"DataItem.Birthday")%>'>
          </asp:TextBox>
         </EditItemTemplate>
        </asp:TemplateColumn>
        <asp:TemplateColumn>
         <HeaderTemplate>
          <FONT face="宋体">电话</FONT>
         </HeaderTemplate>
         <ItemTemplate>
          <asp:Label id="Label6" runat="server">
           <%#DataBinder.Eval(Container,"DataItem.Tel")%>
          </asp:Label>
         </ItemTemplate>
         <EditItemTemplate>
          <asp:TextBox Runat=server ID="Textbox5" Text='<%#DataBinder.Eval(Container,"DataItem.Tel")%>'>
          </asp:TextBox>
         </EditItemTemplate>
        </asp:TemplateColumn>
        <asp:EditCommandColumn ButtonType="LinkButton" UpdateText="更新" HeaderText="编辑" CancelText="取消" EditText="编辑"></asp:EditCommandColumn>
        <asp:ButtonColumn Text="删除" HeaderText="删除" CommandName="Delete"></asp:ButtonColumn>
       </Columns>
       <PagerStyle NextPageText="下一页" PrevPageText="上一页" HorizontalAlign="Center" ForeColor="Black"
        BackColor="#999999"></PagerStyle>
      </asp:datagrid></td>
    </tr>
    <tr align="center">
     <td>
      <asp:label id="lblPageCount" runat="server"></asp:label>&nbsp;
      <asp:label id="lblCurrentIndex" runat="server"></asp:label>&nbsp;
      <asp:linkbutton id="btnFirst" onclick="PagerButtonClick" runat="server" CommandArgument="0">最首页</asp:linkbutton>&nbsp;
      <asp:linkbutton id="btnPrev" onclick="PagerButtonClick" runat="server" CommandArgument="prev">前一页</asp:linkbutton>&nbsp;
      <asp:linkbutton id="btnNext" onclick="PagerButtonClick" runat="server" CommandArgument="next">下一页</asp:linkbutton>&nbsp;
      <asp:linkbutton id="btnLast" onclick="PagerButtonClick" runat="server" CommandArgument="last">最后页</asp:linkbutton></td>
     </TD>
    </tr>
   </table>
  </form>
 </body>
</HTML>

 


WebForm.cs

using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
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 System.Data.SqlClient;

namespace StudyTest.SQLSERVER数据库增改删操作
{
 /// <summary>
 /// WebForm1 的摘要说明。
 /// </summary>
 public class WebForm1 : System.Web.UI.Page
 {
  protected System.Web.UI.WebControls.TextBox txt_Name;
  protected System.Web.UI.WebControls.TextBox txt_Sex;
  protected System.Web.UI.WebControls.TextBox txt_Address;
  protected System.Web.UI.WebControls.TextBox txt_Birthday;
  protected System.Web.UI.WebControls.TextBox txt_Tel;
  protected System.Web.UI.WebControls.Button txt_OK;
  protected System.Web.UI.WebControls.Button txt_Cancel;
  protected System.Web.UI.WebControls.Label lblPageCount;
  protected System.Web.UI.WebControls.Label lblCurrentIndex;
  protected System.Web.UI.WebControls.LinkButton btnFirst;
  protected System.Web.UI.WebControls.LinkButton btnPrev;
  protected System.Web.UI.WebControls.LinkButton btnNext;
  protected System.Web.UI.WebControls.LinkButton btnLast;
  protected System.Web.UI.WebControls.DataGrid DataGrid1;

  /*数据库连接字符串
   * WebConfig.config里面的
   * <appSettings>
   * <add key="SqlConnStr" value="server=.;uid=sa;pwd=;database=Test" />
   * </appSettings>
   * */

  static string  strConn = System.Configuration.ConfigurationSettings.AppSettings["SqlConnStr"];

  private SqlConnection conn ;
  private SqlCommand cmmd ;

  private static DataSet ds = new DataSet();
 
  private void Page_Load(object sender, System.EventArgs e)
  {
   // 在此处放置用户代码以初始化页面
   if(!IsPostBack)
   {
    ShowData();
   }
  }

  #region 显示数据

  private void ShowData()
  {
   string Sql = "select * from Users";
   string SqlCount = "select count(*) from Users";
   ds = new DataSet();

   conn = new SqlConnection(strConn);
   cmmd = new SqlCommand(SqlCount,conn);//获得行数
   conn.Open();
   
   cmmd = new SqlCommand(Sql,conn);//执行查询语句
   SqlDataAdapter dad = new SqlDataAdapter(cmmd);

   dad.Fill(ds);
   this.DataGrid1.DataSource = ds.Tables[0].DefaultView;
   this.DataGrid1.DataKeyField = "ID";
   this.DataGrid1.DataBind();
   conn.Close();
  }
  #endregion

  #region 分页操作

  /*************************************分页操作***************************************************/
  public void PagerButtonClick(object sender, EventArgs e)
  {
   string arg = ((LinkButton)sender).CommandArgument.ToString();
   switch(arg)
   {
    case "next":
     if (DataGrid1.CurrentPageIndex < (DataGrid1.PageCount - 1))
     {
      DataGrid1.CurrentPageIndex += 1;
     }
     break;
    case "prev":
     if (DataGrid1.CurrentPageIndex > 0)
     {
      DataGrid1.CurrentPageIndex -= 1;
     }
     break;
    case "last":
     DataGrid1.CurrentPageIndex = (DataGrid1.PageCount - 1);
     break;
    default:
     DataGrid1.CurrentPageIndex = System.Convert.ToInt32(arg);
     break;
   }
   ShowData();
   ShowStats();
  }

  private void ShowStats()
  {
   lblCurrentIndex.Text = "第 " + (DataGrid1.CurrentPageIndex + 1).ToString() + " 页";
   lblPageCount.Text = "总共 " + DataGrid1.PageCount.ToString() + " 页";
  }

  public void MyDataGrid_Page(object sender, DataGridPageChangedEventArgs e)
  {
   int startIndex ;
   startIndex = DataGrid1.CurrentPageIndex * DataGrid1.PageSize;
   DataGrid1.CurrentPageIndex = e.NewPageIndex;
   ShowData();
   ShowStats();
  }
  #endregion

  #region Web 窗体设计器生成的代码
  override protected void OnInit(EventArgs e)
  {
   //
   // CODEGEN: 该调用是 ASP.NET Web 窗体设计器所必需的。
   //
   InitializeComponent();
   base.OnInit(e);
  }
  
  /// <summary>
  /// 设计器支持所需的方法 - 不要使用代码编辑器修改
  /// 此方法的内容。
  /// </summary>
  private void InitializeComponent()
  {   
   this.txt_OK.Click += new System.EventHandler(this.txt_OK_Click);
   this.DataGrid1.ItemCreated += new System.Web.UI.WebControls.DataGridItemEventHandler(this.DataGrid1_ItemCreated);
   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.Load += new System.EventHandler(this.Page_Load);

  }
  #endregion

  #region 添加按钮
  private void txt_OK_Click(object sender, System.EventArgs e)
  {
   string str_name = this.txt_Name.Text.ToString().Trim();
   string str_sex = this.txt_Sex.Text.ToString().Trim();
   string str_address = this.txt_Address.Text.ToString().Trim();
   string str_birthday = this.txt_Birthday.Text.ToString().Trim();
   string str_tel = this.txt_Tel.Text.ToString().Trim();

   SqlConnection conn = new SqlConnection(strConn);

   string Sql = "insert into Users values('"+str_name+"','"+str_sex+"','"+str_address+"','"+str_birthday+"','"+str_tel+"')";

   SqlCommand cmmd = new SqlCommand(Sql,conn);
   conn.Open();
   cmmd.ExecuteNonQuery();
   conn.Close();
   conn.Dispose();
   ShowData();
  }
  #endregion

  #region 取消
  private void DataGrid1_CancelCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
  {
   this.DataGrid1.EditItemIndex = -1;
   ShowData();
  }
  #endregion

  #region 删除
  private void DataGrid1_DeleteCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
  {
   int int_ID=0;

   int_ID =Convert.ToInt32( this.DataGrid1.DataKeys[e.Item.ItemIndex] );//获得索引得id值

   string Sql = "delete from Users where ID="+int_ID;

   SqlConnection conn = new SqlConnection(strConn);
   SqlCommand cmmd = new SqlCommand(Sql,conn);
   conn.Open();
   cmmd.ExecuteNonQuery();
   conn.Close();

   ShowData();
  }

  

  private void DataGrid1_ItemCreated(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
  {
   if(e.Item.ItemType==ListItemType.Item||e.Item.ItemType==ListItemType.AlternatingItem||e.Item.ItemType==ListItemType.EditItem)
   {
    TableCell myTableCell;
    myTableCell = e.Item.Cells[7];

    LinkButton btnDel = (LinkButton)myTableCell.Controls[0];

    btnDel.Attributes.Add("onclick","return confirm('您确认要删除此条记录吗?');");
    btnDel.Text = "删除";

   }
  }
  #endregion

  #region 编辑

  private void DataGrid1_EditCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
  {
   this.DataGrid1.EditItemIndex = e.Item.ItemIndex;

   ShowData();
  }
  #endregion

  #region 更新

  private void DataGrid1_UpdateCommand(object source, System.Web.UI.WebControls.DataGridCommandEventArgs e)
  {
   int int_ID=0;

   int_ID =Convert.ToInt32( this.DataGrid1.DataKeys[e.Item.ItemIndex] );//获得索引得id值

   TextBox txtName = (TextBox)e.Item.FindControl("Textbox1");
   TextBox txtSex = (TextBox)e.Item.FindControl("Textbox2");
   TextBox txtAddress = (TextBox)e.Item.FindControl("Textbox3");
   TextBox txtBirthday = (TextBox)e.Item.FindControl("Textbox4");
   TextBox txtTel = (TextBox)e.Item.FindControl("Textbox5");

   string Sql = "update Users set Name='"+txtName.Text.ToString()+"',Sex='"+txtSex.Text.ToString()+"',Address='"+txtAddress.Text.ToString()+"',Birthday='"+txtBirthday.Text.ToString()+"',Tel='"+txtTel.Text.ToString()+"' where ID="+int_ID;

   SqlConnection conn = new SqlConnection(strConn);
   SqlCommand cmmd = new SqlCommand(Sql,conn);
   conn.Open();
   cmmd.ExecuteNonQuery();
   conn.Close();

   this.DataGrid1.EditItemIndex = -1;

   ShowData();
  }
  #endregion

  #region DataGrid自带的分页

  private void DataGrid1_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
  {
   DataGrid1.CurrentPageIndex = e.NewPageIndex;
   DataGrid1.DataSource = ds;
   DataGrid1.DataBind();
  }
  #endregion

 

 }
}

评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值