第 9 章 ADO.NET的網頁資料庫查詢

1. 第9章 ADO.NET的網頁資料庫查詢 • 9-1 從資料表取得單一欄位值 • 9-2 DataReader物件以表格顯示資料表 • 9-3 DataSet物件的資料庫查詢 • 9-4 DataSet物件的資料篩選 • 9-5 DataSet物件與XML • 9-6 ASP.NET控制項與資料繫結

2. 9-1 從資料表取得單一欄位值 • ADO.NET的Command物件提供多種Execute()方法，在第8章只使用ExecuteNonQuery()方法，在這一節筆者將說明ExecuteScalar()方法，此方法可以取得資料表指定記錄的單一欄位值，如下所示： msg.Text = objCmd.ExecuteScalar() • 上述程式碼執行Command物件objCmd的SQL指令，如果傳回值不只一個。例如：多筆記錄，取得的是第1筆記錄的第1個欄位。

3. 9-2 DataReader物件以表格顯示資料表 • 9-2-1 取得資料表資訊 • 9-2-2 顯示資料表的所有記錄 • 9-2-3 分頁顯示資料表的記錄

4. 9-2 DataReader物件以表格顯示資料表 • 在ASP.NET程式顯示資料表內容通常是使用表格，以一列代表一筆記錄，每一欄為一個欄位。在本章是使用HTML表格來顯示資料表，第10章則是以資料來源和Web控制項顯示資料表的記錄資料。 • SQL查詢指令的說明請參閱附錄B，本章程式範例都是顯示資料表的所有記錄，SELECT指令並沒有WHERE子句的條件，如下所示： SELECT * FROM Users • 上述SQL指令可以將資料表Users的所有記錄和欄位都取回來，換句話說，就是顯示資料表的所有記錄。

5. 9-2-1 取得資料表資訊-取得資料表資訊DataTable物件 • 我們可以使用ASP.NET程式來取得資料表的定義資訊。例如：欄位名稱、資料型態和欄位尺寸。首先使用ExecuteReader()方法取得DataReader物件，如下所示： objDataReader = objCmd.ExecuteReader() • 程式碼取得ObjDataReader物件後，使用GetSchemaTable()方法取得資料表資訊的DataTable物件，如下所示： Dim objSchema As DataTable = _ objDataReader.GetSchemaTable()

6. 9-2-1 取得資料表資訊-屬性 • DataReader物件是將定義資料儲存在DataTable物件，每一列是一個欄位的資訊。主要欄位說明，如下表所示：

7. 9-2-1 取得資料表資訊-顯示 Dim objRow As DataRow For intI = 0 To objSchema.Rows.Count - 1 Response.Write("<tr>") objRow = objSchema.Rows(intI) Response.Write("<td>" & _ objRow("ColumnOrdinal") & "</td>") Response.Write("<td>"& _ objRow("ColumnName") &"</td>") Response.Write("<td>" & _ objRow(“DataType”).ToString() & "</td>") Response.Write("<td>"& _ objRow("ColumnSize") &"</td>") Response.Write("<tr>") Next

8. 9-2-2 顯示資料表的所有記錄-步驟一：建立Connection物件 • ADO.NET的DataReader物件是以類似檔案串流方式來讀取記錄資料，只能讀取資料，並不能插入、刪除和更新記錄。 • 如同資料庫操作，我們需要使用Connection物件來建立資料庫連結，如下所示： objCon = New OleDbConnection(strDbCon) • 上述程式碼建立objCon的資料庫連結物件。

9. 9-2-2 顯示資料表的所有記錄-步驟二：建立Command物件 • 在建立好Connection物件後，就可以使用SQL指令和Connection物件為參數來建立Command物件，如下所示： objCmd = New OleDbCommand( _ "SELECT * FROM Users",objCon)

10. 9-2-2 顯示資料表的所有記錄-步驟三：執行SQL指令查詢資料表 • 接著使用ExecuteReader()方法來執行SQL指令，可以取得DataReader物件，如下所示： objDR = objCmd.ExecuteReader()

