Microsoft Word - ASP2DB1002.doc

主 題 : 以 ASP.Net 撰 寫 連 結 資 料 庫 的 程 式 所 有 的 程 式 撰 寫 中, 只 要 牽 涉 到 較 大 量 的 資 料, 都 很 難 避 免 與 資 料 庫 的 連 線. 例 如 : - 人 事 系 統 中 必 須 與 人 事 資 料 庫 連 線 以 取 得 相 關 資 訊 - tip 系 統 中 必 須 與 學 籍 資 料 庫 連 線, 以 取 得 相 關 學 生 資 訊 可 以 發 現, 不 論 是 使 用 vb 之 類 的 工 具 所 開 發 之 專 屬 應 用 軟 體, 或 者 是 使 用 asp 之 類 所 開 發 之 web 系 統, 都 會 有 連 線 資 料 庫 的 需 求. 本 課 程 的 主 要 目 的 在 討 論 ASP.Net 與 資 料 庫 連 線 這 類 的 程 式 撰 寫 兩 層 或 三 層 架 構 下 的 資 料 庫 連 線 程 式 工 具 Visual.Net (e.g., VB, ASP, VC.Net ) Java php Delphi Flash 各 種 程 式 工 具 幾 乎 都 可 以 資 料 庫 系 統 SQL Server Oracle Sybase DB2 MS Access MS Excel 各 種 資 料 庫 幾 乎 都 可 以 * 為 了 簡 化 起 見, 本 課 程 設 定 在 (1). ASP.Net + VB + SQL Server (2). ASP.Net + VB + MS Access 其 實 各 家 程 式 設 計 上 連 接 資 料 庫 的 作 法 大 同 小 異, 因 為 都 受 windows 的 環 境 所 控 制. 如 果 有 機 會 在 linux 或 unix 上 撰 寫 程 式, 就 會 有 比 較 大 的 差 異 分 類 : 根 據 資 料 庫 本 身 的 特 性, 在 ASP.Net 上 連 接 資 料 庫 有 兩 種 主 要 的 差 異 1. 如 果 資 料 庫 本 身 有 伺 服 器 機 制, 例 如 SQL Server 等 ( 大 部 分 較 大 型 的 資 料 庫 都 有 ), 則 使 用.Net Data Provider 連 線 2. 如 果 資 料 庫 本 身 沒 有 伺 服 器 機 制, 例 如 MS Access 等, 則 使 用 OLE DB.Net Data Provider 連 線 除 了 以 資 料 庫 本 身 的 特 性 分 類 之 外, 還 可 以 根 據 程 式 開 發 時 使 用 的 方 法 來 分 類 德 明 財 經 科 技 大 學 資 訊 科 技 系 1

1. Connection in Codes: 寫 程 式 完 成 資 料 來 源 設 定. 自 行 撰 寫 需 要 的 程 式 碼 完 成 與 資 料 庫 的 連 線. 基 本 上 需 要 寫 的 程 式 碼 不 多, 但 是 背 起 來 卻 也 不 容 易, 看 似 比 較 困 難, 其 實 這 部 份 的 學 習 可 以 確 實 理 解 資 料 庫 程 式 設 計 的 細 節. 初 學 者 應 該 要 由 此 方 法 開 始 學 習, 才 能 夠 打 好 基 礎 ; 當 撰 寫 過 類 似 工 作 而 程 式 碼 留 著, 後 續 需 要 開 發 新 功 能 時 只 需 要 稍 加 修 改 即 可. 2. Connection in Forms: 直 接 在 form 上 面 新 增 資 料 來 源 當 熟 悉 整 個 資 料 庫 程 式 設 計 的 開 發 後, 掌 握 整 個 程 式 設 計 的 內 涵, 可 以 透 過 系 統 的 GUI 簡 單 操 作, 完 成 資 料 庫 連 線 的 設 計. 但 是 每 次 開 發 程 式 時 都 必 須 重 新 定 義, 比 較 麻 煩. 本 方 法 較 建 議 有 資 料 庫 程 式 設 計 經 驗 的 人 使 用. 最 好 的 方 法 應 該 是 : 寫 成 功 一 次 Connection in Codes 的 作 法, 以 後 需 要 時 直 接 複 製 過 去 修 改 就 好, 簡 單 又 有 效 課 程 內 容 由 於 Connection in Forms 的 作 法 相 對 簡 單 很 多, 同 學 如 果 需 要 可 以 自 行 嘗 試, 市 面 上 也 有 許 多 相 關 的 書 籍 說 明 本 課 程 將 以 Connection in Codes 為 主 要 的 目 標, 希 望 能 建 構 同 學 們 的 資 料 庫 程 式 設 計 知 識 未 來 在 就 業 時, 能 夠 以 上 課 學 過 的 程 式 碼 為 基 礎, 修 改 成 自 己 工 作 上 所 需 要 的 程 式 我 們 的 課 程 內 容 將 包 含 ASP.Net 程 式 中 的 資 料 庫 連 線 Select 的 使 用 : 如 何 由 資 料 庫 中 讀 取 資 料 資 料 的 異 動 : 如 何 在 ASP.Net 程 式 中 對 資 料 表 格 新 增 修 改 刪 除 資 料 程 式 設 計 練 習 : 實 際 開 發 一 個 整 合 的 資 料 庫 應 用 程 式 簡 單 來 說, 前 三 個 部 份 是 資 料 庫 程 式 設 計 的 基 本 學 習, 最 後 再 佐 以 一 個 實 際 的 程 式 練 習, 讓 同 學 可 以 完 整 的 學 習 到 此 重 要 技 能 本 課 程 除 了 讓 同 學 可 以 直 接 學 習 資 料 庫 程 式 設 計 的 技 能 外, 同 時 也 是 重 新 訓 練 程 式 設 計 的 邏 輯 概 念 最 好 的 機 會 除 了 這 門 課 之 外, 很 難 再 有 機 會 在 課 堂 上 有 人 詳 細 介 紹 程 式 的 設 計 概 念 Q: 如 果 不 會 ASP.Net 怎 麼 辦 A: 請 回 去 把 ASP.Net 的 書 拿 出 來 稍 微 練 習 一 下, 不 要 連 介 面 操 作 或 form 的 設 計 都 不 會, 剩 下 的 如 程 式 的 撰 寫 等, 可 以 跟 著 我 們 的 練 習 重 新 建 立 知 識 Q: 不 會 SQL 指 令 怎 麼 辦 A: 請 將 上 學 期 的 SQL 指 令 重 新 復 習, 尤 其 是 select 的 部 份 一 個 好 的 資 料 庫 應 用 程 式 除 了 要 會 寫 ASP.Net 程 式 之 外,SQL 指 令 的 撰 寫 也 是 必 要 的 技 能 德 明 財 經 科 技 大 學 資 訊 科 技 系 2

