SqlTransaction database transactions

Transaction: A set of SQL operations that execute atomically. Either all operations get committed (Commit), or everything rolls back on failure (Rollback), complying with the ACID properties.
Within .NET Framework/.NET, use SqlTransaction (SQL‑Server‑only) for implementation.

Key workflow steps:

1. Open database connection conn.Open()
2. Start transaction from the connection conn.BeginTransaction()
3. Assign the transaction object to SqlCommand.Transaction
4. Run multiple SQL statements in batch
5. No exceptions: tx.Commit(); catch exceptions: tx.Rollback()

Example

The original snippet lacks catch roll‑back logic, here is the complete runnable version:

public static int ExecuteSqlTran(List<string> SQLStringList)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        conn.Open();
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        SqlTransaction tx = conn.BeginTransaction();
        cmd.Transaction = tx;
        try
        {
            int count = 0;
            for (int n = 0; n < SQLStringList.Count; n++)
            {
                string strsql = SQLStringList[n];
                if (strsql.Trim().Length > 1)
                {
                    cmd.CommandText = strsql;
                    count += cmd.ExecuteNonQuery();
                }
            }
            tx.Commit(); // Commit when all statements succeed
            return count;
        }
        catch (Exception ex)
        {
            tx.Rollback(); // Roll everything back upon any failure
            throw ex; // Rethrow so upstream code can detect failure
        }
    }
}Code language: C# (cs)

This implementation directly concatenates SQL strings and carries SQL injection risks; it is good for learning how transactions work. Always prefer parameterized queries in real‑world projects.

Transaction sample with parameterization

Bulk insert with SqlParameter to prevent injection. Important points:

1. Call sqlCom.Parameters.Clear() at the end of each loop iteration to clear old parameters and avoid parameter accumulation errors
2. Reuse the same SqlCommand instance, only update CommandText and parameters

SqlCommand sqlCom = new SqlCommand();
sqlCom.Connection = conn;
SqlTransaction st = conn.BeginTransaction();
sqlCom.Transaction = st;
int res = 0;
try
{
    for (int i = 0; i < 10; i++)
    {
        sqlCom.CommandText = "INSERT INTO [dbo].[Article]([Title])VALUES(@Title)";
        SqlParameter param = new SqlParameter("@Title", SqlDbType.NVarChar, 250);
        param.Value = i + "This is new title" + DateTime.Now;
        sqlCom.Parameters.Add(param);
        res += sqlCom.ExecuteNonQuery();
        sqlCom.Parameters.Clear(); // Clear parameters for next iteration
    }
    st.Commit();
}
catch (Exception)
{
    st.Rollback(); // Undo all changes if exception occurs
}Code language: C# (cs)

1. Mandatory rules you must follow

• BeginTransaction() must be called after connection Open
• You have to set the Transaction property on SqlCommand, otherwise exceptions will be thrown
• After Commit() / Rollback() the transaction terminates automatically; do not invoke them repeatedly

Miscellaneous

• In .NET Core/.NET 5+, use using statement to auto‑dispose SqlTransaction
• For massive batch SQL workloads, consider SqlBulkCopy, it outperforms looping ExecuteNonQuery calls
• SqlTransaction cannot handle cross‑database transactions; use distributed transactions via TransactionScope instead

SqlTransaction database transactions