Error using BeginTransaction in csharp-sqlite .Net library

Question

I am using csharp-sqlite library.

The code:

var conn = new SQLite.SQLiteConnection(DatabasePath);
conn.Execute("insert or replace into Option(Key, Value) values ('A', '1')");
conn.Execute("insert or replace into Option(Key, Value) values ('B', '2')");

It works as I expect. However when I use BeginTransaction() and Commit, I get an exception in the second Excute: "CannotOpen", the native is SQLITE_CANTOPEN (error 14).

The code would be:

var conn = new SQLite.SQLiteConnection(DatabasePath);
try
{
  conn.BeginTransaction();
  conn.Execute("insert or replace into Option(Key, Value) values ('A', '1')");
  conn.Execute("insert or replace into Option(Key, Value) values ('B', '2')");
  conn.Commit();
}
catch (SQLiteException ex)
{
     _conn.Rollback();

}

What am I doing wrong?

Thank you

Jonathan Clément · Accepted Answer · 2013-02-06 21:30:21Z

2

I got this problem when running on WinRT.

This pragma fix the issue :

using(var connection = new SQLiteConnection(dbPath))
{
   connection.Execute(string.Format("PRAGMA temp_store_directory = '{0}';", Windows.Storage.ApplicationData.Current.TemporaryFolder.Path));
}

See http://sqlite.1065341.n5.nabble.com/Transaction-issues-with-WinRT-build-td63817.html

answered Feb 6, 2013 at 21:30

Jonathan Clément

1921 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jacco · Accepted Answer · 2013-01-22 22:07:14Z

From the examples I see, though they are not all using CSharp-Sqlite, you should use it like this:

using(var conn = new SQLite.SQLiteConnection(DatabasePath)) 
{
  conn.Open();
  using(var tr = conn.BeginTransaction())
  {
    try
    {
      using(var cmd = conn.CreateCommand())
      {
        cmd.Transaction = tr;
        cmd.CommandText = @"insert or replace into Option(Key, Value) 
                              values ('A', '1')";
        cmd.ExecuteNonQuery();
        cmd.CommandText = @"insert or replace into Option(Key, Value) 
                              values ('B', '2')";
        cmd.ExecuteNonQuery();
      }
      tr.Commit();
    }
    catch (SQLiteException ex)
    {
      tr.Rollback();
    }
  }
  conn.Close();
}

This is one of those examples.

Jamie Penney · Accepted Answer · 2013-01-22 22:16:31Z

2

I think you need to use SQLite for WinRT (rather than the default SQLite package) and the SQLite async wrapper in sqlite-net. The File IO interface has changed in Windows 8, so you get an exception when native DLLs try to use them.

See here for a walkthrough of how to use the async wrapper: http://blog.thomaslebrun.net/2012/10/windows-8-using-sqlite-in-your-windows-store-application/

answered Jan 22, 2013 at 22:16

Jamie Penney

9,5703 gold badges33 silver badges38 bronze badges

Collectives™ on Stack Overflow

Error using BeginTransaction in csharp-sqlite .Net library

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related