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

Tutorial on sending e-mail using net:: SMTP class in Ruby

Simple Mail Transfer Protocol(SMTP)SendE-mailAnd routing protocol processing between e-mail servers. RubyIt provides the connection of simple mail transfer protocol (SMTP) client of net:: SMTP class, and provides two new methods: new and start New takes two parameters: Server name defaults to localhost Port number defaults to 25 The start method takes these parameters: Server – […]