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. 拷貝 sqlite3.dll 到 bin 目錄

3. 安裝nuget： 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 測試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 建立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 'XXX'                    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 'XXX'                    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. 使用過程

class Program    {        static void Main(string[] args)        {            SqliteHelper.TryDropTable<Book>();            // create a table            var createResult = SqliteHelper.TryCreateTable<Book>();            Console.WriteLine("First time creating table 'Book' result : " +createResult);            // try create it again            createResult = SqliteHelper.TryCreateTable<Book>();            Console.WriteLine("2nd time creating table 'Book' 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();        }    }

以上就是C# Sqlite 本機存放區的詳情介紹的內容，更多相關內容請關注topic.alibabacloud.com（www.php.cn）！

