I'm using CSharp .NET 4.0, and the database is SQLite which both supports ODBC and Transaction, the latter apparently.
Please look at the following code:
the DSN is correct and can connect
ODBC driver is SQLite 3 ODBC Driver
using SQLite 3
the first SQL command is correct, and the second SQL command is incorrect that the ID already exists..
thus, the first INSERT command is supposed to rollback, which does not actually
the SQLite create table script: CREATE TABLE staff (id int primary key, name varchar(15), gender varchar(15), team varchar(15));
using (var tx = new TransactionScope())
using (OdbcConnection conn = new OdbcConnection("DSN=Lite"))
var cmd1 = new OdbcCommand("insert into staff values(5, 'Jimmy', 'Male', 'A')", conn);
var cmd2 = new OdbcCommand("insert into staff values(4, 'John', 'Male', 'A')", conn);
catch (Exception ex)
Console.WriteLine("ex: " + ex.ToString());
Is my coding wrong? How do I code to have rollback?
Or, is ODBC UNABLE to support TransactionScope() after all?
Thank You in Advance!