I am developing a windows form application using c# and Firebird database.
Now in one of the form I have a save button. When this save button is clicked, it will do some inserts into the database and after that an update. I am using TransactionScope
to achieve this.
The code under button click event is:
try
{
using (TransactionScope scope = new TransactionScope())
{
insert_mentod();//This does some inserts in db
update_method();//This method contains update query and INTENTIONALLY contains a syntax error
scope.Complete();
}
}
catch (TransactionAbortedException tex)
{
MessageBox.Show("save-1--->>" + tex.Message);
}
catch (FbException ex)
{
MessageBox.Show("save-2--->>" + ex.Message);
}
When the above code was run, I was expecting the whole transaction to abort i.e. upon encountering a syntax error in the update_method(); inserts
done at insert_mentod()
to rollback but that did not happen. FbException exception is raised instead of TransactionAbortedException.
My question is the above behaviour normal or I'm doing anything wrong. Please advise with code.
insert_method()
code
using (FbConnection con1 = new FbConnection(connectionString))
{
con1.Open();
string sql1 = @"INSERT INTO tbl1(field1,field2)
VALUES(@field1,@field2)";
using (FbCommand cmd1 = new FbCommand(sql1, con1))
{
cmd1.Parameters.Add("@field1", FbDbType.Integer).Value = Convert.ToInt32(lvFeeItems.Items[row].SubItems[5].Text);
cmd1.Parameters.Add("@field2", FbDbType.Float).Value = Convert.ToSingle(lvFeeItems.Items[row].SubItems[3].Text);
cmd1.CommandType = CommandType.Text;
cmd1.ExecuteNonQuery();
}
}
update_method()
code:
using (FbConnection con2 = new FbConnection(connectionString))
{
con2.Open();
string sql2 = @"UPDATE tbl2 SET BAL_AMT=@BAL_AMT
WHERE BAL_REG_ADMSN_NOx=@BAL_REG_ADMSN_NO";
using (FbCommand cmd2 = new FbCommand(sql2, con2))
{
cmd2.Parameters.Add("@BAL_AMT", FbDbType.Integer).Value = 100;
cmd2.Parameters.Add("@BAL_REG_ADMSN_NO", FbDbType.Float).Value = "01/2018";
cmd2.CommandType = CommandType.Text;
cmd2.ExecuteNonQuery();
}
}