Contents
Overview 1
Introduction to the Data Access Layer 2
Nontransactional DAL Technologies 5
Demonstration: ADSI 22
Logical Design of Nontransactional DAL 24
Physical Design of Nontransactional DAL 28
Market Purchasing 46
Best Practices 49
Lab 8: Nontransactional DAL 50
Review 54

Module 8: The
Nontransactional Data
Access Layer

Information in this document is subject to change without notice. The names of companies,
products, people, characters, and/or data mentioned herein are fictitious and are in no way intended
to represent any real individual, company, product, or event, unless otherwise noted. Complying
with all applicable copyright laws is the responsibility of the user. No part of this document may
be reproduced or transmitted in any form or by any means, electronic or mechanical, for any
purpose, without the express written permission of Microsoft Corporation. If, however, your only
means of access is electronic, permission to print one copy is hereby granted.

Lead Product Manager, System Services and Infrastructure: Edward Dudenhoefer
Manufacturing Manager: Rick Terek
Operations Coordinator: John Williams
Manufacturing Support: Laura King; Kathy Hershey
Lead Product Manager, Release Management: Bo Galford
Group Manager, Courseware Infrastructure: David Bramble
General Manager: Robert Stewart
Module 8: The Nontransactional Data Access Layer iii Instructor Notes
This module provides students with a presentation on the nontransactional data
access layer (DAL). Module 8 is the first of two modules that focus on the
DAL: Module 8, “The Nontransactional Data Access Layer” and Module 9,
“The Transactional Data Access Layer.” The DAL is an application’s means of
access to data services.
After completing this module, students will be able to:
!
Describe the differences between the two types of DALs: transactional and
nontransactional.
!
Describe the logical and physical designs of a nontransactional DAL and
how to apply behavioral design patterns.
!
Describe the data access technologies provided by Microsoft
®
Data Access
Connectivity (MDAC).
!
Describe the cursor engine and how to create the physical design for data

30 Minutes
iv Module 8: The Nontransactional Data Access Layer Demonstration
This section provides demonstration procedures that will not fit in the margin
notes or are not appropriate for the student notes.
ADSI
!
To prepare for the demonstration
• Review the instructions in the student notes.

Module 8: The Nontransactional Data Access Layer v Module Strategy
Use the following strategy to present this module:
!
Introduction to the Data Access Layer
The purpose of this section is to introduce students to the DAL and its main
purpose, which is to buffer all other layers from the data services layer. The
section also emphasizes the need to split the DAL into nontransactional and
transactional layers to define the proper boundaries for COM+ applications
and their attributes.
In the topic “Splitting the DAL,” emphasize that the key functions for the
nontransactional DAL are to receive requests from the business or Web
services facade layer or business logic layer, to process the requests and
pass the requests to data services, to retrieve the results from data services,
and to pass the results of the processing back to the facade layer or business
logic layer.

The purpose of this section is to discuss the logical and physical designs of
nontransactional DAL in Market Purchasing and to explain the justification
for the choices made. The logical design incorporates an Iterator while the
physical design uses cursors and ADO for database access.
vi Module 8: The Nontransactional Data Access Layer By using Component Services, you can present the Market Purchasing DAL
COM+ application. Explain that both the nontransactional and transactional
DAL components are registered in the same COM+ application because all
of the data is physically on one computer. You may also consider presenting
the source code of one of the nontransactional DAL components. Don’t
show the source code of the mpdalnt.Requestor class, since that will give
away the answers to the lab.
!
Best Practices
The best practices focus on using an Iterator as a design pattern for a class
rather than having a separate collection class for each business entity. The
physical design best practices focus on the efficient use of cursors: client vs.
server side.

Lab Strategy
!
Lab 8: The Nontransactional DAL
The purpose of Lab 8 is for students to learn how to design the
nontransactional DAL. Students probably will not derive answers that
correspond exactly to the Market Purchasing design. This is acceptable as
long as the student answers are justified and reflect the principles discussed
in the module. Discuss with students their answers to Lab 8.


!
Describe the logical and physical designs of a nontransactional DAL and
how to use behavioral design patterns.
!
Describe the data access technologies provided by Microsoft
®
Data Access
Connectivity (MDAC).
!
Describe the cursor engine and how to create the physical design for data
caching using the cursor engine.
!
Implement the logical and physical designs for a nontransactional DAL
object.

Topic Objective
To provide an overview of
the module topics and
objectives.
Lead-in
In this module, you will learn
about the nontransactional
data access layer and how
to create a logical design
and a physical design for it.
2 Module 8: The Nontransactional Data Access Layer #
##

