ADO.NET 資料庫存取架構

資料庫離線存取模式 建國科技大學資管系饒瑞佶

ADO.NET 資料庫存取架構

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 = 離線式的資料與資料表 一個資料容器 資料是由伺服端複製回來 然後在用戶用端記憶體離線處理資料 能夠 : 減少資料庫伺服器的負載 關閉資料庫連線, 並且離線處理資料 特別是在分散式應用程式中特別有用 但需要處理記憶體與資料庫間同步問題

DataSet 就跟資料庫一樣 DataSet : 可以儲存你想要處理的所有資料 可以搜尋 排序 修改

DataSet 結構 DataSet DataTable1 DataRow DataColumn DataTable2 DataRow DataColumn

如何使用 DataSet- 填滿 DataSet 使用 DataAdapter 物件填滿 DataSet( 容器 ) 需要建立 DataAdapter 與 DataSet 物件 DataSet 是中性的, 與資料庫無關 DataAdapter 與資料庫有關 需要 using 對應的 namespace

ADO.NET namespace 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 利用 Using 指令參考 ADO.NET 物件

ADO.NET 存取架構 資料庫 TableAdapter DataSet BindingSource datagridview 位於電腦 local 的記憶體中裡面存放 DataTable 與 datarelation

伺服器總管可以查看連線的資料庫

從 datagridview 開始

設定資料庫連線

POSDataSet

加入查詢

設定查詢名稱與條件

結果

加入的查詢在 TableAdapter 中

加入新查詢

加入新查詢

編輯項目

透過程式查詢

// 直接顯示到 datagridview 上 datagridview1.datasource = this.posdataset.customer.select("c_age > 20").CopyToDataTable(); // 取出值使用 DataRow[] result = this.posdataset.customer.select("c_age > 10"); foreach (DataRow row in result) { MessageBox.Show(row[0] + "/" + row[1]); }

設定 TextBox 繫結資料欄位

result 這裡輸入, 上面不會改變 這樣做 TextBox 與 datagridview 在修改時不會同步

使用程式碼讓兩者可以同步 全部都要改成程式方式繫結資料 datasource 欄位名稱 處理繫結更新問題

處理繫結更新問題

