An introduction to database programming

acceptableseashoreΑσφάλεια

5 Νοε 2013 (πριν από 4 χρόνια και 5 μέρες)

109 εμφανίσεις

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
1

Chapter 12

An introduction to
database programming

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
2

Objectives

Knowledge



Explain how a table in a relational database is organized.



Explain how the tables in a relational database are related.



Describe the use of these SQL statements: Select, Insert, Update
,
and Delete.



Describe the use of these ADO.NET components: data adapter,
command, connection, data reader, dataset, data table.



Describe concurrency, optimistic concurrency control, and “last in
wins.”

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
3

The Products table in a Halloween database

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
4

How a table is organized



A
relational database
uses
tables
to store and manipulate data.



Each table consists of one or more
re
cords
, or
rows
, that
contain the data for a single entry.



Each row contains one or more
fields
, or
columns
, with each
column representing a single item of data.



Most tables contain a
primary key
that uniquely identifies each
row in the table.



Some databa
se management systems let you define one or more
non
-
primary keys
. In SQL Server, these keys are called
unique
keys
, and they’re implemented using
unique key constraints
.



A table can also be defined with one or more
indexes
to efficiently
access data base
d on the values in specific columns. An index is
automatically created for a table’s primary and non
-
primary keys.

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
5

Two related tables: Categories and Products

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
6

How the tables in a database are related



The tables in a relational database are related t
o each other
through their key columns. A column that identifies a related row
in another table is called a
foreign key
.



Usually, a foreign key corresponds to the primary key in the
related table. In SQL Server, however, a foreign key can also
correspond t
o a unique key in the related table.



When two tables are related via a foreign key, the table with the
foreign key is referred to as the
foreign key table
and the table
with the primary key is referred to as the
primary key table
.



The relationships betwee
n the tables in a database correspond to
the relationships between the entities they represent. The most
common type of relationship is a
one
-
to
-
many relationship
. A
table can also have a
one
-
to
-
one relationship
or a
many
-
to
-
many
relationship
with another
table.

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
7

The Server Explorer design view window for the
Products table

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
8

Common SQL Server data types



bit



char, varchar, text



datetime, smalldatetime



decimal, numeric



float, real



b
igint, int, smallint, tinyint



money, smallmoney

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
9

The tables that make up the Ha
lloween database

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
10

Simplified syntax of the Select statement

Select
column
-
1

[
,
column
-
2]...

From
table
-
1

[
Where
selection
-
criteria]

[
Order By
column
-
1

[
Asc
|
Desc
] [
,
column
-
2 [
Asc
|
Desc
]]...]

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
11

A Select statement that retrieves and sorts
data

Select Produc
tID, Name, UnitPrice

From Products

Where CategoryID = 'Props'

Order By UnitPrice

The result set defined by the Select statement

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
12

The syntax of the Select statement for joining two
tables


Select
column
-
list


From
table
-
1

[
Inner
]
Join
table
-
2


O
n
table
-
1.column
-
1 {
=
|
<
|
>
|
<=
|
>=
|
<>
} table
-
2.column
-
2

[
Where
selection
-
criteria]

[
Order By
column
-
list]

A Select statement that joins data from the
Products and Categories tables

Select ShortName, ProductID, Name, UnitPrice

From Products Inner Join Categori
es

On Categories.CategoryID = Products.CategoryID

Order By Categories.CategoryID

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
13

The result set defined by the Select statement

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
14

How to add a single row

The syntax of the Insert statement for adding a single row

Insert
[
Into
]

table
-
name

[
(
column
-
list
)
]

Values (
value
-
list
)

A statement that adds a single row to a table

Insert Into Categories (CategoryID, ShortName, LongName)

V
alues ("food", "Spooky Food",

"The very best in Halloween cuisine")

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
15

How to update rows

The syntax of the Update statement

Update
table
-
name

Set
expression
-
1 [
,
expression
-
2]...


[
Where
selection
-
criteria]

A statement that changes the value of the ShortName
column for a selected row

Update Categories

Set ShortNam
e = "Halloween cuisine"

Where CategoryID = "food"

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
16

How to delete rows

The syntax of the Delete statement

Delete
[
From
]

table
-
name


[
Where
selection
-
criteria]

A statement that deletes a specified category

Delete From Categories

Where CategoryID = "food"

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
17

Basic ADO.NET objects

.NET data provider
Dataset
Data adapter
Data table
Command
Connection
Database server
Database
Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
18

Two users who are working with copies of the
same data

Database server
DBMS
Products
table
User 2
Products
data table
User 1
Products
data table
Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
19

Concurrency concepts



When two or more users retrieve the data in the same row of a
database table at the same time, it is called
concurrency
. Because
ADO.NET u
ses a disconnected data architecture, the database
management system can’t prevent this from happening.



If two users try to update the same row in a database table at the
same time, the second user’s changes could overwrite the changes
made by the first us
er. Whether or not that happens depends on
the
concurrency control
that the programs use.



By default, ADO.NET uses
optimistic concurrency
. This means
that the program checks to see whether the database row that’s
going to be updated or deleted has been cha
nged since it was
retrieved. If it has, a
concurrency exception
occurs and the update
or deletion is refused. Then, the program should handle the
exception.

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
20

Concurrency concepts (continued)



If optimistic concurrency isn’t in effect, the operation proceeds
without throwing an exception. This is referred to as “
last in wins

because the last update overwrites any previous update. And this
can lead to errors in the database.

How to avoid concurrency errors



For many applications, concurrency errors rarely occur
. As a
result, optimistic concurrency is adequate.



If concurrency is likely to be a problem, a program can be
designed so it updates the database and refreshes the dataset
frequently. That way, concurrency errors are less likely to occur.



Another alternati
ve is to design a program so it retrieves and
updates just one row at a time. That way, there’s less chance that
two users will retrieve and update the same row at the same time.

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
21

Common property and methods of the
SqlConnection class

Property

Description

ConnectionString

Contains information that lets you connect to a
SQL Server database, including the server name,
the database name, and login information.

Method

Description

Open

O
pens a connection to a database.

Close

Closes a connection to a database.

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
22

Common properties of the SqlCommand class



Connection



CommandText



CommandType



Parameters

Common methods of the SqlCommand class



ExecuteReader



ExecuteNonQuery



ExecuteScalar

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
23

Common properties of the SqlParameter class

Property

Description

ParameterName

The name of the parameter.

Value

The value assigned to the parameter.

SqlDbType

The SQL data type for the parameter.

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
24

Common properties and methods of the
SqlDataReader class

Property

Description

Item

Accesses the column with the specified index or
name from the current row.

FieldCount

The number of columns in the current row.

Method

Description

Read

Reads the next row. R
eturns True if there are more
rows. Otherwise, returns False.

Close

Closes the data reader.

Murach’s ASP.NET 2.0/VB, C12

© 2006, Mike Murach & Associates, Inc.


Slide
25

Common properties of the Sq
lDataAdapter class



SelectCommand



DeleteCommand



InsertCommand



UpdateCommand

Common methods of the SqlDataAdapter class



Fill



Update