11. 9-2-2 顯示資料表的所有記錄-步驟四：取出查詢結果的資料表記錄資料 • DataReader物件是一種串流資料，可以使用迴路讀取資料表的每一筆記錄，如下所示： Do While objDR.Read() Response.Write("<tr>") Response.Write("<td>"& objDR.Item("Name") &"</td>") Response.Write("<td>"& objDR.Item("Birthday") &"</td>") Response.Write("<td>"& objDR.Item("Salary") &"</td>") Response.Write("<td>"& objDR.Item("Email") & "</td>") Response.Write("<td>"& objDR.Item("UserName") &"</td>") Response.Write("<td>"& _ objDR.Item("UserPassword") &"</td>") Response.Write("</tr>") Loop

12. 9-2-2 顯示資料表的所有記錄-步驟五：關閉DataReader和資料庫連結 • 最後就是關閉DataReader物件和資料庫連結，如下所示： objDR.Close() objCon.Close() • 上述程式碼使用Close()方法關閉DataReader和Connection物件。

13. 9-2-3 分頁顯示資料表的記錄-計算資料表的記錄數 • 資料表如果記錄很多，我們可以分頁顯示資料表的記錄資料，DataReader物件的分頁顯示需要執行2次SQL指令。 • ASP.NET程式第1次執行SQL指令的目的是計算資料表的記錄數，如下所示： strSQL = "SELECT Count(*) FROM Users" objCmd = New OleDbCommand(strSQL, objCon) objDataReader = objCmd.ExecuteReader() objDataReader.Read() intMaxRec = objDataReader.GetValue(0)

14. 9-2-3 分頁顯示資料表的記錄-顯示分頁的資料表記錄1 • 第2次執行SQL指令是取得資料表記錄的DataReader物件，然後計算目前分頁的開始、結束記錄和最大頁數，如下所示： intStartRec = intPageSize * _ (intPageNo - 1) + 1 intStopRec = intStartRec + intPageSize - 1 intMaxPageCount = intMaxRec \ intPageSize If (intMaxRec MOD intPageSize) > 0 Then intMaxPageCount = intMaxPageCount + 1 End If

15. 9-2-3 分頁顯示資料表的記錄-顯示分頁的資料表記錄2 Do While objDataReader.Read() AND intCount<intStopRec intCount = intCount + 1 If intCount >= intStartRec Then Response.Write("<tr>") For intI = 0 to intFCount If objDataReader.IsDBNull(intI) = False Then Response.Write("<td valign=""top"">" & _ objDataReader.Item(intI) & "</td>") Else Response.Write("<td>---</td>") End If Next Response.Write("</tr>") End If Loop

16. 9-2-3 分頁顯示資料表的記錄-建立切換分頁的超連結1 • 資料表的記錄資料因為是分頁顯示，所以需要建立切換分頁的超連結，首先是頁碼超連結，如下所示： For intI = 1 To intMaxPageCount URL = "<a href='Ch9-2-3.aspx?PageNo=" & intI URL &= "&PageSize=" & intPageSize & "'>" Response.Write(URL & intI & "</a> ") If intI mod 10 = 0 Then Response.Write("<br>") Next

17. 9-2-3 分頁顯示資料表的記錄-建立切換分頁的超連結2 • 至於建立上一頁和下一頁的超連結文字，如下所示： intPPageNo = intPageNo - 1 If intPPageNo > 0 Then URL = "<a href='Ch9-2-3.aspx?PageNo=" & intPPageNo URL &= "&PageSize=" & intPageSize & "'>上一頁</a>" Response.Write(URL & " ") End If intNPageNo = intPageNo + 1 If intNPageNo <= intMaxPageCount Then URL = "<a href='Ch9-2-3.aspx?PageNo=" & intNPageNo URL &= "&PageSize=" & intPageSize & "'>下一頁</a>" Response.Write(URL & " ") End If

18. 9-2-3 分頁顯示資料表的記錄-圖例

19. 9-3 DataSet物件以表格顯示資料表 • 9-3-1 顯示DataSet物件的所有記錄 • 9-3-2 分頁顯示DataTable的記錄 • 9-3-3 使用程式碼建立DataTable資料表物件

