Transaction Management in SQL Server and C#

Create a transfer database in SQL Server with customers and accounts tables, primary keys account_id and customer_id, and foreign key from accounts to customers; include balance as money and auto-increment.

Learn to create and manage stored procedures in SQL Server, including inserting and retrieving customers and accounts, handling parameters, and preparing procedures for use with a C# application.

Learn how to execute stored procedures in SQL Server, add customers and accounts via procedures, manage parameters, and query data, while addressing foreign key constraints.

Learn to handle sql errors and exceptions with try-catch blocks, manage transactions, and rollback failed operations when foreign key constraints are violated.

Learn to manage sql server transactions with begin transaction, commit, and rollback to ensure atomic transfers between accounts. Demonstrate a try-catch approach to rollback on error and preserve data integrity.

Learn how acid properties—the atomicity, consistency, isolation, and durability—govern SQL Server transactions, ensuring transfers update both accounts or roll back, maintain integrity constraints, and persist after failures.

Examine the dirty read problem in SQL Server by simulating two concurrent transfers; observe how uncommitted updates may be read, and how read committed prevents inconsistent views.

Explore the lost update problem in SQL Server and C# by parallel transactions transferring funds; learn how read uncommitted and read committed can fail, and how repeatable read prevents it.

Exhibits the non-repeatable read problem when a second transaction updates data between reads, causing inconsistent balances. Higher isolation levels such as repeatable read, serializable, or snapshot prevent it.

explore how phantom reads occur when a second transaction inserts data matching the first query under read committed or repeatable read, and how serializable prevents them.

snapshot isolation uses versioning in tempdb to enable high-concurrency reads, unlike serializable's locking; it reads pre-commit data and helps prevent lost updates.

Illustrate how read committed and read committed snapshot isolation handle concurrent transactions, using locking versus versioning to preserve correct balances during transfers.

Create a transfer log table in SQL Server to record transfers between accounts, including from, to, amount, date, and notes. Implement insert and select procedures and log a sample transfer.

Explore how deadlocks arise when two transactions hold exclusive locks and wait for each other, and learn how the DBMS detects and resolves them in SQL Server.

This lecture explains how SQL Server selects a deadlock victim by priority (low, normal, high) and cost, showing how to manually set priority to control which transaction aborts.

Demonstrate transaction management in a C# windows form application with a transfer interface and from-to combo boxes. Include a dollar amount textbox and a confirm button.

Connect a C# Windows Forms app to SQL Server, execute the get customers stored procedure, and populate two combo boxes with customer names and IDs using a data adapter.

Master data grid binding in a C# app by loading customer balances from a database via a stored procedure, wiring a refresh mechanism, and displaying updated results on form load.

Demonstrate the problem of executing two statements for a transfer, showing how a failure in the second causes database inconsistency and violates the integrity constraint.

Handle exceptions in C# with a try-catch block to prevent the application from aborting during bank transfers. Display the exception message and recover gracefully.

Learn to manage SQL transactions in C# by beginning a transaction, executing statements under it, and committing or rolling back to preserve data integrity and consistency.