Connected Business
Logic Layer
Disconnected Business
Logic Layer
Facade Layer
Web Services Facade Business Facade
Transactional DAL
Nontransactional DAL
User Services
Data
ServicesThe data services store and manage all of the data used by the system. Access to
the data services requires detailed knowledge of where and how the data is
stored. For example, an order entry system might store orders in several tables
in a Microsoft
®
SQL Server
™
database. To create a new order, a system must
know the name of the computer running SQL Server, the name of the database,
and the names of the tables in which the orders are stored.
If the business logic or facades were to access the data services directly, they
would have to be programmed and compiled with much of this information in
the components. Changes to the data services, such as changing the computer
name, database name, or table structures, would require changes in the business
logic and facades.
A DAL isolates the rest of the system from changes in the data services. Using
the DAL, the business logic or facades only pass or request the data they need,

nontransactional classes.The DAL is split into two separate parts. One part, called the nontransactional
DAL, only exposes functionality to retrieve data. The other part, called the
transactional DAL, exposes functionality to create, update, and delete data. The
DAL is split into two parts because transactions take a lot of overhead. When a
COM+ class declares that it requires transactions, the Microsoft Distributed
Transaction Coordinator (MS DTC) must create and manage the transactions.
This overhead is not necessary when only retrieving data, so the methods
dealing with data retrieval are put in the nontransactional DAL.
This is an important distinction because most classes you derive in your logical
design will combine transactional and nontransactional methods. For example,
a customers table in the database should have a Customers class that exposes
methods such as GetCustomer, CreateCustomer, and UpdateCustomer.
GetCustomer is a nontransactional method because it does not change the
table. However, CreateCustomer and UpdateCustomer require a transaction
to update the database.
COM+ declares transactions at a class level. Either the entire class requires
transactions, or the entire class does not support transactions. Therefore the
GetCustomer method is split off into another class, which only has methods
that retrieve data.
In your logical design, you should already have this architecture in mind while
creating classes for the DAL.
The following are the key functions for the nontransactional DAL:
!
Receive requests from the business or Web services facade layer or business
logic.
!
Process the requests and pass them to data services.

!
Record
!
Stream
!
Cursor Service
!
Data Shaping Service
!
Issues and Challenges of Data Exchange
!
XML Solution
!
Active Directory Service Interfaces The design of components for the nontransactional DAL is based on data access
technologies for accessing hierarchical as well as non-hierarchical data. In this
section, the following data access technology topics will be covered in detail:
!
Microsoft Data Access Components (MDAC)
!
OLE DB
!
ActiveX
™
Data Objects (ADO)
!
Record
!

Relational
Databases
Relational
Databases
Data
Object
Interfaces
Data
Object
Interfaces
Application
Programming
Interfaces
Application
Programming
Interfaces
ADO RDO
ODBCOLE DBMicrosoft Data Access Components (MDAC) version 2.5, which is integrated
into Microsoft Windows
®
2000, provides the key technologies that enable
Universal Data Access (UDA). UDA is a definition of Microsoft's strategy for
providing access to information across the enterprise.
MDAC includes ADO, OLE DB, and Open Database Connectivity (ODBC).
The MDAC technology can be used in the physical design of nontransactional
DAL components to easily integrate information from a variety of sources, both
relational (SQL) and non-relational.

ODBC. MDAC provides universal access to many different data sources by
using a universal mechanism. Microsoft has introduced extensions to both OLE
DB and ADO for specific data sources, such as ActiveX Data Objects
Multidimensional (ADO MD), which provides access to multidimensional data
generated by Microsoft Analysis Services.
For more information on MDAC, see the Microsoft Universal Data Access Web
site at http://www.microsoft.com/data.
8 Module 8: The Nontransactional Data Access Layer OLE DB
!
Consumers
!
Providers
$
Data providers
$
Service providers
SQL
Server
SQL
Server
ADO
Consumer
SQL Server
OLE DB
Provider
Data Shaping
Service

In this topic, you will learn
about the architecture of
OLE DB.
Module 8: The Nontransactional Data Access Layer 9 !
Service providers
A service provider, in general, does not have its own data, but facilitates
data access between a data provider and a consumer. Examples of service
providers are query processors and Cursor Engines. Service providers act
both as consumers and providers. As a consumer, a service provider
retrieves data from the underlying data provider. As a provider, it creates a
rowset from the retrieved data and returns it to the consumer.
OLE DB functionality is exposed through more than 50 different interfaces.
Relatively few of these interfaces are mandatory. Since most interfaces are
optional, this allows a provider to implement only the functionality it needs.
Thus, OLE DB allows providers to better match and expose information
functionality.
Defining new interfaces can extend the OLE DB functionality. This allows
providers to expose custom functionality that cannot be exposed through
ODBC. For example, Microsoft Analysis Services for SQL Server has an
OLE DB MD provider.
10 Module 8: The Nontransactional Data Access Layer ActiveX Data Objects
!
ADO Object Model
!

