Transcript Ch01 slides

Chapter 1
An introduction to
relational databases
and SQL
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 1
Objectives
Knowledge
 Identify the three main hardware components of a client/server
system.
 Describe the way a client accesses the database on a server using
these terms: application software, data access API, database
management system, SQL query, and query results.
 Describe the way a database is organized using these terms: tables,
columns, rows, and cells.
 Describe how the tables in a relational database are related using
these terms: primary key and foreign key.
 Identify the three types of relationships that can exist between two
tables.
 Describe the way the columns in a table are defined using these
terms: data type, null value, default value, and identity column.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 2
Objectives (continued)
 Describe the relationship between standard SQL and Microsoft
SQL Server’s Transact-SQL.
 Describe the difference between DML statements and DDL
statements.
 Describe the difference between an action query and a SELECT
query.
 List three coding techniques that can make your SQL code easier
to read and maintain.
 Explain how views and stored procedures differ from SQL
statements that are issued from an application program.
 Describe the use of command and connection objects when .NET
applications access a SQL Server database.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 3
A simple client/server system
Database
server
Network
Client
Client
Client
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 4
The three hardware components of a client/server
system
 The clients are the PCs, Macintoshes, or workstations of the
system.
 The server is a computer that stores the files and databases of the
system and provides services to the clients. When it stores
databases, it’s often referred to as a database server.
 The network consists of the cabling, communication lines, and
other components that connect the clients and the servers of the
system.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 5
Client/server system implementations
 In a simple client/server system, the server is typically a highpowered PC that communicates with the clients over a local area
network (LAN).
 The server can also be a midrange system, like an IBM iSeries or a
Unix system, or it can be a mainframe system.
 A client/server system can also consist of one or more PC-based
systems, one or more midrange systems, and a mainframe system
in dispersed geographical locations. This is commonly referred to
as an enterprise system.
 Individual systems and LANs can be connected and share data
over larger private networks, such as a wide area network (WAN),
or a public network like the Internet.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 6
Client software, server software, and the SQL
interface
SQL queries
Results
Client
Application software
Data access API
Murach’s SQL Server 2008, C1
Database server
Database management system
Database
© 2008, Mike Murach & Associates, Inc.
Slide 7
Server software
 To store and manage databases, each server requires a database
management system (DBMS) like Microsoft SQL Server.
 The processing that’s done by the DBMS is typically referred to
as back-end processing, and the database server is referred to as
the back end.
Client software
 The application software does the work that the user wants to do.
 The data access API (application programming interface)
provides the interface between the application program and the
DBMS.
 The processing that’s done by the client software is typically
referred to as front-end processing, and the client is typically
referred to as the front end.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 8
The SQL interface
 The application software communicates with the DBMS by sending
SQL queries through the data access API.
 When the DBMS receives a query, it provides a service like
returning the requested data (the query results) to the client.
 SQL stands for Structured Query Language, which is the standard
language for working with a relational database.
Client/server versus file-handling systems
 In a client/server system, the processing done by an application is
typically divided between the client and the server.
 In a file-handling system, all of the processing is done on the clients.
The clients may access data that’s stored in files on the server, but
none of the processing is done by the server.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 9
A Windows-based system that uses an
application server
User request
SQL queries
Response
Client
User interface
Murach’s SQL Server 2008, C1
Results
Application server
Business components
© 2008, Mike Murach & Associates, Inc.
Database server
DBMS
Database
Slide 10
A simple web-based system
User request
User request
SQL queries
Internet
Response
Client
Web browser
Murach’s SQL Server 2008, C1
Results
Response
Web server
Web applications
Web services
© 2008, Mike Murach & Associates, Inc.
Database server
DBMS
Database
Slide 11
Other client/server system architectures
 In addition to a database server and clients, a client/server system
can also include additional servers, such as application servers
and web servers.
 Application servers are typically used to store business
components that do part of the application processing. In
particular, these components are used to process database requests
from the client.
 Web servers are typically used to store web applications and web
services. These are like standard applications and business
components, but they’re designed to run on a web server.
 In a web-based system, a web browser running on a client sends a
request to a web server over the Internet. Then, the web server
passes any requests for data on to the database server.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 12
The Vendors table in an Accounts Payable
database
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 13
How a database table is organized
 A relational database consists of tables. Tables consist of rows
