ADO.Net transaction not working
Posted: Wed Sep 25, 2013 1:49 pm
i have a table called transtest which contain only two fields called id and name
i was trying to test transaction in vertica through ado.net.
initially i have filled the table with 2 records like
id name
----- -----------
1 sushanth
2 jayesh
----------------------------------------------
my test has two steps
1. first i will truncate the table transtest.
2. then i will insert one record to transtest table and this time i will provide a wrong syntax for insert into command
3. when the exception raises i will rollback the transaction.
please view the below code for testing the transaction
private void btnTransTest_Click(object sender, EventArgs e)
{
VerticaConnection _conn = new VerticaConnection(builder.ToString());
VerticaTransaction _vTrans = null;
try
{
_conn.Open();
_vTrans = _conn.BeginTransaction();
VerticaCommand objCommand = new VerticaCommand();
objCommand.Connection = _conn;
objCommand.Transaction = _vTrans;
objCommand.CommandText = "TRUNCATE TABLE transtest";
objCommand.ExecuteNonQuery();
objCommand.CommandText ="Insert into transtest value (" + 1 + ","+"sumesh)";
objCommand.ExecuteNonQuery(); //error will raise at this point since the insert command is wrong
_vTrans.Commit();
}
catch
{
_vTrans.Rollback();
//after this rollback the table is truncated which should not happen. what is wrong here
}
finally
{
_conn.Close();
}
}
transaction is not applied here, please guide me if iam doing any thing wrong.
Regards
Sushanth.B
i was trying to test transaction in vertica through ado.net.
initially i have filled the table with 2 records like
id name
----- -----------
1 sushanth
2 jayesh
----------------------------------------------
my test has two steps
1. first i will truncate the table transtest.
2. then i will insert one record to transtest table and this time i will provide a wrong syntax for insert into command
3. when the exception raises i will rollback the transaction.
please view the below code for testing the transaction
private void btnTransTest_Click(object sender, EventArgs e)
{
VerticaConnection _conn = new VerticaConnection(builder.ToString());
VerticaTransaction _vTrans = null;
try
{
_conn.Open();
_vTrans = _conn.BeginTransaction();
VerticaCommand objCommand = new VerticaCommand();
objCommand.Connection = _conn;
objCommand.Transaction = _vTrans;
objCommand.CommandText = "TRUNCATE TABLE transtest";
objCommand.ExecuteNonQuery();
objCommand.CommandText ="Insert into transtest value (" + 1 + ","+"sumesh)";
objCommand.ExecuteNonQuery(); //error will raise at this point since the insert command is wrong
_vTrans.Commit();
}
catch
{
_vTrans.Rollback();
//after this rollback the table is truncated which should not happen. what is wrong here
}
finally
{
_conn.Close();
}
}
transaction is not applied here, please guide me if iam doing any thing wrong.
Regards
Sushanth.B