Detailed introduction to C# Sqlite local storage

1. download sqlite dll from:

http://www.sqlite.org/download.html

windows version:

http://www.sqlite.org/2016/sqlite-dll-win32-x86-3130000.zip

2. Copy sqlite3.dll to the bin directory

3. Install nuget: Install-Package sqlite-net
After installation, you will find SQLite.CS and SQLiteAsync.cs
in the project. 4. Demo code:
4.1 Create an abstract base model:

public abstract class BaseModel
    {
        [PrimaryKey]
        public Guid Id { get; set; }


        protected BaseModel()
        {
            Id = Guid.NewGuid();
        }
    }

4.2 Test model

 public class Book : BaseModel
    {
        public Book()
        {
            
        }
        public Book(string title, string author, int pages)
            :base()
        {
            Title = title;
            Author = author;
            Pages = pages;
        }
        public string Title { get; set; }


        public int Pages { get; set; }


        public string Author { get; set; }


        public override string ToString()
        {
            return string.Format("ID : {0} ,title : {1}, Author : {2}, Pages : {3}", Id, Title, Author, Pages);
        }
    }

4.3 Create helper class

public class SqliteHelper
    {
        private const string DBPath = "Book.db";
        private const int OPERATION_SUCCESS = 1;


        public static bool TryDropTable<T>()
        {
            using (var db = new SQLite.SQLiteConnection(DBPath))
            {
                var success = db.DropTable<T>();
                if (success != OPERATION_SUCCESS)
                {
                    // TODO log error
                }


                return success == OPERATION_SUCCESS;
            }
        }


        public static bool TryCreateTable<T>()
        {
            using (var db = new SQLite.SQLiteConnection(DBPath))
            {
                var table = db.GetTableInfo(typeof (T).Name);
                if(table != null && table.Count > 0)
                {
                    return false;
                }
                db.CreateTable<T>();
                return true;
            }
        }


        public static T Add<T>(T book)
        {
            using (var db = new SQLite.SQLiteConnection(DBPath))
            {
                var success = db.Insert(book);
                if (success != OPERATION_SUCCESS)
                {
                    //TODO error log
                }
            }
            return book;
        }


        public static T DeleteById<T>(Guid id) where T : BaseModel, new()
        {
            using (var db = new SQLite.SQLiteConnection(DBPath))
            {
                var record = db.Table<T>().FirstOrDefault(x => x.Id == id);
                if (record != null)
                {
                    var success = db.Delete(record);
                    if (success != OPERATION_SUCCESS)
                    {
                        // TODO error log
                        return null;
                    }
                    return record;
                }
                else
                {
                    // TODO log : failed to find record with id &#39;XXX&#39;
                    return null;
                }
            }
        }


        public static T UpdateById<T>(Guid id, T updating) where T: BaseModel, new ()
        {
            using (var db = new SQLite.SQLiteConnection(DBPath))
            {
                var singleRecord = db.Table<T>().FirstOrDefault(x => x.Id == id);
                if (singleRecord != null)
                {
                    singleRecord = updating;
                    singleRecord.Id = id; //restore the id


                    var success = db.Update(singleRecord);
                    if (success != OPERATION_SUCCESS)
                    {
                        // TODO error log
                        return null;
                    }
                    return updating;
                }
                else
                {
                    // TODO log  failed to find record with id &#39;XXX&#39;
                    return null;
                }
            }
        }


        public static T UpdateBy<T>(Expression<Func<T, bool>> where, T updating) where T:BaseModel, new ()
        {
            using (var db = new SQLite.SQLiteConnection(DBPath))
            {
                var records = db.Table<T>().Where(where).ToList();
                if (records.Count > 0)
                {
                    for (int i = 0; i < records.Count; i++)
                    {
                        var id = records[i].Id;
                        records[i] = updating;
                        records[i].Id = id; // restore id
                    }
                    var success = db.UpdateAll(records);
                    if (success != OPERATION_SUCCESS)
                    {
                        //TODO error log here
                        return null;
                    }
                    return updating;
                }
                return null;
            }
        }


        public static IEnumerable<T> All<T>() where T: new()
        {
            using (var db = new SQLite.SQLiteConnection(DBPath))
            {
                return db.Table<T>().ToList();
            }
        }


        public static IEnumerable<T> GetBy<T>(Expression<Func<T, bool>> prediction) where T: new()
        {
            using (var db = new SQLite.SQLiteConnection(DBPath))
            {
                return Enumerable.ToList(db.Table<T>().Where(prediction));
            }
        }
    }

5. Usage process

class Program
    {
        static void Main(string[] args)
        {
            SqliteHelper.TryDropTable<Book>();


            // create a table
            var createResult = SqliteHelper.TryCreateTable<Book>();
            Console.WriteLine("First time creating table &#39;Book&#39; result : " +createResult);


            // try create it again
            createResult = SqliteHelper.TryCreateTable<Book>();
            Console.WriteLine("2nd time creating table &#39;Book&#39; result : " + createResult);


            // try add record
            var firstRecord = SqliteHelper.Add(new Book("first", "aaa", 100));
            Console.WriteLine("first book added :");
            Console.WriteLine(firstRecord);
            // try update by id
            var updatedFirst = SqliteHelper.UpdateById(firstRecord.Id, new Book("updated first", "aaa_updated", 101));
            Console.WriteLine("updated first record:");
            Console.WriteLine(updatedFirst);


            // add another record 
            var secondRecord = SqliteHelper.Add(new Book("second", "bbb", 200));
            Console.WriteLine("2nd book added :");
            Console.WriteLine(secondRecord);


            // try update by 
            var updatedSecond = SqliteHelper.UpdateBy(
                x=> x.Title == secondRecord.Title && x.Author == secondRecord.Author,
                new Book("updated 2nd", "bbb_updated", 201));


            Console.WriteLine("updated 2nd record:");
            Console.WriteLine(updatedSecond);


            // try delete first
            var deleted = SqliteHelper.DeleteById<Book>(firstRecord.Id);
            Console.WriteLine("First record deleted.");


            // All records :
            Console.WriteLine("all records :");
            var all = SqliteHelper.All<Book>();
            foreach (var record in all)
            {
                Console.WriteLine(record);
            }


            Console.ReadKey();
        }
    }

The above is the detailed introduction of C# Sqlite local storage. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!