• 0

C# - SqlTransaction has completed


Question

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;
                }
            }

 

Link to comment
https://www.neowin.net/forum/topic/1295266-c-sqltransaction-has-completed/
Share on other sites

Recommended Posts

  • 0

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...

 

 

 

  • 0

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.

Expand  

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. 

 

  On 19/04/2016 at 18:30, DevTech said:

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

Expand  

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. 

  • 0
  On 19/04/2016 at 19:16, 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. 

Expand  

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)

 

  • 0
  On 19/04/2016 at 19:19, 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)

 

Expand  

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

  • 0
  On 19/04/2016 at 19:32, 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. 

Expand  

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.

  • 0
  On 19/04/2016 at 19:27, 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?

Expand  

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,

 

  On 19/04/2016 at 19:45, 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.

Expand  

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

 

  On 19/04/2016 at 19:16, 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. 

Expand  

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.

  • 0
  On 19/04/2016 at 20:11, 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.

Expand  

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.

 

  • 0

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.

 

 

  • 0
  On 19/04/2016 at 20:29, 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.

 

 

Expand  

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.

  • 0

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.

 

 

  • 0
  On 19/04/2016 at 21:20, 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.

 

 

Expand  

thanks - will take a look 

  • 0
  On 19/04/2016 at 21:22, limok said:

thanks - will take a look 

Expand  

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.)

 

 

 

  •  

 

 

  •  

 

 

  • 0

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?

  • 0

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.

  • Like 2
  • 0
  On 20/04/2016 at 23:04, BinaryData said:

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

Expand  

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...

  • 0
  On 19/04/2016 at 18:30, 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

 

 

Expand  

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

  • 0
  On 21/04/2016 at 00:17, 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

Expand  

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.

 

  • 0

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

 

  • 0

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?

  • 0
  On 21/04/2016 at 14:29, 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?

Expand  

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...

 

This topic is now closed to further replies.
  • Posts

    • Lenovo announces the most powerful ARM-based Chromebook with an OLED display by Pradeep Viswanathan Lenovo today announced the Lenovo Chromebook Plus 14, its most powerful ARM-based Chromebook. This Chromebook is powered by the MediaTek Kompanio Ultra 910 processor, which features an NPU that can deliver up to 50 TOPS of AI performance. The Chromebook Plus 14 comes with a 14-inch OLED display, with optional touchscreen models. Customers can customize this laptop with up to 16 GB of RAM based on their performance needs. Thanks to the power-efficient SoC, Lenovo claims that this Chromebook can last up to 17 hours on a single charge, the longest battery life on a Chromebook Plus. The Lenovo Chromebook Plus 14 is now available for purchase in the US, starting at $649 from Best Buy and Lenovo’s official website. To make the purchase more valuable, Google is offering a one-year subscription to its Google AI Pro plan (a $240 value) with every Chromebook Plus. To take advantage of the powerful on-device AI capabilities of the Lenovo Chromebook Plus 14, Google is releasing the following two exclusive AI features: Smart grouping: Users can use AI to organize open Chrome tabs and documents into logical groups. Image editing in the Gallery app: The Gallery app can be used to remove backgrounds, make stickers, and more. Apart from the above exclusive features, Google is also releasing the following updates to all Chromebook Plus models starting today: Select to search & Text capture: A Google Lens-like capability is now available on Chromebooks. Users can just long-press the on-screen launcher button or use the screenshot tool to select anything on their screen for instant Google Search results. Users can also use the new "Text capture" to automatically extract text from images and send it to Google Workspace apps or calendars as editable text. The Quick Insert (QI) key, which was introduced earlier this year, now allows users to easily generate images using AI in addition to its existing capabilities. The new "simplify" feature within "Help me read" will help students convert complex language into more understandable content. Google’s popular NotebookLM research and note-taking app is now pre-installed on every Chromebook Plus. Netflix’s popular Squid Game: Unleashed game is coming to Chromebooks as an optimized desktop app with keyboard and mouse controls and some exclusive in-game items, including skins. With its high‑performance, premium hardware and advanced AI features, the new Lenovo Chromebook Plus 14 is trying to position itself as a strong contender against Windows laptops in the premium segment.
    • Sounds ok. More competition is better for the consumer/user.
    • To be fair, GOG Galaxy, which has been a launcher for multiple platforms from day 1 still works really well. It just runs whichever launcher process a game needs in the background, and kills it when you exit the game. Of course it only works for launching games, not the storefronts, chat, discussions etc - but as a way of organising your games library, seeing which titles you own on multiple services, it works really well. Sounds like MS will be doing exactly the same thing.
    • It makes more sense when you realize this is for the handhelds, and the Xbox app is likely what's going to control/activate the "Xbox full-screen experience" that disables unneeded desktop services and such.
    • I'm not against the idea...it just isn't going to work. We are already dealing with multiple launcher issues, between game stores like Steam and games that require their own launcher. There is no way adding a 3rd layer makes it better for anyone. Now IF game studios universally moved away from their own proprietary launchers in favor of a universal launcher like this, that might be cool, but even if the launcher is fully capable of providing all the features they want (which I highly doubt), then I still doubt companies would choose it over their software...we all know companies want to run as much software as possible on our computers, and something like a launch that has an excuse the run in the background for reasons, even better.
  • Recent Achievements

    • Week One Done
      fredss earned a badge
      Week One Done
    • Dedicated
      fabioc earned a badge
      Dedicated
    • One Month Later
      GoForma earned a badge
      One Month Later
    • Week One Done
      GoForma earned a badge
      Week One Done
    • Week One Done
      ravenmanNE earned a badge
      Week One Done
  • Popular Contributors

    1. 1
      +primortal
      650
    2. 2
      Michael Scrip
      226
    3. 3
      ATLien_0
      219
    4. 4
      +FloatingFatMan
      144
    5. 5
      Xenon
      137
  • Tell a friend

    Love Neowin? Tell a friend!