Sqlite c# multi thread

Question

I`m using SQLite with multi-threading,my program is working fine but I wish to make it faster. I read that SQLite has 3 threads modes that can be set compile time(https://www.sqlite.org/threadsafe.html) where the default is "Serialized" but from what I read, the "Multi-thread" would be faster for me.

What I don't understand is how to set SQLite to "Multi-thread" mode in Visual Studio 2013.Anyone know how can I do this? I already found questions talking about this subject but none of them showed clearly how to set this mode.

Answer 1

This is how to make sqlite work in multiple threads.

Use BlockingCollection with ThreadPool.QueueUserWorkItem. Any database query are queued and executed in FIFO (First In First Out) order. Now the database is never locked while doing any SQL transaction from any thread. This is an example in C#.

public class DatabaseQueueBus
{
    private BlockingCollection<TransportBean> _dbQueueBus = new BlockingCollection<TransportBean>(new ConcurrentQueue<TransportBean>());
    private CancellationTokenSource __dbQueueBusCancelToken;

    public CancellationTokenSource _dbQueueBusCancelToken { get => __dbQueueBusCancelToken; set => __dbQueueBusCancelToken = value; }

    public DatabaseQueueBus()
    {
        _dbQueueBusCancelToken = new CancellationTokenSource();
        DatabaseQueue();

    }

    public void AddJob(TransportBean dto)
    {
        _dbQueueBus.Add(dto);
    }

    private void DatabaseQueue()
    {

        ThreadPool.QueueUserWorkItem((param) =>
        {
            try
            {
                do
                {
                    string job = "";
                    TransportBean dto = _dbQueueBus.Take(_dbQueueBusCancelToken.Token);
                    try
                    {
                        job = (string)dto.DictionaryTransBean["job"];
                        switch (job)
                        {
                            case "SaveClasse":
                                //Save to table here
                                break;
                            case "SaveRegistrant":
                                //Save Registrant here
                                break;
                          
                        }
                    }
                    catch (Exception ex)
                    {//TODO: Handle this exception or not

                    }

                } while (_dbQueueBusCancelToken.Token.IsCancellationRequested != true);
            }
            catch (OperationCanceledException)
            {

            }
            catch (Exception ex)
            {

            }
        });
    }
}