按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
a database。 What is frustrating about database types is that they are similar but not identical to
Visual Basic types。 To make things even more frustrating； not all database implementations
have the exact same types。 Fortunately； if you use the Visual Studio tools； the wizard will map
a specific database type to a Visual Basic type。
■Tip The Microsoft Visual Studio documentation has an excellent reference on the various data types
and their accuracy。 See the “Data Types” section of the Microsoft SQL Server Books Online documentation
（http：//msdn2。microsoft。/en…us/library/ms130214。aspx）。
Draws Table
The draws table contains all of the drawn lottery numbers。 Table 14…1 shows the column names
and types for this table。
…………………………………………………………Page 402……………………………………………………………
380 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
Table 14…1。 Draws Table Columns
Name Type
draw_date datetime
first_number int
second_number int
third_number int
fourth_number int
fifth_number int
sixth_number int
bonus int
The draw_date column holds the date of the draw。 The declared type is datetime； which
is like the datetime type in 。 However； you will need to be careful in mapping types； as
explained in Chapter 3。
The rest of the columns represent a number in the winning draw； including the bonus
number。 As in Visual Basic； SQL Server includes various numeric types。 The number columns
in the draws table are declared as the int type。
■Note The one SQL Server numeric type that does not exist in is numeric。 This type behaves like the
decimal type in ； except for the precision。 With numeric； you can specify the number of digits before
and after the decimal point。
Persons Table
The persons table lists all of the people who have won a lottery drawing。 Table 14…2 shows the
column names and types for the persons table。
Table 14…2。 Persons Table Columns
Name Type
id uniqueidentifier
first_name nvarchar（100）
last_name nvarchar（100）
The persons table is a collection of people with their first names and last names。 The challenge
in a relational database is uniquely identifying a user。 Think of it as trying to define a unique
hash code。 The solution most databases use is a number。 When you have millions of records； a
…………………………………………………………Page 403……………………………………………………………
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 381
number might not be adequate as a unique identifier。 In that case； you can use the SQL Server
uniqueidentifier type； as we’re doing for the id column of the persons table。
The first_name and last_name columns both have the type nvarchar（100）。 A string in a
database behaves like a number type； in that strings have length limits。 In the example； we use
the nvarchar type； for a variable…length string with a maximum length of 100 characters。 In
contrast； specifying char（100） would give you a string 100 characters long； regardless of how
many bytes contain letters。 If the entry in a char column has fewer characters than specified；
the remainder of the char string is filled with space characters； by default。
Winners Table
The winners table matches the winning people to their lottery drawing。 Table 14…3 shows the
column names and types for the winners table。
Table 14…3。 Winners Table Columns
Name Type
id uniqueidentifier
draw_date datetime
Both columns are the types of the specific columns in the tables being referenced。 The
idea is to use the winners table in conjunction with the persons table and the draws table to
create the list that shows who won which lottery drawing and their numbers。
After you’ve created the three tables； your Database Explorer will resemble Figure 14…5。
Figure 14…5。 Modified database structure with the added tables
Now that we have a database with some tables； let’s see how to access that database
directly； using ADO。
…………………………………………………………Page 404……………………………………………………………
382 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
Accessing the Database Using ADO
Accessing the database directly using ADO involves using the ADO interfaces。 The
first step is to define a connection。 Once the connection has been established； you can manip
ulate the tables in the database—to add； remove； and update records。
Now we will continue with the sample lottery database created in the previous section。
We’ll write code to add； select； and delete records。
Connecting to a Database
Define a database connection with the following code； which illustrates a general approach
（added to the DatabaseConsoleEx application）。
Imports System。Data。SqlClient
。 。 。
Dim connection As IDbConnection = _
New SqlConnection（DatabaseConsoleEx。My。Settings。Default。lotteryConnectionString）
We need to import the System。Data。SqlClient namespace because it contains the classes
to access and use SQL Server （that is； the ADO driver）。
The variable connection is a SqlConnection object； which is specifically for accessing SQL
Server。 Think of it as picking up the telephone and hearing the telephone tone。 The connection
requires a username； password； and the name of the database to which you want to connect； which
are bundled into a connection string。 That information is stored in lotteryConnectionString；
which was defined when you configured the data source in Visual Basic Express。
Once you have a connection instance； you can create a live connection； which is akin to
dialing a telephone number and hearing that telephone ring。 Here is the code for opening
the connection：
connection。Open（）
Now you can work with the tables in the database。
Closing a Database Connection
After having processed your SQL statements； you should close the connection to indicate that
you are finished using the database。 Here’s how：
connection。Close（）
It is good practice to open and close database connections as quickly as possible。 You
should open a connection only when you are absolutely ready； and close it as soon as you are
finished with it。
Adding Table Data
The draws database table you created earlier is empty。 Now we will add some content。 To add
data to a database using SQL； use the SQL INSERT mand； as follows：
…………………………………………………………Page 405……………………………………………………………
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 383
Imports System。Data。SqlClient
。 。 。
Dim cmd As IDbmand = _
New Sqlmand（〃INSERT INTO draws （draw_date； first_number； second_number； 〃 _
& 〃third_nu