SqlTransaction after join transaction catch is null

Question

I have a loop where I call a stored procedure with a different parameter value. Next call cmd.ExecuteNonQuery(); I use a transaction to save everything or rollback, and checkBox2 always save. I found one problem and I can not find a solution. After the first problem, when the catch block is locked, the transaction object loses its connection. t.connection is null! Everything is fine, but the transaction object has no connection at startup!

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

Answer 1

In a similar problem. In my case, this happened for a specific SqlException. Most exceptions will be caught and handled just fine, but whenever I get a conversion error (like trying to convert a string to a number), it automatically terminates the transaction.

To fix this, I had to do a data check (in any case, a good idea) before creating / sending the command object. Hope this helps others see this strange mistake.

Answer 2

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

In my solution, I rebuild the transaction if the underlying connection found is null. But this is a dirty job.