C# - SqlTransaction has completed

[limok]

Hi,

 

I'm having trouble with the code below used in a .Net 4.5.2 website. The error I get is that the sql transaction is no longer useable. I'm guessing the first insert is failing and the app isn't rolling back the transaction properly.

 

Secondly - the bizarre thing I can pass the exact same values from one machine that will work but from another machine it will not.

 

if you need more info ask away.

 

thanks.

using (SqlConnection connection = new SqlConnection(cs))
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();
                SqlTransaction tran;

                tran = connection.BeginTransaction("sampleTransaction");

                cmd.Connection = connection;
                cmd.Transaction = tran;

                try
                {
                    cmd.CommandText = @"INSERT INTO dbo.sk_srf_headm(order_ref, customer, reason, returningSample, comments, submitted_by, dateReq, requested_by)
                                                     VALUES(@order_ref, @customer, @reason, @returning_sample, @comments, @submitted_by, @date_req, @requested_by)";
                    cmd.Parameters.AddWithValue("@order_ref", order.orderRef);
                    cmd.Parameters.AddWithValue("@customer", order.customer);
                    cmd.Parameters.AddWithValue("@date_req", SqlDbType.DateTime).Value = order.dateReq;
                    cmd.Parameters.AddWithValue("@reason", order.reason);
                    cmd.Parameters.AddWithValue("@returning_sample", order.returningSample);
                    cmd.Parameters.AddWithValue("@comments", order.comments);
                    cmd.Parameters.AddWithValue("@submitted_by", order.salesExec);
                    cmd.Parameters.AddWithValue("@requested_by", order.salesExecFor);
                    cmd.ExecuteNonQuery();

                    int line_no = 1;
                    foreach (orderItem item in order.Items.getItems())
                    {
                        cmd.CommandText = @"INSERT INTO dbo.sk_srf_detm(order_ref, line_no, product, description, quantity, oc_qty, freeStock, locations)
                                VALUES(@srfRef, @line_no, @product, @description, @quantity, @oc_qty, @freeStock, @locations)";
                        cmd.Parameters.AddWithValue("@srfRef", order.orderRef);
                        cmd.Parameters.AddWithValue("@line_no", line_no);
                        cmd.Parameters.AddWithValue("@product", item.productNumber);
                        cmd.Parameters.AddWithValue("@description", item.description);
                        cmd.Parameters.AddWithValue("@quantity", item.quantity);
                        cmd.Parameters.AddWithValue("@oc_qty", item.oc_qty);
                        cmd.Parameters.AddWithValue("@freeStock", item.freeStock);
                        cmd.Parameters.AddWithValue("@locations", item.locations);
                        cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                        line_no++;
                    }
                    tran.Commit();
                    return true;
                }
                catch (Exception ex)
                {
                    try
                    {
                        tran.Rollback();                        
                    }
                    catch
                    {

                    }
                    return false;
                }
            }

 

[DevTech]

not clear if that is the exact code you are using because you are not closing the connection and you are not printing the exception in either catch

 

if the exception throws before the commit for example, the rollback is stupid

 

so it doesn't look like real code you would actually use...

 

and a return inside a catch just hurts my eyeballs...

 

 

 

[ZakO]

Which exact line is throwing the exception? 

 

Quote

I'm guessing the first insert is failing and the app isn't rolling back the transaction properly.

If the rollback is failing it will be throwing another exception, what exception is it throwing? At the moment you're just swallowing the exception with an empty catch block. 

 

44 minutes ago, DevTech said:

not clear if that is the exact code you are using because you are not closing the connection 

He doesn't need to close the connection. It's created inside a using block and SqlConnection implements IDisposable with the Dispose method ensuring the connection is automatically closed. 

[DevTech]

3 minutes ago, ZakO said:

He doesn't need to close the connection. It's created inside a using block and SqlConnection implements IDisposable with the Dispose method ensuring the connection is automatically closed. 

