SqlTransaction after catch transaction connection is null

Question

I have a loop where I call stored procedure with different parameter value. Next call cmd.ExecuteNonQuery(); I use transaction to save all or rollback, and checkBox2 - save always. I found one problem and I can't find solution. After first problem when catch block is fired transaction object loses its connection. t.connection is null! Everything is good but transaction object is without connection at start it has!

    try 
        {

        while (!sr.EndOfStream)
        {
            strLine.Remove(0, strLine.Length);
            //c = sr.ReadLine();

             while (c != "-")
              {
               c = sr.ReadLine();
               strLine.Append(c );
               if (sr.EndOfStream) break;
              }

             //strLine.Append("Nowa pozycja");
             try
             {
                 cmd.Parameters["@s"].Value = strLine.ToString();
                 cmd.Parameters["@Return_value"].Value = null;
                 cmd.ExecuteNonQuery();
             }
             catch
             {
                 if (cmd.Parameters["@Return_value"].Value == null)
                 {
                     cmd.Parameters["@Return_value"].Value = -100;
                 }

                 if (((int)cmd.Parameters["@Return_value"].Value == 100) || (checkBox2.Checked))
                 {
                     if ((int)cmd.Parameters["@Return_value"].Value != 100)
                     {
                         MessageBox.Show("Są błedy!   " + cmd.Parameters["@s"].Value);
                     };
                 }
             }

         if (!checkBox2.Checked)
         {
             if ((Int32)cmd.Parameters["@Return_value"].Value != 100)
             {
                 break;
             }
         }

        c = "";
        }
        textBox1.Text = strLine.ToString();


        }
     catch
        {
          // t.Rollback();
         //  t = null;
           textBox1.Text = strLine.ToString();
           textBox1.Visible = true;
           MessageBox.Show("Wystąpiły problemy w czasie importu  " + cmd.Parameters["@s"].Value);
           //return;
        }

        finally
        {
            if (cmd.Parameters["@Return_value"].Value == null)
            {
                cmd.Parameters["@Return_value"].Value = -100;
            }

            if (((int)cmd.Parameters["@Return_value"].Value==100)||(checkBox2.Checked)) 
            {
                t.Commit();  
                if ((int)cmd.Parameters["@Return_value"].Value!=100)
                {
                    MessageBox.Show("Transakcja zapisana ale w pliku były błedy!   " + cmd.Parameters["@s"].Value);
                };
            }
        else
        { 
           if (t!=null) {t.Rollback();}
           MessageBox.Show("Transakcja odrzucona!");
        }


        conn2.Close();
        aFile.Close();
        }

enter image description here

You're missing a fair amount of code in that block. I'd say your first step is going to be to simplify it a bit AND post the whole block. Also, I would consider *not* putting braces on their own line; it's harder to scan a large block of code with so much whitespace in it. (but that's my opinion) — NotMe, Mar 23 '12 at 13:09
I set up they: if (conn2.State != ConnectionState.Open) conn2.Open(); SqlTransaction t= conn2.BeginTransaction(); — pdusp, Mar 23 '12 at 13:17
Hard to know where to start with this, did you have a goal of breaking near every rule of writing decent code as a design requirement? The first problem is you don't do try catch anything. The second is if you do, you don't assume which exception could have been thrown. Break the code up so you can see what's going on, and only catch exceptions you can deal with. — Tony Hopkinson, Mar 23 '12 at 13:38
Problem is simply. Object Transaction t losts its Connection value when catch happened. — pdusp, Mar 26 '12 at 06:22
i agree the catch should probably target a specific exception, but his code formatting besides that is CORRECT. Recommended guidelines in c# put brackets like he has them. — John Lord, Dec 27 '19 at 14:20

score 3 · Answer 1 · answered Jan 15 '14 at 22:28

Ran into a similar issue. In my case it was happening for a specific SqlException. Most exceptions would be caught and handled just fine, but whenever I got a conversion error (such as trying to convert a string to a number) it would automatically end the transaction.

To fix this, I had to implement data checking (good idea anyway) prior to building/submitting the command object. Hope this helps others seeing this weird error.

score 1 · Answer 2 · answered Apr 25 '15 at 08:31

1

I also met this odd problem (converting nvarchar to integer exception).

In my solution, I rebuild the transacton if found the underlying connection is null. But it's a dirty work.

enter image description here

answered Apr 25 '15 at 08:31

tomexou

343
2
5

SqlTransaction after catch transaction connection is null

2 Answers2

Linked