and columns, which can also be referred to as records and fields.
 A table is typically modeled after a real-world entity, such as an
invoice or a vendor.
 A column represents some attribute of the entity, such as the
amount of an invoice or a vendor’s address.
 A row contains a set of values for a single instance of the entity,
such as one invoice or one vendor.
 The intersection of a row and a column is sometimes called a cell.
A cell stores a single value.
 Most tables have a primary key that uniquely identifies each row
in the table. If a primary key consists of two or more columns, it’s
called a composite primary key.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 14
How a database table is organized (continued)
 Some database management systems also let you define one or
more non-primary keys. Like a primary key, a non-primary key
uniquely identifies each row in the table.
 In SQL Server, non-primary keys are called unique keys.
 A table can also be defined with one or more indexes. An index
provides an efficient way to access data from a table based on the
values in specific columns.
 An index is automatically created for a table’s primary and nonprimary keys.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 15
The relationship between the Vendors and
Invoices tables in the database
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 16
How the tables in a relational database are related
 The tables in a relational database are related to each other through
their key columns. A foreign key identifies a primary key in
another table. A table may contain one or more foreign keys.
 When you define a foreign key for a table in SQL Server, you
can’t add rows to the table with the foreign key unless there’s a
matching primary key in the related table.
 The relationships between 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 SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 17
The columns of the Invoices table
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 18
Common SQL Server data types
Type
bit
int, bigint, smallint, tinyint
money, smallmoney
decimal, numeric
float, real
datetime, smalldatetime
char, varchar
nchar, nvarchar
Murach’s SQL Server 2008, C1
Description
A value of 1 or 0 for True or False.
Integer values of various sizes.
Monetary values that are accurate to
four decimal places.
Decimal values that are accurate to the
least significant digit.
Floating-point values that contain an
approximation of a decimal value.
Dates and times.
A string of letters, symbols, and
numbers in the ASCII character set.
A string of letters, symbols, and
numbers in the Unicode character set.
© 2008, Mike Murach & Associates, Inc.
Slide 19
How the columns in a table are defined
 The data type that’s assigned to a column determines the type and
size of the information that can be stored in the column.
 Each column definition indicates whether or not it can contain null
values. A null value indicates that the value of the column is
unknown.
 A column can also be defined with a default value. Then, that value
is used if another value isn’t provided when a row is added to the
table.
 A column can also be defined as an identity column. An identity
column is a numeric column whose value is generated
automatically when a row is added to the table.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 20
A comparison of relational databases and
conventional file systems
Feature
Definition
File system
Each program that uses
the file must define the
file and its record layout
Maintenance
If the file definition
changes, each program
that uses the file must be
modified
Each program that
updates a file must
include code to check for
valid data
Validity
checking
Murach’s SQL Server 2008, C1
Relational database
Tables, rows, and
columns are defined
within the database and
can be accessed by name
Programs can be used
without modification
when the definition of a
table changes
Can include checks for
valid data
© 2008, Mike Murach & Associates, Inc.
Slide 21
A comparison of relational databases and
conventional file systems (continued)
Feature
Relationships
File system
Each program must
provide for and enforce
relationships between
files
Data access
Each I/O operation
targets a specific record
in a file based on its
relative position in the
file or its key value
Murach’s SQL Server 2008, C1
Relational database
Can enforce relationships
between tables using
foreign keys; ad hoc
relationships can also be
used
A program can use SQL
to access selected data in
one or more tables of a
database
© 2008, Mike Murach & Associates, Inc.
Slide 22
A comparison of relational databases and
other database systems
Feature
Supported
relationships
Data access
Murach’s SQL Server 2008, C1
Hierarchical
database
One-to-many
only
Network
database
One-to-many,
one-to-one, and
many-to-many
Relational
database
One-to-many,
one-to-one, and
many-to-many;
ad hoc
relationships can
also be used
Programs must
Programs must
Programs can
include code to
include code to
access data
navigate through navigate through without knowing
the physical
the physical
its physical
structure of the
structure of the
structure
database
database
© 2008, Mike Murach & Associates, Inc.
Slide 23
A comparison of relational databases and other
database systems (continued)
Hierarchical
Feature
database
Maintenance New and
modified
relationships can
be difficult to
implement in
application
programs
Murach’s SQL Server 2008, C1
Network
database
New and
modified
relationships can
be difficult to
implement in
application
programs
© 2008, Mike Murach & Associates, Inc.
Relational
database
Programs can be
used without
modification
when the
definition of a
table changes
Slide 24
How relational databases compare to other
data models
 To work with any of the data models other than the relational
