📄 adapter.aspx

📁 asp.net经典案例资料
💻 ASPX
字号:
<%@ Page Debug="true" Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<html>
  <head>
    <title>Modify and Delete Data</title>
    <script language="C#" runat="server" >
    
    // Public variable, DataSet is kept in Session.
    SqlConnection connection;
    SqlCommand command;
    SqlDataAdapter adapter;
    SqlCommandBuilder commandBuilder;
    DataSet dataSet;

    private void DataBind()
    {
      grid1.DataSource = dataSet;
      grid1.DataBind();
    }

    private void PrepareConnection()
    {
        string dataSource = "Data Source=localhost;";
        string security = "user id=sa; password=;";
        string initialCatalog = "initial catalog=pubs;";
        string cnnString = dataSource + security + initialCatalog;
        connection = new SqlConnection(cnnString);

        // Create Data Command
        string strSql = "select au_id, au_lname, au_fname, address, phone from [authors]";
        command = new SqlCommand(strSql, connection);

        // Create Data Adapter and its commands
        adapter = new SqlDataAdapter();
        adapter.SelectCommand = command;
        commandBuilder = new SqlCommandBuilder(adapter);
        adapter.UpdateCommand = commandBuilder.GetUpdateCommand();
        adapter.DeleteCommand = commandBuilder.GetDeleteCommand();
        adapter.InsertCommand = commandBuilder.GetInsertCommand();
    }

    public void Page_Load()
    {
      if(!IsPostBack)
      {
        PrepareConnection();
        dataSet = new DataSet();

        try
        {
          connection.Open();
          adapter.FillSchema(dataSet, SchemaType.Mapped);
          adapter.Fill(dataSet);
        }
        catch(SqlException e)
        {
          Response.Write(e.ToString());
          return;
        } 
        finally
        {
          connection.Close();
        }

        Session["dataSet"] = dataSet;
        DataBind();
      }

      dataSet = (DataSet)Session["dataSet"];
    }

    void OnClick(object sender, EventArgs e)
    {
      string au_id = txtau_id.Text;
      DataRow row = dataSet.Tables[0].Rows.Find(au_id);
      row["au_lname"] = txtau_lname.Text;
      row["au_fname"] = txtau_fname.Text;
      row["phone"] = txtphone.Text;
      row["address"] = txtaddress.Text;

      // Update database
      PrepareConnection();
      try
      {
        connection.Open();
        adapter.Update(dataSet.Tables[0]);
      }
      catch(SqlException ex)
      {
        Response.Write(ex.ToString());
        return;
      }
      finally
      {
        connection.Close();
      }
      dataSet.Tables[0].AcceptChanges();
      Session["dataSet"] = dataSet;

      // Update Display
      table1.Visible=false;
      DataBind();
    }

    void OnItemCommand(Object sender, DataGridCommandEventArgs e)
    {
      if( ((Button)e.CommandSource).CommandName == "Modify")
      {
        table1.Visible = true;
        txtau_id.Text = e.Item.Cells[2].Text;
        txtau_lname.Text = e.Item.Cells[3].Text;
        txtau_fname.Text = e.Item.Cells[4].Text;
        txtaddress.Text = e.Item.Cells[5].Text;
        txtphone.Text = e.Item.Cells[6].Text;
      }
      else if( ((Button)e.CommandSource).CommandName == "Delete")
      {
        // Hidden table1 when Delete button is pressed
        table1.Visible = false;

        string au_id = e.Item.Cells[2].Text;
        DataRow row = dataSet.Tables[0].Rows.Find(au_id);
        row.Delete();
        
        // Update database
        PrepareConnection();
        try
        {
          connection.Open();
          adapter.Update(dataSet.Tables[0]);
        }
        catch(SqlException ex)
        {
          Response.Write(ex.ToString());
          return;
        }
        finally
        {
          connection.Close();
        }
        dataSet.Tables[0].AcceptChanges();
        Session["dataSet"] = dataSet;

        // Update Display
        DataBind();
      }
    }

    </script>
  </head>

  <body>
  <form id="form1" runat="server">
    <h3>Modify and Delete Data</h3>
    <asp:Table id="table1" Visible="false" runat="server">
      <asp:TableRow>
        <asp:TableCell>au_id</asp:TableCell>
        <asp:TableCell>au_lname</asp:TableCell>
        <asp:TableCell>au_fname</asp:TableCell>
        <asp:TableCell>address</asp:TableCell>
        <asp:TableCell>phone</asp:TableCell>
        <asp:TableCell>Action</asp:TableCell>
      </asp:TableRow>
      <asp:TableRow>
        <asp:TableCell>
          <asp:TextBox enabled="false" id="txtau_id" runat="server" width="60pt" />
        </asp:TableCell>
        <asp:TableCell>
          <asp:TextBox id="txtau_lname" runat="server" width="60pt"/>
        </asp:TableCell>
        <asp:TableCell>
          <asp:TextBox id="txtau_fname" runat="server" width="60pt"/>
        </asp:TableCell>
        <asp:TableCell>
          <asp:TextBox id="txtaddress" runat="server" width="100pt"/>
        </asp:TableCell>
        <asp:TableCell>
          <asp:TextBox id="txtphone" runat="server" width="80pt"/>
        </asp:TableCell>
        <asp:TableCell>
          <asp:Button id="Button1" runat="server" Text="完成" OnClick="OnClick"/>
        </asp:TableCell>
      </asp:TableRow>
    </asp:Table>
    <asp:DataGrid id="grid1" runat="server" 
      AutoGenerateColumns="true" OnItemCommand="OnItemCommand">
        <HeaderStyle BackColor="lightblue" Font-Name="Arial" Font-Bold="true" />
        <ItemStyle BackColor="lightyellow"/>
        <Columns>
          <asp:ButtonColumn HeaderText="修改" ButtonType="PushButton" 
            Text="修改" CommandName="Modify" />
          <asp:ButtonColumn HeaderText="删除" ButtonType="PushButton"
            Text="删除" CommandName="Delete" />
        </Columns>
    </asp:DataGrid>
  </form>
  </body>
</html>
⌨️ 快捷键说明

复制代码 Ctrl + C
搜索代码 Ctrl + F
全屏模式 F11
切换主题 Ctrl + Shift + D
显示快捷键 ?
增大字号 Ctrl + =
减小字号 Ctrl + -