主 題 :.NET Framework 的 基 本 架 構 及 操 作 原 理.NET Framework 的 基 本 架 構 可 分 為 下 列 三 大 部 分 所 組 成 : 共 同 語 言 規 範 (Common Language Specification,CLS) NET Framework 類 別 庫 共 同 語 言 執 行 環 境 (Common Language Runtime,CLR) 簡 單 來 說, 我 們 在.NET Framework 下 可 以 挑 選 自 己 順 手 的 程 式 語 言, 透 過 CLS 與 CLR 而 能 夠 建 立 一 個 網 站 的 程 式 系 統 在 本 課 程 中, 我 們 使 用 Visual Basic 的 程 式 語 言, 透 過.NET Framework 的 架 構, 建 立 網 站 的 程 式 ; 而 程 式 的 介 紹 重 點 在 連 接 資 料 庫 SQL Server, 學 習 如 何 在 網 站 程 式 中, 對 資 料 庫 系 統 的 連 結 新 增 修 改 刪 除 查 詢 等 工 作 同 時 藉 由 這 樣 課 程 的 練 習, 可 以 讓 自 己 的 程 式 能 力 更 進 步 德 明 財 經 科 技 大 學 資 訊 科 技 系 3

主 題 : 建 立 ASP.NET Web 應 用 程 式 網 路 上 或 者 市 面 上 書 籍 中, 有 大 量 的 ASP.Net 或 者 是.NET Framework 的 相 關 資 料, 請 同 學 自 行 尋 找.NET Framework 開 發 人 員 手 冊 : 建 立 ASP.NET Web 應 用 程 式 http://technet.microsoft.com/zh-tw/query/aa719794 當 啟 動 Visual Studio 2008 之 後, 新 增 一 個 專 案 會 出 現 如 下 圖 的 對 話 框 我 們 將 以 Visual Basic 為 程 式 語 言, 所 以 請 如 下 圖 挑 選 Web 內 的 ASP.Net Web 應 用 程 式 建 立 一 個 空 白 專 案 後, 請 看 一 下 視 窗 右 邊 的 方 案 總 管, 熟 悉 一 下 整 個 專 案 的 組 成 主 要 的 設 計 對 象 是 form, 如 VB 一 樣 可 以 直 接 在 form 上 面 設 計 UI 啟 動 form 後 可 以 在 設 計 / 原 始 檔 兩 個 模 式 下 切 換, 而 程 式 的 撰 寫 就 如 同 VB 的 語 法, 再 加 上 一 些 網 頁 設 計 的 思 考 由 於 本 課 程 主 軸 是 程 式 連 接 資 料 庫 的 應 用, 所 以 我 們 的 範 例 將 不 會 針 對 網 頁 或 美 工 的 部 份 德 明 財 經 科 技 大 學 資 訊 科 技 系 4

主 題 : 資 料 庫 應 用 程 式 的 功 能 分 類 一 個 資 料 庫 應 用 程 式 的 組 成 功 能 可 能 非 常 複 雜, 例 如 TIP 上 的 功 能 非 常 的 多 樣, 這 樣 我 們 在 學 習 時, 該 如 何 著 手 寫 資 料 庫 的 應 用 程 式 呢? 初 學 者 只 能 看 到 問 題 的 表 面, 因 此 容 易 被 整 個 系 統 所 包 含 的 五 花 八 門 功 能 所 迷 惑 ; 如 果 我 們 從 資 料 庫 運 作 的 角 度 來 看, 一 個 外 部 程 式 ( 例 如 VB) 對 於 資 料 庫 伺 服 器 所 需 要 的 工 作 主 要 可 以 分 成 下 列 幾 項 1. 連 線, database connection. 先 建 立 好 跟 資 料 庫 伺 服 器 的 連 線 後, 其 他 的 工 作 才 能 繼 續, 類 似 網 路 連 線 建 立 的 概 念 2. 斷 線, database disconnection. 當 所 有 工 作 都 做 完 後, 記 得 要 把 連 線 切 斷, 才 不 會 浪 費 資 料 庫 伺 服 器 的 資 源 3. 新 增 資 料, 對 應 的 SQL 指 令 為 insert 4. 修 改 資 料, 對 應 的 SQL 指 令 為 update 5. 刪 除 資 料, 對 應 的 SQL 指 令 為 delete 6. 查 詢, 對 應 的 SQL 指 令 為 select 7. 執 行 預 儲 程 序 (stored procedure), 呼 叫 資 料 庫 伺 服 器 內 的 程 序 ( 省 略 ) 8. 其 他 資 料 庫 的 維 護 等, 例 如 建 立 資 料 庫, 建 立 資 料 表 格 等 ( 省 略 ) 資 料 庫 連 線 與 斷 線, 分 別 在 一 開 始 與 結 束 時 執 行, 所 以 重 頭 戲 會 落 在 中 間 3-6 點, 這 也 是 我 們 課 程 的 主 要 著 墨 之 處. 3-6 點 的 工 作 分 別 有 對 應 到 SQL 指 令, 我 們 要 介 紹 的 是 在 ASP.Net 中 如 何 完 成 這 些 SQL 指 令. 不 論 多 複 雜 的 系 統, 在 資 料 庫 的 操 作 設 計 上 大 致 上 就 是 由 這 六 點 所 排 列 組 成 而 成, 本 課 程 的 目 的 就 在 於 讓 同 學 學 會 這 基 本 的 六 個 工 作, 進 而 能 順 利 的 在.Net 上 開 發 出 資 料 庫 應 用 程 式. 我 們 先 以 TIP 的 功 能, 認 識 這 些 資 料 庫 應 用 系 統 的 程 式 設 計 目 標 基 本 程 式 流 程 1. database connection: 設 定 資 料 庫 連 線 2. 準 備 好 sql 指 令 3. 送 至 database server 上 執 行 sql 指 令 4. 接 收 資 料 庫 傳 回 的 資 料, 予 以 適 當 處 理 5. 重 複 step 2-4, 直 到 該 做 的 事 情 做 完 6. database disconnection 初 學 階 段, 建 議 先 把 想 做 的 事 情 依 照 邏 輯 條 列, 在 撰 寫 程 式 時, 都 想 想 程 式 的 哪 個 部 份 對 應 到 哪 個 步 驟 當 整 個 邏 輯 步 驟 構 思 好, 實 際 指 令 怎 麼 寫 出 來 是 下 一 個 問 題, 指 令 的 正 確 包 含 兩 個 層 次 (1). 指 令 撰 寫 格 式 的 正 確 (2). 指 令 安 排 順 序 的 正 確 德 明 財 經 科 技 大 學 資 訊 科 技 系 5

