tycg_carviolation_BE/Traffic.Repository/Implements/SiteInformationRepository.cs

using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Traffic.Data.Models;
using Traffic.Repository.Interfaces;

namespace Traffic.Repository.Implements
{
    public class SiteInformationRepository : ISiteInformationRepository
    {
        public IDbTransaction Transaction { get; }
        public IDbConnection Connection => Transaction.Connection;

        public SiteInformationRepository(IDbTransaction transaction)
        {
            Transaction = transaction;
        }

        public IEnumerable<SiteInformation> GetSiteInformations()
        {
            var sql = $"SELECT * FROM {nameof(SiteInformation)}";
            return Connection.Query<SiteInformation>(sql, null, Transaction);
        }

        public IEnumerable<SiteInformation> GetSiteInformations(List<string> eventTypes)
        {
            var sql = $"SELECT * FROM {nameof(SiteInformation)} where eventType in @eventTypes";
            return Connection.Query<SiteInformation>(sql, new { eventTypes }, Transaction);
        }

        public SiteInformation GetSiteInformationById(int id)
        {
            var sql = $"SELECT * FROM {nameof(SiteInformation)} Where {nameof(SiteInformation.Id)} = @{nameof(SiteInformation.Id)}";
            return Connection.QueryFirstOrDefault<SiteInformation>(sql, new { id }, Transaction);
        }

        public SiteInformation GetSiteInformationBySiteId(string siteId)
        {
            var sql = $"SELECT * FROM {nameof(SiteInformation)} Where {nameof(SiteInformation.SiteID)} = @{nameof(SiteInformation.SiteID)}";
            return Connection.QueryFirstOrDefault<SiteInformation>(sql, new { siteId }, Transaction);
        }

        public bool InsertSiteInformation(SiteInformation data)
        {
            var sql = $@"INSERT INTO {nameof(SiteInformation)} (
                                     {nameof(SiteInformation.County)}, 
                                     {nameof(SiteInformation.Area)}, 
                                     {nameof(SiteInformation.SiteID)}, 
                                     {nameof(SiteInformation.SiteName)}, 
                                     {nameof(SiteInformation.EventType)}, 
                                     {nameof(SiteInformation.Ip)}, 
                                     {nameof(SiteInformation.DbName)}, 
                                     {nameof(SiteInformation.TableName)}, 
                                     {nameof(SiteInformation.User)}, 
                                     {nameof(SiteInformation.Password)}, 
                                     {nameof(SiteInformation.FinalRecord)}, 
                                     {nameof(SiteInformation.ARHFinalRecord)}, 
                                     {nameof(SiteInformation.CompanyCode)}, 
                                     {nameof(SiteInformation.Laws)})
	                          VALUES(@{nameof(SiteInformation.County)}, 
                                     @{nameof(SiteInformation.Area)}, 
                                     @{nameof(SiteInformation.SiteID)}, 
                                     @{nameof(SiteInformation.SiteName)}, 
                                     @{nameof(SiteInformation.EventType)}, 
                                     @{nameof(SiteInformation.Ip)}, 
                                     @{nameof(SiteInformation.DbName)}, 
                                     @{nameof(SiteInformation.TableName)}, 
                                     @{nameof(SiteInformation.User)}, 
                                     @{nameof(SiteInformation.Password)}, 
                                     @{nameof(SiteInformation.FinalRecord)}, 
                                     @{nameof(SiteInformation.ARHFinalRecord)}, 
                                     @{nameof(SiteInformation.CompanyCode)}, 
                                     @{nameof(SiteInformation.Laws)})";

            var result = Connection.Execute(sql, data, Transaction);
            return result == 1;
        }

        public bool UpdateSiteInformation(SiteInformation data)
        {
            var sql = $@"UPDATE {nameof(SiteInformation)}
	                        SET {nameof(SiteInformation.County)} = @{nameof(SiteInformation.County)}, 
                                {nameof(SiteInformation.Area)} = @{nameof(SiteInformation.Area)}, 
                                {nameof(SiteInformation.SiteID)} = @{nameof(SiteInformation.SiteID)}, 
                                {nameof(SiteInformation.SiteName)} = @{nameof(SiteInformation.SiteName)}, 
                                {nameof(SiteInformation.EventType)} = @{nameof(SiteInformation.EventType)}, 
                                {nameof(SiteInformation.Ip)} = @{nameof(SiteInformation.Ip)}, 
                                {nameof(SiteInformation.DbName)} = @{nameof(SiteInformation.DbName)}, 
                                {nameof(SiteInformation.TableName)} = @{nameof(SiteInformation.TableName)}, 
                                {nameof(SiteInformation.User)} = @{nameof(SiteInformation.User)}, 
                                {nameof(SiteInformation.Password)} = @{nameof(SiteInformation.Password)}, 
                                {nameof(SiteInformation.FinalRecord)} = @{nameof(SiteInformation.FinalRecord)}, 
                                {nameof(SiteInformation.Target)} = @{nameof(SiteInformation.Target)}, 
                                {nameof(SiteInformation.ClusterHead)} = @{nameof(SiteInformation.ClusterHead)}, 
                                {nameof(SiteInformation.ARHFinalRecord)} = @{nameof(SiteInformation.ARHFinalRecord)}, 
                                {nameof(SiteInformation.CompanyCode)} = @{nameof(SiteInformation.CompanyCode)}, 
                                {nameof(SiteInformation.Laws)} = @{nameof(SiteInformation.Laws)}  
	                      WHERE {nameof(SiteInformation.Id)} = @{nameof(SiteInformation.Id)}";
            var result = Connection.Execute(sql, data, Transaction);
            return result == 1;
        }

        public bool DeleteSiteInformation(int id)
        {
            var sql = $@"DELETE FROM {nameof(SiteInformation)} WHERE  {nameof(SiteInformation.Id)} = @{nameof(SiteInformation.Id)}";
            var result = Connection.Execute(sql, new { id }, Transaction);
            return result == 1;
        }
    }
}