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.......

Answer 1

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";

Answer 2

thanks its working thanks again.