20. 9-3 DataSet物件以表格顯示資料表 • DataSet物件可以建立儲存在記憶體的資料庫，它是將資料表的記錄和欄位資料，轉換成物件架構，如下圖所示：

21. 9-3-1 顯示DataSet物件的所有記錄 • 使用For Each迴路從DataRowCollection集合物件，取出每一個DataRow物件，如下所示： For Each objRow in objDataSet.Tables("Users").Rows Response.Write("<tr>") Response.Write("<td>" & objRow("name") & _ "</td>") Response.Write("<td>" & objRow("birthday") & _ "</td>") Response.Write("<td>" & objRow("salary") & _ "</td>") ……… Response.Write("</tr>") Next

22. 9-3-2 分頁顯示DataTable的記錄-取得記錄數 intMaxRec = objDS.Tables("Users").Rows.Count intStartRec = intPageSize * (intPageNo - 1) + 1 intStopRec = intStartRec + intPageSize - 1 If intStopRec >= intMaxRec Then intStopRec = intMaxRec - 1 End If intMaxPageCount = intMaxRec \ intPageSize If (intMaxRec MOD intPageSize) > 0 Then intMaxPageCount = intMaxPageCount + 1 End If

23. 9-3-2 分頁顯示DataTable的記錄-顯示欄位名稱 • 接著使用DataColumn集合物件顯示資料表的欄位名稱，如下所示： For Each objCol in objDS.Tables("Users").Columns Response.Write("<td><b>" & _ objCol.ColumnName & "</b></td>") Next • 然後使用Count屬性取得欄位數，如下所示： intFCount = objDS.Tables("Users").Columns.Count

24. 9-3-2 分頁顯示DataTable的記錄-分頁顯示資料表記錄 Dim objRow As DataRow intJ = intStartRec Do objRow = objDS.Tables("Users").Rows(intJ-1) Response.Write("<tr>") For intI = 0 To intFCount - 1 If objRow.IsNull(intI) = False Then Response.Write("<td>"& objRow(intI) &"</td>") Else Response.Write("<td>---</td>") End If Next Response.Write("</tr>") intJ = intJ + 1 Loop Until intJ > intStopRec

25. 9-3-3 使用程式碼建立DataTable資料表物件-說明 • DataSet物件是由DataTable物件組成，以DataRow物件組成DataTable物件。 • 換句話說，ASP.NET程式可以自行建立DataSet和DataTable物件，將它視為儲存在記憶體的資料表。例如：儲存網路商店的購物車資料。

26. 9-3-3 使用程式碼建立DataTable資料表物件-欄位定義 建立DataTable物件的欄位定義資料1 • DataTable物件可以對應資料庫的資料表，在建立DataTable物件後，我們需要新增資料表欄位。首先建立DataTable物件，如下所示： Dim objDT As DataTable = _ New DataTable("ShoppingCart") objDT.MinimumCapacity = 5 objDT.CaseSensitive = False

27. 9-3-3 使用程式碼建立DataTable資料表物件-欄位定義 建立DataTable物件的欄位定義資料2 • 在建立好DataTable物件後，就可以使用DataColumnCollection物件的Add()方法新增欄位資料，如下所示： objCol = objDT.Columns.Add("ID", _ System.Type.GetType("System.Int32")) objCol.AutoIncrement = TRUE objCol.AllowDBNull = False

28. 9-3-3 使用程式碼建立DataTable資料表物件-新增至DataSet物件 將DataTable物件新增到DataSet物件 • 在建立好DataTable物件後，就可以將它新增到DataSet物件，如下所示： Dim objDS As DataSet = New DataSet() objDS.Tables.Add(objDT) • 上述程式碼在建立DataSet物件後，使用DataTableCollection集合物件的Add()方法新增到DataSet物件。

29. 9-3-3 使用程式碼建立DataTable資料表物件-指定主索引 指定DataTable物件的主索引 • 接著建立DataTable物件的主索引，如下所示： Dim keyArray() As DataColumn = _ {objDS.Tables("ShoppingCart").Columns("ID")} objDS.Tables("ShoppingCart").PrimaryKey = _ keyArray

