How does SQLTransaction.Commit() work?

Question

A few day ago, I have studied SqlTransaction and I know the purpose of SqlTransaction.Commit() - it should "commit the database transaction." - MSDN.

But HOW DOES IT WORK?

For example: I wrote a piece of code like this:

using (SqlTransaction tran = connection.BeginTransaction())
{
    try
    {
        using (SqlCommand cmd = connection.CreateCommand())
        {
            cmd.CommandText = msg.command;
            cmd.Transaction = tran;

            cmd.ExecuteNonQuery();                        
        }
    }
    catch (Exception)
    {
        // if all of above have any exception, that's mean my transaction is 
        // failure and my database has no change. 
        return false;
    }

    tran.Commit();

    // if all of above have no problems, that's mean my transaction is successful
    return true;
    connection.Dispose();
}

In this case, SQL Server is on another computer.

I guess: commit method has two periods, Period 1: when I implement tran.Commit(), compiler will signal SQL Server and talk to SQL Server that: "I'm ok, please help me commit (change) data", and then SQL Server will implement compiler's request. Period 2: when SQL Server implement compiler's request completely, implement result will be return to our compiler. When our compiler receive implement result, our compiler will continue compile the next command line ("return true").

But if in second period, the connection is broken and implement result isn't transferred back to our compiler. In this case, our transaction is success or not? Is data persisted in SQL Server or not?

Additional question: my prediction about two period of SQLTransaction.Commit() is true or not?

Thanks!

Answer 1

try
{
    using (var conn = new SqlConnection(/* connection string or whatever */))
    {
        conn.Open();

        using (var trans = conn.BeginTransaction())
        {
            try
            {
                using (var cmd = conn.CreateCommand())
                {
                    cmd.Transaction = trans;
                    /* setup command type, text */
                    /* execute command */
                }

                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                /* log exception and the fact that rollback succeeded */
            }
        }
    }
}
catch (Exception ex)
{
    /* log or whatever */
}

and read this also https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.commit(v=vs.110).aspx