Part I: 程 式 完 全 控 制 資 料 庫 連 線 (Connection in Codes) 我 們 的 介 紹 就 以 登 錄 帳 號 密 碼 程 式 為 範 例, 看 看 一 個 資 料 庫 應 用 程 式 的 長 相 準 備 : 安 裝 上 學 期 的 資 料 庫, 並 增 加 一 個 table UserTable(UserID, Pwd), 包 含 UserID 跟 Pwd 兩 個 文 字 型 態 的 欄 位, 並 且 自 行 建 立 幾 筆 使 用 者 的 帳 號 密 碼 資 料 當 成 測 試 資 料 畫 面 設 計 : 準 備 : 增 加 一 個 SQL Server 的 使 用 者, 請 在 SQL Server Management Studio 操 作 如 左 圖, 增 加 一 個 登 入 帳 號 後, 輸 入 帳 號 名 稱, 密 碼, 資 料 庫, 以 及 在 使 用 者 對 應 內 勾 選 資 料 庫 並 設 定 為 owner Q: 在 SQL Server 內 增 加 的 帳 號, 與 UserTable 這 個 資 料 表 個 內 的 資 料 有 什 麼 不 同? 程 式 : 你 已 經 知 道 要 做 的 工 作, 現 在 由 資 料 來 源 與 連 線 開 始 至 取 出 資 料 為 止, 都 以 完 全 程 式 控 制 的 方 式 完 成. 程 式 可 分 成 : (1) 連 接 資 料 庫 (2) 準 備 SQL 指 令 (3) 執 行 SQL (4) 取 出 資 料 並 做 你 的 工 作 (5) 斷 線 並 且 你 得 自 行 對 應 到 你 想 做 的 工 作 ( 也 就 是 登 入 帳 號 密 碼 這 件 事 ) Connection String 我 們 是 假 設 以 伺 服 器 上 建 立 新 的 帳 號, 這 才 是 正 常 工 作 狀 態 SqlConn = New SqlConnection("user id=abc;password=abc123;initial catalog= 銷 售 管 理 ;data source=(local);connect Timeout=10") 如 果 同 學 在 伺 服 器 上 建 立 帳 號 會 有 困 難, 也 可 以 改 成 windows 驗 證 的 帳 號 模 式, 這 樣 就 可 以 省 去 建 帳 號 的 工 作, 避 免 帳 號 的 困 擾 SqlConn = New SqlConnection("Data Source=(local);Initial Catalog= 銷 售 管 理 ;Integrated Security=True") 但 是 在 資 料 庫 伺 服 器 上 建 立 帳 號 這 是 必 須 學 習 的 能 力, 請 同 學 以 後 可 以 找 時 間 自 行 練 習 德 明 財 經 科 技 大 學 資 訊 科 技 系 6

' 注 意 : 不 要 只 會 抄 程 式, 要 依 照 你 的 環 境 等 適 當 的 修 改 程 式 中 的 資 訊, 例 如?? ' 程 式 執 行 成 功 後, 請 從 頭 想 一 遍 各 程 式 碼 的 功 能, 以 及 彼 此 順 序 的 安 排, 才 能 夠 真 正 理 解 程 式 的 撰 寫 Dim SqlConn As SqlConnection Dim SqlCmd As SqlCommand Dim Reader As SqlDataReader Dim sql As String Dim pwd1, pwd2 As String ' (1) 連 接 資 料 庫 ' 注 意 SQL Server 要 設 定 帳 號 密 碼 sa/sa123, 如 果 使 用 本 機 帳 號 請 參 考 先 前 的 範 例 SqlConn = New SqlConnection("user id=abc;password=abc123;initial catalog= 銷 售 管 理 ;data source=(local);connect Timeout=10") SqlConn.Open() ' (2) 準 備 SQL 指 令, 這 裡 得 很 小 心, 注 意 你 的 字 串 是 否 正 確 sql = "select * from UserTable where UserID = '" & TextBox1.Text & "'" ' (3) 執 行 SQL SqlCmd = New SqlCommand(sql, SqlConn) Reader = SqlCmd.ExecuteReader() Reader.Read() ' (4) 取 出 資 料 並 做 你 的 工 作 ' 從 這 裡 開 始 是 抓 出 一 個 資 料 的 寫 法 If Reader.HasRows() = True Then pwd1 = TextBox2.Text pwd2 = Trim(Reader.Item("Pwd")) If pwd1 = pwd2 Then Label1.Text = "Login Successful" Else Label1.Text = "Login Failed" End If End If ' (5) 斷 線 Reader.Close() SqlConn.Close() ' 這 是 連 access 資 料 庫 的 固 定 寫 法, 請 注 意 寫 的 內 容 與 順 序 Sub UserLogin() Dim SqlConn As OleDbConnection Dim SqlCmd As OleDbCommand Dim Reader As OleDbDataReader Dim Sql As String SqlConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Source=D:\pluto\course\ 講 義 \db\samples\dbc.mdb") SqlConn.Open Sql = ("select * from UserTable where UserID = '" & UID.Text & "'") SqlCmd = New OleDbCommand(Sql, SqlConn) Reader = SqlCmd.ExecuteReader() ' 從 這 裡 開 始 是 抓 出 一 個 資 料 的 寫 法 Reader.Read() ' 以 下 同 SQL Server 的 版 本 ** 有 錯? 注 意 你 有 沒 有 imports ' import 在 程 式 最 前 面 SQL Server: Imports System Imports System.Data Imports System.Data.SqlClient Access: Imports System Imports System.Data Imports System.Data.OleDb 德 明 財 經 科 技 大 學 資 訊 科 技 系 7