30. 9-3-3 使用程式碼建立DataTable資料表物件-新增記錄資料 新增DataRow物件的記錄資料 • 現在可以新增DataTable物件的欄位資料，也就是DataRow物件，如下所示： objRow = objDT.NewRow() objRow("UserID") = 100 objRow("ProductID") = 5474 objRow("ProductName") = _ "XML網頁製作徹底研究-第三版" objRow("ProductPrice") = 650.00 • 在指定欄位值後，使用DataRowCollection物件的Add()方法新增至DataTable物件，如下所示： objDT.Rows.Add(objRow) objDT.AcceptChanges()

31. 9-4 DataSet物件的資料篩選 • 9-4-1 DataTable物件的資料篩選 • 9-4-2 DataView物件的資料篩選 • 9-4-3 建立DataTable的關聯性

32. 9-4-1 DataTable物件的資料篩選 • 在DataTable物件提供Select()方法，可以取得指定過濾條件和排序欄位的DataRow物件陣列，如下所示： Dim selectRows() As DataRow selectRows = _ objDataSet.Tables("Users").Select( _ "salary > 30000", "username DESC", _ DataViewRowState.CurrentRows)

33. 9-4-2 DataView物件的資料篩選-說明 • DataView物件是附屬於DataTable物件，可以建立第9-6節資料繫結所需的資料來源。 • 事實上，它就是使用者依DataTable物件自訂的查詢結果，可以指定排序方式、過濾條件、搜尋、編輯和瀏覽方式。

34. 9-4-2 DataView物件的資料篩選-篩選 • 在ASP.NET程式首先宣告DataView物件和代表每一筆記錄的DataRowView物件的變數，如下所示： Dim objDataView As DataView Dim oRow As DataRowView • 然後使用DefaultView屬性取得DataTable預設的DataView物件，如下所示： objDataView = objDataSet.Tables("Users" ).DefaultView objDataView.RowFilter = "salary > 30000" objDataView.Sort = "username DESC"

35. 9-4-3 建立DataTable的關聯性-說明 • DataSet物件可以包含多個DataTable物件，在DataTable物件間能夠建立關聯性（Relationship）的DataRelation物件，然後使用關聯性來取出不同DataTable物件的記錄資料。 • 本節ASP.NET程式範例的DataSet物件共新增名為Users和Types兩個DataTable（在Users.mdb有Users和SalaryType兩個資料表）物件。

36. 9-4-3 建立DataTable的關聯性-建立DataRelation物件 • 因為Types的記錄是Users資料表Salary欄位的薪資分類資料，所以可以使用此欄位建立資料表關聯性的DataRelation物件，如下所示： objRelation = New DataRelation("SalaryType", _ objDataSet.Tables("Types").Columns("Salary"), _ objDataSet.Tables("Users").Columns("Salary")) • 在建立好DataRelation物件後，就可以使用DataRelationCollection物件的Add()方法新增到DataSet物件，如下所示： objDataSet.Relations.Add(objRelation)

37. 9-4-3 建立DataTable的關聯性-顯示兩個資料表的關聯資料 • 現在已經建立好資料表關聯性後，接著使用For Each迴路從父資料表欄位，取得關聯子資料表的記錄資料，如下所示： For Each TypeRow In objDataSet.Tables("Types").Rows Response.Write(TypeRow("SalaryType") & _ "<br>" ) For Each UserRow In TypeRow.GetChildRows("SalaryType") Response.Write("+---- " & _ UserRow("Name") & "<br>") Next Next

38. 9-5 DataSet物件與XML • 9-5-1 將DataSet輸出成XML文件 • 9-5-2 讀入XML文件

39. 9-5-1 將DataSet輸出成XML文件 • DataSet物件可以使用WriteXML()方法輸出成XML文件，如下所示： Dim sw As StreamWriter = _ New StreamWriter(Server.MapPath(xmlFile)) objDS.WriteXML(sw) • 上述程式碼在建立StreamWriter串流物件後，使用DataSet物件的WriteXML()方法輸出成XML文件檔案，參數是StreamWriter物件。

