按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
Dim paramDate As IDbDataParameter = New SqlParameter（）
paramDate。ParameterName = 〃@pDrawDate〃
paramDate。DbType = System。Data。DbType。DateTime
paramDate。Size = 8
paramDate。SourceColumn = 〃draw_date〃
paramDate。Value = DateTime。Now
cmd。Parameters。Add（paramDate）
cmd。ExecuteNonQuery（）
connection。Close（）
As with the INSERT statement； you use ExecuteNonQuery（） with DELETE； which does not
return any results （except for the number of rows affected by the mand）。
Recapping ADO Usage
Looking at all of the code presented in this section； you should notice the following points
about using ADO directly：
o There are general interfaces； implemented by a specific library。
o The IDbConnection and IDbmand interfaces are implemented by all ADO drivers。
o SqlConnection； Sqlmand； and SqlParameter are specific classes from the database driver。
o The database code involves opening a connection； defining a SQL mand such as
INSERT； SELECT； and so on； assigning the SQL parameters； executing the SQL mand；
and then closing the connection。
■Note For more details on SQL； see Wikipedia’s SQL entry （http：//en。wikipedia。org/wiki/SQL） and
W3School’s SQL tutorial （http：//w3schools。/sql/default。asp）。 These provide a good expla
nation of the basics of SQL。 The MSDN documentation is fairly good as well。
Next； let’s take a look at another Visual Studio tool for working with database applications。
…………………………………………………………Page 409……………………………………………………………
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 387
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〃； 〃dr