大牛's 筆記

此篇內容是承接 在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 增加資料庫連線

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);
}

參考

• 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