Finny's Notes

我的檔案總管

結果畫面

SQL Server內容

---------------- Default.aspx.cs -----------------

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    //建立資料庫連線
    SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=myDB;User Id=sa;Password=密碼");
    SqlCommand cmd = null;

    protected void Page_Load(object sender, EventArgs e)
    {
        string sSql = "select * from myProducts";
        conn.Open();
        cmd = new SqlCommand(sSql, conn);
        //執行此SQL查詢
        SqlDataReader dr = cmd.ExecuteReader();
        //指定GV的資料是查到的SQL
        GridView1.DataSource = dr;
        //資料連接
        GridView1.DataBind();

        //release物件與資源
        conn.Close();
       
     
    }
    protected void Button1_Click(object sender, EventArgs e) //查詢
    {
        
        //開啟資料庫
        conn.Open();
        cmd = new SqlCommand("select * from myProducts Where ProductID=@PID", conn);
        //指定變數
        cmd.Parameters.Add("@PID", SqlDbType.NVarChar, 10).Value = TextBox5.Text;
        SqlDataReader dr = cmd.ExecuteReader();
        GridView1.DataSource = dr;
        GridView1.DataBind();

        //release物件與資源
        dr.Dispose();
        cmd.Dispose();
        conn.Close();
        conn.Dispose();


    }
    protected void Button2_Click(object sender, EventArgs e) //新增
    {
        
        try {
            //新增
            string sSql = "Insert Into myProducts (ProductID,ProductName,Quantity,Price)";
            sSql += "Values ('" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "')";
            conn.Open();
            cmd = new SqlCommand(sSql, conn);
            cmd.ExecuteNonQuery();

            //查詢確認新增成功
            string sSql2 = "Select * from myProducts Where ProductID = '" + TextBox1.Text + "' ";
            cmd = new SqlCommand(sSql2, conn);
            SqlDataReader dr = cmd.ExecuteReader();
            GridView1.DataSource = dr;
            GridView1.DataBind();

            Label6.Text = "新增產品成功。";
           
        }
        catch(Exception ex){
            Label6.Text = ex.ToString();
        }
        finally{
            cmd.Dispose();
            conn.Close();
            conn.Dispose();
        }

    }

    protected void Button3_Click(object sender, EventArgs e) //更新
    {
        try {
            //更新
            string sSql = "Update myProducts Set ProductName='" + TextBox7.Text + "',Quantity ='" + TextBox8.Text + "',Price ='" + TextBox9.Text + "' ";
            sSql += " Where ProductID='" + TextBox6.Text + "' ";
            conn.Open();
            cmd = new SqlCommand(sSql, conn);
            cmd.ExecuteNonQuery();

            //查詢確認新增成功
            string sSql2 = "Select * from myProducts Where ProductID = '" + TextBox6.Text + "' ";
            cmd = new SqlCommand(sSql2, conn);
            SqlDataReader dr = cmd.ExecuteReader();
            GridView1.DataSource = dr;
            GridView1.DataBind();

            Label6.Text = "產品更新成功。";
        }
        
        catch(Exception ex){
            Label6.Text = ex.ToString();
        }
        finally{
            cmd.Dispose();
            conn.Close();
            conn.Dispose();
        }
    }

        protected void Button4_Click1(object sender, EventArgs e) //刪除
        {
            string sSql = "Delete From myProducts Where ProductID='" + TextBox10.Text + "' ";
            conn.Open();
            cmd = new SqlCommand(sSql, conn);
            cmd.ExecuteNonQuery();

            //查詢確認新增成功
            string sSql2 = "Select * from myProducts";
            cmd = new SqlCommand(sSql2, conn);
            SqlDataReader dr = cmd.ExecuteReader();
            GridView1.DataSource = dr;
            GridView1.DataBind();

            Label6.Text = "產品刪除成功。";
        }
}

----------------- Default.aspx -------------------

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
    
</head>
<body>
    <form id="form1" runat="server">
    <div class="style">
    
        <asp:Button ID="Button1"  runat="server" OnClick="Button1_Click" Text="Select" />
&nbsp;
        <asp:Label ID="Label5" runat="server" Text="PID:"></asp:Label>
        <asp:TextBox ID="TextBox5" runat="server" Width="62px"></asp:TextBox>
        <br />
        <br />
        <asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="Insert" />
&nbsp;
        <asp:Label ID="Label1" runat="server" Text="PID:"></asp:Label>
        <asp:TextBox ID="TextBox1" runat="server" Width="62px"></asp:TextBox>
&nbsp;<asp:Label ID="Label2" runat="server" Text="PName:"></asp:Label>
        <asp:TextBox ID="TextBox2" runat="server" Width="62px"></asp:TextBox>
&nbsp;<asp:Label ID="Label3" runat="server" Text="PQ:"></asp:Label>
        <asp:TextBox ID="TextBox3" runat="server" Width="62px"></asp:TextBox>
&nbsp;<asp:Label ID="Label4" runat="server" Text="PPrice:"></asp:Label>
        <asp:TextBox ID="TextBox4" runat="server" Width="62px"></asp:TextBox>
    
        <br />
        <br />
        <asp:Button ID="Button3" runat="server" OnClick="Button3_Click" Text="Update" style="height: 21px" />
&nbsp;<asp:Label ID="Label7" runat="server" Text="PID:"></asp:Label>
        <asp:TextBox ID="TextBox6" runat="server" Width="62px"></asp:TextBox>
&nbsp;<asp:Label ID="Label8" runat="server" Text="PName:"></asp:Label>
        <asp:TextBox ID="TextBox7" runat="server" Width="62px"></asp:TextBox>
&nbsp;<asp:Label ID="Label9" runat="server" Text="PQ:"></asp:Label>
        <asp:TextBox ID="TextBox8" runat="server" Width="62px"></asp:TextBox>
&nbsp;<asp:Label ID="Label10" runat="server" Text="PPrice:"></asp:Label>
        <asp:TextBox ID="TextBox9" runat="server" Width="62px"></asp:TextBox>
    
        <br />
        <br />
        <asp:Button ID="Button4" runat="server" OnClick="Button4_Click1" Text="Delete" />
&nbsp;<asp:Label ID="Label11" runat="server" Text="PID:"></asp:Label>
        <asp:TextBox ID="TextBox10" runat="server" Width="62px"></asp:TextBox>
    
        <br />
    
    </div>
        <br />
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
        <br />
        <asp:Label ID="Label6" runat="server"></asp:Label>
    </form>
</body>
</html>