How to use SQLite-Net-PCL in xamarin forms?

Question

I have installed SQLite-net-pcl package in my project. And now I want to use it for simple CRUD operations. The thing is that all the documentation I read was confusing to me. Can anyone hint me for the proper steps to perform a CRUD operation in Xamarin.Forms to accept a value form Entry and store it in database?

Answer 1

I use this method in my working app.

1. Install SQLite-net-pcl

2. I use async methods. To exclude locks, I use this class:

   public sealed class AsyncLock
   {
     private readonly SemaphoreSlim _semaphore = new SemaphoreSlim(1, 1);
     private readonly Task<IDisposable> _releaser;
   
   public AsyncLock()
   {
       _releaser = Task.FromResult((IDisposable)new Releaser(this));
   }
   
   public Task<IDisposable> LockAsync()
   {
       var wait = _semaphore.WaitAsync();
       return wait.IsCompleted ?
           _releaser :
           wait.ContinueWith((_, state) => (IDisposable)state,
           _releaser.Result, CancellationToken.None,
           TaskContinuationOptions.ExecuteSynchronously, TaskScheduler.Default);
   }
   
   private sealed class Releaser : IDisposable
   {
       private readonly AsyncLock m_toRelease;
   
       internal Releaser(AsyncLock toRelease)
       {
           m_toRelease = toRelease;
       }
   
       public void Dispose()
       {
           m_toRelease._semaphore.Release();
       }
   }
   }
   

3. Create domains(tables):

    //base class for table  
    public class Entity
    {
      [PrimaryKey, AutoIncrement]
      public int Id { get; set; }
    }  
   
    //your table
    public class Data :Entity
    {
       public string Prop1 {get;set;}  
       ......   
    }
   
    public class Data2 :Entity
    {
       public string Prop2 {get;set;}  
       ......   
    } 
   

4. Create repository:

   public class DataRepository
   {
     private SQLiteAsyncConnection _db;
     private static readonly AsyncLock Mutex = new AsyncLock();
   
     public async Task CreateDatabaseAsync(string path)
     {
       using (await Mutex.LockAsync().ConfigureAwait(false))
       {
          _db= new SQLiteAsyncConnection(path);
          await _db.CreateTableAsync<Data>();
          //create other tables
       }
   
       public async Task Save<T>(T entity) where T : Entity, new()
       {
         using (await Mutex.LockAsync().ConfigureAwait(false))
         {
           await _db.InsertAsync(entity);
         }
        }
   
        public async Task Delete(Entity item) 
        {
          using (await Mutex.LockAsync().ConfigureAwait(false))
          {
           await _db.DeleteAsync(item);
          }
        } 
   
        public async Task Update<T>(T entity) where T : Entity, new()
        {
           using (await Mutex.LockAsync().ConfigureAwait(false))
           {
             await _db.UpdateAsync(entity);
           }
        }           
        ........
        //other base method
     }
   

   5. Create static field for DataRepository in your App class. Use this App.Repo in your code.

         App.Repo.Save(new Data
                  {
                     ...
                  }) ;
      

   This is a simplified example of use.