Topic Objective
To provide an overview of
ADO.
Lead-in
In this topic, you will learn
about the architecture of
ADO.
Module 8: The Nontransactional Data Access Layer 11 ADO Programming
ADO provides the means for an application to perform the following sequence
of actions:
1. Connect to a data source.
2. Specify a command to gain access to the data source.
3. Execute the command.
4. If the command causes data to be returned in the form of rows in a table,
store the rows in a cache that you can easily examine, manipulate, or
change.
5. If appropriate, update the data source with changes from the cache of rows.
6. Provide a general means to detect errors (usually as a result of making a
connection or executing a command).

Remote Data Service
In addition, ADO provides an interface, Remote Data Services (RDS), which
allows you to gain access to a data source through an intermediary, such as
Internet Information Services (IIS).
RDS provides the means for a remote application to perform the following
sequence of actions:
1. Specify the program to be invoked on the server, and obtain a way to refer

on the Record object identify the type of node.
The Record object also represents an alternative way for navigating
hierarchically organized data. A Record object may be created to represent the
root of a specific sub-tree in a large tree structure, and new Record objects may
be opened to represent child nodes.
A file or directory (that is, a resource) is uniquely identified by an absolute
Uniform Resource Locator (URL). A Connection object is implicitly created
and set to the Record object when the record is opened with an absolute URL.
A Connection object may explicitly be set to the Record object by using the
ActiveConnection property. The files and directories accessible using the
Connection object define the context in which record operations can occur.
Data modification and navigation methods on the Record object also accept a
relative URL, which locates a resource using an absolute URL or the
Connection object context as a starting point.
Topic Objective
To provide an overview of
the Record object.
Lead-in
In this topic, you will learn
about one of the new
objects available in ADO:
Record.
Module 8: The Nontransactional Data Access Layer 13 Stream
!
Represents a Stream of Binary Data or Text
!
Used with the Record Object to Access the

ADO: Stream.
14 Module 8: The Nontransactional Data Access Layer Cursor Service
!
Provided by OLE DB
!
Supports Working with a Rowset
Independently of the Database
!
ADO Exposes a Cursor Service Through a
Disconnected RecordsetOLE DB includes a Cursor Service that provides a client-tier and middle-tier
cache for data sets and implements smart synchronization functions. Using the
Cursor Service can dramatically reduce network traffic while giving you full
control over data synchronization. The Cursor Service can be installed on each
tier of a multitier application, providing an effective solution for scalability.
ADO exposes the Cursor Service through the Recordset object as a client-side
cursor. This is the easiest way to program for the Cursor Service.

Topic Objective
To present features of the
OLE DB Cursor Service.
Lead-in
In this topic, you will learn
about the OLE DB Cursor
Service.

for each partition in the child rowset.
Topic Objective
To provide an overview of
the Data Shaping OLE DB
provider and the Shape
language.
Lead-in
In this topic, you will learn
about the architecture of
Data Shaping and the
Shape language syntax.
16 Module 8: The Nontransactional Data Access Layer Using Data Shaping
Creating a hierarchy is a two-step process that requires connection to the Data
Shaping Provider and then uses the SHAPE command.
You can also use the Data Shaping provider from ADO. When you use Data
Shaping from ADO, you must setup an explicit connection to the OLE DB
provider, as shown in the following example:
Provider=MSDataShape; data provider=MSDASQL; Driver={SQL
Server};Server=xxx; Database=yyy; UID=zzz; PWD=;

You can also create an empty hierarchical rowset that can later be populated
with data or reshaped. In this case, it is not necessary to connect to a data
provider, as shown in the following example:
Provider=MSDataShape; data provider=NONE

Module 8: The Nontransactional Data Access Layer 17


applications to complete a particular business scenario. For example, after a
requestor submits a requisition, the requisition must be forwarded to a vendor
for fulfillment. The transfer of data to other systems can be done on an
individual basis, and each time such a need arises a new data transfer protocol
can be defined.
Over time, it became apparent that a structural representation of data needed to
be defined so that the data, together with its schema, can be passed to another
application that would be able to retrieve and use data based on the schema
provided.
In addition, it is important that the standard for data exchange should be
extensible. Since application needs change, it is necessary to be able to extend
the schema for the data exchange to accommodate new requirements.
Finally, the actual data being exchanged should be separated from how it
should be represented and the process that will facilitate its use.
Topic Objective
To discuss issues related to
data exchange.
Lead-in
In this topic, you will learn
about issues related to data
exchange.
Module 8: The Nontransactional Data Access Layer 19 XML Solution
!
XML
!
DTD
!

files.
Topic Objective
To discuss how XML can
provide a solution to data
exchange issues.
Lead-in
In this topic, you will learn
about XML as a solution for
the challenges of data
exchange.