LinqDB 查询数据库

/// <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     }

 

LinqDB 查询数据库

标签：creat   sqli   dir   contains   isnull   class   str   通过   only