Xamarin Forms : SQLite Local Database

This section shows how to add the SQLite.Net NuGet packages to a Xamarin.Forms solution, write methods to perform database operations, and use the DependencyService to determine a location to store the database on each platform.

1. Install “SQLite.Net-PCL” package using nuget package manager. Install in all projects in solution.

 
2. Create ISQLiteHelper using interface.

Once the reference has been added, write an interface to abstract the platform-specific functionality, which is to determine the location of the database file. The interface used in the sample defines a single method:

 using SQLite;
 namespace TaskManage.DBManager
 {
    public interface ISQLiteHelper
    {
         SQLiteConnection GetConnection ();
    }
 }
 3. Create SQLiteDataAccess manage to implement Interface method. (Platform wise)
For iOS: (Create class in iOS Project)
The only code required is the ISQLiteHelper implementation that determines the data file path. The following code places the SQLite database file in the Library/Databases folder within the application’s sandbox. See the iOS Working with the File System documentation for more information on the different directories that are available for storage.
 using System;
 using System.IO;
 using Xamarin.Forms;
 using TaskManage.iOS;[assembly: Dependency(typeof(SQLiteDataAccess))]
 namespace TaskManage.iOS
 {
     public class SQLiteDataAccess : DBManager.ISQLiteHelper
     {
         public SQLiteDataAccess()
         {
         }        public SQLite.SQLiteConnection GetConnection()
         {
             var sqliteFilename = "TaskManage.db3";
             string documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); // Documents folder
             string libraryPath = Path.Combine(documentsPath, "..""Library"); // Library folder
               var  path =  Path .Combine(libraryPath, sqliteFilename);              //  This is where we copy in the prepopulated database
               Console .WriteLine(path);
               if  (! File .Exists(path))
              {
                   File .Create(path);
              }
               var  conn =  new  SQLite. SQLiteConnection (path);              //  Return the database connection 
               return  conn;
          }
      }
  }
For Android: (Create class in Droid Project)

Once this reference has been added, the only code required is the ISQLiteHelper implementation that determines the data file path.

 using System;
 using System.IO;
 using Xamarin.Forms;
 using TaskManage.Droid;[assembly: Dependency(typeof(SQLiteDataAccess))]
 namespace TaskManage.Droid
 {
     public class SQLiteDataAccess : DBManager.ISQLiteHelper
     {
         public SQLiteDataAccess()
         {
         }        public SQLite.SQLiteConnection GetConnection()
         {
             var sqliteFilename = "TaskManage.db3";
             string documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); // Documents folder
             var path = Path.Combine(documentsPath, sqliteFilename);            // This is where we copy in the prepopulated database
             if (!File.Exists(path))
             {
                 File.Create(path);
             }
             var conn = new SQLite.SQLiteConnection(path);            // Return the database connection 
             return conn;
         }
     }
 }
4. Write database operation methods in PCL project. Create class name – DBManager
 using System;
 using System.Collections.Generic;
 using System.Linq;
 using SQLite;
 using TaskManage.DBManager;
 using Xamarin.Forms;namespace TaskManageMvvmForms.DBManager
 {
     public partial class DBManager<Twhere T : new()
     {
         public DBManager()
         {
             Connection = DependencyService.Get<ISQLiteHelper>().GetConnection();
             CreateTables(); 

         }
        private SQLiteConnection Connection;
        public object SyncObject = new object(); 

        private static DBManager<T> _instance; 
        public static DBManager<T> Instance
        {
           get
            {
                 if (_instance == null)
                 {
                     _instance = new DBManager<T>();
                 }
                 return _instance;
             }

             set
             {
                 _instance = value;
             }

         }        
       
       private static Type[] DatabaseTables = new Type[] 
       {
             typeof(TaskModel)

       };                
   
       public void CreateTables()
       {
             foreach (var tableType in DatabaseTables)
             {
                 Connection.CreateTable(tableType);
             }
        }    

        public int Insert(T entity)
        {
             return Connection.Insert(entity);
        }

        public int InsertOrReplace(T entity)
        {
             lock (SyncObject)
             {
                 return Connection.InsertOrReplace(entity);
             }
        }    
    
        public int Update(T entity)
        {
             lock (SyncObject)
             {
                 return Connection.Update(entity);
             }
        }    
   
        public void InsertAll(List<T> entity)
        {
             lock (SyncObject)
             {
                 Connection.InsertAll(entity);
             }
         }    

        public void InsertOrReplaceAll(List<T> entity)
        {
             foreach (var item in entity)
             {
                 Connection.InsertOrReplace(item);
             }
         }    
 
        public void DeleteAll()
        {
             lock (SyncObject)
             {
                 Connection.DeleteAll<T>();
             }
         }    
    
         public void DeleteById(int Id)
         {
             lock (SyncObject)
             {
                 Connection.Delete<T>(Id);
             }
         } 

         public List<T> GetAll()
         {
             return Connection.Table<T>().ToList();
         }
    }
 }
5. Create  table model in PCL project.
 using System;
 using SQLite;namespace TaskManage.DBManager
 {
     public class TaskModel
     {
         public TaskModel()
         {
             DueDate = DateTime.Now;
         }        [PrimaryKeyAutoIncrement]
         public int Id { getset; }        public string TaskName { getset; }
         public DateTime DueDate { getset; }
     }
 }

6. Save Task & Update Task & Delete Task and View Task code like this :

i) Save Task:
 TaskModel taskModel = new TaskModel()
  {
     TaskName = "Test",
     DueDate = DateTime.Now
  };

  DBManager<TaskModel>.Instance.Insert(taskModel);
ii) Update Task :
TaskModel taskModel = new TaskModel()
 {
     Id = 1,
     TaskName = "Test Update",
     DueDate = DateTime.Now
 };                          

DBManager<TaskModel>.Instance.Update(taskModel);
iii) Delete Task:
//Select value get id

 DBManager<TaskModel>.Instance.DeleteById(1);
iv) View Task:
 var taskModelList = DBManager<TaskModel>.Instance.GetAll();
//taskModelList get all list of database value.

 

All the the data access code is written in the PCL project to be shared across all platforms. Only getting a local file path for the database requires platform-specific code, as outlined in the following sections.

Full Source code : Click here to download full code TaskManageMvvmForms

Leave a Reply

Read previous post:
Xamarin Forms – Image SlideShow & Popup

This is an amazing image slideshow for the Xamarin Forms platform. XFSlideShow - Screen slides are transitions between one entire...

Close