database model, you must know the physical structure of the data
and the relationships between the files or tables.
 Because relationships are difficult to implement in a conventional
file system, redundant data is often stored in these types of files.
 The hierarchical database model provides only for one-to-many
relationships, called parent/child relationships.
 The network database model can accommodate any type of
relationship.
 Relational databases are less efficient than the other data models
because they require more system resources. However, their
flexibility and ease of use typically outweighs this inefficiency.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 25
Important events in the history of SQL
Year Event
1970 Dr. E. F. Codd developed the relational database model.
1978 IBM developed the predecessor to SQL, called Structured
English Query Language (SEQUEL). This language was used
on a database system called System/R, but neither the system
nor the query language was ever released.
1979 Relational Software, Inc. (later renamed Oracle) released the
first relational DBMS, Oracle.
1982 IBM released their first relational database system, SQL/DS
(SQL/Data System).
1985 IBM released DB2 (Database 2).
1987 Microsoft released SQL Server.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 26
Important events in the history of SQL (continued)
Year Event
1989 The American National Standards Institute (ANSI) published
the first set of standards for a database query language, called
ANSI/ISO SQL-89, or SQL1.
1992 ANSI published standards (ANSI/ISO SQL-92, or SQL2) that
were more stringent than SQL1 and incorporated many new
features. These standards introduced levels of compliance that
indicated the extent to which a dialect met the standards.
1999 ANSI published SQL3 (ANSI/ISO SQL-99). These standards
incorporated new features, including support for objects.
Levels of compliance were replaced by a core specification
along with specifications for nine additional packages.
2003 ANSI published SQL4 (ANSI/ISO SQL:2003). These
standards introduced XML-related features, standardized
sequences, and identity columns.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 27
The ANSI SQL standards
 SQL-92 initially provided for three levels of compliance, or levels of
conformance: entry, intermediate, and full. A transitional level was
later added between the entry and intermediate levels because the
jump between those levels was too great.
 SQL:1999 includes a core specification that defines the essential
elements for compliance, plus nine packages. Each package is
designed to serve a specific market niche.
 Although SQL is a standard language, each vendor has its own SQL
dialect, or variant, that may include extensions to the standards.
SQL Server’s SQL dialect is called Transact-SQL.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 28
How knowing “standard SQL” helps you
 The most basic SQL statements are the same for all SQL
dialects.
 Once you have learned one SQL dialect, you can easily learn
other dialects.
How knowing “standard SQL” does not help you
 Any non-trivial application will require modification when
moved from one SQL database to another.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 29
Features of Oracle, DB2, and SQL Server
Category
Released
Current
version
Platforms
Oracle
1979
Oracle 11g
IBM
1985
DB2 9
Microsoft
1987
SQL Server 2008
Unix
OS/390
Windows
Windows
Strengths
Typical
system
Reliable
Medium to very
large
Network-based
Mission-critical
Enterprise-wide
OS/390, z/OS,
and AIX
Unix
Windows
Reliable
Large to very
large
Centralized
architecture
Mission-critical
Enterprise-wide
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Easy to use
Small to medium
Network-based
Slide 30
A comparison of Oracle, DB2, and SQL Server
 Oracle is typically used for large, mission-critical, systems that run
on one or more Unix servers.
 DB2 is typically used for large, mission-critical systems that run
on legacy IBM mainframe systems using the z/OS or OS/390
operating system.
 SQL Server is typically used for small- to medium-sized systems
