VB2008从入门到精通(PDF格式英文版)-第138章
按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
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 Generated code is both a blessing and a curse。 Generated code hides plexity and makes it
simpler for you to get your job done。 But as you saw in the code excerpts； there is quite a bit going on behind
the scenes； and if you don’t understand ADO； you will not know what to do when things go wrong。 For
example； when is a connection to the database made？ The only way to know that is to look at the generated
source code and follow the ADO calls。 A connection to a database is made the first time you call one of
the SQL methods （for example； Insert（））； not when the adapter is initialized。
To retrieve and iterate the data in the table； use the following code。
Dim dataset As DatabaseConsoleEx。lotteryDataSet = _
New DatabaseConsoleEx。lotteryDataSet（）
Dim table As DatabaseConsoleEx。lotteryDataSetTableAdapters。drawsTableAdapter = _
New DatabaseConsoleEx。lotteryDataSetTableAdapters。drawsTableAdapter（）
Dim count As Integer = table。Fill（dataset。draws）
Console。WriteLine（〃Record count is （〃 & dataset。draws。Count & 〃）（〃 & count & 〃）〃）
For Each row As DatabaseConsoleEx。lotteryDataSet。drawsRow In dataset。draws
Console。WriteLine（〃Date （〃 ＋ row。draw_date。ToString（） & 〃） （〃 _
& row。first_number & 〃）〃）
Next
The variable table is initialized to an adapter that connects to the draws table。 The variable
dataset is an empty collection ready for the draws table。
To fill the data table in the dataset； the method table。Fill（） is called； and the destination
is the dataset。draws data table。 After having called Fill（）； the number of records read is returned
and assigned to count。
To iterate the individual rows； a For Each loop is used。 It references the type drawsRow； and
each instance of drawsRow has data members that represent the draw_date； first_number； and
other columns。 The iteration of the individual rows resembles the iteration of a collection。 The
ADO example used a While loop and required you to know which column was associated
with which SELECT field。
The Important Stuff to Remember
In this chapter； you learned about the basics of ADO and the Dataset Designer。 Here are
the main points to remember：
o The real problem when using a relational database and a programming language like
Visual Basic is the mismatch of set…based operations and individual object operations。
o To access a relational database； you can use ADO。 There is an ADO database
driver for each relational database。
o The Dataset Designer code is based on ADO； and thus if you understand ADO； you
will be able to understand how the Dataset Designer works and how it can be optimized。
…………………………………………………………Page 415……………………………………………………………
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 393
o When using ADO； the steps are typically to connect to a database； create a mand；
populate the parameters； execute the mand； retrieve the data （if necessary）； close
the mand； and close the connection。
o SQL is a language used to manipulate the tables of a relational database。 You need to
learn SQL on top of learning how to use ADO。
Some Things for You to Do
The following are two exercises for applying what you’ve learned in this chapter。
1。 The basis of the lottery application is defined in terms of a database； tables； and data。
Write a console application that p
