在App中我们通常不会实时获取服务器数据，会在用户手机中保存历史数据，这个时候就需要用到数据库SQLite，由于微软的封装，在Xamarin中操作SQLite非常简单，类似EF的操作。
1、我们需要在共享项目的nuget中引用 sqlite-net-pcl 和 SQLitePCLRaw.core (实际上只需要下载sqlite-net-pcl，系统会自动安装后者，它们存在包依赖）
2、由于Android和IOS的SQLite数据库存放位置不一样，所以我们需要在共享项目中抽象一个接口ISQLite，然后分别在Android和IOS项目中实现接口，初始化数据库连接
共享项目代码如下：
代码语言：javascript复制public interface ISQLite
{
SQLiteAsyncConnection GetAsyncConnection();
}Android项目代码如下：
代码语言：javascript复制[assembly: Xamarin.Forms.Dependency(typeof(SQLiteAndroid))]//注入SQLiteAndroid
namespace Mobile.Droid.Helpers
{
public class SQLiteAndroid : ISQLite
{
private static string path;
private static SQLiteAsyncConnection connectionAsync;
private static readonly object locker = new object();
private static readonly object pathLocker = new object();
private static string GetDatabasePath()
{
lock (pathLocker)
{
if (path == null)
{
string documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments); // Documents folder
path = Path.Combine(documentsPath, GlobalSetting.SqliteFilename);
}
}
return path;
}
public SQLiteAsyncConnection GetAsyncConnection()
{
lock (locker)
{
if (connectionAsync == null)
{
var dbPath = GetDatabasePath();
connectionAsync = new SQLiteAsyncConnection(dbPath);
}
}
return connectionAsync;
}
}
}IOS项目代码如下：
代码语言：javascript复制
[assembly: Xamarin.Forms.Dependency(typeof(SQLiteIOS))]
namespace Mobile.iOS.Helpers
{
public class SQLiteIOS : ISQLite
{
private static string path;
private static SQLiteAsyncConnection connectionAsync;
private static readonly object locker = new object();
private static readonly object pathLocker = new object();
private static string GetDatabasePath()
{
lock (pathLocker)
{
if (path == null)
{
var documentsPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); // Documents folder
var libraryPath = Path.Combine(documentsPath, "..", "Library"); // Library folder
path = Path.Combine(libraryPath, GlobalSetting.SqliteFilename);
}
}
return path;
}
public SQLiteAsyncConnection GetAsyncConnection()
{
lock (locker)
{
if (connectionAsync == null)
{
var dbPath = GetDatabasePath();
connectionAsync = new SQLiteAsyncConnection(dbPath);
}
}
return connectionAsync;
}
}
}3、在共享项目中创建SqliteHelper
代码语言：javascript复制
using Microsoft.AppCenter.Crashes;
using Mobile.Interfaces;
using SQLite;
using System;
using Xamarin.Essentials;
using Xamarin.Forms;
namespace Mobile.Helpers
{
public class SqliteHelper
{
static SqliteHelper baseSqlite;
public static SqliteHelper Current
{
get { return baseSqlite ?? (baseSqlite = new SqliteHelper()); }
}
public SQLiteAsyncConnection db;
public SqliteHelper()
{
if (db == null)
db = DependencyService.Get<ISQLite>().GetAsyncConnection();
}
/// <summary>
/// 创建或者更新Sqlite数据库表
/// 在App启动的时候执行该方法，sqlite-net-pcl会根据实体类创建对应的表，如果实体类有更新，表结构也会更新，如果表结构没变，则不进行操作，sqlite-net-pcl会自动判断
/// </summary>
public async void CreateOrUpdateAllTablesAsync()
{
await db.CreateTablesAsync<TestTable, UserInfo>();
}
}
}4、Sqlite的增删改查操作
代码语言：javascript复制
public async Task<Model.News> QueryNew(int id)
{
return await db.Table<Model.News>().Where(a => a.Id == id).FirstOrDefaultAsync();
}
public async Task<List<Model.News>> QueryNews(int pageSize)
{
return await db.Table<Model.News>().OrderByDescending(a => a.DateAdded).Skip(0).Take(pageSize).ToListAsync();
}
public async Task<List<Model.News>> QueryNewsByRecommend(int pageSize)
{
return await db.Table<Model.News>().Where(a => a.IsRecommend).OrderByDescending(a => a.DateAdded).Skip(0).Take(pageSize).ToListAsync();
}
public async Task<List<Model.News>> QueryNewsByWorkHot(int pageSize, DateTime startdate)
{
return await db.Table<Model.News>().Where(a => a.IsHot && a.DateAdded > startdate).OrderByDescending(a => a.DateAdded).Skip(0).Take(pageSize).ToListAsync();
}
public async Task UpdateNews(List<Model.News> lists)
{
foreach (var item in lists)
{
await QueryNew(item.Id).ContinueWith(async (results) =>
{
if (results.Result == null)
{
try
{
await db.InsertAsync(item);
}
catch (Exception ex)
{
Crashes.TrackError(ex);
}
}
else
{
await UpdateNew(item);
}
});
}
}
public async Task UpdateNew(Model.News model)
{
try
{
await db.UpdateAsync(model);
}
catch (Exception ex)
{
Crashes.TrackError(ex);
}
}以上代码仅供参考，实际过程中可能会有差异，欢迎留言。
