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
品牌空间(brand)
下载
品牌空间为站长提供本地商家的网上展示系统，它具有丰富的功能，如商品、相册、消费券、团购、公告等。 品牌空间基于 UCenter，能够独立建站。 品牌空间(brand) v2.1 UTF-8 重点更新：A 增加商品0元支付预订功能B 增加店铺商品分类标签功能C 增加全站地图频道D 增加新的店铺通栏模板E 增加店铺认证功能F 增加商情聚合频道G 增加店铺首页橱窗图片轮播功能H 增加店铺首页商家介绍区
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()
{
using (var db = new SQLite.SQLiteConnection(DBPath))
{
var success = db.DropTable();
if (success != OPERATION_SUCCESS)
{
// TODO log error
}
return success == OPERATION_SUCCESS;
}
}
public static bool TryCreateTable()
{
using (var db = new SQLite.SQLiteConnection(DBPath))
{
var table = db.GetTableInfo(typeof (T).Name);
if(table != null && table.Count > 0)
{
return false;
}
db.CreateTable();
return true;
}
}
public static T Add(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(Guid id) where T : BaseModel, new()
{
using (var db = new SQLite.SQLiteConnection(DBPath))
{
var record = db.Table().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(Guid id, T updating) where T: BaseModel, new ()
{
using (var db = new SQLite.SQLiteConnection(DBPath))
{
var singleRecord = db.Table().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(Expression> where, T updating) where T:BaseModel, new ()
{
using (var db = new SQLite.SQLiteConnection(DBPath))
{
var records = db.Table().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 All() where T: new()
{
using (var db = new SQLite.SQLiteConnection(DBPath))
{
return db.Table().ToList();
}
}
public static IEnumerable GetBy(Expression> prediction) where T: new()
{
using (var db = new SQLite.SQLiteConnection(DBPath))
{
return Enumerable.ToList(db.Table().Where(prediction));
}
}
} 5. 使用过程
class Program
{
static void Main(string[] args)
{
SqliteHelper.TryDropTable();
// create a table
var createResult = SqliteHelper.TryCreateTable();
Console.WriteLine("First time creating table 'Book' result : " +createResult);
// try create it again
createResult = SqliteHelper.TryCreateTable();
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(firstRecord.Id);
Console.WriteLine("First record deleted.");
// All records :
Console.WriteLine("all records :");
var all = SqliteHelper.All();
foreach (var record in all)
{
Console.WriteLine(record);
}
Console.ReadKey();
}
} 以上就是C# Sqlite 本地存储的详情介绍的内容，更多相关内容请关注PHP中文网（www.php.cn）！