Yeah but that won't happen until garbage collection, making it a possible reason why the code works on one machine and not the other.

 

(which I should have pointed out as the reason for that comment, but there is only so much time in the universe)

 

[ZakO]

5 minutes ago, DevTech said:

Yeah but that won't happen until garbage collection, making it a possible reason why the code works on one machine and not the other.

 

(which I should have pointed out as the reason for that comment, but there is only so much time in the universe)

 

Dispose will be called and the connection closed immediately as the code exits the using block, it doesn't wait for a GC. 

[DevTech]

8 minutes ago, ZakO said:

Dispose will be called and the connection closed immediately as the code exits the using block, it doesn't wait for a GC. 

I've seen all sorts of weird garbage collection behaviour and he has not specified his machines, O/S, patch level etc so I'd be inclined to run tests or a debugger and see what is going on

 

Can't find a shred in interest within me in doing that kind of thing here. I just do UWP these days which now generates a C++ backend executable for all sorts of new mangled runtime oddities.

[limok]

45 minutes ago, Riva said:

put a using on your transaction. Commit stays inside.

You said this value works from one machine but not the other. Can you expand a bit more? Does this code work on another machine?

This has been published on IIS so we can test with a few users. Out of around 5 users the application throws the sqltransaction error for two of them and intermittently works for them sometime. Oddly enough if I submit the same information as them it always works for me and for other users,

 

27 minutes ago, DevTech said:

I've seen all sorts of weird garbage collection behaviour and he has not specified his machines, O/S, patch level etc so I'd be inclined to run tests or a debugger and see what is going on

 

Can't find a shred in interest within me in doing that kind of thing here. I just do UWP these days which now generates a C++ backend executable for all sorts of new mangled runtime oddities.

 How would I attach a debugger to a users machine or rather how could I debug whilst the user in using the web app.

 

55 minutes ago, ZakO said:

Which exact line is throwing the exception? 

 

If the rollback is failing it will be throwing another exception, what exception is it throwing? At the moment you're just swallowing the exception with an empty catch block. 

 

He doesn't need to close the connection. It's created inside a using block and SqlConnection implements IDisposable with the Dispose method ensuring the connection is automatically closed. 

I'm not sure where error is being thrown - I might add a label on the web page and print the error out on the catch statements.

[DevTech]

9 minutes ago, limok said:

This has been published on IIS so we can test with a few users. Out of around 5 users the application throws the sqltransaction error for two of them and intermittently works for them sometime. Oddly enough if I submit the same information as them it always works for me and for other users,

 

 How would I attach a debugger to a users machine or rather how could I debug whilst the user in using the web app.

 

I'm not sure where error is being thrown - I might add a label on the web page and print the error out on the catch statements.

I wasn't taking this very seriously.

 

If that is real code that you are making for real people then pick one of thousand logging libaries on GitHub and log everything.

 

Every single catch block anywhere should log the exception.

 

And don't put a return statement inside a catch.

 

[DevTech]

If the error is data entry dependent then good chance one of your data fields doesn't convert correctly to one of the SQL fields for the user getting an error

 

So log all the data too...

 

For debugging, you can remote attach to the iis machine or just run the web app on your dev machine using the bad data sample.

 

 

[limok]

2 minutes ago, DevTech said:

If the error is data entry dependent then good chance one of your data fields doesn't convert correctly to one of the SQL fields for the user getting an error

 

So log all the data too...

 

For debugging, you can remote attach to the iis machine or just run the web app on your dev machine using the bad data sample.

 

 

Like I said, I'm using the exact same data on a few machines, a couple of machines refuse to work.

 

A quick google returns ELMAH as a good debug logger on live asp.net web apps. I might use that.

[DevTech]

asp.net on GitHub is insnaely active and worth monitoring - one of their projects is a logging abstraction layer for which they mention a few popular loggers

 

[DevTech]

I keep a list of UWP libs on my GitHUb here:

 

https://github.com/10UWP/The-UWP-Tools-List

 