主 題 : 產 生 功 能 畫 面 說 明 : 為 了 後 續 程 式 的 練 習, 我 們 設 計 一 個 主 畫 面, 當 使 用 者 登 入 後 顯 示 主 畫 面, 主 畫 面 內 包 含 多 個 按 鈕, 每 個 按 鈕 都 對 應 到 一 個 後 面 要 開 發 的 功 能 專 案 中 新 加 入 一 個 form, 取 名 為 MainForm 在 form 內 設 計 好 該 有 的 文 字 按 鈕, 先 弄 六 個 按 鈕 功 能 在 前 一 個 form 的 login 按 鈕 內, 判 斷 登 入 成 功 後 的 指 令 中 加 入 Server.Transfer("MainForm.aspx") 在 顯 示 單 筆 資 料 的 按 鈕 上 用 滑 鼠 點 兩 下, 出 現 的 函 數 中 輸 入 Server.Transfer("WebForm1.aspx") 這 裡 我 們 假 設 要 設 計 顯 示 單 筆 資 料 功 能 的 form 名 稱 為 WebForm1 德 明 財 經 科 技 大 學 資 訊 科 技 系 8

主 題 : 顯 示 單 筆 資 料 說 明 : 設 計 一 個 顯 示 單 筆 資 料 的 簡 單 功 能, 畫 面 如 下 圖 當 使 用 者 輸 入 產 品 編 號 並 按 下 查 詢, 每 個 欄 位 即 自 動 顯 示 這 筆 資 料 的 相 對 應 資 訊 在 開 始 撰 寫 程 式 前, 請 練 習 思 考 此 程 式 該 做 的 事 情, 而 不 是 思 考 該 用 哪 些 指 令 程 式 思 考 : 這 個 程 式 包 含 三 個 工 作 部 份 1. 連 接 資 料 庫 : 使 用 SqlConnection 2. 讀 取 產 品 編 號 欄 位 內 的 值 3. 當 使 用 者 按 下 查 詢 按 鈕, 程 式 必 須 連 到 資 料 庫 把 此 筆 資 料 的 各 欄 位 值 取 出 Q: 請 問 此 三 個 工 作 所 對 應 的 程 式, 分 別 要 寫 在 哪 個 地 方? Hint: 請 參 考 前 面 Login 的 範 例, 使 用 的 程 式 技 巧 基 本 上 是 一 樣 的 Hint: 寫 程 式 出 錯 不 知 道 怎 麼 辦? 不 要 忘 記 debug 工 具, 例 如 中 斷 點 s 德 明 財 經 科 技 大 學 資 訊 科 技 系 9

主 題 : 多 筆 資 料 讀 取 說 明 : 前 面 的 範 例 中, 讀 取 的 資 料 只 有 單 筆, 但 是 大 部 分 的 應 用 都 是 多 筆 資 料 的 讀 取. 現 在 我 們 設 計 一 個 範 例, 可 以 讀 取 table s 內 所 有 的 資 料 並 顯 示 在 畫 面 上 元 件 : 在 工 具 箱 內 資 料 區 的 GridView, 如 下 圖 放 上 GridView 後 就 不 必 其 他 設 定 不 是 只 有 GridView 可 以 顯 示 多 筆 資 料, 如 前 面 單 筆 資 料 畫 面 中, 如 果 搭 配 上 一 筆 / 下 一 筆 的 功 能, 或 者 更 進 一 步 的 查 詢 功 能, 同 樣 是 很 常 見 的 資 料 顯 示 方 式 程 式 : 延 續 前 面 資 料 連 線 與 基 本 select 的 動 作, 當 Reader = SqlCmd.ExecuteReader() 執 行 後 取 得 多 筆 資 料, 只 需 要 在 程 式 中 將 此 GridView 的 資 料 來 源 設 定 為 Reader 就 可 以, 程 式 碼 如 下 '(3). 與 GridView 連 結 GridView1.DataSource = Reader ' 設 定 GridView 的 資 料 來 源 GridView1.DataBind() ' 將 GridView 內 容 與 資 料 來 源 結 合 請 依 照 上 面 的 說 明, 自 行 嘗 試 看 看 是 否 可 以 寫 出 完 整 的 程 式 PS: 當 我 們 想 撰 寫 一 個 功 能 但 是 沒 學 過, 在 網 路 上 找 到 的 大 多 是 類 似 簡 略 的 說 明, 或 者 是 範 例 中 部 分 與 想 做 的 功 能 相 似, 很 少 有 完 整 範 例 複 製 後 剛 好 能 直 接 用, 所 以 請 練 習 依 照 部 分 的 線 索 而 寫 出 完 整 程 式 的 能 力 PS: GridView 的 使 用 可 以 很 複 雜, 有 興 趣 的 同 學, 請 自 行 參 考 相 關 書 籍 或 網 站 上 的 資 料 Q: 有 沒 有 少 出 現 一 筆 資 料? 如 果 少 一 筆, 拿 掉 Reader.Read() 這 個 指 令 Q: 當 功 能 完 成 後, 你 覺 得 這 個 功 能 執 行 起 來 有 沒 有 什 麼 需 要 修 改 或 加 強 之 處? 德 明 財 經 科 技 大 學 資 訊 科 技 系 10

