Contents
Overview 1
Introduction to Transactional DAL 2
Technologies 5
Logical Design of Transactional DAL 12
Physical Design of Transactional DAL 13
Market Purchasing 18
Best Practices 21
Lab 9: Transactional DAL 22
Review 30

Module 9: The
Transactional 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.

Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual

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 9: The Transactional Data Access Layer iii Instructor Notes
This module provides students with a presentation on the transactional data
access layer (DAL). Module 9 is the second 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 logical design of a transactional DAL and how to apply design
patterns.
!
Describe Microsoft
®
SQL Server
™
, Microsoft Distributed Transaction
Coordinator (MS DTC), and COM+ transactions and synchronization.
!
Describe the Compensating Resource Manager (CRM).
!
Describe the physical design of a transactional DAL and how to use the


Module Strategy
Use the following strategy to present this module:
!
Introduction to Transactional DAL
The purpose of this section is to introduce students to the business problem
and the requirements for transactional DAL.
In the topic “Business Requirements,” emphasize the need for transactions
with either MS DTC–compliant resource managers or for compensating
transactions with CRMs.
!
TechnologiesThe purpose of this section is to introduce students to the
technologies that can be used in the physical design of the transactional
DAL.
The topics covered include the COM+ and SQL Server transaction
mechanism and the CRM architecture.
!
Logical Design of Transactional DALThe purpose of this section is to
identify the two design patterns that might be useful in the logical design of
transactional DAL: Iterator and Composite.
!
Physical Design of Transactional DALThe purpose of this section is to show
how transactions and CRM can be applied to the physical design of
transactional DAL components.
!
Market Purchasing
The purpose of this section is to discuss the logical and physical designs of
transactional DAL in Market Purchasing and to justify the choices made.
By using Component Services, you can present the Market Purchasing DAL
COM+ application. Explain that the nontransactional and transactional DAL

Introduction to Transactional DAL
!
Technologies
!
Logical Design of Transactional DAL
!
Physical Design of Transactional DAL
!
Market Purchasing
!
Best PracticesModule 9 is the second of two modules that focus on the data access layer
(DAL). In Module 8, “The Nontransactional Data Access Layer,” you learned
about data retrieval. In this module, you will learn about data manipulation.
After completing this module, you will be able to:
!
Describe the logical design of a transactional DAL and how to use design
patterns.
!
Describe Microsoft
®
SQL Server
™
, the Microsoft Distributed Transaction
Coordinator (MS DTC), and COM+ transactions and synchronization.
!
Describe the Compensating Resource Manager (CRM).
!

In this section, the transactional DAL will be placed in the proper context of the
business problem. This will be followed by a presentation about the business
requirements of a transactional DAL.
Topic Objective
To provide an overview of
the section topics and
objectives.
Lead-in
In this section, you will learn
what makes up a
transactional DAL.
Module 9: The Transactional Data Access Layer 3 The Business Problem
DAL
Connected Business
Logic Layer
Disconnected Business
Logic Layer
Facade Layer
Web Services Facade Business Facade
Transactional DAL
Nontransactional DAL
User Services
Data
ServicesThe transactional DAL facilitates both the retrieval and the modification of data

computers is an important and difficult task. The data access associated with
transferring a student from one course to another is straightforward: you drop
the student from one course and you add the student to the other. When you
scale this operation to an enterprise application, you need an infrastructure to
carry out the transfer. What happens if the drop succeeds and the add fails?
What happens if there is a computer failure immediately after the drop is
executed but before the add is executed? For these reasons, you need an
infrastructure that provides transaction support.
CRMs
Typically, most data services are databases that can participate in a transaction,
such as SQL Server. Unfortunately, not all data services are databases. For
example, Active Directory and Extensible Markup Language (XML) files
cannot participate in transactions unless a special effort is undertaken.
Nevertheless, the business requirement is for these resources to participate in a
transaction. The solution is to implement CRM components for these resources
whose data service providers do not allow them to participate in a transaction,
thus fulfilling the business requirement.
Topic Objective
To provide background
about the business
requirements for
implementing the
transactional DAL.
Lead-in
In this topic, you will learn
about the business
requirements for
implementing transactional
DAL.
Module 9: The Transactional Data Access Layer 5

Stream
!
Cursor Service
!
Data Shaping Service
!
XML
!
Active Directory Service Interfaces (ADSI)

The design of physical components for the transactional DAL is based on using
transactions. In this section, you will learn about the following transaction
technology topics:
!
Transactions
!
Distributed transactions
!
COM+ transactions