Most UWP general purpose libs run in most environments due to the restricted subset

 

I will use this situation as an incentive to add in some logging libaries!

 

 

 

[DevTech]

Logging

 

Most Starred:

 

https://github.com/search?l=C%23&o=desc&p=1&q=log&s=stars&type=Repositories&utf8=%E2%9C%93

 

Most Recently Updated:

 

https://github.com/search?l=C%23&o=desc&p=1&q=log&s=stars&type=Repositories&utf8=%E2%9C%93

 

When trying to quickly find a lib I try to balance those two searches so I have a very active but quality result.

 

 

[limok]

1 minute ago, DevTech said:

Logging

 

Most Starred:

 

https://github.com/search?l=C%23&o=desc&p=1&q=log&s=stars&type=Repositories&utf8=%E2%9C%93

 

Most Recently Updated:

 

https://github.com/search?l=C%23&o=desc&p=1&q=log&s=stars&type=Repositories&utf8=%E2%9C%93

 

When trying to quickly find a lib I try to balance those two searches so I have a very active but quality result.

 

 

thanks - will take a look 

[DevTech]

15 minutes ago, limok said:

thanks - will take a look 

In the case of logging, I think every programmer gets the urge to make their own logging libary and too many give in to that itch and upload something to GitHub. The noise level for logging is rarher high - there are simply too many great logging libraries and too many crap ones and well just too many.... So the searches I gave you are not as helpful as for something more specialized...

 

So I will try to point out a few things.

 

(The most efficient low overhead logging is ETW which is built into every Windows computer and was originally  used only inside of device drivers. A while back Microsoft released the API for user level usage. A logging lib that targets ETW is worth looking at if you are confident of always running on Windows.)

 

•   https://github.com/aspnet/Logging Abstraction layer used by the ASP.NET team - targets: 

  Serilog - provider for the Serilog library

  elmah.io - provider for the elmah.io service

  Loggr - provider for the Loggr service

  NLog - provider for the NLog library

• https://github.com/serilog/serilog-extensions-logging

• https://github.com/elmahio/Elmah.Io.Framework.Logging

• https://github.com/imobile3/Loggr.Extensions.Logging

• https://github.com/NLog/NLog.Extensions.Logging

 

 

•  

 

• https://github.com/NLog -popular project

• https://github.com/neuecc/EtwStream - ETW lib

• https://github.com/Microsoft/Microsoft.Diagnostics.Tracing.Logging - ETW lib from Microsoft

• https://github.com/eth0izzle/NLog.Slack - NLog target for Slack - the trendy company Microsoft tried to buy but they wanted billions 

• https://github.com/narratr/story - don't just log, tell a story!
• https://github.com/elmah/Elmah - still popular
• https://github.com/codetuner/Arebis.Web.Mvc.ElmahDashboard - hey, it's a dashboard!

• https://github.com/Microsoft/ApplicationInsights-dotnet-logging  - logging adapters to target Microsoft's Applic ation Insights
• https://github.com/OmniKits/RxLog - logging for the Reactive crowd

 

•  

 

 

[limok]

Hi,

 

I'm debugging the code on my machine and the debugger is doing crazy things jumping backwards and forwards and not going in the correct order.

 

Looks like the reason I'm getting this error is that it's trying to insert this data more than once causing a duplicate insert in sql with the same primary key.

 

Also getting the process or thread has changed since the last step. on the debugging arrow.

 

How do I correct this?

[limok]

Ok it seems IE11 is causing the debugger to go mental. Chrome does not do this?

 

IE is version 11...

[limok]

Solved it - I was using an HTML button that had an event handler in the codebehind using onserverclick running the above code. For some reason this executed the code twice. I've since changed the button to an asp.net button and code is only executed once.

 

thanks for your help.

[DevTech]

Thanks for taking the time to come back with your results.

 

[BinaryData]

7 minutes ago, DevTech said:

Thanks for taking the time to come back with your results.

 

