ADO.NET 架構 資料 讀取 資料異動 DataSet 工具

1. 內容 • ADO.NET 架構 • 資料讀取 • 資料異動 • DataSet • 工具

2. 架構 • ADO.NET 是根基於資料提供者之上 • 資料提供者將有關資料庫存取的細節封裝起來 .NET 程式設計師 ADO.NET 資料提供者 DB

3. 資料提供者 • .NET 可以存取多種資料提供者: • 原生提供者: SQL Server , Oracle • 非原生提供者: OLEDB , ODBC • 也支援其它資料提供者: • Oracle, IBM, MySQL, … • http://otn.oracle.com/tech/windows/odpnet/content.html • http://www.mysql.com/, http://crlab.com/mysqlnet/ 未來出現在CONNECTION物件的連線字串

4. ADO.NET • ADO.NET 是用來存取資料庫的物件集合 • 核心命名空間: • 一般: System.Data, System.Data.Common • SQL Server:System.Data.SqlClient • Oracle: System.Data.OracleClient • OleDB:System.Data.OleDb (Access) • ODBC: System.Data.Odbc 利用Imports指令參考ADO.NET物件

5. ADO .NET 資料存取方式 • 目前只有 4 種 …

6. 內容 • ADO.NET 架構 • 資料讀取 • 資料異動 • DataSet • 工具

7. 記錄 record record DataReader • 透過 DataReader 物件可以逐一讀取資料 • DataReader = 只能下一筆、唯讀的讀取 • 根據要求將記錄以串流的方式傳回用戶端 • 佔用資料庫連線，使用伺服端資源直到連線關閉 用戶端 資料庫引擎 DataReader

8. 資料庫存取四步驟 • 存取資料時，都是使用以下四大步驟: • Imports ADO.NET物件 (依資料庫種類而定) • 開啟資料庫連線  DBMS+DB層(CONNECTION物件) • 進行資料庫操作 (讀取/寫入)  TABLE層(COMMAND+DATAREADER物件) • 關閉資料庫連線

9. DBMS DB TABLE DataReader資料存取示意圖 • SQL SERVER • SERVER位置 • 帳號 • 密碼 • DATABASE • Access • DATABASE CONNECTION物件 負責連結DBMS與DB COMMAND物件 負責SQL指令 連線字串 DataReader物件 負責取出DATA

10. 連線字串 • Access Provider=Microsoft.Jet.OLEDB.4.0;Data Source=資料庫位置;User Id=admin;Password=; • SQL Server Data Source=server位置;Initial Catalog=資料庫;User Id=帳號;Password=密碼;

11. 建立連線字串 • 連線字串的格式要根據不同資料庫而定 • 那裡可以找到連線字串的說明 ? • http://www.connectionstrings.com • http://www.carlprothman.net/Default.aspx?tabid=81

12. (1) 開啟資料庫連線 • 建立連線物件，並且執行 Open() 方法 • 重點在於正確設定連線字串 … Imports db = System.Data.OleDb . . . ‘** 使用 OLEDB for Access 2000, .MDB 必須和 .EXE放在同一個資料夾下 ** Dim sConnection As String, dbConn as db.OleDbConnection sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ “Data Source=資料庫.mdb" dbConn = New db.OleDbConnection(sConnection) dbConn.Open() MessageBox.Show( dbConn.State.ToString() )

13. 開啟ACCESS資料庫連線範例

14. 開啟ACCESS資料庫連線範例 步驟1 步驟2

15. 開啟ACCESS資料庫連線範例結果畫面

16. 開啟SQL Server資料庫連線範例

17. 開啟SQL Server資料庫連線範例 步驟1 步驟2

18. 開啟SQL Server資料庫連線範例

19. (2) 進行資料庫操作 (讀取) • 建立 DataReader 物件，並且逐一讀取 Dim sql, fn, ln As String Dim dbCmd As db.OleDbCommand, dbReader As db.OleDbDataReader sql = "Select * From Customers Order By LastName Asc, FirstName Asc;" dbCmd = New db.OleDbCommand(sql, dbConn) dbReader = dbCmd.ExecuteReader() ‘** 執行 SQL指令，回傳 DataReader 物件 Do While dbReader.Read() ‘** 讀取下一筆資料 … ‘** 記錄已經暫存在用戶端，可以存取每一個欄位的值 … fn = System.Convert.ToString( dbReader("FirstName") ) ln = System.Convert.ToString( dbReader("LastName") ) Me.ListBox1.Items.Add(ln + ", " + fn) Loop dbReader.Close() 步驟3 步驟4

20. (3) 關閉資料庫連線 • 記得一定要關閉資料庫連線 ! • 釋放連線資源給其它用戶端 … dbConn.Close() 步驟4

