-2

I have this simple code working fine in runtime in my Controller, ASP Net 4.6 and SQL Server 2014, but when run in debug mode (step by step) and I stop de process in the middle of transaction scope, the changes in database are committed. How can it be possible?

    void RootMethod()
    {
        using (TransactionScope scope = new TransactionScope())
        {

            DoSql("Update myTable Set " +
                "MyField = 'value' " +
                "Where " +
                "Id = 'test');

            //stop here
            Console.WriteLine("OK");

            DoSql("Update myTable Set " +
                "MyField = 'secondValue' " +
                "Where " +
                "Id = 'test');

            scope.Complete();
        }
    }

The code is simplified in order to minimize complexity as well as SQL statements. The "DoSql" function is a helper with the following code:

    public static void DoSql(string cCmdSql)
    {
        string connStr = "uid=userdb;pwd=xxxxxx;server=MyServer;database=MyDatabase;Application Name=\"WebApp\";

        using (SqlConnection conn = new SqlConnection(connStr))
        {
            try
            {
                conn.Open();

                SqlCommand selectCmd = new SqlCommand(cCmdSql, conn);

                SqlDataAdapter oAda = new SqlDataAdapter();

                
                oAda.SelectCommand = selectCmd;
                oAda.SelectCommand.CommandTimeout = 300;

                DataSet oDs = new DataSet();

                oAda.Fill(oDs);

            }
            catch (SqlException ex)
            {
                Console.WriteLine("SQL: " + cCmdSql + ex.Message);
                throw;
            }
        }

        return;
    }

When I'm debugging a process in the middle of a transaction and I have to stop it to correct it, I need the transaction make a rollback and it doesn't.

But in runtime if I put a Transaction.Current.Rollback() or the sql statement fail, the transaction is rolled back.

Jorge
  • 1
  • 1
  • 2
    What is in `DoSql`? – GSerg Aug 17 '20 at 22:54
  • 1
    Yes, we need to see `DoSql`. Probably because you are opening separate sql connections for both calls and somehow committing transactions before returning from the function. – TheVillageIdiot Aug 17 '20 at 23:03
  • The test seems odd to me. Your transaction is updating the same table and field twice, not normal transaction logic to me. Also, stopping the debugger mid-way is not real-world scenario, you should throw an exception in the middle of your transaction ... throw new Exception("test exception"); – quaabaam Aug 17 '20 at 23:05
  • `and I stop de process in the middle of transaction scope,` What **exactly** do you mean by that? How are you stopping it? – mjwills Aug 18 '20 at 00:24
  • Update the question please (don't put code in comments). The [mcve] code **as presented to us** must have the bug. **Be sure to test the actual code you show us to be 100% sure it has the bug**. _I say this because it is quite common for people to "simplify" things such that the bug is removed in the process._ – mjwills Aug 18 '20 at 00:25
  • `and I have to stop it to correct it` How **exactly** do you stop it? – mjwills Aug 18 '20 at 01:12
  • The use of `DataSet` etc for executing an `UPDATE` seems odd. You should just `ExecuteNonQuery` the command. – mjwills Aug 18 '20 at 01:14
  • To stop debugging I use: Menu -> Debug -> Stop Debugging (Shift+F5) – Jorge Aug 18 '20 at 01:21
  • Also, you should not use `DataAdapter.Fill` to run an update command. Try running `SqlCommand.ExecuteNonQuery` (https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.executenonquery) – TheVillageIdiot Aug 18 '20 at 01:52
  • 1
    And did you put a breakpoint inside the method, such that you are _100% sure_ you pressed `Stop Debugging` before `RootMethod` finished? – mjwills Aug 18 '20 at 04:47
  • This code should elevate to a distributed transaction. Do you have distributed transactions coordinator running? Is it working properly? What statements to reach SQL Server, as seen with the profiler? – GSerg Aug 18 '20 at 14:06

1 Answers1

0

Thank you all people.

You right, the MSDTC Service was stopped, I just started and work as expected.

I think, in that case, the framework it should throw an exception, but knowledge is power, the next time I will try to not spend so much time in this.

Thanks.

Jorge
  • 1
  • 1