<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.OleDb" %>
<html>
<head runat=server>
<script language="VB" runat="Server">
Function convertDataReaderToDataSet(objReader As OleDbDataReader, name As String) As DataSet
Dim dataSet = New DataSet()
Dim i As Integer
Dim dataRow As DataRow
Dim column As DataColumn
Dim columnName As String
Dim schemaTable As DataTable = objReader.GetSchemaTable()
Dim dataTable As DataTable = New DataTable(name)
' 建立DataTable的欄位名稱與資料型態
For i = 0 To schemaTable.Rows.Count - 1
dataRow = schemaTable.Rows(i)
' 取得欄位名稱
columnName = dataRow("ColumnName")
' 新增到DataTable物件
column = New DataColumn(columnName, dataRow("DataType"))
dataTable.Columns.Add(column) ' 新增欄位
Next
dataSet.Tables.Add(dataTable) ' 新增到DataSet物件
' 將DataReader的記錄資料填入DataTable
While objReader.Read()
dataRow = dataTable.NewRow()
For i = 0 To objReader.FieldCount - 1
dataRow(i) = objReader.Item(i)
Next
dataTable.Rows.Add(dataRow)
dataTable.AcceptChanges() ' 更改DataSet物件
End While
Return dataSet
End Function
</script>
</head>
<body>
<%
Dim objCon As OleDbConnection
Dim objCmd As OleDbCommand
Dim objDataReader As OleDbDataReader
Dim objDataSet As DataSet
Dim objColumn As DataColumn
Dim objRow As DataRow
Dim i As Integer
' OLEDB提供者字串
Dim strDbCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.Mappath("Users.mdb")
' 建立Connection物件
objCon = New OleDbConnection(strDbCon)
objCon.Open() ' 開啟資料庫連結
' 建立Command物件的SQL指令
objCmd = New OleDbCommand("SELECT * FROM Users", objCon)
' 取得DataReader物件
objDataReader = objCmd.ExecuteReader()
' 將DataReader轉換成DataSet
objDataSet = convertDataReaderToDataSet(objDataReader, "Users")
' 顯示DataSet的記錄資料
Response.Write("<table border=1>")
Response.Write("<tr>")
For Each objColumn in objDataSet.Tables("Users").Columns
Response.Write("<td><b>" & objColumn.ColumnName & "</b></td>")
Next
Response.Write("</tr>")
For Each objRow In objDataSet.Tables("Users").Rows
Response.Write("<tr>")
For Each objColumn In objDataSet.Tables("Users").Columns
If objRow.IsNull(objColumn) = False Then
Response.Write("<td>" & objRow.Item(objColumn).toString() & "</td>")
Else
Response.Write("<td>---</td>")
End If
Next
Response.Write("</tr>")
Next
Response.Write("</table>")
objDataReader.Close() ' 關閉DataReader
objCon.Close() ' 關閉資料庫連結
%>
</body>
</html>
<%@ Import NameSpace="System.Data.OleDb" %>
<html>
<head runat=server>
<script language="VB" runat="Server">
Function convertDataReaderToDataSet(objReader As OleDbDataReader, name As String) As DataSet
Dim dataSet = New DataSet()
Dim i As Integer
Dim dataRow As DataRow
Dim column As DataColumn
Dim columnName As String
Dim schemaTable As DataTable = objReader.GetSchemaTable()
Dim dataTable As DataTable = New DataTable(name)
' 建立DataTable的欄位名稱與資料型態
For i = 0 To schemaTable.Rows.Count - 1
dataRow = schemaTable.Rows(i)
' 取得欄位名稱
columnName = dataRow("ColumnName")
' 新增到DataTable物件
column = New DataColumn(columnName, dataRow("DataType"))
dataTable.Columns.Add(column) ' 新增欄位
Next
dataSet.Tables.Add(dataTable) ' 新增到DataSet物件
' 將DataReader的記錄資料填入DataTable
While objReader.Read()
dataRow = dataTable.NewRow()
For i = 0 To objReader.FieldCount - 1
dataRow(i) = objReader.Item(i)
Next
dataTable.Rows.Add(dataRow)
dataTable.AcceptChanges() ' 更改DataSet物件
End While
Return dataSet
End Function
</script>
</head>
<body>
<%
Dim objCon As OleDbConnection
Dim objCmd As OleDbCommand
Dim objDataReader As OleDbDataReader
Dim objDataSet As DataSet
Dim objColumn As DataColumn
Dim objRow As DataRow
Dim i As Integer
' OLEDB提供者字串
Dim strDbCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.Mappath("Users.mdb")
' 建立Connection物件
objCon = New OleDbConnection(strDbCon)
objCon.Open() ' 開啟資料庫連結
' 建立Command物件的SQL指令
objCmd = New OleDbCommand("SELECT * FROM Users", objCon)
' 取得DataReader物件
objDataReader = objCmd.ExecuteReader()
' 將DataReader轉換成DataSet
objDataSet = convertDataReaderToDataSet(objDataReader, "Users")
' 顯示DataSet的記錄資料
Response.Write("<table border=1>")
Response.Write("<tr>")
For Each objColumn in objDataSet.Tables("Users").Columns
Response.Write("<td><b>" & objColumn.ColumnName & "</b></td>")
Next
Response.Write("</tr>")
For Each objRow In objDataSet.Tables("Users").Rows
Response.Write("<tr>")
For Each objColumn In objDataSet.Tables("Users").Columns
If objRow.IsNull(objColumn) = False Then
Response.Write("<td>" & objRow.Item(objColumn).toString() & "</td>")
Else
Response.Write("<td>---</td>")
End If
Next
Response.Write("</tr>")
Next
Response.Write("</table>")
objDataReader.Close() ' 關閉DataReader
objCon.Close() ' 關閉資料庫連結
%>
</body>
</html>