locked
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 09, 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 09, 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 09, 2014 9:56 AM
  • thanks its working thanks again.
    Friday, April 11, 2014 7:24 AM