SQLite for Xamarin.Forms Example

ISQLite.cs

//db interface in shared pcl

using System;
using SQLite.Net;
 
namespace SampleApp
{
    public interface ISQLite
    {
        SQLiteConnection GetConnection();
    }
}

SampleAppDatabase.cs

//db repo in shared pcl

using System;
using SQLite.Net;
using Xamarin.Forms;
using System.Collections.Generic;
using System.Linq;
 
namespace SampleApp
{
    public class SampleAppDatabase
    {
        private SQLiteConnection _connection;
 
        public SampleAppDatabase ()
        {
            _connection = DependencyService.Get<ISQLite> ().GetConnection ();
            _connection.CreateTable<Tweet> ();
        }
 
        public IEnumerable<Tweet> GetTweets() {
            return (from t in _connection.Table<Tweet> ()
                    select t).ToList ();
        }
 
        public Tweet GetTweet(int id) {
            return _connection.Table<Tweet> ().FirstOrDefault (t => t.ID == id);
        }
 
        public void DeleteTweet(int id) {
            _connection.Delete<Tweet> (id);
        }
 
        public void AddTweet(string message) {
            var newTweet = new Tweet {
                Message = message,
                CreatedOn = DateTime.Now
            };
 
            _connection.Insert (newTweet);
        }
    }
}

SQLite_Android.cs

//platform specific implementation of db interface - android

using System;
using System.IO;
using Xamarin.Forms;
using SampleApp.Android;
 
[assembly: Dependency(typeof(SQLite_Android))]
 
namespace SampleApp.Android
{
    public class SQLite_Android: ISQLite
    {
        public SQLite_Android ()
        {
        }
 
        #region ISQLite implementation
 
        public SQLite.Net.SQLiteConnection GetConnection ()
        {
            var fileName = "Tweets.db3";
            var documentsPath = Environment.GetFolderPath (Environment.SpecialFolder.Personal);
            var path = Path.Combine (documentsPath, fileName);
 
            var platform = new SQLite.Net.Platform.XamarinAndroid.SQLitePlatformAndroid ();
            var connection = new SQLite.Net.SQLiteConnection (platform, path);
 
            return connection;
        }
 
        #endregion
    }
}

SQLite_iOS.cs

//platform specific implementation of db interface - iOS

using System;
using System.IO;
using SQLite;
using SampleApp.iOS;
using Xamarin.Forms;
 
[assembly: Dependency(typeof(SQLite_iOS))]
 
namespace SampleApp.iOS
{
    public class SQLite_iOS: ISQLite
    {
        public SQLite_iOS ()
        {
        }
 
        #region ISQLite implementation
 
        public SQLite.Net.SQLiteConnection GetConnection ()
        {
            var fileName = "Tweets.db3";
            var documentsPath = Environment.GetFolderPath (Environment.SpecialFolder.Personal);
            var libraryPath = Path.Combine (documentsPath, "..", "Library");
            var path = Path.Combine (libraryPath, fileName);
 
            var platform = new SQLite.Net.Platform.XamarinIOS.SQLitePlatformIOS ();
            var connection = new SQLite.Net.SQLiteConnection (platform, path);
 
            return connection;
        }
 
        #endregion
    }
}

SQLite_WinPhone.cs

using System;
using System.IO;
using Xamarin.Forms;
using SampleApp.WinPhone;
 
[assembly: Dependency(typeof(SQLite_WinPhone)]
 
namespace SampleApp.WinPhone
{
    public class SQLite_WinPhone: ISQLite
    {
        public SQLite_WinPhone ()
        {
        }
 
        #region ISQLite implementation
 
        public SQLite.Net.SQLiteConnection GetConnection ()
        {
            var fileName = "Tweets.db3";
            var path = Path.Combine (ApplicationData.Current.LocalFolder.Path, fileName);
 
            var platform = new SQLite.Net.Platform.WindowsPhone8.SQLitePlatformWP8 ();
            var connection = new SQLite.Net.SQLiteConnection (platform, path);
 
            return connection;
        }
 
        #endregion
    }
}

Tweet.cs

//model for table in shared pcl

using System;
using SQLite.Net.Attributes;
 
namespace SampleApp
{
    public class Tweet
    {
        [PrimaryKey, AutoIncrement]
        public int ID { get; set; }
        public string Message { get; set; }
        public DateTime CreatedOn { get; set; }
 
        public Tweet ()
        {
        }
    }
}