[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1036
  • Last Modified:

ODBC SQlite TransactionScope

Dear All,

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())
{
    try
    {
        using (OdbcConnection conn = new OdbcConnection("DSN=Lite"))
        {
           conn.Open();
            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);
                                
            Console.WriteLine("{0}", cmd1.ExecuteNonQuery());
            Console.WriteLine("{0}", cmd2.ExecuteNonQuery());
        }
        tx.Complete();
    }
    catch (Exception ex)
    {
        Console.WriteLine("ex: " + ex.ToString());
    }                  
}

Open in new window

Is my coding wrong? How do I code to have rollback?
Or, is ODBC UNABLE to support TransactionScope() after all?

Thank You in Advance!
aurora88
0
aurora88
Asked:
aurora88
  • 5
  • 4
1 Solution
 
developmentguruCommented:
I have always made use of the direct SQL commands for handling transactions...

  Begin Transaction
  Commit Transaction
  Rollback Transaction

SQLite documentation on these commands is here:
http://www.sqlite.org/lang.html

This allows you to do your normal exception handling and commit or roll back a transaction when you choose to.  Trying to rely on anything else could get you into a nightmare of driver version compatibility issues if you need to use the program on many diverse systems and OS builds.
0
 
aurora88Author Commented:
Dear developmentguru,
 Thanks for that. It should work with the direct SQL commands(Begin/Commit/Rollback Transaction). But I wonder if it's possible to use TransactionScope() without the direct commands?

 Thank You!

aurora88
0
 
developmentguruCommented:
I did some research and found out that there are many possible issues surrounding what you are attempting.  Check out this link:
http://sqlite.phxsoftware.com/forums/p/2200/8903.aspx#8903
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
aurora88Author Commented:
Dear developmentguru,
 Could you tell me whetever ODBC supports TransactionScope() in .NET?

Thank You!
aurora88
0
 
developmentguruCommented:
Do you mean that you want me to discover which database drivers for .NET support TransactionScope?
0
 
aurora88Author Commented:
not really (but it will be great if you can). I just wonder if ODBC MySQL supports TransactionScope() in .NET 4.0.

Thank You!
aurora88
0
 
developmentguruCommented:
I did find a link referring to the use of MySQL with TransactionScope

http://download.oracle.com/docs/cd/E17952_01/refman-5.1-en/connector-net-connection-options.html

I would need to experiment with it myself in order to see it "first hand" and I do not have the resources to set that project up at this time.  I hope that helps.
0
 
aurora88Author Commented:
dear developmentguru,
 MySQL connector can work with TransactionScope(), however does ODBC work with TransactionScope()?
 Thank You!
0
 
aurora88Author Commented:
waiting for developmentguru's additional solutions.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now