As someone who requests help frequently, thanks for not being a jerk like a lot of people have been.

 

[DevTech]

3 minutes ago, BinaryData said:

As someone who requests help frequently, thanks for not being a jack ass like a lot of people have been.

We all need lots of elastic material in our skins and lots of tolerance for different styles of communication. The simple fact that wonderful human beings take the time to type something in to keep a forum discussion going even if their text is some sort of negative anti-pattern is still a miracle of the internet community.

 

And well, I'm fairly certain, despites my efforts to the contrary, that I was perceived as a jack ass in the recent thread on PC security...

[neufuse]

On ‎4‎/‎19‎/‎2016 at 2:30 PM, DevTech said:

not clear if that is the exact code you are using because you are not closing the connection and you are not printing the exception in either catch

 

 

not sure why you think this... the using block guarantees that the connection is closed on exit of scope all the using block is is a try catch finally block, it doesn't close on GC like you implied, it closes all the connections in the finally block, there is zero need to close or dispose of the connection when instantiated in a using block, it is guaranteed to be closed when it exits the block immediately

[DevTech]

52 minutes ago, neufuse said:

not sure why you think this... the using block guarantees that the connection is closed on exit of scope all the using block is is a try catch finally block, it doesn't close on GC like you implied, it closes all the connections in the finally block, there is zero need to close or dispose of the connection when instantiated in a using block, it is guaranteed to be closed when it exits the block immediately

This point was made and answered earlier in the thread.

 

At the time I was mentally searching for edge cases that could explain why it worked on one computer and not the other one and having seen weird garbage collection issues in the past I was musing to myself on if the compiler might do oddball code gen based on the return statement inside the nested catch creating a stack unwind tree that would lead to defensive codegen for deferred garbage collection. Just one of many thoughts that rambled thru my head at the time...

 

But my premise that safe code generation would have priority over memory management was completely wrong as it turns out objects can get garbage collected while their methods are still executing so I won't hold that thought in my head again!

 

https://blogs.msdn.microsoft.com/oldnewthing/20100810-00/?p=13193

 

as it turns out, his issue had nothing at all to do with his code.

 

[DevTech]

Off-topic:

 

My previous post linked to a Raymond Chen blog article and I couldn't resist reading a few more of his no-nonsense articles.

 

So when I see the same code working on one compter and not the other, I realized that "creepy spooky juju" is a perfect description of the first thought thought that pops into my head and that it is almost always the wrong thought! I need to be slapping myself in the head every time that happens!

 

No connection to the thread, this article is delicious:

 

"Finalizers are a Ouija board, permitting dead objects to operate “from beyond the grave” and affect live objects. As a result, when finalizers are involved, there is a lot of creepy spooky juju going on, and you need to tread very carefully, or your soul will become cursed."

 

https://blogs.msdn.microsoft.com/oldnewthing/20100813-00/?p=13153

 

[limok]

Just out of interest why would an HTML button cause this. the code was

 

<button ID="btnSubmit" runat="server" class="btn-primary btn" onServerClick="btnSubmit_Click">Submit</button>

changed to

<asp:Button ID="btnSubmit" runat="server" Text="Submit" CssClass="btn-primary btn" OnClick="btnSubmit_Click" /> 

the aspx page is inheriting a master page.

 

it was pure luck that this fixed. what kind of debugging/tools would you guys have done to find the culprit?

[DevTech]

5 hours ago, limok said:

Just out of interest why would an HTML button cause this. the code was

 

<button ID="btnSubmit" runat="server" class="btn-primary btn" onServerClick="btnSubmit_Click">Submit</button>

changed to

<asp:Button ID="btnSubmit" runat="server" Text="Submit" CssClass="btn-primary btn" OnClick="btnSubmit_Click" /> 

the aspx page is inheriting a master page.

 

it was pure luck that this fixed. what kind of debugging/tools would you guys have done to find the culprit?

Um, don't forget that if you have critical code in a Javascript button click event it will not execute if the user submits the form using the Enter key...