主 題 : 合 併 運 算 資 料 顯 示 說 明 : 合 併 運 算 (join) 是 select 中 重 要 的 技 術, 可 以 將 資 料 由 兩 個 以 上 的 資 料 表 格 中 取 出 並 整 合 學 會 合 併 運 算 是 SQL 指 令 的 重 要 技 巧, 請 自 行 複 習 上 學 期 SQL 指 令 關 於 合 併 運 算 的 部 份 現 在 我 們 依 據 上 一 頁 多 筆 資 料 顯 示 的 程 式 功 能 加 以 修 改... 前 面 用 GridView 顯 示 資 料 時, 很 多 代 碼 的 欄 位 看 不 出 資 料 的 本 質, 例 如 供 應 商 編 號 對 使 用 者 很 不 直 覺, 最 好 是 顯 示 供 應 商 名 稱 ; 同 樣 的 類 別 編 號 也 應 該 由 類 別 名 稱 取 代 比 較 好 另 一 個 問 題 是 資 料 全 部 顯 示, 比 較 常 見 的 是 根 據 某 個 條 件 而 查 詢, 在 下 面 的 例 子 中, 我 們 以 類 別 編 號 為 查 詢 條 件 功 能 : 如 下 圖 ( 設 計 畫 面 與 執 行 結 果 ), 讓 使 用 者 輸 入 類 別 編 號, 顯 示 對 應 的 資 料 Hint: 先 想 對 應 的 SQL 指 令 怎 麼 寫, 在 SQL Server 環 境 下 能 寫 出 正 確 的 SQL 指 令 後, 把 這 個 SQL 指 令 移 植 到 對 應 的 ASP.Net 指 令 位 置 德 明 財 經 科 技 大 學 資 訊 科 技 系 11

主 題 : 動 態 的 資 料 查 詢 說 明 : 為 了 避 免 使 用 者 在 輸 入 查 詢 條 件 時, 會 打 錯 字 或 不 知 如 何 輸 入, 一 種 做 法 是 顯 示 可 以 選 擇 的 資 料 如 下 圖 在 畫 面 啟 動 時, 系 統 自 動 載 入 產 品 名 稱 的 資 料, 使 用 者 以 挑 選 而 非 自 行 輸 入 條 件, 可 避 免 許 多 的 錯 誤 設 計 畫 面 執 行 時 自 動 載 入 下 拉 式 選 單 內 容 當 使 用 者 選 擇 條 件 並 按 下 查 詢 可 顯 示 詳 細 資 料 在 開 始 撰 寫 程 式 前, 請 練 習 思 考 此 程 式 該 做 的 事 情, 而 不 是 思 考 該 用 哪 些 指 令 程 式 思 考 : 這 個 程 式 包 含 三 個 工 作 部 份 1. 連 接 資 料 庫 : 使 用 SqlConnection 2. 讀 取 產 品 名 稱 欄 位 內 的 值 3. 當 使 用 者 挑 選 某 個 產 品 名 稱, 程 式 必 須 連 到 資 料 庫 把 此 筆 資 料 的 各 欄 位 值 取 出 Q: 請 問 此 三 個 工 作 所 對 應 的 程 式, 分 別 要 寫 在 哪 個 地 方? 程 式 設 計 : 元 件 : 下 拉 式 選 單 提 供 使 用 者 挑 選 產 品 名 稱 : DropDownList 動 態 顯 示 欄 位 資 訊 : TextBox 查 詢 按 鈕 程 式 : 請 思 考 上 述 三 個 工 作 個 別 該 放 在 程 式 的 哪 個 地 方, 個 別 該 怎 麼 完 成?? 1. 連 接 資 料 庫 : 使 用 SqlConnection 讀 取 產 品 名 稱 欄 位 內 的 值 畫 面 出 現 時 必 須 執 行 -> 類 似 VB 的 form_load?? 2. 當 使 用 者 挑 選 某 個 產 品 名 稱, 程 式 必 須 連 到 資 料 庫 把 此 筆 資 料 的 各 欄 位 值 取 出 使 用 者 按 下 查 詢 按 鈕 後 執 行, 所 以 放 在 click 事 件 撰 寫 程 式 時, 可 以 個 別 完 成 的 功 能, 請 各 別 先 debug, 這 樣 會 比 較 輕 鬆 例 如 step 2 會 先 做, 那 就 得 如 上 圖 中 的 結 果 成 功, 才 接 著 撰 寫 step 3 的 程 式 德 明 財 經 科 技 大 學 資 訊 科 技 系 12