40. 9-5-2 讀入XML文件 • DataSet資料來源也可以是XML文件，我們可以使用DataSet物件的ReadXML()方法讀取XML文件的元素資料，如下所示： Dim sr As StreamReader = New _ StreamReader(Server.MapPath(xmlFile)) objDS.ReadXML(sr) • 上述程式碼在建立StreamReader串流物件後，使用DataSet物件的ReadXML()方法讀取XML文件檔案，參數是StreamReader物件。

41. 9-6 ASP.NET控制項與資料繫結 • 9-6-1 資料繫結的基礎 • 9-6-2 ArrayList物件的資料繫結 • 9-6-3 DataReader物件的資料繫結 • 9-6-4 DataSet物件的資料繫結

42. 9-6-1 資料繫結的基礎-伺服端資料繫結 • 伺服端資料繫結擁有高擴充性、可重複使用和容易維護的特點，ASP.NET的資料繫結就是.NET Framework的資料繫結技術，這是一種不同於微軟之前產品的資料繫結技術。 • 在.NET Framework的資料繫結技術是指將控制項屬性連結到任何可用「資料」（Data），在此的資料可以是單純資料、物件屬性，控制項名稱的集合物件等，.NET Framework可以將這些資料視為類別的屬性來存取。

43. 9-6-1 資料繫結的基礎-ASP.NET控制項與資料繫結 • ASP.NET控制項支援資料繫結，我們可以將不同資料來源的集合物件、陣列、DataReader或DataView物件整合到ASP.NET控制項。 • 不過，不是每一個ASP.NET控制項都支援資料繫結，只有擁有DataSource屬性的控制項，例如：ListBox、DropDownList、CheckBoxList、RadioButtonList、Select、Repeater、DataList、GridView等才可以使用。

44. 9-6-1 資料繫結的基礎-建立步驟 • 在ASP.NET控制項使用資料繫結技術的步驟，如下所示： • Step 1：定義資料來源和取得資料來源的資料物件。 • Step 2：指定控制項的DataSource屬性為資料來源的物件，以資料庫來說就是DataReader或DataView物件。 • Step 3：執行控制項的DataBind()方法建立資料繫結。

45. 9-6-2 ArrayList物件的資料繫結-說明 • 雖然ASP.NET資料繫結技術主要是使用在資料庫的記錄資料，事實上，資料繫結技術也可以使用在其他資料來源。 • 筆者分別使用ArrayList、DataReader和DataView物件作為資料來源，以資料繫結技術顯示List控制項的選項清單。

46. 9-6-2 ArrayList物件的資料繫結-步驟一 • 首先使用ArrayList物件的資料來源，在ListBox控制項顯示資料來源的選項清單，第一步是建立資料來源，如下所示： Dim names As ArrayList= New ArrayList() names.Add ("陳會安") names.Add ("江小魚") names.Add ("張無忌") names.Add ("楊過")

47. 9-6-2 ArrayList物件的資料繫結-步驟二 • 接著在第二步指定DataSource屬性為資料來源，如下所示： listbox.DataSource = names • 上述程式碼指定ListBox控制項的DataSource屬性為前面的ArrayList物件name，最後一個步驟是執行DataBind()方法，如下所示： listbox.DataBind()

48. 9-6-3 DataReader物件的資料繫結 • DataReader物件也可以作為資料繫結的資料來源，如下所示： dropdown.DataSource = objCmd.ExecuteReader( _ CommandBehavior.CloseConnection) dropdown.DataTextField = "Name" dropdown.DataBind() • 上述程式碼使用ExecuteReader()方法取得DataReader物件，參數指定Command物件執行SQL指令的行為值，可以使用此參數來取得特定資料或執行額外處理，以此例是額外關閉Connection物件。

49. 9-6-4 DataSet物件的資料繫結 • ADO.NET資料表查詢可以使用DataReader或DataSet物件，上一節是使用DataReader物件作為資料繫結的資料來源，DataSet物件則需使用DataView物件來作為資料來源，如下所示： objDataAdapter.Fill(objDS, "Users") rbt.DataSource = _ objDS.Tables("Users").DefaultView rbt.DataTextField = "Salary" rbt.DataBind()