按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
■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； 〃 _