📄 table-mappings.aspx
字号:
<%@Page Language="VB"%>
<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.OleDb" %>
<%@Import Namespace="System.Data.Common" %>
<%@ Register TagPrefix="wrox" TagName="connect" Src="..\global\connect-strings.ascx" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html><head>
<title>Using Default and Specific Table and Column Mappings</title>
<!-- #include file="..\global\style.inc" -->
</head>
<body bgcolor="#ffffff">
<span class="heading">Using Default and Specific Table and Column Mappings</span><hr />
<!--------------------------------------------------------------------------->
<%'-- insert connection string script --%>
<wrox:connect id="ctlConnectStrings" runat="server"/>
<div>Connection string: <b><span id="outConnect" runat="server"></span></b></div>
<div>Books SELECT command: <b><span id="outSelectBooks" runat="server"></span></b></div>
<div>Authors SELECT command: <b><span id="outSelectAuthors" runat="server"></span></b></div>
<div id="outError" runat="server"> </div>
<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(0)</b>
<asp:datagrid id="dgrBooksData" runat="server" /><br />
<b>Contents of DataSet.Tables(1)</b>
<asp:datagrid id="dgrAuthorsData" runat="server" /><br />
<script language="vb" runat="server">
Sub Page_Load()
'get connection string from ..\global\connect-strings.ascx user control
Dim strConnect = ctlConnectStrings.OLEDBConnectionString
outConnect.innerText = strConnect 'and display it
'specify the SELECT statement to extract the BookList data
Dim strSelectBooks As String
strSelectBooks = "SELECT * FROM BookList WHERE ISBN LIKE '18610033%'"
outSelectBooks.innerText = strSelectBooks 'and display it
'specify the SELECT statement to extract the BookAuthor data
Dim strSelectAuthors As String
strSelectAuthors = "SELECT * FROM BookAuthors WHERE ISBN LIKE '18610033%'"
outSelectAuthors.innerText = strSelectAuthors 'and display it
'create a new Connection object using the connection string
Dim objConnect As New OleDbConnection(strConnect)
'create a new Command object
Dim objCommand As New OleDbCommand()
'set the properties
objCommand.Connection = objConnect
objCommand.CommandType = CommandType.Text
objCommand.CommandText = strSelectBooks
'create a new DataAdapter object
Dim objDataAdapter As New OleDbDataAdapter
'and assign the Command object to it
objDataAdapter.SelectCommand = objCommand
'declare a variable to hold a DataTableMapping object
Dim objTableMapping As DataTableMapping
'add the default table mapping "Table" - this table name will be used
'if you don't provide a table name when filling the DataSet
objTableMapping = objDataAdapter.TableMappings.Add("Table", "DefaultBookList")
'now add the column mappings for this table
With objTableMapping.ColumnMappings
.Add("ISBN", "BookCode")
.Add("Title", "BookTitle")
.Add("PublicationDate", "Published")
End With
'add a table mapping so that data from the table named "BookAuthors" in
'the database will be placed in a table named "AuthorList"
objTableMapping = objDataAdapter.TableMappings.Add("BookAuthors", "AuthorList")
'add the column mappings for this table
With objTableMapping.ColumnMappings
.Add("ISBN", "BookCode")
.Add("FirstName", "Forename")
.Add("LastName", "Surname")
End With
'new we're ready to fill a DataSet object from the database
'create a new DataSet object
Dim objDataSet As New DataSet()
Try
'get the data from the "BookList" table in the database and
'put it into a the default table in the DataSet object
objDataAdapter.Fill(objDataSet)
'change the SELECT statement in the Command object
objCommand.CommandText = strSelectAuthors
'then get data from "BookAuthors" table in the database
'and put it into the mapped table in the DataSet
objDataAdapter.Fill(objDataSet, "BookAuthors")
Catch objError As Exception
'display error details
outError.innerHTML = "<b>* Error while accesing data</b>.<br />" _
& objError.Message & "<br />" & objError.Source
Exit Sub ' and stop execution
End Try
'declare a variable to hold a DataRelation object
Dim objRelation As DataRelation
'create a Relation object to link the two tables
'note that it uses the new mapped table and column names
objRelation = New DataRelation("BookAuthors", _
objDataSet.Tables("DefaultBookList").Columns("BookCode"), _
objDataSet.Tables("AuthorList").Columns("BookCode"))
'and add it to the DataSet object's Relations collection
objDataSet.Relations.Add(objRelation)
'now we're ready to display the contents of the DataSet object
'bind the collection of Tables to the first DataGrid on the page
dgrTables.DataSource = objDataSet.Tables
dgrTables.DataBind()
'bind the collection of Relations to the second DataGrid on the page
dgrRelations.DataSource = objDataSet.Relations
dgrRelations.DataBind()
'create a DataView object to use with the tables in the DataSet
Dim objDataView As New DataView
'get the default view of the default table into the DataView object
objDataView = objDataSet.Tables("DefaultBookList").DefaultView
'and bind it to the third DataGrid on the page
dgrBooksData.DataSource = objDataView
dgrBooksData.DataBind()
'then do the same for the mapped BookAuthors table
objDataView = objDataSet.Tables("AuthorList").DefaultView
dgrAuthorsData.DataSource = objDataView
dgrAuthorsData.DataBind()
End Sub
</script>
<!--------------------------------------------------------------------------->
<!-- #include file="..\global\foot.inc" -->
</body>
</html>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -