Chapter 7. Maintaining Database Integrity

7.0. Introduction

Transactions allow a system to maintain integrity when interacting with multiple data sources. If an update to one data source fails, all changes are rolled back to a known good state. This chapter focuses on using transactions from ADO.NET, maintaining database integrity, and resolving conflicts and concurrency problems.

.NET supports both manual and automatic transactions with classes in the System.Transaction namespace. Manual transactions use the transactional capabilities of the data source. The Microsoft Distributed Transaction Coordinator (DTC) manages automatic transactions.

In a manual transaction, a transaction object is associated with a connection to a data source. Multiple commands against the data source can be associated with the transaction, grouping the commands together as a single transaction. Manual transactions can also be controlled using SQL commands in a stored procedure. Manual transactions are significantly faster than automatic transactions because they do not require interprocess communication (IPC) with the DTC. Manual transactions are limited to performing transactions against a single data source. Recipe 7.2 shows how to work with manual transactions.

Automatic transactions are easier to program, can span multiple data sources, and can use multiple resource managers. They are significantly slower than manual transactions. Distributed transactions are demonstrated in Recipe 7.1.

Concurrency problems ...

Get ADO.NET 3.5 Cookbook, 2nd Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.