Step 2 Step 3 Step 2 說 明 DropDownList 跟 GridView 類 似, 都 得 設 定 資 料 來 源 以 便 資 料 整 個 灌 進 來, 不 同 的 是 必 須 額 外 設 定 DataTextField( 畫 面 顯 示 的 資 料 ) 與 DataValeField( 實 際 內 含 的 資 料 ) 屬 性, 決 定 對 應 SQL 指 令 過 來 的 資 料 欄 位 Q: DropDownList 的 資 料 是 中 文, 顯 示 時 如 果 希 望 照 產 品 編 號 排 序, 該 怎 麼 修 正? 也 可 以 不 用 DataSource 的 設 定, 而 是 Reader 內 一 筆 筆 的 資 料 慢 慢 填 入 DropDownList 上, 請 自 行 查 資 料 練 習 Step 3 說 明 SQL 指 令 就 如 同 p11 合 併 運 算 的 指 令, 請 注 意 條 件 不 同 所 以 最 後 的 結 尾 也 不 同 Reader 取 出 的 結 果 要 放 入 TextBox 內, 與 p9 的 練 習 一 樣 當 程 式 ok, 執 行 時 有 出 現 什 麼 錯 誤? 真 的 能 夠 依 照 產 品 名 稱 挑 選 而 查 詢 資 料 嗎? 怎 麼 改? Hint: 可 以 將 step 2 程 式 改 放 至 DropDownList 的 Init 事 件 內 請 練 習 自 行 找 到 適 當 的 屬 性 函 數 事 件, 這 是 重 要 的 技 能 如 果 有 兩 筆 以 上 相 同 名 稱 的 產 品 資 料, 這 個 程 式 能 秀 出 一 筆 或 是 多 筆 資 料? 怎 麼 改? 這 個 問 題 比 較 困 難, 我 們 留 待 下 個 主 題 一 起 學 習 德 明 財 經 科 技 大 學 資 訊 科 技 系 13

主 題 : 資 料 搜 尋 - 複 雜 的 查 詢 條 件 說 明 : 在 大 部 分 的 資 料 庫 應 用 系 統 中, 我 們 並 無 法 事 前 確 知 使 用 者 想 要 的 資 訊, 或 者 說 我 們 希 望 讓 使 用 者 可 以 更 有 彈 性 的 輸 入 查 詢 條 件, 例 如 TIP 在 查 詢 班 級 課 程 或 個 人 課 程 的 資 料 是 放 在 同 一 個 功 能 內, 而 不 是 寫 成 固 定 的 單 一 功 能. 在 這 種 情 形 下, 我 們 必 須 設 計 有 彈 性 的 查 詢 輸 入 方 式. 程 式 功 能 : 提 供 使 用 者 查 詢 產 品 資 訊, 輸 入 條 件 是 : 類 別 名 稱 與 供 應 商 名 稱 兩 項, 使 用 者 可 輸 入 部 分 條 件, 或 者 是 輸 入 空 白, 不 同 的 輸 入 條 件 有 不 同 的 查 詢 結 果. 果 汁 類 產 品 果 汁 類 and 權 勝 供 應 商 思 考 : 請 對 照 先 前 的 程 式, 你 覺 得 哪 邊 必 須 是 動 態 的?? sql = "select A. 產 品 編 號, C. 類 別 名 稱, B. 供 應 商 名 稱, A. 產 品 名 稱, " sql = sql + "A. 建 議 單 價, A. 平 均 成 本, A. 庫 存 量, A. 安 全 存 量 " sql = sql + "from 產 品 資 料 as A, 供 應 商 as B, 產 品 類 別 as C " sql = sql + "where A. 供 應 商 編 號 = B. 供 應 商 編 號 " sql = sql + "and A. 類 別 編 號 = C. 類 別 編 號 " ' an extra space ' 上 面 是 固 定 的 部 份, 下 面 是 變 動 的 部 份 If DropDownList1.Text <> "" Then sql = sql + "and C. 類 別 編 號 = '" + DropDownList1.SelectedValue + "' " End If If DropDownList2.Text <> "" Then sql = sql + "and B. 供 應 商 編 號 = '" + DropDownList2.SelectedValue + "' " End If 做 法 很 簡 單, 首 先 你 想 想 最 終 要 使 用 的 sql 字 串 會 長 什 麼 樣 子, 哪 邊 是 變 動 的 哪 邊 是 固 定 的, 固 定 的 就 放 在 前 面, 變 動 的 放 在 if 內 控 制. 德 明 財 經 科 技 大 學 資 訊 科 技 系 14

思 考 : 還 有 呢? 上 一 筆 / 下 一 筆 的 控 制, 代 表 要 將 查 詢 的 結 果 跨 函 數 控 制. 可 以 直 接 將 SqlDataReader 變 數 放 在 global, 或 者 用 另 一 種 方 法 接 收 多 筆 資 料 於 物 件 內 : DataSet 跟 SqlDataAdapter Q: 在 ASP.Net 中,global 變 數 或 是 跨 函 數 之 間 的 資 料 傳 遞, 是 一 個 很 麻 煩 的 問 題!! ASP.NET 儲 存 及 傳 遞 資 料 的 網 頁 變 數 http://dotnetdic.blogspot.com/2009/01/aspnet.html 將 多 筆 資 料 放 入 dataset 內 以 及 將 相 關 資 料 放 入 session 內 傳 遞 由 dataset 抓 出 資 料 的 作 法 並 寫 成 函 數 供 其 他 函 數 呼 叫 由 dataset 控 制 上 一 筆 的 功 能 由 dataset 控 制 下 一 筆 的 功 能 德 明 財 經 科 技 大 學 資 訊 科 技 系 15

