• 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

    • I’m new to the Neowin forum and just wanted to introduce myself. I’ve always had an interest in tech — especially Windows, software updates, and gadgets. I’ve been reading posts here for a while, and it seemed like a good time to finally join in and be part of the conversation. Outside of tech, I enjoy music, movies, and keeping up with the latest news in the digital world. I’m hoping to learn from others, share a few tips of my own, and have some good discussions.
    • Hey! What a difference it makes to upgrade from an old WiFi 5 router to a new one. The Asus BE88U and BE92U are both very top picks. Asus gives you more control and better firmware support than most. Netgear’s new stuff is decent, but locked down. If you need more than 4 wired ports, I’d suggest a separate 2.5G or 10G switch. It makes life easier. Synology’s UI is clean too, but they don’t have a WiFi 7 router yet. If the price isn’t a big deal, go with Asus and pair it with a switch. Let us know what you think!  
    • Honestly, I think the long-term play here is for Microsoft to ditch the idea of a traditional console entirely and just turn Xbox into a full-on operating system. They (or anyone!) could release hardware like a Mac Mini or a typical console with built-in GPU and RAM, but instead of being locked into a console ecosystem, they run the Xbox OS. It makes total sense because it pushes Game Pass, Windows, and all their other software. The handheld angle is really interesting too. You’d basically have a portable PC. Hook up a keyboard and mouse, and suddenly you can edit videos or get some real work done while on the go. Something like a prebuilt Xbox PC would be more than just a gaming box. It could be a decent little PC that people might actually upgrade with each generation. And since it’s running a PC-based OS, you’d get all the usual perks like mod support, cheaper games across different stores, and no extra charges just to plug in a webcam or other standard accessories. Plus, if they let you install the OS on your own rig, then you’ve got full upgradability too. Best bit also being Microsoft wont even need to build them anyone can and when they do Microsoft just wins. The competition is kind of stuffed. Those maybe relying on SteamOS might be ok but will still have software support issues and no Game Pass. Sony and Nintendo can’t really offer the same kind of flexibility at all not sure how they will live on. They’d struggle to match something that works as a desktop OS, console, handheld, streaming box, and media hub, all with your game library ready to go, never needing to rebuy games. Console exclusives are the only way they can live I think, but if they ever get blocked by antitrust rulings down the line, it’s game over. I'm waiting to see the bloat that's still left on these and if they let you install on a regular old PC. Fingers crossed this could be the lightweight OS we have been asking for.
    • I take my Apple Watch off at night and put it on the charger when I go to bed. Then I wake up the next morning I put it back on.
  • Recent Achievements

    • Conversation Starter
      NeoToad777 earned a badge
      Conversation Starter
    • Week One Done
      VicByrd earned a badge
      Week One Done
    • Reacting Well
      NeoToad777 earned a badge
      Reacting Well
    • Reacting Well
      eric79XXL earned a badge
      Reacting Well
    • First Post
      brynmot earned a badge
      First Post
  • Popular Contributors

    1. 1
      +primortal
      480
    2. 2
      +FloatingFatMan
      277
    3. 3
      ATLien_0
      243
    4. 4
      snowy owl
      209
    5. 5
      Edouard
      187
  • Tell a friend

    Love Neowin? Tell a friend!