In addition, in this section you will learn about the architecture of the CRM.
Topic Objective
To provide an overview of
the section topics and
objectives.
Lead-in
In this section, you will
review the technologies
associated with
transactional DAL.

UPDATE or INSERT, will begin a new transaction. The transaction will end
when you issue a COMMIT TRANSACTION or ROLLBACK statement. Then
the next SQL statement will implicitly begin yet another transaction.
Avoid Local Transactions When Using COM+ and the MS
DTC
When using the MS DTC or COM+ transactions, you should avoid using
implicit or explicit transactions since the SQL statements you issue will run
inside the distributed transaction. Beginning new transactions, implicitly or
explicitly, can be semantically confusing and might cause problems with the
distributed transaction.
Topic Objective
To provide a review of
transactions.
Lead-in
In this topic, you will learn
how to design transactions
in the transactional DAL.
Module 9: The Transactional Data Access Layer 7 Active Directory Transactions
Active Directory does not participate in transactions with the MS DTC.
Therefore, any changes you make to Active Directory from withina transaction
cannot be rolled back if the transaction aborts.
However, you can create CRMs to make some of the ADSI interfaces
transactional. For example, when accessing properties in Active Directory it is
possible to cache the properties locally, and to manipulate the properties in the
local cache.
A CRM can use the cache to write properties while the transaction is pending.
When the transaction commits, the CRM writes the properties to the underlying

To provide a review of
distributed transactions.
Lead-in
In this topic, you will learn
the details of the physical
design of distributed
transactions.
Module 9: The Transactional Data Access Layer 9 COM+ Transactions
COM+
Transfer
Add
DropImplement each individual transaction that is in a complex transaction as a
separate COM+ component, and configure the component as Participating in a
Transaction in Component Services. The complex transaction should also be a
COM+ component configured as Participating in a Transaction in Component
Services, and should create instances of the participating simple transactions by
using the context object. For further information about how to create and
manage transactions, refer to Module 4, “Managing Transactions and State,” in
Course 1907A: Building Distributed Applications for Microsoft Windows 2000
with Visual Basic.
In the preceding slide, there is an illustration of COM+ invoking a complex
transactional DAL component, Transfer, which is composed of two
transactional DAL components, Drop and Add.
Topic Objective

example, if you want to make updates to an XML file in the context of a
transaction, you have to build a resource manager for XML files. The resource
manager would be responsible for voting on the outcome of the transaction and
for committing or rolling back changes to the XML file, depending on the final
result of the transaction. Building this type of resource manager is not easy, but
the CRM provides an architecture that makes it easier to include resources such
as XML files in transactions.
To build a CRM, you must create two components: a CRM Worker and a CRM
Compensator. The CRM Worker is responsible for performing the work on the
resource. In the case of an XML file, it updates the XML. The CRM Worker
also exposes a custom interface for the client component to use. For example,
for XML files it might expose a method named WriteToXML. The CRM
Compensator is responsible for participating in the two-phase commit. It
responds to each phase, ultimately committing the changes or rolling them back
depending on the outcome of the transaction.
Operation of CRM
The first step in the operation of the CRM is the client component’s creation of
the CRM Worker. The client component then begins using the CRM Worker. In
the case of the XML CRM Worker, the client component would call the
WriteToXML method to write some XML to a file.
Topic Objective
To define how CRM is used
in the physical design.
Lead-in
In this topic, you will learn
how and when to include
CRM in your physical
design.
Module 9: The Transactional Data Access Layer 11


Iterator
!
CompositeThe transactional DAL logical design can use the same design patterns as that
of the nontransactional DAL: the Iterator and Composite patterns.
Iterator
The Iterator design pattern allows a client to access a collection of objects
without relying on the internal structure of those objects. In the DAL, you will
frequently be dealing with collections of records being passed back and forth
across the system. The Iterator is an excellent design pattern to model record
collections, and frequently the Iterator is implemented as a recordset.
Composite
The Composite design pattern combines multiple types of objects into tree
hierarchies or part/whole hierarchies. Data often has hierarchical or part/whole
relationships. For example, in a bill of materials a part might also be an
assembly of other parts. Although the Composite design pattern is a structural
design pattern, it works very well to help organize data. Often the Data Shaping
Service implements the Composite design pattern.
Topic Objective
To provide information
about the logical design of a
transactional DAL.
Lead-in
In this topic, you will learn
about the design patterns
that can be applied to a
transactional DAL.
Module 9: The Transactional Data Access Layer 13

