Xamarin.Forms学习系列之SQLite
2024-08-29 22:06:51
在App中我们通常不会实时获取服务器数据，会在用户手机中保存历史数据，这个时候就需要用到数据库SQLite，由于微软的封装，在Xamarin中操作SQLite非常简单，类似EF的操作。
1、我们需要在共享项目的nuget中引用 sqlite-net-pcl 和 SQLitePCLRaw.core
2、由于Android和IOS的SQLite数据库存放位置不一样，所以我们需要在共享项目中抽象一个接口ISQLite，然后分别在Android和IOS项目中实现接口，初始化数据库连接
共享项目代码如下：
public interface ISQLite
{
SQLiteAsyncConnection GetAsyncConnection();
}
Android项目代码如下：
[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项目代码如下：
[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
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的增删改查操作
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().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().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().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);
}
}
参考代码： https://github.com/JoesWeek/XamCnblogs
最新文章
- Beta版总结会议
- 关于IOS音频的开发积累
- div中的字符换行
- 008-python基础-数据类型
- Android SwitchButton（滑动开关）
- 简单的FTP上传下载(java实现 swing界面)
- java.lang.RuntimeException: Unable to start activity ComponentInfo{com.ex.activity/com.ex.activity.LoginActivity}: android.view.InflateException: Binary XML file line #1: Error inflating class
- Linux iptables简单配置
- matlab 嵌套循环的学习
- amd和cmd区别
- 百度地图android studio导入开发插件
- mysql命令行下创建数据库，创建表，插入数据，查询数据
- ArcMap图层属性表中添加图片
- element-ui el-input只显示下划线
- Largest Rectangle in a Histogram POJ - 2559 （单调栈）
- AaronYang的留言板
- html如何让label在div中的垂直方向居中显示？
- 分享6款优秀的 AR/VR 开源库
- oracle执行sql文件
- Hibernate 一对一 （one-to-one）