EleKitService/RabbitMQ/DAL/BaseDAL.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;

namespace RDH.PharmacyPlatform.Sync.Core
{
    internal abstract class BaseDAL
    {
        public BaseDAL() { ColumnPropMaps = InitMaps(); }

        internal Dictionary<string, string> ColumnPropMaps;
        internal virtual String TableName { get; set; }//TB_NAME
        internal virtual string EntityName { get; set; }
        internal virtual String KeyColumnName { get; set; }//KEY_COLUMN

        internal abstract Dictionary<String, String> InitMaps();

        #region Protected Methods
        internal string BuildSelectSql()
        {
            StringBuilder selectValues = new StringBuilder();
            foreach (var c in ColumnPropMaps)
            {
                selectValues.AppendFormat("{0} {1},", c.Key, c.Value);
            }
            selectValues.Remove(selectValues.Length - 1, 1);
            return string.Format("SELECT {1} FROM {0} ", TableName, selectValues);
        }
        internal string BuildGetSql()
        {
            StringBuilder selectValues = new StringBuilder();
            string keyWhere = string.Format("{0}=:{1}", KeyColumnName, ColumnPropMaps[KeyColumnName]);
            foreach (var c in ColumnPropMaps)
            {
                selectValues.AppendFormat("{0} {1},", c.Key, c.Value);
            }
            selectValues.Remove(selectValues.Length - 1, 1);
            return string.Format("SELECT {1} FROM {0} WHERE {2}", TableName, selectValues, keyWhere);
        }
        internal String BuildCountSql()
        {
            return String.Format("SELECT COUNT({0}) FROM {1}", KeyColumnName, TableName);
        }
        internal string BuildInsertSql()
        {
            StringBuilder cols = new StringBuilder();
            StringBuilder parms = new StringBuilder();
            foreach (var c in ColumnPropMaps)
            {
                cols.AppendFormat("{0},", c.Key);
                parms.AppendFormat(":{0},", c.Value);
            }
            cols.Remove(cols.Length - 1, 1);
            parms.Remove(parms.Length - 1, 1);
            return string.Format("INSERT INTO {0} ({1}) VALUES ({2})", TableName, cols, parms);
        }

        internal string BuildUpdateSql()
        {
            StringBuilder setters = new StringBuilder();
            string keyWhere = string.Format("{0}=:{1}", KeyColumnName, ColumnPropMaps[KeyColumnName]);
            foreach (var c in ColumnPropMaps)
            {
                if (c.Key != KeyColumnName)
                {
                    setters.AppendFormat("{0}=:{1},", c.Key, c.Value);
                }
            }
            setters.Remove(setters.Length - 1, 1);
            return string.Format("UPDATE {0} SET {1} WHERE {2}", TableName, setters, keyWhere);
        }

        internal string BuildDeleteSql()
        {
            string keyWhere = string.Format("{0}=:{1}", KeyColumnName, ColumnPropMaps[KeyColumnName]);
            return string.Format("DELETE FROM {0} WHERE {1}", TableName, keyWhere);
        }
        #endregion
    }
    internal abstract class BaseDAL<T> : BaseDAL
    {
        internal virtual object GetParam(T obj) 
        {
            return obj;
        }
        #region public Methods

        public virtual IEnumerable<T> ListModels()
        {
            return SqlMapper.Query<T>(ConnectionSessionFactory.GetConnection(), BuildSelectSql());
        }
        public virtual Boolean DeleteModel(T obj)
        {
            return SqlMapper.Execute(ConnectionSessionFactory.GetConnection(), this.BuildDeleteSql(), GetParam(obj)) > 0;
        }

        public Boolean InsertModel(T obj)
        {
            return SqlMapper.Execute(ConnectionSessionFactory.GetConnection(), BuildInsertSql(), GetParam(obj)) > 0;
        }

        public Boolean UpdateModel(T obj)
        {
            return SqlMapper.Execute(ConnectionSessionFactory.GetConnection(), BuildUpdateSql(), GetParam(obj)) > 0;
        }

        public T GetModel(T obj)
        {
            return SqlMapper.Query<T>(ConnectionSessionFactory.GetConnection(), BuildGetSql(), GetParam(obj)).FirstOrDefault();
        }
        #endregion
    }
}