主 題 : 資 料 異 動 新 增 修 改 刪 除 除 了 select 之 外, 別 忘 記 insert/update/delete 這 三 個 要 角, 現 在 我 們 設 計 一 個 具 有 這 三 個 功 能 的 程 式. 說 明 : 以 程 式 撰 寫 資 料 庫 相 關 功 能, 大 約 包 含 三 種 類 型 資 料 庫 連 線 : SqlConnection, 這 是 在 資 料 庫 相 關 功 能 一 開 始 就 得 撰 寫 select: 資 料 查 詢, 也 就 是 將 資 料 庫 的 資 料 傳 送 到 程 式 內 的 變 數, 這 類 程 式 撰 寫 大 約 包 含 兩 部 分, 一 是 以 SqlCommand 執 行 準 備 好 的 sql 指 令, 二 是 以 SqlDataReader 將 資 料 轉 至 程 式 內 的 變 數. insert/update/delete: 資 料 異 動, 主 要 的 目 的 是 直 接 把 資 料 庫 內 的 資 料 異 動. 這 類 程 式. 這 類 程 式 的 重 點 在 sql 指 令, 以 SqlCommand 執 行 準 備 好 的 sql 指 令. 與 select 不 同 的 是 這 類 指 令 不 需 要 由 資 料 庫 傳 資 料 回 程 式 內, 所 以 不 需 要 SqlDataReader, 而 是 使 用 SqlCommand 的 ExecuteNonQuery 直 接 執 行. 不 論 是 select 或 資 料 異 動 這 兩 類, 最 重 要 的 是 寫 好 對 應 的 sql 指 令. (1). Insert. 主 要 的 工 作 有 兩 部 份 : 一 是 把 sql 字 串 內 寫 成 insert 的 指 令, 二 是 讓 SqlCmd 能 夠 執 行 這 個 非 select 的 SQL 命 令 程 式 流 程 如 下 : 檢 查 欲 輸 入 的 資 料 是 否 已 經 存 在 If 資 料 已 經 存 在 顯 示 錯 誤 訊 息 Else 輸 入 資 料 檢 查 資 料 輸 入 是 否 正 確 End if 程 式 流 程 代 表 著 你 的 邏 輯 思 考, 先 把 流 程 想 好 再 把 每 一 個 步 驟 轉 換 成 程 式 碼. 程 式 碼 轉 換 只 是 程 式 撰 寫 的 熟 練 度, 真 正 重 要 的 是 邏 輯 思 考 能 力 功 能 畫 面 設 計 與 說 明 輸 入 產 品 名 稱, 可 查 詢 產 品 資 訊 修 改 欄 位 內 容, 按 下 新 增 按 鈕, 將 資 料 寫 入 德 明 財 經 科 技 大 學 資 訊 科 技 系 16

說 明 顯 示 欄 位 的 類 別 編 號 與 供 應 商 編 號, 不 像 前 一 個 例 子 使 用 名 稱, 因 為 寫 入 資 料 庫 時 必 須 依 循 原 先 編 號 的 規 則 因 此, 在 較 佳 的 系 統 設 計 上, 這 兩 個 顯 示 欄 位 應 該 以 下 拉 式 選 單, 顯 示 名 稱 但 對 應 到 編 號 以 兼 顧 顯 示 與 寫 入 的 需 求 為 了 方 便 操 作, 設 計 成 先 查 詢 一 筆 資 料, 然 後 才 新 增 修 改 刪 除 此 筆 資 料 新 增 資 料 時, 必 須 注 意 主 鍵 不 能 重 複 外 鍵 資 料 一 致 的 限 制 ( 如 果 table 有 設 計 外 鍵 ) 等 修 改 資 料 時, 一 般 會 設 計 成 主 鍵 資 料 不 能 修 改 ; 因 為 我 們 不 知 道 哪 個 欄 位 需 要 修 改, 所 以 在 update 指 令 上 就 修 改 所 有 的 欄 位 ( 主 鍵 例 外 ) Q: 可 以 設 計 成 知 道 使 用 者 修 改 過 哪 些 欄 位 嗎? 怎 麼 做? 刪 除 資 料 時, 不 必 管 各 欄 位 資 料 的 改 變, 直 接 將 主 鍵 欄 位 的 那 筆 資 料 整 個 砍 掉 需 要 一 個 額 外 的 位 置 顯 示 新 增 修 改 刪 除 的 結 果 (2). Insert 功 能 的 程 式 碼 如 下. 如 果 你 的 思 考 流 程 不 同, 即 使 是 相 同 insert 功 能 也 會 有 不 同 的 寫 法. 當 然 思 考 流 程 的 結 果 絕 對 是 受 到 user 要 求 所 影 響 程 式 流 程 如 下 : 檢 查 欲 輸 入 的 資 料 是 否 已 經 存 在 If 資 料 已 經 存 在 顯 示 錯 誤 訊 息 Else 輸 入 資 料 檢 查 資 料 輸 入 是 否 正 確 End if 說 明 請 仔 細 對 照 程 式 流 程 與 指 令 之 間 的 對 照 請 不 要 只 會 抄 程 式, 那 樣 只 是 練 打 字 ; 必 須 仔 細 觀 察 指 令 與 流 程 之 間 的 關 係, 想 像 要 怎 樣 的 動 作 可 以 搭 配 這 些 指 令 注 意 insert into 指 令 在 程 式 中 的 寫 法 怎 麼 檢 查 資 料 是 否 存 在? 很 簡 單, 就 是 以 select 尋 找 這 筆 資 料, 找 的 到 就 是 存 在 Q: 為 什 麼 要 檢 查 資 料 是 否 存 在? 存 在 時 新 增 會 發 生 什 麼 錯 誤? Q: 那 麼 update/delete 呢?? 找 不 到 才 可 以 修 改 刪 除 當 sql 指 令 是 insert/update/delete 這 種 沒 有 回 傳 資 料 的 指 令, 使 用 ExecuteNonQuery 指 令, 傳 回 值 是 資 料 異 動 的 筆 數, 因 為 我 們 預 期 只 新 增 一 筆 資 料, 所 以 用 x=1 來 判 斷 是 否 新 增 正 確 德 明 財 經 科 技 大 學 資 訊 科 技 系 17

