友情提示：如果本网页打开太慢或显示不完整，请尝试鼠标右键“刷新”本网页！
VB2008从入门到精通(PDF格式英文版)-第73部分
快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部! 如果本书没有阅读完，想下次继续接着阅读，可使用上方 "收藏到我的浏览器" 功能 和 "加入书签" 功能！
Dim cmd As IDbmand = _
New Sqlmand（〃INSERT INTO draws （draw_date； first_number； second_number； 〃 _
& 〃third_number； fourth_number； 〃 _
& 〃fifth_number； sixth_number； bonus） 〃 _
& 〃VALUES （@pDrawDate； @pFirstNumber； @pSecondNumber； 〃 _
& 〃@pThirdNumber； @pFourthNumber； @pFifthNumber； @pSixthNumber； @pBonus）〃）
cmd。Connection = connection
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）
Dim param As IDbDataParameter = New SqlParameter（）
param。ParameterName = 〃@pFirstNumber〃
param。DbType = System。Data。DbType。Int32
param。Size = 4
param。SourceColumn = 〃first_number〃
param。Value = 1
cmd。Parameters。Add（param）
。 。 。
param = New SqlParameter（）
param。ParameterName = 〃@pBonus〃
param。DbType = System。Data。DbType。Int32
param。Size = 4
param。SourceColumn = 〃bonus〃
param。Value = 1
cmd。Parameters。Add（param）
Dim retval As Integer = cmd。ExecuteNonQuery（）
Console。WriteLine（〃retval （〃 & retval & 〃）〃）
connection。Close（）
To execute a mand； you need to instantiate a class that implements the IDbmand
interface； where the constructor parameter is the SQL statement that you want to execute。 The
SQL statement contains SQL parameters。 How a SQL parameter is defined often depends on
the ADO implementation。 The mand is associated with the database connection using
the cmd。Connection property。
Each parameter； regardless of the database driver used； is of type IDataParameter or
IDbDataParameter （which implements IDataParameter）。 The type associated with IDbDataParameter
is SqlParameter in this case； because we are using SQL Server。
…………………………………………………………Page 406……………………………………………………………
384 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
The properties of IDbDataParameter are as follows：
o ParameterName： Specifies the name of the parameter and must be prefixed with an at sign
（@） character。
o DbType： Specifies the type of the parameter。
o Size： Specifies the size of the parameter （in bytes）。
o SourceColumn： Specifies in which column the parameter will be stored。
o Value： Specifies the value for the parameter。
■Note As a general rule of thumb； stick to using and manipulating the standard ADO interfaces； rather
than the types specific to the ADO driver。
The example shows code that you would write when you want to explicitly define the
attributes of every parameter。 This was to allow you to see what is happening when parameters
are converted and stored in a table。 A simpler notation is as follows：
Dim connection As IDbConnection = _
New SqlConnection（DatabaseConsoleEx。My。Settings。Default。lotteryConnectionString）
connection。Open（）
Dim cmd As IDbmand = New Sqlmand（〃INSERT INTO draws （draw_date； 〃 _
& 〃first_number； second_number； third_number； fourth_number； 〃 _
& 〃fifth_number； sixth_number； bonus） VALUES （@pDrawDate； 〃 _
& 〃@pFirstNumber； @pSecondNumber； @pThirdNumber；〃 _
& 〃@pFourthNumber；@pFifthNumber；@pSixthNumber；@pBonus）〃）
cmd。Connection = connection
cmd。Parameters。Add（New SqlParameter（〃@pDrawDate〃； DateTime。Now））
cmd。Parameters。Add（New SqlParameter（〃@pFirstNumber〃； 10））
cmd。Parameters。Add（New SqlParameter（〃@pSecondNumber〃； 1））
cmd。Parameters。Add（New SqlParameter（〃@pThirdNumber〃； 30））
cmd。Parameters。Add（New SqlParameter（〃@pFourthNumber〃； 19））
cmd。Parameters。Add（New SqlParameter（〃@pFifthNumber〃； 2））
cmd。Parameters。Add（New SqlParameter（〃@pSixthNumber〃； 21））
cmd。Parameters。Add（new SqlParameter（〃@pBonus〃； 5））
Once you have defined the parameters and assigned them with a value； the SQL query can
be executed。 In the case of a SQL INSERT statement； no data will be returned； because you are
sending data from the application to the database。 Thus； you will need to execute the method
that does not expect any return data； which is ExecuteNonQuery（）； like this：
…………………………………………………………Page 407……………………………………………………………
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 385
Dim retval As Integer = cmd。ExecuteNonQuery（）
Console。WriteLine（〃retval （〃 & retval & 〃）〃）
ExecuteNonQuery（） returns the number of rows affected by the statement （which should be
1； because we’ve inserted one row）。
Selecting Data from a Table
After your tables have some data； you probably will want to retrieve the data。 To do that； you
use the SELECT statement。 Here is the code for viewing data in the draws table：
Dim connection As IDbConnection = _
New SqlConnection（DatabaseConsoleEx。My。Settings。Default。lotteryConnectionString）
connection。Open（）
Dim cmd As IDbmand = New Sqlmand（〃SELECT * FROM draws〃）
cmd。Connection = connection
Dim reader As IDataReader = cmd。ExecuteReader（）
While reader。Read（）
Console。WriteLine（〃（〃 & reader。GetDateTime（0）。ToString（） & 〃） 〃 _
& reader。GetInt32（1））
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 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 Data
快捷操作: 按键盘上方向键 ← 或 → 可快速上下翻页 按键盘上的 Enter 键可回到本书目录页 按键盘上方向键 ↑ 可回到本页顶部!
温馨提示： 温看小说的同时发表评论，说出自己的看法和其它小伙伴们分享也不错哦！发表书评还可以获得积分和经验奖励，认真写原创书评 被采纳为精评可以获得大量金币、积分和经验奖励哦！