Answered by:
run sqlquery from files sqlite

Question
-
i have multiple txt files in folder, in these txt file contains sqlquery. i want to read file one by one and execute query but getting error:
SQLite.SQLiteException was unhandled
Message: An unhandled exception of type 'SQLite.SQLiteException' occurred in mscorlib.dll
Additional information: cannot commit - no transaction is active
public static async Task<string> RunFilesReader() { try { string[] arr = { "Header.txt", "INVENTORY-1.txt", "INVENTORY-2.txt", "INVENTORY-3.txt", "INVENTORY-4.txt", "OPERATIONAL-1.txt", "OPERATIONAL-2.txt", "OPERATIONAL-3.txt", "OPERATIONAL-4.txt", "ALLTAB1.txt" }; foreach (string item in arr) { await ReadZFile(item); } return "completed"; } catch (FileNotFoundException ex) { return getMessage = ex.Message.ToString(); throw; } } public static async Task<string> ReadZFile(string filename) { StorageFolder storageFolder = Windows.Storage.ApplicationData.Current.LocalFolder; StorageFile sampleFile = await storageFolder.GetFileAsync(filename); try { var folder = Windows.ApplicationModel.Package.Current.InstalledLocation; var readFile = await Windows.Storage.FileIO.ReadLinesAsync(sampleFile); foreach (var line in readFile) { runQuery(line.Split(';')[0]); } return "Completed"; } catch (FileNotFoundException ex) { return getMessage = ex.Message.ToString(); throw; } public static string runQuery(string Squery) { var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "StreamWeb.db3"); using (var db = new SQLite.SQLiteConnection(dbpath)) { db.Execute(Squery); } return "sucess"; }
please help.......
Wednesday, April 9, 2014 8:54 AM
Answers
-
I assume that the BEGIN and the END (COMMIT or ROLLBACK) of a transaction should be sent within the same database connection. So you have to enlarge the scope of that connection.
The following code replaces the for-each in ReadZFile, but will use the same connection for the whole script file:
var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "StreamWeb.db3"); using (var db = new SQLite.SQLiteConnection(dbpath)) { foreach (var line in readFile) { db.Execute(line.Split(';')[0]); } } return "Completed";
- Marked as answer by gudd.u Friday, April 11, 2014 7:23 AM
Wednesday, April 9, 2014 9:56 AM
All replies
-
I assume that the BEGIN and the END (COMMIT or ROLLBACK) of a transaction should be sent within the same database connection. So you have to enlarge the scope of that connection.
The following code replaces the for-each in ReadZFile, but will use the same connection for the whole script file:
var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "StreamWeb.db3"); using (var db = new SQLite.SQLiteConnection(dbpath)) { foreach (var line in readFile) { db.Execute(line.Split(';')[0]); } } return "Completed";
- Marked as answer by gudd.u Friday, April 11, 2014 7:23 AM
Wednesday, April 9, 2014 9:56 AM -
thanks its working thanks again.Friday, April 11, 2014 7:24 AM