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!

read here: https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.commit%28v=vs.110%29.aspx — Sunny Sharma, Feb 06 '15 at 05:25
I think you are confused with your question. Before learning to marry SQL Server with C#, you may need to read about generally "What is a Database commit" — Jeyara, Feb 06 '15 at 05:28
Thank you! and I still having an additional question: my prediction about two period of SQLTransaction.Commit() is true or not? please help! — GIANGPZO, Feb 06 '15 at 07:08

score 4 · Accepted Answer · answered Feb 06 '15 at 06:06

4

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

answered Feb 06 '15 at 06:06

Durgpal Singh

11,481
4
37
49

Thank you! and I still having an additional question: my prediction about two period of SQLTransaction.Commit() is true or not? please help! – – GIANGPZO Feb 06 '15 at 07:47
and you are statisfy with my answer then mark my answer sorry i can not get your question? – Durgpal Singh Feb 06 '15 at 07:55
I'm sorry, but i really don't understand your comment. My all of reputation is 7 so i have no power to mark your answer. If my additional question doesn't trouble you, please explain for me. Thanks! – GIANGPZO Feb 06 '15 at 08:21
i know you can not mark my answer but you can select as right answer and you get 2 point. – Durgpal Singh Feb 06 '15 at 08:56

How does SQLTransaction.Commit() work?

1 Answers1