VB2008从入门到精通(PDF格式英文版)-第110部分
按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
Using the Dataset Designer
The Dataset Designer is a utility provided by Visual Basic Express to help you write database
applications。 MSDN provides a detailed tutorial on how to create client data applications
（http：//msdn2。microsoft。/en…us/library/h0y4a0f6。aspx）。 The tutorial describes how to
click options and use the wizards； but it does not explain some of the underlying details。 Here；
we’ll use the Dataset Designer to set up the relations between the lottery database tables； and
take a look at the code that is generated automatically to create those relations。
A dataset is an in…memory representation of the data in the database。 It is used for manip
ulating the data away from the database server before you update the database。 This disconnected
form of working with the data improves performance； because you reduce the number of times
you need to query the database server。
Building Relations Between Tables
The first step in using the Dataset Designer is to convert the tables in the Database Explorer to
something that the Dataset Designer can use。 To do this； from the Solution Explorer； double
click the file that has the extension 。xsd—lotteryDataSet。xsd in our example。 The 。xsd file is
the XML Schema Definition file； which translates the database’s datasets into XML files。
The lotteryDataSet。xsd file has a number of child files； which you can open and inspect。
These files are part of a collection that is used by the Dataset Designer。 The only file that you
can modify is lotteryDataSet。vb （which you can view by right…clicking lotteryDataSet。xsd
and selecting View Code）。 The other files are managed by the Dataset Designer。
We want to build relations between the three tables。 Relations are important because they
allow you to maintain database consistency。 For example； imagine adding a winner to the
winners table for a lottery drawing date that does not exist。 Using relations； you can enforce a
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。DataRowVersion。Current； false； Nothing； 〃〃； 〃〃； 〃〃））
。 。 。
End Sub
The bolded code shows how close the generated code is to a variation of the code used to
insert data in the ADO section。 The generated code is more explicit and verbose than the
previous code； but that does not matter； since you are not supposed to edit the generated code。
You are supposed to use the Dataset Designer。
■Note The Dataset Designer gives you the advantage of not having to provide bindings between Visual
Basic and a database； and an easy binding between a user interface and the database。 By providing a GUI
where you can drag； drop； and so on； the Dataset Designer just makes it much simpler to write that code。
Using the Generated Code
Using the code generated by the Dataset Designer is easy； as long as you know what is going on。
The Dataset Designer generates two major pieces of code： a table adapter and a dataset。 The
table adapter is code used to interact with the table directly and is the link between your code
and the database。 If you wanted to add； select； or delete items； use the table adapter。 When you
select data； the data will fill a dataset。
The following is the code to insert a record into the draws table （in the DatabaseConsoleEx
application）。
Dim table As DatabaseConsoleEx。lotteryDataSetTableAdapters。drawsTableAdapter = _
New DatabaseConsoleEx。lotteryDataSetTableAdapters。drawsTableAdapter（）
table。Insert（DateTime。Now； 2； 12； 14； 31； 18； 4； 20）
The code is a trivial two…liner。 It instantiates the drawsTableAdapter； and then calls the
Insert（） method to add a record。 Connecting to the database； executing the mand； and
assigning the parameters are done automatically。
…………………………………………………………Page 414……………………………………………………………
392 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
■Note
