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

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

資料提供者 .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物件的連線字串

ADO.NET ADO.NET 是用來存取資料庫的物件集合 核心命名空間: 利用Imports指令參考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物件

ADO .NET 資料存取方式 目前只有 4 種 … 工作 ADO.NET 對資料庫進行唯讀存取 (伺服端) DataReader 對資料庫進行讀寫 (用戶端) DataSet 修改資料庫資料 ExecuteNonQuery() 取得單一資料 ExecuteScalar()

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

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

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

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

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

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

(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() )

開啟ACCESS資料庫連線範例

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

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

開啟SQL Server資料庫連線範例

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

開啟SQL Server資料庫連線範例

(2) 進行資料庫操作 (讀取) 建立 DataReader 物件，並且逐一讀取 步驟3 步驟4 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

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

顯示ACCESS資料庫資料到ListBox

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

顯示SQL Server資料庫資料到ListBox

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

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

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

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

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

預防錯誤 用 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

預防錯誤

執行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()

執行SQL Server預儲程序

執行SQL Server預儲程序

執行SQL Server預儲程序

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

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

避免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被刪除

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

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

範例 刪除選取的客戶資料 … 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)

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

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

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

執行預儲程序

執行預儲程序

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

DataSet DataSet 其實就是一個在記憶中的資料結構 將資料庫的資料複製到記憶體中，用表格的方式來儲存 很容易進行取得、傳遞與顯示裡面的資料 DataAdapter Command Connection DB Product Price Quantity Ants $ 0.49 5000 Birds $ 4.49 500 Cats $29.95 100 Dogs $79.95 20 DataSet

為什麼要使用 DataSet ? DataSet = 離線式的 Recordset 允許你 : 特別是在分散式應用程式中特別有用 … 資料是由伺服端複製回來 然後在用戶用端離線處理資料 允許你 : 減少資料庫伺服器的負載 關閉資料庫連線，並且離線處理資料 特別是在分散式應用程式中特別有用 …

填滿 DataSet 使用 DataAdapter 物件填滿 DataSet 例如: 將產品資料填入 DataSet Product Price Quantity Ants $ 0.49 5000 Birds $ 4.49 500 Cats $29.95 100 Dogs $79.95 20 sql = "Select * From Products;" . Dim adapter As ss.SqlDataAdapter, ds As System.Data.DataSet dbConn = New ... dbCmd = New ... adapter = New ss.SqlDataAdapter(dbCmd) ds = New System.Data.DataSet() dbConn.Open() adapter.Fill(ds) dbConn.Close() ds.Tables(“Table”).TableName = “Products” ‘** 修改 DataSet 中表格的名稱 … "Products"

新增Form2表單

DataSet 就像資料庫一樣 DataSet : 範例: 可以儲存你想要處理的所有資料 可以搜尋、排序、修改 將整個 Sales 資料庫的資料填滿 DataSet sql = "Select * From Customers; Select * From Orders; " & _ "Select * From OrderItems; Select * From Products;" . dbConn.Open() adapter.Fill(ds) dbConn.Close()

瀏覽 DataSet 可以逐筆、逐欄位存取 … 例如: 顯示產品名稱 . Dim row As System.Data.DataRow, name As String For Each row In ds.Tables("Products").Rows name = Convert.ToString(row.Item("ProductName")) Me.ListBox1.Items.Add(name) Next row

DataGridView 是很強大的控制項 使用者可以修改資料 使用者可以新增資料 使用者可刪除資料 但是，所有資料修改都只在 DataSet 中 如果要寫回資料庫，要另外連接資料庫進行更新 …

確認是否修改DataSet

將修改寫回資料庫 連線資料庫，執行 DataAdapter 的 Update() 方法 問題: 如何解決資料修改的衝突問題? 如果不需要修改資料，請將 DataGridView 設定成 ReadOnly = True . ‘** 從 DataGrid 物件取回原本的 DataSet … ds = CType(Me.DataGrid1.DataSource, System.Data.DataSet) ‘** 重設表格名稱 ‘** 讓 .NET 來建立 Insert, Update, Delete 三個命令 Dim cmdBuilder As ss.SqlCommandBuilder cmdBuilder = New ss.SqlCommandBuilder(adapter) dbConn.Open() adapter.Update(ds) ‘** 如果發生衝突，會產生例外 … dbConn.Close()

將修改寫回資料庫

將修改寫回資料庫

其他資訊 DataGridView1_Click & DataGridView1_SelectionChanged 事件 目前筆數/總筆數 (DataGridView1.CurrentRow.Index + 1).ToString() >> 目前筆數 (DataGridView1.RowCount - 1).ToString >> 總筆數 DataGridView1_Click & DataGridView1_SelectionChanged 事件 防止新增時上下筆錯誤 用LABEL物件顯示詳細資料 Sub show_new_record() If (DataGridView1.CurrentRow.Index) < (DataGridView1.RowCount - 1) And DataGridView1.CurrentRow.Cells(0).Value.ToString() <> "" Then Label1.Text = (DataGridView1.CurrentRow.Index + 1).ToString() + "/ " + (DataGridView1.RowCount - 1).ToString Label2.Text = DataGridView1.CurrentRow.Cells(0).Value Label3.Text = DataGridView1.CurrentRow.Cells(1).Value End If End Sub 顯示圖片 PictureBox1.Load(Application.StartupPath + "\" + DataGridView1.CurrentRow.Cells(4).Value)