ADO.NET Cookbook

Chapter 6. Maintaining Database Integrity

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. Manual transactions are supported through ADO.NET classes and 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.

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

Concurrency problems occur when multiple users attempt to modify unlocked data. Possible problems include lost updates, dirty reads, nonrepeatable ...