(3). update 跟 delete 功 能, 請 同 學 自 行 完 成 注 意 操 作 上 的 差 異 update: 每 個 欄 位 都 可 以 修 改, 所 以 應 該 是 把 每 個 欄 位 ( 不 管 使 用 者 是 不 是 真 的 有 改 到 ) 都 放 入 update 命 令 中, 但 是 主 鍵 是 在 where 而 不 在 異 動 的 欄 位 e.g. update 員 工 set 職 稱 = ' 工 程 師 ', 主 管 = 2, 分 機 號 碼 = 2411 where 員 工 編 號 = 9 delete: 欲 決 定 單 一 筆 資 料 只 需 要 主 鍵, 所 以 一 般 只 需 要 e.g. delete from 員 工 where 員 工 編 號 = 9 這 樣 的 指 令 就 可 以, 而 不 必 每 個 欄 位 都 在 where 條 件 內 出 現 注 意 : 上 面 的 update/delete 只 是 範 例, 請 自 己 寫 出 符 合 程 式 需 求 的 正 確 指 令 問 題 : 當 你 完 成 這 個 程 式 時, 仔 細 想 想 程 式 的 功 能 或 操 作 上 是 否 有 所 不 足? 新 增 修 改 刪 除 執 行 時, 顯 示 成 功 與 失 敗 似 乎 有 點 單 調, 可 以 顯 示 更 詳 細 的 資 訊, 例 如 在 新 增 時 如 果 select count(*) 那 邊 發 現 資 料 已 經 重 複, 可 以 顯 示 訊 息 是 欲 新 增 之 資 料 已 經 存 在. 請 嘗 試 修 改 這 些 訊 尋 資 訊 真 實 的 系 統 在 設 計 上 必 須 仔 細 考 慮 使 用 者 在 操 作 上 的 便 利, 回 想 你 自 己 在 使 用 資 訊 系 統 時, 是 否 會 抱 怨 這 個 功 能 怎 麼 這 麼 笨 之 類 的 ; 同 樣 的 在 設 計 系 統 時, 尤 其 是 系 統 的 操 作 介 面 時, 必 須 練 習 以 使 用 者 的 角 度 來 思 考, 這 樣 才 有 可 能 得 到 使 用 者 的 認 同 請 參 考 上 課 操 作 的 範 例, 構 思 自 己 想 要 的 功 能 並 且 完 成 它 德 明 財 經 科 技 大 學 資 訊 科 技 系 18

討 論 : 不 論 是 update 或 delete, 其 實 就 是 修 改 sql 字 串 的 內 容, 但 是 邊 想 SQL 命 令 邊 用 ASP.Net 程 式 碼 寫 sql 字 串 很 容 易 出 錯, 可 以 先 在 SQL Server (or Access) 上 測 試 你 想 好 的 SQL 命 令 是 否 正 確, 然 後 在.net 上 修 改 sql 字 串. 比 較 兩 種 資 料 庫 程 式 撰 寫 方 式 : 使 用 DataSet 的 介 面 設 定 方 式 ( 請 參 考 VB.Net 的 講 義 ) 與 完 全 使 用 程 式 控 制 方 式. (1) DataSet 的 介 面 設 定 方 式 操 作 上 較 簡 單, 比 較 容 易 理 解 與 學 習. (2) 完 全 以 程 式 控 制 資 料 庫 連 線, 簡 單 來 說 就 分 成 三 種 類 型, 也 就 是 說 只 需 要 熟 悉 這 三 類 的 程 式, 後 續 撰 寫 其 他 的 資 料 庫 程 式 會 非 常 簡 單 從 事 程 式 設 計 工 作 時, 我 們 會 先 撰 寫 資 料 庫 連 線 與 斷 線, select 單 筆 資 料, select 多 筆 資 料, 新 增, 修 改, 刪 除 等 這 幾 個 功 能 當 樣 板. 當 我 們 真 的 需 要 撰 寫 資 料 庫 的 應 用 程 式 時, 直 接 拿 這 些 程 式 範 例 參 考, 只 需 要 針 對 個 別 的 sql 指 令 與 資 料 處 理 修 改, 就 可 以 很 快 完 成 資 料 庫 應 用 程 式 的 撰 寫. 這 一 招 在 其 他 程 式 語 言 處 理 資 料 庫 系 統 仍 然 有 效. 由 於 資 料 庫 應 用 程 式 的 撰 寫 只 牽 涉 到 前 面 幾 項 工 作, 其 實 都 不 會 很 困 難 ; 真 正 困 難 的 是 環 境 的 設 定. 例 如 我 們 以 SQL Server 為 例 介 紹 程 式 連 線 與 撰 寫, 但 是 未 來 如 果 你 碰 到 其 他 資 料 庫 系 統 如 Oracle 或 IBM DB2 等 怎 麼 辦?? 在 程 式 的 部 份 其 實 差 不 多, 最 大 的 差 異 在 連 線 的 環 境 設 定. 這 部 份 我 們 沒 辦 法 一 一 介 紹, 當 未 來 碰 到 時 請 詢 問 其 他 同 事, 或 者 參 考 相 關 之 技 術 手 冊 完 成. 這 部 份 的 環 境 設 定 可 以 說 是 資 料 庫 系 統 建 置 的 最 大 困 難 之 處. 在 學 習 sql 時, 還 有 其 他 的 動 作 ( 例 如 建 立 資 料 庫, 建 立 索 引 等 ), 是 否 可 以 在 ASP.Net 內 以 程 式 撰 寫 完 成 呢? 理 論 上 ExecuteNonQuery 可 以 完 成 大 部 份 的 資 料 庫 動 作, 但 是 在 實 務 上 資 料 庫 的 建 立 是 整 個 程 式 工 作 的 先 決 條 件, 所 以 大 多 是 在 資 料 庫 端 完 成, 很 少 的 情 形 需 要 在 ASP.Net 上 執 行 這 些 特 殊 的 sql 指 令. 程 式 控 制 資 料 庫 連 線 的 最 大 好 處, 是 將 這 些 連 線 控 制 變 成 程 式 撰 寫 邏 輯 的 一 部 分, 也 就 是 我 們 在 撰 寫 程 式 時, 只 需 要 注 意 整 體 的 邏 輯 思 考, 而 不 是 將 資 料 庫 連 線 變 成 獨 立 的 問 題, 這 樣 可 以 簡 化 程 式 時 構 思 的 困 難. 德 明 財 經 科 技 大 學 資 訊 科 技 系 19