How to store multiple database tables in a DataSet
A DataSet is not equivalent to the old RecordSet. Instead, a DataSet can contain numerous tables. This code shows you how to load tables from an Access database into a DataSet object and then display these tables with DataGrid.
<%@Page Language="VB"%>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.OleDb" %>
<script language="vb" runat="server">
Sub Page_Load()
Dim strConnect As String
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\edward\books2.mdb"
Dim strSelectBooks As String
strSelectBooks = "SELECT * FROM BookList WHERE ISBN LIKE '18610033%'"
Dim strSelectAuthors As String
strSelectAuthors = "SELECT * FROM BookAuthors WHERE ISBN LIKE '18610033%'"
Dim strSelectPrices As String
strSelectPrices = "SELECT * FROM BookPrices WHERE ISBN LIKE '18610033%'"
Dim objDataSet As New DataSet()
Dim objConnect As New OleDbConnection(strConnect)
Dim objCommand As New OleDbCommand()
objCommand.Connection = objConnect
objCommand.CommandType = CommandType.Text
objCommand.CommandText = strSelectBooks
Dim objDataAdapter As New OleDbDataAdapter()
objDataAdapter.SelectCommand = objCommand
objDataAdapter.Fill(objDataSet, "Books")
objCommand.CommandText = strSelectAuthors
objDataAdapter.Fill(objDataSet, "Authors")
objCommand.CommandText = strSelectPrices
objDataAdapter.Fill(objDataSet, "Prices")
Dim objRelation As DataRelation
objRelation = New DataRelation("BookAuthors", objDataSet.Tables("Books").Columns("ISBN"), objDataSet.Tables("Authors").Columns("ISBN"))
objDataSet.Relations.Add(objRelation)
objRelation = New DataRelation("BookPrices",objDataSet.Tables("Books").Columns("ISBN"),objDataSet.Tables("Prices").Columns("ISBN"))
objDataSet.Relations.Add(objRelation)
dgrTables.DataSource = objDataSet.Tables
dgrTables.DataBind()
dgrRelations.DataSource = objDataSet.Relations
dgrRelations.DataBind()
Dim objDataView As New DataView()
objDataView = objDataSet.Tables("Books").DefaultView
dgrBooksData.DataSource = objDataView
dgrBooksData.DataBind()
objDataView = objDataSet.Tables("Authors").DefaultView
dgrAuthorsData.DataSource = objDataView
dgrAuthorsData.DataBind()
objDataView = objDataSet.Tables("Prices").DefaultView
dgrPricesData.DataSource = objDataView
dgrPricesData.DataBind()
End Sub
</script>
<html>
<body>
<b>DataSet.Tables Collection</b>
<asp:datagrid id="dgrTables" runat="server" /><br />
<b>DataSet.Relations Collection</b>
<asp:datagrid id="dgrRelations" runat="server" /><br />
<b>Contents of DataSet.Tables("Books")</b>
<asp:datagrid id="dgrBooksData" runat="server" /><br />
<b>Contents of DataSet.Tables("Authors")</b>
<asp:datagrid id="dgrAuthorsData" runat="server" /><br />
<b>Contents of DataSet.Tables("Prices")</b>
<asp:datagrid id="dgrPricesData" runat="server" />
</body>
</html>
11:06:05 AM 
|
