Finny's Notes

有一次看到下面這個圖在介紹Dataset取值的說明，就覺得整個都通了:) 

以下是C#實作程式碼

我的檔案總管

結果畫面

SQL Server內容

---------------- Default2.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 Default2 : System.Web.UI.Page
{
    //建立資料庫連線
    SqlConnection conn = new SqlConnection("Data Source=.;Initial Catalog=myDB;User Id=sa;Password=密碼");
    SqlDataAdapter da = null;
    SqlCommand cmd = null;
    DataSet ds = new DataSet(); //建立DatsSet

    protected void Page_Load(object sender, EventArgs e)
    {
        string sSql = "select * from myProducts";
        conn.Open();
        da = new SqlDataAdapter(sSql, conn); //建SqlDataAdapter
        da.Fill(ds, "Products"); //把資料放入DatsSet

        GridView1.DataSource = ds; //資料放入GridView
        GridView1.DataBind();

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


    }
    protected void Button1_Click(object sender, EventArgs e) //查詢
    {

        string sSql = "select * from myProducts where ProductID = '" + TextBox5.Text + "' ";
        conn.Open();
        da = new SqlDataAdapter(sSql, conn); //建SqlDataAdapter
        da.Fill(ds, "Products"); //把資料放入DatsSet

        GridView1.DataSource = ds;
        GridView1.DataBind();

        //release物件與資源
        ds.Dispose();
        da.Dispose();
        conn.Close();
        conn.Dispose();


    }

//下面沒有使用SqlDataAdapter了

    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 = "產品刪除成功。";
    }
}

----------------- Default2.aspx -------------------

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

<!DOCTYPE html>

    <meta content="text/html; charset=utf-8" http-equiv="Content-Type" />
    <title></title>
    <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" style="height: 21px" Text="Update" />
            &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>