LinqDB 查询数据库

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     }