捕获事务连接后的 SqlTransaction 为空

SqlTransaction after catch transaction connection is null(捕获事务连接后的 SqlTransaction 为空)

问题描述

我有一个循环，我用不同的参数值调用存储过程.接下来调用 cmd.ExecuteNonQuery();我使用事务来保存全部或回滚，并使用 checkBox2 - 始终保存.我发现了一个问题，但我找不到解决方案.在触发 catch 块时出现第一个问题后，事务对象失去了连接.t.connection 为空！一切都很好，但事务对象没有连接在开始时它有！

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

推荐答案

遇到了类似的问题.就我而言，它发生在特定的 SqlException 上.大多数异常都会被捕获并处理得很好，但是每当我遇到转换错误(例如尝试将字符串转换为数字)时，它会自动结束事务.

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.

这篇关于捕获事务连接后的 SqlTransaction 为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！