that run on one or more Windows servers.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 31
SQL statements used to work with data (DML)
Statement
SELECT
INSERT
UPDATE
DELETE
Murach’s SQL Server 2008, C1
Description
Retrieves data from one or more tables.
Adds one or more new rows to a table.
Changes one or more existing rows in a table.
Deletes one or more existing rows from a table.
© 2008, Mike Murach & Associates, Inc.
Slide 32
SQL statements used to work with database
objects (DDL)
Statement
CREATE DATABASE
CREATE TABLE
CREATE INDEX
ALTER TABLE
DROP DATABASE
DROP TABLE
DROP INDEX
Murach’s SQL Server 2008, C1
Description
Creates a new database.
Creates a new table in a database.
Creates a new index for a table.
Changes the structure of an existing table.
Deletes an existing database.
Deletes an existing table.
Deletes an existing index.
© 2008, Mike Murach & Associates, Inc.
Slide 33
Two types of SQL statements
 The SQL statements can be divided into two categories: the data
manipulation language (DML) and the data definition language
(DDL).
 The DML statements let you work with the data in the database.
 The DDL statements let you work with the objects in the
database.
 SQL programmers typically work with the DML statements,
while database administrators (DBAs) use the DDL statements.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 34
A statement that creates a new database
CREATE DATABASE AP
A statement that creates a new table
CREATE TABLE Invoices
(InvoiceID
INT
NOT NULL IDENTITY PRIMARY KEY,
VendorID
INT
NOT NULL
REFERENCES Vendors(VendorID),
InvoiceNumber
VARCHAR(50)
NOT NULL,
InvoiceDate
SMALLDATETIME NOT NULL,
InvoiceTotal
MONEY
NOT NULL,
PaymentTotal
MONEY
NOT NULL DEFAULT 0,
CreditTotal
MONEY
NOT NULL DEFAULT 0,
TermsID
INT
NOT NULL
REFERENCES Terms(TermsID),
InvoiceDueDate SMALLDATETIME NOT NULL,
PaymentDate
SMALLDATETIME NULL)
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 35
A statement that adds a new column to the table
ALTER TABLE Invoices
ADD BalanceDue MONEY NOT NULL
A statement that deletes the new column
ALTER TABLE Invoices
DROP COLUMN BalanceDue
A statement that creates an index on the table
CREATE INDEX IX_Invoices_VendorID
ON Invoices (VendorID)
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 36
How to query a single table
 You use the SELECT statement to retrieve selected columns and
rows from a base table.
 The result of a SELECT statement is a result table, or result set.
 A result set can include calculated values that are calculated
from columns in the table.
 The execution of a SELECT statement is commonly referred to
as a query.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 37
The Invoices base table
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 38
A SELECT statement that retrieves and sorts
selected columns and rows from the Invoices table
SELECT InvoiceNumber, InvoiceDate, InvoiceTotal,
PaymentTotal, CreditTotal,
InvoiceTotal – PaymentTotal – CreditTotal
AS BalanceDue
FROM Invoices
WHERE InvoiceTotal – PaymentTotal – CreditTotal > 0
ORDER BY InvoiceDate
The result set defined by the SELECT statement
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 39
A SELECT statement that joins data from the
Vendors and Invoices tables
SELECT VendorName, InvoiceNumber, InvoiceDate,
InvoiceTotal
FROM Vendors INNER JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
WHERE InvoiceTotal >= 500
ORDER BY VendorName, InvoiceTotal DESC
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 40
The result set defined by the SELECT statement
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 41
How to join data from two or more tables
 A join lets you combine data from two or more tables into a
single result set.
 The most common type of join is an inner join. This type of join
returns rows from both tables only if their related columns match.
 An outer join returns rows from one table in the join even if the
other table doesn’t contain a matching row.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 42
How to add, update, and delete data in a table
 You use the INSERT statement to add rows to a table.
 You use the UPDATE statement to change the values in one or
more rows of a table based on the condition you specify.
 You use the DELETE statement to delete one or more rows from a
table based on the condition you specify.
 The execution of an INSERT, UPDATE, or DELETE statement is
