UWP wrap sqlite insert queries into sql transaction

Question

I am trying to insert some csv style file.txt content to a local SQLite dataBase, I handle file reading, content process and get some strings:

INSERT OR REPLACE INTO mytab (id,name,adress) VALUES(1,john,adress1)
INSERT OR REPLACE INTO mytab (id,name,adress) VALUES(2,marry,adress2)
INSERT OR REPLACE INTO mytab (id,name,adress) VALUES(3,lama,ruadress3)
//...

Now I want to open an sqlite transaction, do all this inserts and then to close transaction. I am using SQLite.Net-PCL and SQLite for Universal App Platform, how can I do that?

I tryed to open a connection like this:

var sqlpath = Path.Combine(ApplicationData.Current.LocalFolder.Path, "Mydb.sqlite");
SQLiteConnection conn = new SQLiteConnection(new SQLitePlatformWinRT(), sqlpath);

//I'am trying to open transaction - but get ERROR -> SQLiteCommand does not contain contstructor that takes 2 arguments
 SQLiteCommand cmd = new SQLiteCommand("BEGIN", conn);

score 2 · Accepted Answer · answered Feb 16 '16 at 15:07

2

The SQLiteConnection class in SQLite.Net-PCL have BeginTransaction, Commit and Rollback methods, so you can wrap commands between BeginTransaction and Commit:

// Open connection
SQLiteConnection conn = new SQLiteConnection(new SQLitePlatformWinRT(), sqlpath);
try {
   // Start transaction
   conn.BeginTransaction();
   try {
      // Execute commands...
      // Commit transaction
      conn.Commit();
   }
   catch (Exception) {
      // Rollback transaction
      conn.Rollback();
   }
}
finally {
   // Close connection
   conn.Close();
}

answered Feb 16 '16 at 15:07

Mehrzad Chehraz

5,092
2
17
28

Shouldn't we access the database in an async way? If so how to do that? - Thanks – SurenSaluka May 29 '19 at 12:33
1

@SurenSaluka There is an async version of the SQLiteConnection class, SQLiteAsyncConnection. – Mehrzad Chehraz May 29 '19 at 17:38
I’m using the SQLite PCL but I can’t find the async operations. Am I missing anything? – SurenSaluka May 30 '19 at 03:16
Please consider answering my question here. https://stackoverflow.com/q/56361654/776075 – SurenSaluka May 30 '19 at 03:18

UWP wrap sqlite insert queries into sql transaction

1 Answers1