!
COM+
!
MS DTCIn general, all updates to databases should be transacted. Embed simple
transactions in a COM+ component. Design complex transactions as a
hierarchy of COM+ components that invoke simple transaction components.
COM+, through MS DTC, will manage the hierarchy of complex data
modification components and their participation in transactions.
Avoid the explicit use of distributed transactions through MS DTC when
creating an enterprise system that uses COM+ components. The COM+
components already support transactions, eliminating the need for you to write
MS DTC code. Also, MS DTC code that you write could interfere with COM+
transactions in the system.
Topic Objective
To provide a summary of
transactions.
Lead-in
In this topic, you will learn
about the important physical
design issues associated
with transactions.
Module 9: The Transactional Data Access Layer 15 CRM Physical Design
!
CRM Worker

Platform SDK Help in Microsoft MSDN
®
Library.
Topic Objective
To provide a summary of
CRM.
Lead-in
In this topic, you will learn
about the important design
considerations for CRM
components.
16 Module 9: The Transactional Data Access Layer Components
!
Transactional Support
!
Synchronization
!
ActivationIt is recommended that you implement the transactional DAL using COM+
components. COM+ provides synchronization, activation, transaction, and
security services. Therefore, the best approach is to let COM+ handle these
services while you program the logic of your components.
Transactional Support
The transactional DAL requires transactions. Therefore, configure the classes as
Required or Requires New. This ensures that the classes will always run inside

configuration information
related to COM+
transactional support,
synchronization, and
activation services.
Module 9: The Transactional Data Access Layer 17 Activation
Because the transactional classes require transactions and synchronization, they
must support just-in-time (JIT) activation. The best way to support activation
from a transactional class is to vote on the transaction outcome. If a method call
successfully completes its work, you can call IObjectContext::SetComplete.
If a method call fails for some reason, call IObjectContext::SetAbort to abort
the transaction.
Calling SetComplete or SetAbort will cause your object instance to be
deactivated when the current method call returns to the caller. If your object
requires that more work be completed before deactivation, you can call
IObjectContext::EnableCommit or IObjectContext::DisableCommit before
exiting the method call. For more information about EnableCommit and
DisableCommit, see MSDN Library.
JIT activation requires that your components conform to a stateless
programming model. Therefore, you should write the methods for the interfaces
to either store, update, or retrieve data. The interfaces should not store
properties. For example, if you have a customers table and a Customers class,
the Customers class should have an interface with methods such as
GetCustomer, CreateCustomer, and UpdateCustomer, which pass all of the
necessary parameters for working with a customer. The interface should not
have methods such as SetName or GetName, which set individual properties of
the customer before taking an action like creating or getting customer records.


Market Purchasing Logical Design
!
Uses the Iterator Design Pattern for Data Sets
!
There Is One Design Class for Each Entity
!
Both a Worker Class and Compensator Class
Are Created for an XML CRMThe logical design for the transactional DAL for Market Purchasing, as shown
in the preceding slide, incorporates several principles:
!
The Iterator design pattern is used to represent data sets.
!
There is one design class for each entity in the database.
Thus the LineItemDetail entity has a daltr_LineItemDetail class, the
Requisition entity has a daltr_Requisition class, and so forth.
!
Because Market Purchasing must write to XML files in the context of a
transaction, a CRM for XML files was designed. Both a Worker class and a
Compensator class were designed for writing to XML files.

The naming convention is to use a prefix of daltr (data access layer
transactional) for each class.
Topic Objective
To provide an overview of
the logical design for the
Market Purchasing

The COM+ classes are contained in one dynamic-link library (DLL) called
mpdaltr.dll.
!
The mpdaltr.dll is registered in the Market Purchasing DAL COM+
application.
!
The CRM classes are contained in a DLL called mpXMLCRM.dll.
!
The mpXMLCRM.dll is registered in the Market Purchasing XML CRM
COM+ application, which is configured to support CRMs.

The naming convention is to use the name mpdaltr (Market Purchasing data
access layer transactional) for the DLL file name.
Topic Objective
To provide an overview of
the physical design for the
Market Purchasing
transactional DAL.
Lead-in
In this topic, you will learn
the how physical design for
the Market Purchasing
transactional DAL was
implemented.
Module 9: The Transactional Data Access Layer 21 Best Practices
!
Transacted components are preferred over transacted