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     }