数据库数据未更新排查：SqlTransaction提交语句问题分析

Hey there, let's walk through targeted steps to diagnose why your commit step might be causing problems (or appearing to do so). Since you noted other parts of the code work as expected, here's how to narrow down the root cause:

1. First, Catch and Inspect Commit Exceptions #

The most likely issue is that Commit() is throwing an exception you're not currently handling. Wrap the commit logic in a try/catch block to capture specific errors like constraint violations, lock timeouts, or invalid transaction states:

try
{
    trans.Commit(); //Commit the changes to database
    Console.WriteLine("Commit succeeded!");
}
catch (SqlException ex)
{
    // Log these details critical for debugging:
    Console.WriteLine($"SQL Error during commit: {ex.Message}");
    Console.WriteLine($"Error Number: {ex.Number}");
    Console.WriteLine($"Stack Trace: {ex.StackTrace}");
    trans.Rollback();
    throw; // Re-throw if you want to propagate the error upstream
}
catch (InvalidOperationException ex)
{
    Console.WriteLine($"Commit failed due to invalid transaction state: {ex.Message}");
    // This usually happens if the transaction was already rolled back or disposed
}

2. Verify DataTable Row States Before Commit #

The DataAdapter.Update() only processes rows marked as Added, Modified, or Deleted. If your merge isn't setting these states correctly, count will be 0—and the commit will have nothing to persist. Add this check right after da.Update(sourceDt):

int count = da.Update(sourceDt);
Console.WriteLine($"Rows updated via DataAdapter: {count}");

// Check modified rows and their states
var changedRows = sourceDt.GetChanges();
if (changedRows != null)
{
    foreach (DataRow row in changedRows.Rows)
    {
        Console.WriteLine($"Row State: {row.RowState}, Row Error: {row.RowError}");
    }
}
else
{
    Console.WriteLine("No rows were marked as modified—nothing to commit!");
}

Pay attention to:

• If count is 0: Your Merge call might not be marking rows as modified. Double-check the preserveChanges parameter (you used false, which should overwrite existing rows with clonedDt values and mark them as Modified if they differ).
• If rows have RowError values: This indicates data validation failures that prevent the update from proceeding.

3. Check Transaction and Connection State #

Before calling Commit(), confirm your transaction is still active and the connection is open:

Console.WriteLine($"Transaction State: {trans.State}");
Console.WriteLine($"Connection State: {con.State}");

• If the transaction state is Aborted or Committed, calling Commit() will throw an error.
• If the connection is closed, the transaction may have been implicitly rolled back by SQL Server.

4. Validate Auto-Generated Commands from SqlCommandBuilder #

Sometimes the auto-generated UpdateCommand or InsertCommand has issues (e.g., missing primary key references in the WHERE clause). Print the command text to verify:

Console.WriteLine($"Generated Update Command: {da.UpdateCommand.CommandText}");
Console.WriteLine($"Generated Insert Command: {da.InsertCommand.CommandText}");

Ensure the WHERE clause in the update command correctly targets your table's primary key—this ensures rows are actually modified instead of being skipped.

5. Check Database-Side Constraints/Triggers #

Even if your code looks correct, database-level logic can block commits:

• Use SQL Server Management Studio (SSMS) to check for:
  • Primary key/foreign key violations that only surface during commit
  • Triggers that fail or roll back changes silently
• Run a SQL Profiler trace or use Extended Events to capture database errors occurring during the commit operation.

内容的提问来源于stack exchange，提问作者Wocugon