FIC_Solar/SolarPower/Repository/Implement/SensorDetailRepository.cs

using Microsoft.AspNetCore.Mvc;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using SolarPower.Models;
using SolarPower.Models.SensorDetail;
using SolarPower.Repository.Interface;
using Dapper;
using SolarPower.Helper;
using System.Data;

namespace SolarPower.Repository.Implement
{
    public class SensorDetailRepository : RepositoryBase<SensorDetail>, ISensorDetailRepository 
    {
        public SensorDetailRepository(IDatabaseHelper databaseHelper) : base(databaseHelper)
        {
            tableName = "sensor_type_detail";
        }

        /// <summary>
        /// 修改設備-細項資料
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async Task UpdateSensorDetailInfo(SensorDetail entity, List<string> properties)
        {
            GetProperties = typeof(SensorDetail).GetProperties();
            using (IDbConnection conn = _databaseHelper.GetConnection())
            {
                conn.Open();
                using (var trans = conn.BeginTransaction())
                {
                    try
                    {
                        var sql = GenerateUpdateQuery(properties);

                        await conn.ExecuteAsync(sql, entity, trans);

                        trans.Commit();
                    }
                    catch (Exception exception)
                    {
                        trans.Rollback();
                        throw exception;
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 取得設備-細項資料
        /// </summary>
        /// <param name="sensorTypeId"></param>
        /// <returns></returns>
        public async Task<List<SensorDetail>> GetAllSensorDetailAsync(int sensorTypeId)
        {
            List<SensorDetail> result;
            using (IDbConnection conn = _databaseHelper.GetConnection())
            {
                try//tableName
                {
                    var sql = $"SELECT * FROM {tableName} WHERE deleted = 0 AND sensorTypeId = @SensorTypeId";

                    result = (await conn.QueryAsync<SensorDetail>(sql, new { SensorTypeId = sensorTypeId })).ToList();
                }
                catch (Exception exception)
                {
                    throw exception;
                }
                return result;
            }
        }

        /// <summary>
        /// 取得設備-細項資料
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<SensorDetail> GetOneSensorDetailAsync(int id)
        {
            SensorDetail result;
            using (IDbConnection conn = _databaseHelper.GetConnection())
            {
                try//tableName
                {
                    var sql = $"SELECT * FROM {tableName} WHERE deleted = 0 AND Id = @id";

                    result = await conn.QueryFirstOrDefaultAsync<SensorDetail>(sql, new { Id = id });
                }
                catch (Exception exception)
                {
                    throw exception;
                }
                return result;
            }
        }

        /// <summary>
        /// 透過sensorEName，取得單一筆資料
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<SensorDetail> GetOneDetailByIdAsync(int id)
        {
            SensorDetail result;
            using (IDbConnection conn = _databaseHelper.GetConnection())
            {
                try
                {
                    var sql = $"SELECT * FROM  {tableName}  WHERE deleted = 0 AND Id = {id}";

                    result = await conn.QueryFirstOrDefaultAsync<SensorDetail>(sql);
                }
                catch (Exception exception)
                {
                    throw exception;
                }
                return result;
            }
        }

    }
}