often referred to as an action query.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 43
A statement that adds a row to the Invoices table
INSERT INTO Invoices (VendorID, InvoiceNumber,
InvoiceDate, InvoiceTotal, TermsID, InvoiceDueDate)
VALUES (12, '3289175', '7/18/2008', 165, 3, '8/17/2008')
A statement that changes the value of the
CreditTotal column for a selected row
UPDATE Invoices
SET CreditTotal = 35.89
WHERE InvoiceNumber = '367447'
A statement that changes the values in the
InvoiceDueDate column for all invoices with the
specified TermsID
UPDATE Invoices
SET InvoiceDueDate = InvoiceDueDate + 30
WHERE TermsID = 4
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 44
A statement that deletes a selected invoice from
the Invoices table
DELETE FROM Invoices
WHERE InvoiceNumber = '4-342-8069'
A statement that deletes all paid invoices
DELETE FROM Invoices
WHERE InvoiceTotal – PaymentTotal – CreditTotal = 0
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 45
A SELECT statement that’s difficult to read
select invoicenumber, invoicedate, invoicetotal,
invoicetotal – paymenttotal – credittotal as balancedue
from invoices where invoicetotal – paymenttotal –
credittotal > 0 order by invoicedate
A SELECT statement with a readable style
Select InvoiceNumber, InvoiceDate, InvoiceTotal,
InvoiceTotal – PaymentTotal – CreditTotal
As BalanceDue
From Invoices
Where InvoiceTotal – PaymentTotal – CreditTotal > 0
Order By InvoiceDate
Note
 Line breaks, white space, indentation, and capitalization have no
effect on the operation of a statement.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 46
A SELECT statement with a block comment
/*
Author: Bryan Syverson
Date: 8/22/08
*/
SELECT InvoiceNumber, InvoiceDate, InvoiceTotal,
InvoiceTotal – PaymentTotal – CreditTotal
AS BalanceDue
FROM Invoices
A SELECT statement with a single-line comment
SELECT InvoiceNumber, InvoiceDate, InvoiceTotal,
InvoiceTotal – PaymentTotal – CreditTotal
AS BalanceDue
-- The 4th column calculates the invoice balance due
FROM Invoices
Note
 Comments can be used to document portions of code or to clarify
what the coding does. They aren’t executed by the system.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 47
How to code a comment
 To code a block comment, type /* at the start of the block and */ at
the end.
 To code a single-line comment, type -- followed by the comment.
SQL coding recommendations
 Start each new clause on a new line.
 Break long clauses into multiple lines and indent continued lines.
 Capitalize the first letter of each keyword and each word in
column and table names.
 Use comments only for portions of code that are difficult to
understand. Then, make sure that the comments are correct and
up-to-date.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 48
A CREATE VIEW statement for a view named
VendorsMin
CREATE VIEW VendorsMin AS
SELECT VendorName, VendorState, VendorPhone
FROM Vendors
The virtual table that’s represented by the view
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 49
A SELECT statement that uses the VendorsMin
view
SELECT * FROM VendorsMin
WHERE VendorState = 'CA'
ORDER BY VendorName
The result set that’s returned by the SELECT
statement
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 50
How to work with views
 A view consists of a SELECT statement that’s stored with the
database.
 A view behaves like a virtual table. Since you can code a view
name anywhere you’d code a table name, a view is sometimes
called a viewed table.
 Views can be used to restrict the data that a user is allowed to
access or to present data in a form that’s easy for the user to
understand.
 Because views are stored as part of the database, they can be
managed independently of the applications that use them.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 51
A CREATE PROCEDURE statement for a
procedure named spVendorsByState
CREATE PROCEDURE spVendorsByState @State char(2) AS
SELECT VendorName, VendorState, VendorPhone
FROM Vendors
WHERE VendorState = @State
ORDER BY VendorName
A statement that executes the stored procedure
EXEC spVendorsByState 'CA'
The result set
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 52
How to use stored procedures, triggers, and
user-defined functions
 A stored procedure is one or more SQL statements that have been
compiled and stored with the database. It can be started by
application code on the client.
 A stored procedure can also include control-of-flow language,
which lets you perform conditional processing.
 Stored procedures can improve database performance because the
SQL statements in each procedure are only compiled and
optimized the first time they’re executed.
 A trigger is a special type of procedure that’s executed when rows
are inserted, updated, or deleted from a table. Triggers are
typically used to check the validity of the data in a row.
 A user-defined function (UDF) is a special type of procedure that
can return a value or a table.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 53
Common options for accessing SQL Server data
.NET application
Java application
Visual Basic 6 application
ADO.NET
JDBC
ADO
Java driver
OLE DB
SQL Server
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 54
Common data access models
 To work with the data in a SQL Server database, an application
uses a data access model.
 For a Visual Basic .NET application, that model is typically
