SQL Data Adapter – Transactions and Advanced Usage

This page describes advanced usage patterns for SqlDataAdapter when you need explicit control over transaction boundaries or want to execute multiple SQL commands as a single atomic unit of work.

When do you need explicit transactions?

The default SaveChangesAsync workflow is sufficient for the majority of applications. It automatically:

• Opens a SQL connection
• Begins a transaction
• Saves changes
• Commits or rolls back on failure
• Disposes all resources

Explicit transactions are useful when you need to:

• Execute multiple SQL commands under one transaction
• Mix ad-hoc SQL with dataset persistence
• Control exactly when commit or rollback occurs
• Implement complex application or process transitions

Transaction scope overview

Advanced scenarios are handled using SqlDataAdapterTransaction, which represents a single transactional unit of work.

The transaction scope:

• Owns a SQL connection and transaction
• Exposes execution methods for commands and queries
• Allows saving dataset changes within the same transaction
• Ensures rollback if the transaction is not committed

Basic transaction usage

string connectionString = "Server=.;Database=HumanResources;Trusted_Connection=True;";

SqlDataAdapter adapter = new SqlDataAdapter(connectionString);
await using (SqlDataAdapterTransaction tx = await adapter.BeginTransactionAsync())
{
    await tx.ExecuteNonQueryAsync("UPDATE Department SET Name = 'HR' WHERE Id = 1", false);
    await tx.ExecuteNonQueryAsync("UPDATE Employee SET DepartmentId = 1 WHERE DepartmentId IS NULL", false);

    await tx.CommitAsync();
}

If CommitAsync is not called, the transaction is automatically rolled back when the scope is disposed.

Saving dataset changes within a transaction

Dataset persistence can be combined with other SQL operations under the same transaction. This example assumes that you received the changeset from the application layer where it was created.

using PocoDataSet.IData;
using PocoDataSet.SqlServerDataAdapter;

string connectionString = "Server=.;Database=HumanResources;Trusted_Connection=True;";

SqlDataAdapter adapter = new SqlDataAdapter(connectionString);
await using (SqlDataAdapterTransaction tx = await adapter.BeginTransactionAsync())
{
    await tx.ExecuteNonQueryAsync("INSERT INTO AuditLog (Message) VALUES ('Saving changes')", false);

    await tx.SaveChangesAsync(changeset);
    await tx.CommitAsync();
}

This ensures that both ad-hoc SQL and dataset persistence either succeed together or fail together.

Error handling and rollback

Transaction scopes are designed to be safe by default. If an exception occurs:

• The transaction is rolled back automatically
• The connection is disposed if owned by the scope
• No partial changes are committed

This makes transactional code easier to reason about and avoids leaking partially completed operations.

Design notes

The SQL Data Adapter separates responsibilities deliberately:

• SqlDataAdapter provides a simple, safe facade
• SqlDataAdapterTransaction represents a unit of work
• Internal execution logic is shared and reused

This design allows advanced scenarios without complicating the default workflow shown in the Quick Start.

Table of Content POCO DataSet Quick Start Previous: SQL Data Adapter – Quick Start

 

---

Business Process Programming in .Net
© 2004–2026 Laskarzhevsky Software Inc.
Unless otherwise noted, the content of this website is licensed under the Creative Commons Attribution 4.0 International License (CC BY 4.0).
Code examples are provided under the MIT License.
You are free to share and adapt the material provided that appropriate credit is given and any modifications are clearly indicated.
The information provided on this website is for educational purposes only.
The author and publisher make no warranties regarding the completeness or suitability of the information and are not responsible for any damages resulting from its use.