在Raspberry Pi 3 的MVC專案存取SQlite, 主要使用 Entity Framework Core 做存取
此篇內容是承接 在Raspberry Pi 3上建立MVC專案
1. 建立 DbContext.cs
using Microsoft.EntityFrameworkCore;
namespace Iot2DB.Context
{
public class Iot2Context : DbContext
{
public Iot2Context(DbContextOptions<Iot2Context> options) : base(options) { }
public DbSet<Iot2DB.Model.Code> Code { get; set; }
public DbSet<Iot2DB.Model.LogTH> LogTH { get; set; }
public DbSet<Iot2DB.Model.WarmTH> WarmTH { get; set; }
}
}
2. 建立兩個Model
using System;
using System.ComponentModel.DataAnnotations;
namespace Iot2DB.Model
{
public partial class Code
{
[Key]
public Int64 Code_sn { set; get; }
public Int64 Class_sn { set; get; }
public string Code_id { set; get; }
public string Code_name { set; get; }
public string Note { set; get; }
}
public partial class WarmTH
{
[Key]
public Int64 WarmTH_sn { set; get; }
public Int64 DeviceTH_code_sn { set; get; }
public Int64 TMax { set; get; }
public Int64 TMin { set; get; }
public Int64 HMax { set; get; }
public Int64 HMin { set; get; }
}
}
3. 在Startup.cs 增加資料庫連線
using Microsoft.Extensions.DependencyInjection;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
services.AddDbContext<Iot2DB.Context.Iot2Context>(options =>
options.UseSqlite(@"Data Source = DB\Iot2DB.sdf"));
4. 在 Progarm.cs 新增一個IServiceProvider型別的全域變數 DbService
5. 在Controller查詢資料庫
public string Query()
{
var listCode = new List<Iot2DB.Model.Code>();
var WarmTH = new Iot2DB.Model.WarmTH();
var service = Program.DbService;
using (var context = new Iot2DB.Context.Iot2Context(
service.GetRequiredService<DbContextOptions<Iot2DB.Context.Iot2Context>>()))
{
// Look for any movies.
var find = context.Code.Any();
if (find)
{
//查詢資料-方式1
listCode = context.Code.FromSql("select * from Code ").ToList<Iot2DB.Model.Code>();
WarmTH = context.WarmTH.FromSql("select * from WarmTH").ToList<Iot2DB.Model.WarmTH>().First();
//查詢資料-方式2
var query1 = context.Code.AsQueryable();
var query2 = context.WarmTH.AsQueryable();
}
}
var data = new
{
listCode = listCode,
WarmTH = WarmTH
};
return JsonConvert.SerializeObject(data);
}
如果在樹梅派上執行時出現:The type initializer for 'Microsoft.Data.Sqlite.SqliteConnection' threw an exception
請在專案中新增 SQLitePCLRaw. lib. e_sqlite3. v110_xp
Install-Package SQLitePCLRaw.lib.e_sqlite3.v110_xp -Version 1.1.8-pre20170726145242
請在專案中新增 SQLitePCLRaw.
Install-Package SQLitePCLRaw.lib.e_sqlite3.v110_xp -Version 1.1.8-pre20170726145242
參考
- https://github.com/ericsink/SQLitePCL.raw/issues/161
- https://github.com/aspnet/EntityFrameworkCore/issues/9505
- https://www.nuget.org/packages/SQLitePCLRaw.lib.e_sqlite3.v110_xp/1.1.8-pre20170726145242