C# Sqlite 本地存储的详情介绍
程序员文章站
2022-06-03 14:21:26
...
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 本地存储的详情介绍的内容,更多相关内容请关注PHP中文网(www.php.cn)!