按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
consistency check； so that the drawing date must exist in the database before you can add a
winner for that date。 We will define two relations： winners with draws and winners with persons。
Remember that the winners table is a cross…reference between the persons and draws table。
Follow these steps to define the relations：
1。 Double…click lotteryDataSet。xsd in the Solution Explorer。 You will see a message indi
cating that the Dataset Designer has no data。
2。 Drag and drop each of the three tables you created earlier from the Database Explorer
onto the surface of the Dataset Designer； as shown in Figure 14…6。 This automatically
adds default support for the three tables。
Figure 14…6。 Dataset Designer with the three tables
…………………………………………………………Page 410……………………………………………………………
388 CH AP T E R 1 4 ■ L E A R N I N G A B OU T R E L A TI O N AL DA TA B AS E D AT A
3。 To build a relation； right…click the data generator surface and choose Add Relation。
The Relation dialog box appears。 This dialog box allows you to associate two tables via
a specific field。
4。 As shown in Figure 14…7； specify winners as the parent table and persons as the child。
The key column is id。 Click OK to create the relation。
Figure 14…7。 Creating the winners and persons relation
The winners and draws relation is created in the same way as the winners and persons relation；
except that the columns linked are draw_date； as shown in Figure 14…8。
After you have created both relations； the Dataset Designer surface should look like
Figure 14…9。
Figure 14…9 illustrates a well…defined database structure that includes relations。 The structure
is very important for the Dataset Designer； because it defines how the generated code will appear。
Look closely at Figure 14…9 and notice how each table representation shows Fill； GetData（） at
the bottom。 The Fill（） and GetData（） methods are used to retrieve the data from the database
and convert it into data that Visual Basic can process。
…………………………………………………………Page 411……………………………………………………………
C HA P TE R 1 4 ■ L E AR N I N G AB O U T R E L AT IO N A L D AT AB A SE D A TA 389
Figure 14…8。 Creating the winners and draws relation
Figure 14…9。 Dataset Designer surface with all tables and relations
If you were to click the table adapter； the Dataset Designer would display the properties；
similar to Figure 14…10。
…………………………………………………………Page 412……………………………………………………………
390 CH AP T E R 1 4 ■ L E A R N I N G A B OU T R E L A TI O N AL DA TA B AS E D AT A
Figure 14…10。 Properties of Dataset Designer table structure
The properties show the exact syntax of the SQL INSERT and SELECT mands。 Remember
these two statements are used to add and select data from database tables。 This illustrates that
the Dataset Designer code is no different than the ADO code （except the dataset is a cache）。
Now consider the following code； generated by the Dataset Designer； to bind the columns
of the draws table to the generated data structure （in lotteryDataSet。Designer。vb）。
_
Private Sub InitAdapter（）
Me。_adapter = New Global。System。Data。SqlClient。SqlDataAdapter
Dim tableMapping As Global。System。Data。mon。DataTableMapping =
New Global。System。Data。mon。DataTableMapping
tableMapping。SourceTable = 〃Table〃
tableMapping。DataSetTable = 〃draws〃
tableMapping。ColumnMappings。Add（〃draw_date〃； 〃draw_date〃）
tableMapping。ColumnMappings。Add（〃first_number〃； 〃first_number〃）
tableMapping。ColumnMappings。Add（〃second_number〃； 〃second_number〃）
tableMapping。ColumnMappings。Add（〃third_number〃； 〃third_number〃）
tableMapping。ColumnMappings。Add（〃fourth_number〃； 〃fourth_number〃）
tableMapping。ColumnMappings。Add（〃fifth_number〃； 〃fifth_number〃）
tableMapping。ColumnMappings。Add（〃sixth_number〃； 〃sixth_number〃）
tableMapping。ColumnMappings。Add（〃bonus〃； 〃bonus〃）
Me。_adapter。TableMappings。Add（tableMapping）
Me。_adapter。Insertmand = New Global。System。Data。SqlClient。Sqlmand
Me。_adapter。Insertmand。Connection = Me。Connection
Me。_adapter。Insertmand。mandText = 〃INSERT INTO ＇dbo＇。＇draws＇
（＇draw_date＇； ＇first_number＇； ＇second_number＇； ＇third_n〃& _
〃umber＇； ＇fourth_number＇； ＇fifth_number＇； ＇sixth_number＇； ＇bonus＇）
…………………………………………………………Page 413……………………………………………………………
C HA P TE R 1 4 ■ L E AR N I N G AB O U T R E L AT IO N A L D AT AB A SE D A TA 391
VALUES （@draw_〃& _
〃date； @first_number； @second_number； @third_number； @fourth_number；
@fifth_numbe〃& _
〃r； @sixth_number； @bonus）〃
Me。_adapter。Insertmand。mandType = Global。System。Data。mandType。Text
Me。_adapter。Insertmand。Parameters。Add（New
Global。System。Data。SqlClient。SqlParameter（〃@draw_date〃；
Global。System。Data。SqlDbType。DateTime； 0；
Global。System。Data。ParameterDirection。Input； 0； 0； 〃draw_date〃；
Global。System。Data。DataRowVersion。Current； false； Nothing； 〃〃； 〃〃； 〃〃））
Me。_adapter。Insertmand。Parameters。Add（New
Global。System。Data。SqlClient。SqlParameter（〃@first_number〃；
Global。System。Data。SqlDbType。Int； 0；
Global。System。Data。ParameterDirection。Input； 0； 0； 〃first_number〃；
Global。System。Data。DataRow