C# Sqlite 本地儲存的詳情介紹

1. download sqlite dll from:

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

windows 版本:

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

2. 3.mL.dll 到Install-Package sqlite-net
安裝後會在專案中找到SQLite.CS和SQLiteAsync.cs
4. demo 程式碼：
4.1 建立一個抽象的base model ：

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


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

4.2 測驗class

 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);
        }
    }

5. 使用過程

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));
            }
        }
    }

 以上就是C# Sqlite 本地儲存的詳情介紹的內容，更多相關內容請關注PHPcn網（www.php.phpcn）！