textbox1.databindings.add("text", customerbindingsource,"c_name", true, DataSourceUpdateMode.OnPropertyChanged); textbox2.databindings.add("text", customerbindingsource, "c_age", true, DataSourceUpdateMode.OnPropertyChanged); customerbindingsource.bindingcomplete += new BindingCompleteEventHandler(bindingSource1_BindingComplete); private void bindingsource1_bindingcomplete(object sender, BindingCompleteEventArgs e) { // Check if the data source has been updated, and that no error has occured. if (e.bindingcompletecontext == BindingCompleteContext.DataSourceUpdate && e.exception == null) // If not, end the current edit. e.binding.bindingmanagerbase.endcurrentedit(); }

加上同步後更新 直接透過 datagridview 來進行更新

result 可以同步了!

datagridview 可以設定 CRUD

資料列異動物件 -BindingNavigator 再設定 BindingSource 屬性就可以

result 可以 顯示總筆數 移動資料列 新增資料列 刪除資料列

從 DataSet 開始

加入資料集

拉出需要的表格 拖曳出來

產生 TableAdapter

也可以多表的關聯

加入關聯查詢

設定需要顯示的資料與關聯

拖曳出 bindingsource

建立 bindingsource 與 DataSet 關聯

自動新增出 TableAdapter Form_Load 會加入以下這行 this.customertableadapter.fill(this.dataset1.customer);

後續繫結方式與前面相同 後續繫結方式

刪除與新增 刪除可以直接從 datagridview 來做, 完成後再一起同步更新就可以 ( 與修改相同 ) 新增部分建議透過程式碼 SqlCommand cmd = new SqlCommand("insert into [Devices_ItemName] (Devnum,itemname,itemorder) values (@Devnum,@itemname,@itemorder)", dbconn); dbconn.open(); cmd.parameters.addwithvalue("@devnum", datagridview1.currentrow.cells[0].value); cmd.parameters.addwithvalue("@itemname", textbox1.text); cmd.parameters.addwithvalue("@itemorder", textbox2.text); cmd.executenonquery(); dbconn.close(); // 重新刷新 //this.customertableadapter.fill(this.posdataset.customer);

使用刪除確認提示 - 使用者按下鍵盤的刪除 使用 datagridview 的 UserDeletingRow 事件

使用者透過 Navigator 上的刪除按鈕刪除 使用 bindingnavigator 的 ItemClicked 事件

if (object.referenceequals(e.clickeditem, this.bindingnavigatordeleteitem)) { DialogResult useranswer = MessageBox.Show(" 確定要刪除?", " 刪除提示 ", MessageBoxButtons.OKCancel, MessageBoxIcon.Question); if (useranswer == DialogResult.OK) { this.bindingnavigator1.deleteitem = this.bindingnavigatordeleteitem; }else { this.bindingnavigator1.deleteitem = null; } }

datagridview 修改的確認 透過 datagridview 的 CellEndEdit 事件

DialogResult useranswer = MessageBox.Show(" 確定要修改?", " 修改提示 ", MessageBoxButtons.OKCancel, MessageBoxIcon.Question); if (useranswer == DialogResult.Cancel) { datagridview1.canceledit(); datagridview1.refreshedit(); }

切換資料表

資料來源是 DataSet

datagridview 要取消資料來源綁定, 後面透過程式進行動態綁定

透過程式綁定第一個資料表 private void Form2_Load(object sender, EventArgs e) { // TODO: 這行程式碼會將資料載入 'dataset1.customer' 資料表 您可以視需要進行移動或移除 this.customertableadapter.fill(this.dataset1.customer); datagridview1.datasource = bindingsource1; // 輸入框與資料庫綁定 textbox1.databindings.add("text", bindingsource1, "c_name", true, DataSourceUpdateMode.OnPropertyChanged); }

切換成其他資料表

private void 產品 ToolStripButton_Click(object sender, EventArgs e) { try { // 移除前一個輸入框綁定 textbox1.databindings.removeat(0); this.producttableadapter.fill(this.dataset1.product); bindingsource1.datamember = "product"; datagridview1.datasource = bindingsource1; // 輸入框與資料庫綁定 textbox1.databindings.add("text", bindingsource1, "p_name", true, DataSourceUpdateMode.OnPropertyChanged); } catch (System.Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); } }

完整流程

先建立 DataSet1

從 datagridview/textbox 開始 出現需要的 dataset 與 bindingsource

接著需要 tableadapter 設定 bindingsource 的 DataMember 屬性就可以出現需要的 tableadapter

選擇資料表後 (I) datagridview 出現綁定 出現 tableadapter

選擇資料表後 (II) 自動填入一行程式 透過 tableadapter 將 dataset 中的 customer 資料表填入到 bindingsource 中, 再填入到 dtagridview

選取所有需要的 tableadapter 設定 bindingsource 的 DataMember 屬性所有的資料表都選一次, 就可以出現需要的 tableadapter

選擇留下一個

取消 datagridivew 的資料來源

自己補上 datagridview 與 bindingsource 的綁定 dataset1bindingsource.datamember = "customer"; this.customertableadapter.fill(this.dataset1.customer); datagridview1.datasource = dataset1bindingsource; // 輸入框與資料庫綁定 textbox1.databindings.add("text", dataset1bindingsource, "c_name", true, DataSourceUpdateMode.OnPropertyChanged);

切換資料表 try { // 移除前一個輸入框綁定 textbox1.databindings.removeat(0); this.producttableadapter.fill(this.dataset1.product); dataset1bindingsource.datamember = "product"; datagridview1.datasource = dataset1bindingsource; // 輸入框與資料庫綁定 textbox1.databindings.add("text", dataset1bindingsource, "p_name", true, DataSourceUpdateMode.OnPropertyChanged); } catch (System.Exception ex) { System.Windows.Forms.MessageBox.Show(ex.Message); }

讓 TextBox 與 datagrdiview 能同步更新 先加入以下方法 // 處理綁定同步 private void bindingsource1_bindingcomplete(object sender, BindingCompleteEventArgs e) { // Check if the data source has been updated, and that no error has occured. if (e.bindingcompletecontext == BindingCompleteContext.DataSourceUpdate && e.exception == null) // If not, end the current edit. e.binding.bindingmanagerbase.endcurrentedit(); } Form_Load 中加入以下程式 dataset1bindingsource.bindingcomplete += new BindingCompleteEventHandler(bindingSource1_BindingComplete);

加入更新程式 配合如果有切換資料表, 需要加入所有需要更新的資料表 修改與刪除都可以透過 datagridview 直接進行刪除需要判斷關聯

加入搜尋 這裡需要判斷目前顯示的資料表來決定搜尋的條件與對象以下以 customer 資料表為例

加入新增

讀取同一個 App.config 連線 先加入 System.Configuration 參考

讀取同一個 App.config 連線 接著 using System.Configuration; 讀取指令 string connection = ConfigurationManager.ConnectionStrings["TestDBBinding2.Properties.S ettings.posconnectionstring"].connectionstring; MessageBox.Show(connection);

code // 讀取 App.config 內的連線字串 string connection = ConfigurationManager.ConnectionStrings[ 連線字串名稱 "].ConnectionString; // 建立連線物件 SqlConnection dbconn = new SqlConnection(connection); SqlCommand cmd = new SqlCommand("insert into [customer] (c_name,c_account,c_pwd,c_age,c_memo) values (@c_name,@c_account,@c_pwd,@c_age,@c_memo)", dbconn); dbconn.open(); cmd.parameters.addwithvalue("@c_name", textbox3.text); cmd.parameters.addwithvalue("@c_account", textbox4.text); cmd.parameters.addwithvalue("@c_pwd", textbox5.text); cmd.parameters.addwithvalue("@c_age", textbox6.text); cmd.parameters.addwithvalue("@c_memo", textbox7.text); cmd.executenonquery(); dbconn.close(); // 重新刷新 this.customertableadapter.fill(this.dataset1.customer);

result

顯示關聯表 (Master/Detail)

需要建立具備關聯的 DataSet 前置作業

先建立 Master 建立方式與前面敘述的單一資料表相同 完成後可以取得以下四個物件 並在 Form_Load 中加入以下程式碼 dataset1bindingsource.datamember = "sales"; // TODO: 這行程式碼會將資料載入 'dataset1.sales' 資料表 您可以視需要進行移動或移除 this.salestableadapter.fill(this.dataset1.sales); datagridview1.datasource = dataset1bindingsource;

再加入一個 bindingsource 給 Detail 使用

設定 bindingsource1 的 datasource 與 datamember 屬性

檢視關聯屬性 後續設定需要這個名稱

Form_Load 中加入以下程式 this.sales_detailtableadapter.fill(this.dataset1.sales_detail); // Detail 的 bindingsource 來源設定成 master 的 bindingsource bindingsource1.datasource = dataset1bindingsource; // datamember 填的就是關聯的名稱 bindingsource1.datamember = "FK_sales_detail_sales"; datagridview2.datasource = bindingsource1;

result

全部透過程式進行

ADO.NET MS ADO.NET Connection Command DataReader DataAdapter 理論 ( 大物件 ) 實作 ( 實作物件時會因資料庫不同而不同 ) SQL Server ACCESS Oracle MySQL sqlconnection sqlcommand sqldatareader sqldataadapter oledbconnection oledbcommand oledbdatareader oledbdataadapter oracleconnection oraclecommand oracledatareader oracledataadapter odbcconnection odbccommand odbcdatareader odbcdataadapter 字首 =sql 字首 =oledb 字首 =oracle 字首 =odbc

資料庫存取步驟 ADO.NET 存取資料時, 使用以下步驟 : 1. Using ADO.NET 物件 ( 依資料庫種類而定 ) 2. 開啟資料庫連線 DBMS+DB 層 (CONNECTION 物件 ) 3. 進行資料庫操作 ( 讀取 / 寫入 ) TABLE 層 (COMMAND+DATAREADER 物件 ) 4. 顯示資料 5. 關閉資料庫連線

使用 DataSet 要修正步驟 3 ADO.NET 存取資料時, 使用以下五大步驟 : 1. Using ADO.NET 物件 ( 依資料庫種類而定 ) 2. 開啟資料庫連線 DBMS+DB 層 (CONNECTION 物件 ) 3. 進行資料庫操作 ( 讀取 / 寫入 ) TABLE 層 (COMMAND+DATAADAPTER 物件 ) 4. 顯示資料 5. 關閉資料庫連線

方式 建立 SqlConnection 物件 建立 DataSet 建立 SqlDataAdapter 連結資料表 從 SqlDataAdapter 將資料表填入到 DataSet 內 建立 BindingSource 綁定 DataSet 與資料表 將 datagridview 綁定到 BindingSource

建立一個 datagridview

Form_Load try { // 從 App.config 讀取連線字串並設定 SQLConnection String connectionstring = ConfigurationManager.ConnectionStrings["TestDBBinding2.Properties.Settings.POSConnectionString"].Conne ctionstring; SqlConnection connection = new SqlConnection(connectionString); // 建立 DataSet. DataSet data = new DataSet(); // 將 customer 資料表從資料庫加入到 DataSet SqlDataAdapter masterdataadapter = new SqlDataAdapter("select * from customer", connection); masterdataadapter.fill(data, "customer");

// 將 dataset 與 bindingsource 進行綁定 BindingSource bindingsource = new BindingSource(); bindingsource.datasource = data; // 主表綁定 dataset bindingsource.datamember = "customer"; // 綁定 customer 資料表 // datagridview 綁定 bindingsource datagridview1.datasource = bindingsource; datagridview1.autoresizecolumns(); // 自動調整欄位大小 connection.close(); } catch (SqlException) { MessageBox.Show("error!"); }

result

資料移動 bindingsource1.movefirst(); bindingsource1.moveprevious(); bindingsource1.movenext(); bindingsource1.movelast();

從 DataSet 中取出所要的欄位與資料

增加一個 ListBox 物件

瀏覽 DataSet DataSet 可以逐筆 逐欄位存取 需要 DataRow 物件

// 從 dataset 的 customer 資料表取出所有列 row foreach (DataRow dr in data.tables["customer"].rows) { listbox1.items.add(dr["c_name"].tostring()); }

result

加入搜尋 以下可以避免沒資料時的錯誤 var filterrows = (from row in data.tables[0].asenumerable() where row.field<int>( age") > 200 select row); if (filterrows.any()) { // 有資料才 Copy datagridview1.datasource = filterrows.copytodatatable(); }else { datagridview1.datasource = null; }

顯示關聯表 (Master/Detail)

只需要拉入兩個 datagridview

先建立兩個 bindingsource // 建立 2 個 bindingsource private BindingSource masterbindingsource = new BindingSource(); private BindingSource detailsbindingsource = new BindingSource();

Fomr_Load 中加入以下程式碼 try { // 從 App.config 讀取連線字串並設定 SQLConnection String connectionstring = ConfigurationManager.ConnectionStrings["TestDBBinding2.Properties.Settings.POSConnectionString"].ConnectionString; SqlConnection connection = new SqlConnection(connectionString); // 建立 DataSet. DataSet data = new DataSet(); // 將 sales 資料表從資料庫加入到 DataSet SqlDataAdapter masterdataadapter = new SqlDataAdapter("select * from sales", connection); masterdataadapter.fill(data, "sales"); // 將 sales_detail 資料表從資料庫加入到 DataSet SqlDataAdapter detailsdataadapter = new SqlDataAdapter("select * from sales_detail", connection); detailsdataadapter.fill(data, "sales_detail"); 這裡需要加入 System.Configuration 參考

// 建立 sales 與 sales_detail 兩張資料表間的關聯 // 關聯名稱為 SalesRelation, 關聯欄位為 s_id DataRelation relation = new DataRelation("SalesRelation", data.tables["sales"].columns["s_id"], data.tables["sales_detail"].columns["s_id"]); // 將關聯加入到 DataSet data.relations.add(relation); // 將 dataset 與 bindingsource 進行綁定 masterbindingsource.datasource = data; // 主表綁定 dataset masterbindingsource.datamember = "sales"; // 綁定 sales 資料表 // 副表綁定到主表的 bindingsource 上 detailsbindingsource.datasource = masterbindingsource; detailsbindingsource.datamember = "SalesRelation";// 綁定關聯

// datagridview 綁定 bindingsource datagridview1.datasource = masterbindingsource; datagridview2.datasource = detailsbindingsource; datagridview1.autoresizecolumns(); // 自動調整欄位大小 // 讓 datagridview2 可以在變動時自動調整大小 datagridview2.autosizecolumnsmode = DataGridViewAutoSizeColumnsMode.AllCells; } catch (SqlException) { MessageBox.Show("error!"); }

result