21. 顯示ACCESS資料庫資料到ListBox

23. 取出 SQL Server資料 ? • 一樣的做法，只是用不同的物件 • SqlConnection, SqlCommand, … • 當然，連線字串也不一樣

24. 顯示SQL Server資料庫資料到ListBox

26. 記錄 record record DataReader小結 用戶端 資料庫引擎 DataReader Connection DataReader Command

27. 使用插入程式碼片段連結access資料庫 datagridview

28. 使用插入程式碼片段連結access資料庫

29. 使用插入程式碼片段連結access資料庫

30. 使用插入程式碼片段連結access資料庫

31. 預防錯誤 • 用 Try-Catch-Finally來確保資料庫連線被關閉 … Dim dbConn As db.OleDbConnection Dim dbReader As db.OleDbDataReader Try dbConn = New db.OleDbConnection(…) . . . Catch ex As Exception MessageBox.Show("Data Error: " & ex.Message) Finally If Not dbReader Is Nothing Then dbReader.Close() If Not dbConn Is Nothing Then dbConn.Close() End Try

32. 預防錯誤

33. 執行SQL Server預儲程序 • 使用 DataReader 執行預儲程序，並且取得資料表資料 • 範例: • 呼叫預儲程序，回傳前 10 個客戶 Imports ss = System.Data.SqlClient . . . sql = “Execute 預存程序名稱;" dbConn = New ss.SqlConnection(sConn) dbCmd = New ss.SqlCommand(sql, dbConn) dbConn.Open() dbReader = dbCmd.ExecuteReader() . . .

34. 執行SQL Server預儲程序

35. 執行SQL Server預儲程序

36. 執行SQL Server預儲程序

37. 練習 • 使用connection,command,datareader • 搭配SQL指令 • 練習設計登入畫面與程式

38. SQL Injection • 資料隱碼攻擊 • SQL指令的漏洞 • ‘ or 1=1 – • ‘ or 1=1 or 1=‘ • ' or ''=' • 取代有問題的字元 • ‘ - / 等

39. 避免SQL Injection function fixsql(sql as String)sql=replace(sql,"'","''")sql=replace(sql,"\","/")sql=replace(sql,"-","_")sql=replace(sql,"*","_")sql=replace(sql,"+","_")fixsql=sql end function 制輸入的長度降低sql server使用者權限 至少不幸被入侵,只是修改或刪除記錄而不會整個database被刪除

40. 內容 • ADO.NET 架構 • 資料讀取 • 資料異動 • DataSet • 工具

41. ExecuteNonQuery • 透過以下三種 DML 語法來修改資料庫資料 … • 新增：INSERT • 修改：UPDATE • 刪除：DELETE • 透過 ExecuteNonQuery() 方法 • 回傳修改的資料筆數 用戶端 資料庫引擎 Command

42. 範例 • 刪除選取的客戶資料 … Dim sConnection, sql, fn, ln As String Dim dbConn As db.OleDbConnection, dbCmd As db.OleDbCommand fn = ... ln = ... sConnection = ... sql = String.Format("Delete From Customers Where FirstName='{0}' And LastName='{1}';", fn, ln) dbConn = New db.OleDbConnection(sConnection) dbCmd = New db.OleDbCommand(sql, dbConn) dbConn.Open() rows = dbCmd.ExecuteNonQuery() dbConn.Close() System.Diagnostics.Debug.Assert(rows = 1)

45. 文字欄位處理 • 記得要加上分隔符號「‘」 • 記得要將字串中有 ‘ 部份的換成兩個 ‘ 引號 • 不然像是 “Peter’s name” 就會有問題 ! sql = String.Format("Delete From Customers Where " & _ "FirstName='{0}' And LastName='{1}';", _ fn.Replace("'", "''"), _ ln.Replace("'", "''")) 練習刪除SQL Server資料

46. 執行預儲程序 • 使用 ExecuteNonQuery 來執行預儲程序，只是不會回傳表格 • 可能什麼都不回傳 • 可能回傳一個數值 (修改筆數) • 可能使用回傳型參數來回傳多個值 . . . sql = String.Format("Execute sprocCustomersDelete '{0}', '{1}';", _ fn.Replace("'", "''"), ln.Replace("'", "''")) . . . dbConn.Open() dbCmd.ExecuteNonQuery() dbConn.Close()

47. 新增刪除預儲程序 CREATE PROCEDURE deltest @CID varchar AS delete from Customers where CustomerID='@CID'

48. 執行預儲程序

49. 執行預儲程序

50. 內容 • ADO.NET 架構 • 資料讀取 • 資料寫入 • DataSet • 工具