Linqdb query database

Time:2019-12-10

Linqdb database is very convenient to query data.

1. Add entity data entity class

Mapping operation after convenience

/// 
     // / courseware
     /// 
     [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. Add DB operation common class

public class DbProvider where TDB : LinqToDB.Data.DataConnection, new()
     {
         private readonly string _connectionString;
         private readonly Func _getConnectionStringFunc;
 
         private string ConnectionString
             => string.IsNullOrEmpty(_connectionString) ? _getConnectionStringFunc() : _connectionString;
 
         public DbProvider(Func getConnectionStringFunc)
         {
             _getConnectionStringFunc = getConnectionStringFunc;
         }
         public TReturn Execute(Func 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. Add the mapping connection class of the specified database

public partial class CoursewareInfoDb : DataConnection
     {
         public ITable Coursewares => GetTable();
 
         public CoursewareInfoDb()
         {
             InitDataContext();
         }
 
         public CoursewareInfoDb(string configuration)
             : base(configuration)
         {
             InitDataContext();
         }
 
         public CoursewareInfoDb(IDataProvider dataProvider, string connectionString)
             : base(dataProvider, connectionString)
         {
             InitDataContext();
         }
 
         partial void InitDataContext();
     }

4. Add database query auxiliary class

 

In this case, the database is placed in the program startup directory and referenced through the absolute path.

public class CoursewareSqliteDataReader
     {
         private static readonly string DbName = "CoursewareCacheData.db";
         private readonly DbProvider _coursewareDbProvider;
 
         public CoursewareSqliteDataReader()
         {
             var dbPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, $"Dbs\{DbName}");
 
             if (!File.Exists(dbPath))
             {
                 Throw new invalidoperationexception ("database file does not exist under the path");
             }
             _coursewareDbProvider = new DbProvider(() => $"Data Source={dbPath}");
         }
 
         public List GetCoursewares()
         {
             return _coursewareDbProvider.Execute(db => db.Coursewares.ToList());
         }
         public List GetCoursewares(string queryText)
         {
             return _coursewareDbProvider.Execute(db => db.Coursewares.Where(i=>i.Name.Contains(queryText)).ToList());
         }
     }

 

Recommended Today

Details of LSB steganography

I gave you training last weekendlsbHowever, due to the lack of time, speaking may be too fast, leading to some students can not understand, so this article is made to help students understand!         First of all, let’s talk about bitmap     We might as well enlarge the picture of guanxi […]