ADO.NET. For a Java application, that model is typically JDBC
(Java Database Connectivity). And for a Visual Basic 6
application, that model is typically ADO (ActiveX Data Objects).
 Each data access model defines a set of objects you can use to
connect to and work with a SQL Server database.
 Some of the data access models require additional software, called
drivers, to communicate with SQL Server. For example, ADO
requires an OLE DB driver, and JDBC requires a Java driver.
 ADO.NET, which is Microsoft’s newest data access model,
includes its own driver so it can communicate directly with SQL
Server.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 55
Basic ADO.NET objects in a.NET application
.NET application
Dataset
Data table
Data
adapter
Command
Connection
SQL Server
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 56
How to use ADO.NET from a .NET application
 The data used by a .NET application can be stored in a data table
within a dataset. A dataset can contain multiple data tables.
 To retrieve data from a database and store it in a data table: (1) a
data adapter issues a SELECT statement that’s stored in a
command; (2) the command uses a connection to connect to the
database and retrieve the data; (3) the data is passed back to the
data adapter, which stores the data in the dataset.
 To update the data in a database based on the data in a data table,
the data adapter issues an INSERT, UPDATE, or DELETE
statement that’s stored in a command. Then, the command uses a
connection to connect to and update the database.
 After the database data is retrieved or updated, the connection is
closed and the resources used by the connection are released. This
is referred to as the disconnected data architecture.
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 57
Visual Basic code that uses ADO.NET to retrieve
data from a SQL Server database
' Create the Connection, DataAdapter, Command,
' and DataSet objects
Dim connAP As New SqlClient.SqlConnection()
Dim daVendors As New SqlClient.SqlDataAdapter()
Dim cmdVendorsSelect As New SqlClient.SqlCommand()
Dim dsAP As New DataSet()
' Set the connection string for the Connection object
connAP.ConnectionString = _
"Data Source=localhost\SqlExpress;" _
& "Initial Catalog=AP;Integrated Security=True"
' Set the Connection object used by the Command object
cmdVendorsSelect.Connection = connAP
' Set the SELECT statement for the Command object
cmdVendorsSelect.CommandText = "SELECT VendorID, " _
& "VendorName, VendorAddress1, VendorAddress2, " _
& "VendorCity, VendorState, VendorZipCode " _
& "FROM Vendors ORDER BY VendorName"
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 58
Visual Basic code that uses ADO.NET to retrieve
data from a SQL Server database (continued)
' Set the Command object for the DataAdapter
daVendors.SelectCommand = cmdVendorsSelect
' Open the connection to the database
connAP.Open()
' Retrieve the data specified by the SELECT statement
' and load it into the Vendors table that is stored in the
' DataSet object
daVendors.Fill(dsAP, "Vendors")
' Close the connection to the database
connAP.Close()
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 59
C# code that uses ADO.NET to retrieve data from
a SQL Server database
// Create the Connection, DataAdapter, Command, and DataSet
// objects
SqlConnection connAP = new SqlConnection();
SqlDataAdapter daVendors = new SqlDataAdapter();
SqlCommand cmdVendorsSelect = new SqlCommand();
DataSet dsAP = new DataSet();
// Set the connection string for the Connection object
connAP.ConnectionString =
"Data Source=localhost\\SqlExpress;"
+ "Initial Catalog=AP;Integrated Security=True";
// Set the Connection object used by the Command object
cmdVendorsSelect.Connection = connAP;
// Set the SELECT statement for the Command object
cmdVendorsSelect.CommandText = "SELECT VendorID, "
+ "VendorName, VendorAddress1, VendorAddress2, "
+ "VendorCity, VendorState, VendorZipCode "
+ "FROM Vendors ORDER BY VendorName";
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 60
C# code that uses ADO.NET to retrieve data from
a SQL Server database (continued)
// Set the Command object for the DataAdapter
daVendors.SelectCommand = cmdVendorsSelect;
// Open the connection to the database
connAP.Open();
// Retrieve the data specified by the SELECT statement
// and load it into the Vendors table that is stored in the
// DataSet object
daVendors.Fill(dsAP, "Vendors");
// Close the connection to the database
connAP.Close();
Murach’s SQL Server 2008, C1
© 2008, Mike Murach & Associates, Inc.
Slide 61