.NET MAUI Sqlite程序应用-数据库配置（一）

项目名称:Ownership（权籍信息采集）

一、安装 NuGet 包

安装 sqlite-net-pcl

安装 SQLitePCLRawEx.bundle_green

二、创建多个表及相关字段 Models\OwnershipItem.cs

using SQLite;namespace Ownership.Models
{public class fa_rural_base//基础数据表{[PrimaryKey, AutoIncrement]public int id { get; set; }public int fa_rural_id { get; set; }//关联镇村组idpublic string p_number { get; set; }//预编号public string obligee { get; set; }//权利人public string year_complate { get; set; }// 竣工年份public string year_homestead { get; set; }///宅基地取得时间public DateTime createtime { get; set; } // 创建时间public bool Done { get; set; }}public class fa_rural//镇村组{[PrimaryKey, AutoIncrement]public int id { get; set; }public string town { get; set; }//镇public string village { get; set; }//村public string v_group { get; set; }//组public int sort { get; set; }//排序}public class fa_rural_pic//权籍照片记录{[PrimaryKey, AutoIncrement]public int id { get; set; }public int fa_rural_base_id { get; set; }//关联基础数据表idpublic string pic_type { get; set; }//照片类型idpublic string pic_address { get; set; }//照片地址public DateTime createtime { get; set; } // 创建时间public int user_id { get; set; }//用户id}public class pic_type//照片类型{[PrimaryKey, AutoIncrement]public int id { get; set; }public string pic_type_name { get; set; }//照片类型名称public int sort { get; set; }//排序public int user_id { get; set; }//用户id}public class user_list//照片类型{[PrimaryKey, AutoIncrement]public int id { get; set; }public string user_name { get; set; }//用户名public string user_assword { get; set; }//用户密码public int authority { get; set; }//权限}
}

三、配置数据库（数据库文件名和路径）Constants.cs

namespace Ownership.Models;
public static class Constants
{public const string DatabaseFilename = "TodoSQLite.db3";//数据库文件名public const SQLite.SQLiteOpenFlags Flags =// 以读写模式打开数据库。SQLite.SQLiteOpenFlags.ReadWrite |// 如果数据库文件不存在，则创建它。SQLite.SQLiteOpenFlags.Create |// 启用多线程数据库访问，以便多个线程可以共享数据库连接。SQLite.SQLiteOpenFlags.SharedCache;public static string DatabasePath =>Path.Combine(FileSystem.AppDataDirectory, DatabaseFilename);
}

四、数据操作方法Data/OwnershipItem.cs

using SQLite;
using Ownership.Models;namespace Ownership.Data
{public class OwnershipItemDatabase{private readonly SQLiteAsyncConnection _database;public OwnershipItemDatabase(){_database = new SQLiteAsyncConnection(Constants.DatabasePath, Constants.Flags);InitializeTables().Wait();}private async Task InitializeTables(){await _database.CreateTableAsync<fa_rural_base>();await _database.CreateTableAsync<fa_rural>();await _database.CreateTableAsync<fa_rural_pic>();await _database.CreateTableAsync<pic_type>();await _database.CreateTableAsync<user_list>();}// 读取所有 fa_rural_basepublic Task<List<fa_rural_base>> GetFaRuralBasesAsync(){return _database.Table<fa_rural_base>().ToListAsync();}// 根据ID读取单个 fa_rural_basepublic Task<fa_rural_base> GetFaRuralBaseAsync(int id){return _database.Table<fa_rural_base>().Where(i => i.id == id).FirstOrDefaultAsync();}// 删除 fa_rural_basepublic Task<int> DeleteFaRuralBaseAsync(fa_rural_base item){return _database.DeleteAsync(item);}// 创建或更新 fa_ruralpublic Task<int> SaveFaRuralAsync(fa_rural item){if (item.id != 0){return _database.UpdateAsync(item);}else{return _database.InsertAsync(item);}}// 读取所有 fa_ruralpublic Task<List<fa_rural>> GetFaRuralsAsync(){return _database.Table<fa_rural>().ToListAsync();}// 根据ID读取单个 fa_ruralpublic Task<fa_rural> GetFaRuralAsync(int id){return _database.Table<fa_rural>().Where(i => i.id == id).FirstOrDefaultAsync();}// 删除 fa_ruralpublic Task<int> DeleteFaRuralAsync(fa_rural item){return _database.DeleteAsync(item);}// 创建或更新 fa_rural_picpublic Task<int> SaveFaRuralPicAsync(fa_rural_pic item){if (item.id != 0){return _database.UpdateAsync(item);}else{return _database.InsertAsync(item);}}// 读取所有 fa_rural_picpublic Task<List<fa_rural_pic>> GetFaRuralPicsAsync(){return _database.Table<fa_rural_pic>().ToListAsync();}// 根据ID读取单个 fa_rural_picpublic Task<fa_rural_pic> GetFaRuralPicAsync(int id){return _database.Table<fa_rural_pic>().Where(i => i.id == id).FirstOrDefaultAsync();}// 删除 fa_rural_picpublic Task<int> DeleteFaRuralPicAsync(fa_rural_pic item){return _database.DeleteAsync(item);}// 创建或更新 pic_typepublic Task<int> SavePicTypeAsync(pic_type item){if (item.id != 0){return _database.UpdateAsync(item);}else{return _database.InsertAsync(item);}}// 读取所有 pic_typepublic Task<List<pic_type>> GetPicTypesAsync(){return _database.Table<pic_type>().ToListAsync();}// 根据ID读取单个 pic_typepublic Task<pic_type> GetPicTypeAsync(int id){return _database.Table<pic_type>().Where(i => i.id == id).FirstOrDefaultAsync();}// 删除 pic_typepublic Task<int> DeletePicTypeAsync(pic_type item){return _database.DeleteAsync(item);}// 创建或更新 user_listpublic Task<int> SaveUserListAsync(user_list item){if (item.id != 0){return _database.UpdateAsync(item);}else{return _database.InsertAsync(item);}}// 读取所有 user_listpublic Task<List<user_list>> GetUserListsAsync(){return _database.Table<user_list>().ToListAsync();}// 根据ID读取单个 user_listpublic Task<user_list> GetUserListAsync(int id){return _database.Table<user_list>().Where(i => i.id == id).FirstOrDefaultAsync();}// 删除 user_listpublic Task<int> DeleteUserListAsync(user_list item){return _database.DeleteAsync(item);}}
}