4-5. Data Set

1. 4-5. Data Set Ch. 18

2. Data Set • 位於記憶體的資料庫 • 可包含多個的資料表 • 搭配DataAdapter使用 • 使用Fill()方法讀入DataSet物件 • 可將更新後的資料送回資料庫 • 離線模式 • 使用Update()方法

4. DataSet物件一覽表

5. DataTableCollection

6. DataColumn

7. DataRow

8. 使用DataSet物件的步驟 • 建立資料連結 • 對資料來源下SQL命令 • 使用DataAdapter物件 • 存取DataSet物件

9. 使用DataAdapter物件獲取DataSet物件 • 宣告方式 • Dim objCmd As New OleDbDataAdapter() • Dim objCmd As New OleDbDataAdapter(selectCommand) • Dim objCmd As New OleDbDataAdapter(selectCommandText,selectConnection) • Dim objCmd As New OleDbDataAdapter(selectCommandText,selectConnectionString) Command 物件 SQL 字串

10. Example (範例 –方法二) Dim objConn As New OleDbConnection() objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB. 4.0;" & "Data Source=" & Server.MapPath("~/App_Data/ Friend.mdb") objConn.Open() Dim SelectCmd As New OleDbCommand("Select * From 擇友俱樂部", objConn) Dim objCmd As New OleDbDataAdapter(SelectCmd)

11. Example (範例 –方法四) Dim objCmd As New OleDbDataAdapter("Select * From 擇友 俱樂部", "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("~/App_Data/Friend.mdb"))

12. 建立DataSet物件 Dim DS As New DataSet() Dim DS As New DataSet(dataSetName)

13. 連結DataAdapter and DataSet Dim objConn As New OleDbConnection() objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB. 4.0;" & "Data Source=" & Server.MapPath("~/App_Data/ Friend.mdb") Dim objCmd As New OleDbDataAdapter("Select * From 擇友俱樂部", objConn) Dim DS As New DataSet() objCmd.Fill(DS, "擇友俱樂部")

14. Example (範例) <%@ Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script runat="server"> Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) '取得 web.config 組態檔的資料連接設定 Dim setting As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("OLEDB_Friend") '建立 DataAdpter 物件 Dim objCmd As New OleDbDataAdapter("Select * From 擇友俱樂部", setting.ConnectionString) '建立 DataSet 物件 Dim DS As New DataSet() objCmd.Fill(DS, "擇友俱樂部") '宣告 myTable 變數為DataTable物件，並將「擇友俱樂部」資料表指派給它 Dim myTable As DataTable = DS.Tables("擇友俱樂部") '顯示 DataSet 物件內「擇友俱樂部」資料表的所有欄位名稱 Dim I, J As Integer Response.Write("<table border='1'><tr align='Center'>") For I = 0 To myTable.Columns.Count - 1 Response.Write("<td>" & myTable.Columns(I).Caption & "</td>") Next Response.Write("</tr>")

15. '顯示 DataSet 物件內「擇友俱樂部」資料表的所有資料 For I = 0 To myTable.Rows.Count - 1 Response.Write("<tr>") For J = 0 To myTable.Columns.Count - 1 Response.Write("<td>" & myTable.Rows(I).Item(J) & "</td>") Next Response.Write("</tr>") Next Response.Write("</table>") End Sub </script>

16. Practice (練習) • 利用asp.net網頁,顯示price.mdb資料表的所有資料 • 利用asp.net網頁,查詢所有資料的姓名欄位,並且使用BulletedList的方法呈現

17. 運用 DataSet • DataTableCollection (資料表集合) • DataColumnCollectoin (資料欄集合) • DataRowCollection (資料列集合)

18. DataTableCollection • 屬性 • Count • Item({index|name}) • 方法 • Add() • Add(table) • Add(tableName) • Clear() • Contains(tableName) • Remove({table|tableName}) • RemoveAt(index)

19. DataTable • 屬性 • CaseSensitive={True|False} • Columns • DataSet • HasErrors • MinimumCapacity=n • Rows • TableName="…“ • 方法 • AcceptChanges() • Clear() • Clone() • Compute(expression, filter) • Copy() • GetChanges({Added|Deleted|Detached|Modified|Unchanged}) • GetErrors() • ImportRow(row) • NewRow() • RejectChanges() • Select()

20. DataColumnCollection • 屬性 • Count • Item({index|name}) • 方法 • Add() • Add(column) • Add(columnName) • Add(columnName, type) • Clear() • Contains(columnName) • Remove({column|columnName}) • RemoveAt(index)

21. DataColumn • 屬性 • AutoIncrement={True|False} • AutoIncrementSeed=n • AutoIncrementStep=n • AllowDBNull={True|False} • Caption="…" • ColumnName="…" • DataType • DefaultValue="…" • Expression="…“ • MaxLength=n • Ordinal • ReadOnly={True|False} • Table • Unique={True|False}

22. DataRowCollection • 屬性 • Count • Item(index) • 方法 • Add(row) • Clear() • Contains(key) • Find(key) • IndexOf(row) • Remove(row) • RemoveAt(index)

23. DataRow • 屬性 • HasErrors • Item({columnName|index}) • ItemArray=… • RowError="…" • RowState • Table • 方法 • AcceptChanges() • ClearErrors() • Delete() • GetColumnError({columnName|index|column}) • GetColumnsInError() • IsNull({columnName|index|column}) • RejectChanges() • SetAdded() SetColumnError({columnName|index|column}, string) • SetModified()

24. Practice (練習)

25. <%@ Page Language="VB" %> <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.OleDb" %> <script runat="server"> Public myTable As DataTable Sub Page_Load(ByVal sender As Object, ByVal e As Eventargs) Dim setting As ConnectionStringSettings = _ ConfigurationManager.ConnectionStrings("OLEDB_Price") '建立 DataAdpter 物件 Dim objCmd As New OleDbDataAdapter("Select * From 零組件報 價表", setting.ConnectionString) '建立 DataSet 物件 Dim DS As New DataSet() objCmd.Fill(DS, "零組件報價表")

26. 將「零組件報價表」資料表指派給 myTable 變數 myTable = DS.Tables("零組件報價表") If Not IsPostBack Then Dim I As Integer For I = 0 To myTable.Rows.Count - 1 row.Items.Add(I) row.Items(I).Text = "第 " & I + 1 & " 列" row.Items(I).Value = I Next For I = 0 To myTable.Columns.Count - 1 column.Items.Add(I) column.Items(I).Text = myTable.Columns(I).ColumnName column.Items(I).Value = myTable.Columns(I).ColumnName Next End If End Sub

27. Sub show_Click(sender As Object, e As EventArgs) Message.Text = row.SelectedItem.Text & "，<" & column.SelectedValue & "> 欄位的值為【" & myTable.Rows(row.SelectedValue).Item( column.SelectedValue) & "】" End Sub </script> <html> <body> <form id="form1" runat="server"> 請選擇要顯示的資料列及欄位：<br> 資料列：<asp:DropDownList ID="row" runat="server" /> 欄位：<asp:DropDownList ID="column" runat="server" /> <asp:Button ID="show" runat="server" OnClick="show_Click" Text="顯示" /><br><br> 結果：<br><asp:Label ID="Message" runat="server" /> </form> </body> </html>