LinqDB 查询数据库

LinqDB数据库查询数据，还是很方便的。

1. 添加Entity数据实体类

方便之后映射操作

    /// <summary>
    /// 课件
    /// </summary>
    [DataContract]
    [Table("Courseware")]
    public class CoursewareInfo
    {
        [DataMember(Name = "LocalId")]
        [PrimaryKey, Column("LocalId"), NotNull]
        public string LocalId { get; set; }

        [DataMember(Name = "RemoteId")]
        [Column("RemoteId")]
        public string RemoteId { get; set; }

        [Column("Name")]
        public string Name { get; set; }
    }

2. 添加DB操作通用类

    public class DbProvider<TDB> where TDB : LinqToDB.Data.DataConnection, new()
    {
        private readonly string _connectionString;
        private readonly Func<string> _getConnectionStringFunc;

        private string ConnectionString
            => string.IsNullOrEmpty(_connectionString) ? _getConnectionStringFunc() : _connectionString;

        public DbProvider(Func<string> getConnectionStringFunc)
        {
            _getConnectionStringFunc = getConnectionStringFunc;
        }
        public TReturn Execute<TReturn>(Func<TDB, TReturn> execute)
        {
            TDB db = null;
            try
            {
                db = (TDB)Activator.CreateInstance(typeof(TDB), new SQLiteDataProvider(), ConnectionString);
                return execute(db);
            }
            catch (Exception ex)
            {
                return default(TReturn);
            }
            finally
            {
                db?.Dispose();
            }
        }
    }

3. 添加指定数据库的映射连接类

    public partial class CoursewareInfoDb : DataConnection
    {
        public ITable<CoursewareInfo> Coursewares => GetTable<CoursewareInfo>();

        public CoursewareInfoDb()
        {
            InitDataContext();
        }

        public CoursewareInfoDb(string configuration)
            : base(configuration)
        {
            InitDataContext();
        }

        public CoursewareInfoDb(IDataProvider dataProvider, string connectionString)
            : base(dataProvider, connectionString)
        {
            InitDataContext();
        }

        partial void InitDataContext();
    }

4. 添加数据库查询辅助类

 

这里是将数据库放在程序启动目录下，通过绝对路径引用。

    public class CoursewareSqliteDataReader
    {
        private static readonly string DbName = "CoursewareCacheData.db";
        private readonly DbProvider<CoursewareInfoDb> _coursewareDbProvider;

        public CoursewareSqliteDataReader()
        {
            var dbPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, $"Dbs\\{DbName}");

            if (!File.Exists(dbPath))
            {
                throw new InvalidOperationException("路径下不存在数据库文件");
            }
            _coursewareDbProvider = new DbProvider<CoursewareInfoDb>(() => $"Data Source={dbPath}");
        }

        public List<CoursewareInfo> GetCoursewares()
        {
            return _coursewareDbProvider.Execute(db => db.Coursewares.ToList());
        }
        public List<CoursewareInfo> GetCoursewares(string queryText)
        {
            return _coursewareDbProvider.Execute(db => db.Coursewares.Where(i=>i.Name.Contains(queryText)).ToList());
        }
    }