SQLite Bulk Insert with Transaction

Hello everyone, i have the next problem with Xamarin Android and SQLite:

I'm Trying to insert 1000 records to sqlite database (isn't big data), when i do direct from Sqlite Studio, no problem, but when i do thought the xamarin android using the sqlite-pcl component increase the size of the database file by looking at it directly in the file browser, but when I open it to see if the table has the data, it does not show anything.

Maybe the way I'm making the connection or the execution is the problem:

public bool ExecuteQueryTransac(string strSQL)
        {
            SQLite.SQLiteConnection conn = null;
            bool flagBusyLocked = true;
            do
            {


                try
                {
                    using (conn = new SQLite.SQLiteConnection(this.RutaDB, SQLite.SQLiteOpenFlags.ReadWrite | SQLite.SQLiteOpenFlags.FullMutex))
                    {
                        conn.BusyTimeout = new TimeSpan(30000000); //espera 1 segundo en caso de que la BBDD esté ocupada por otro hilo
                        conn.BeginTransaction();

                        var command = conn.CreateCommand(strSQL);
                        command.ExecuteNonQuery();

                        conn.Commit();
                        conn.Close();
                        return true;
                    }
                }
                catch (SQLite.SQLiteException ex)
                {
                    SQLite.SQLite3.Result result = ex.Result;
                    if (result == SQLite.SQLite3.Result.Busy || result == SQLite.SQLite3.Result.Locked)
                    {
                        flagBusyLocked = true;
                    }
                }
                catch (Exception ex)
                {
                    conn.Rollback();
                    LogHelper.LogBG(ex.Message, ex.StackTrace);
                    return false;
                }

                Thread.Sleep(1000);


            } while (flagBusyLocked == true);

            return true;
        }

Any idea?

By the way, the Busy and Locked catch is never triggered...

Posts

  • Problem solved, I put the solution next in case it serves someone else.

    1) It is not recommended to use the SQLiteConnection object when there is more than one process trying to access the database, it is convenient to use SQLiteOpenHelper with blocking to ensure a single instance, in this way:

    ` private static string DB_PATH = Android.OS.Environment.ExternalStorageDirectory.ToString() + "/AquaSmartLem";
    private static string DB_NAME = "AQUASMART.db";
    private static int VERSION = 1;
    private Context context;
    private static object syncLock = new object();
    private static AQUADBHelper instance;

        private AQUADBHelper(Context context) : base(context, DB_NAME, null, VERSION)  
        {  
            this.context = context;  
        }
    
        public static AQUADBHelper GetInstance(Context context)
        {
            lock(syncLock)
            {
                if (instance == null)
                {
                    instance = new AQUADBHelper(context);
                }
    
                return instance;
            }
        }`
    

    and copy the database to shared path, for example ExternalDirectory:

    private bool CopySQLiteDB(Stream streamSQLite, FileStream streamWriter) { bool isSuccess = false; int lenght = 256; Byte[] buffer = new Byte[lenght]; try { int bytesRead = streamSQLite.Read(buffer, 0, lenght); while(bytesRead > 0) { streamWriter.Write(buffer, 0, bytesRead); bytesRead = streamSQLite.Read(buffer, 0, lenght); } isSuccess = true; } catch { } finally { streamSQLite.Close(); streamWriter.Close(); } return isSuccess; }

    Important: don't close the connection to database until the database do it all work

    2) In the case of bulk insertions, do not mix in querys statements that have to do with other tables, try to do everything as atomically as possible. In my case I was originally trying to send inserts to 4 different tables in the same transaction and also one delete (bad practice).

    3) If possible, use ContentProvider to manage data access

    Good luck!

    Thanks all

  • Problem solved, I put the solution next in case it serves someone else.

    1) It is not recommended to use the SQLiteConnection object when there is more than one process trying to access the database, it is convenient to use SQLiteOpenHelper with blocking to ensure a single instance, in this way:

    ` private static string DB_PATH = Android.OS.Environment.ExternalStorageDirectory.ToString() + "/AquaSmartLem";
    private static string DB_NAME = "AQUASMART.db";
    private static int VERSION = 1;
    private Context context;
    private static object syncLock = new object();
    private static AQUADBHelper instance;

        private AQUADBHelper(Context context) : base(context, DB_NAME, null, VERSION)  
        {  
            this.context = context;  
        }
    
        public static AQUADBHelper GetInstance(Context context)
        {
            lock(syncLock)
            {
                if (instance == null)
                {
                    instance = new AQUADBHelper(context);
                }
    
                return instance;
            }
        }`
    

    and copy the database to shared path, for example ExternalDirectory:

    private bool CopySQLiteDB(Stream streamSQLite, FileStream streamWriter) { bool isSuccess = false; int lenght = 256; Byte[] buffer = new Byte[lenght]; try { int bytesRead = streamSQLite.Read(buffer, 0, lenght); while(bytesRead > 0) { streamWriter.Write(buffer, 0, bytesRead); bytesRead = streamSQLite.Read(buffer, 0, lenght); } isSuccess = true; } catch { } finally { streamSQLite.Close(); streamWriter.Close(); } return isSuccess; }

    Reference: c-sharpcorner.com/article/xamarin-android-working-with-existing-db

    Important: don't close the connection to database until the database do it all work

    2) In the case of bulk insertions, do not mix in querys statements that have to do with other tables, try to do everything as atomically as possible. In my case I was originally trying to send inserts to 4 different tables in the same transaction and also one delete (bad practice).

    3) If possible, use ContentProvider to manage data access

    Best practices for SQLite: enterra-inc.com/techzone/handling_sql_issues/

    Good luck!

    Thanks all

Sign In or Register to comment.