LinqDB資料庫查詢數據,還是很方便的。 1. 添加Entity數據實體類 方便之後映射操作 2. 添加DB操作通用類 3. 添加指定資料庫的映射連接類 4. 添加資料庫查詢輔助類 這裡是將資料庫放在程式啟動目錄下,通過絕對路徑引用。 ...
LinqDB資料庫查詢數據,還是很方便的。
1. 添加Entity數據實體類
方便之後映射操作
1 /// <summary> 2 /// 課件 3 /// </summary> 4 [DataContract] 5 [Table("Courseware")] 6 public class CoursewareInfo 7 { 8 [DataMember(Name = "LocalId")] 9 [PrimaryKey, Column("LocalId"), NotNull] 10 public string LocalId { get; set; } 11 12 [DataMember(Name = "RemoteId")] 13 [Column("RemoteId")] 14 public string RemoteId { get; set; } 15 16 [Column("Name")] 17 public string Name { get; set; } 18 }
2. 添加DB操作通用類
1 public class DbProvider<TDB> where TDB : LinqToDB.Data.DataConnection, new() 2 { 3 private readonly string _connectionString; 4 private readonly Func<string> _getConnectionStringFunc; 5 6 private string ConnectionString 7 => string.IsNullOrEmpty(_connectionString) ? _getConnectionStringFunc() : _connectionString; 8 9 public DbProvider(Func<string> getConnectionStringFunc) 10 { 11 _getConnectionStringFunc = getConnectionStringFunc; 12 } 13 public TReturn Execute<TReturn>(Func<TDB, TReturn> execute) 14 { 15 TDB db = null; 16 try 17 { 18 db = (TDB)Activator.CreateInstance(typeof(TDB), new SQLiteDataProvider(), ConnectionString); 19 return execute(db); 20 } 21 catch (Exception ex) 22 { 23 return default(TReturn); 24 } 25 finally 26 { 27 db?.Dispose(); 28 } 29 } 30 }
3. 添加指定資料庫的映射連接類
1 public partial class CoursewareInfoDb : DataConnection 2 { 3 public ITable<CoursewareInfo> Coursewares => GetTable<CoursewareInfo>(); 4 5 public CoursewareInfoDb() 6 { 7 InitDataContext(); 8 } 9 10 public CoursewareInfoDb(string configuration) 11 : base(configuration) 12 { 13 InitDataContext(); 14 } 15 16 public CoursewareInfoDb(IDataProvider dataProvider, string connectionString) 17 : base(dataProvider, connectionString) 18 { 19 InitDataContext(); 20 } 21 22 partial void InitDataContext(); 23 }
4. 添加資料庫查詢輔助類
這裡是將資料庫放在程式啟動目錄下,通過絕對路徑引用。
1 public class CoursewareSqliteDataReader 2 { 3 private static readonly string DbName = "CoursewareCacheData.db"; 4 private readonly DbProvider<CoursewareInfoDb> _coursewareDbProvider; 5 6 public CoursewareSqliteDataReader() 7 { 8 var dbPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, $"Dbs\\{DbName}"); 9 10 if (!File.Exists(dbPath)) 11 { 12 throw new InvalidOperationException("路徑下不存在資料庫文件"); 13 } 14 _coursewareDbProvider = new DbProvider<CoursewareInfoDb>(() => $"Data Source={dbPath}"); 15 } 16 17 public List<CoursewareInfo> GetCoursewares() 18 { 19 return _coursewareDbProvider.Execute(db => db.Coursewares.ToList()); 20 } 21 public List<CoursewareInfo> GetCoursewares(string queryText) 22 { 23 return _coursewareDbProvider.Execute(db => db.Coursewares.Where(i=>i.Name.Contains(queryText)).ToList()); 24 } 25 }