按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
End While
reader。Close（）
connection。Close（）
To select data； the steps are to open a connection； create a mand； and execute the
mand。 In the example； the SELECT statement did not have any parameters。 SELECT * means
to select all columns。 Alternatively； you could define identifiers to select specific columns。
When you are using a SELECT statement； the server will return data。 To read the returned
data； you call the method ExecuteReader（）； which will also execute the mand。 This is different
from the ExecuteNonQuery（） method you use to insert data； mainly in that ExecuteReader（） returns
an instance of IDataReader。 IDataReader is an interface used to iterate individual records； which
gives you the chance to retrieve the individual fields of the records； and you can examine the
data in the columns for each record。
To access the fields； use the appropriate GetNNN （） method； with the index of the field。
Knowing which index to use is a bit perplexing。 The index of the appropriate field is related to
the position of the column in the SELECT mand （though； in this case； we’ve selected all the
data with *； so it depends on the position of the column in the table）。 For example； the following
code retrieves the fourth column from a result set。
reader。GetDouble（3）
In our example； we obtain the date and the first draw number。
…………………………………………………………Page 408……………………………………………………………
386 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
Deleting Data from the Database
Of course； you may need to delete data from table。 The SQL statement for removing data from
a table is DELETE。 The following example deletes a particular lottery drawing entry from the
draws table。
Dim cmd As IDbmand = _
New Sqlmand（〃DELETE FROM draws WHERE draw_date=@pDrawDate〃； _
CType（connection； SqlConnection））
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 m