EleKitService/RDH.Data/BaseOrclBLL.cs

using RDH.Data.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using RDH.Data.BLL;

namespace RDH.Data
{
    public abstract class BaseOrclBLL<T> : BaseBLL, IBaseBLL<T>
    {
        public BaseOrclBLL() : base()
        {
        }

        internal abstract object GetParam(T obj);

        #region SqlBuilding
        private string BuildPageNumSql()
        {
            StringBuilder selectValues = new StringBuilder();

            foreach (var c in ColumnPropMaps)
            {
                selectValues.AppendFormat(" \"{2}\".\"{0}\" \"{1}\",", c.Key, c.Value, this.TABLE_NAME);
            }
            selectValues.Remove(selectValues.Length - 1, 1);

            return string.Format("SELECT {1} FROM \"{0}\" ", TABLE_NAME, selectValues);
        }

        protected string BuildPageSql(string where, int index, int size)
        {
            StringBuilder selectValues = new StringBuilder();
            foreach (var c in ColumnPropMaps)
            {
                selectValues.AppendFormat(" \"{0}\",", c.Value);
            }

            selectValues.Remove(selectValues.Length - 1, 1);
            return string.Format("select {1} from ( SELECT {1}, rownum as rn FROM ({0} {2}) t ) where rn<{3} and rn>={4} ", BuildPageNumSql(), selectValues, where, size + index, index);

            //return string.Format("select {1} from ( SELECT {1}, rownum as rn FROM ({0} {2}) t ) where rn<={3} and rn>{4} ", BuildPageNumSql(), selectValues, where, size + index, index);
        }
        protected String BuildSimplePageSql(String selection, Int32 index, Int32 size)
        {
            return String.Format("SELECT * FROM (SELECT t1.*,ROWNUM AS rn FROM ({0}) t1) WHERE rn<{1} and rn>={2}",
                selection, (index + size).ToString(), index.ToString());
        }
        protected string BuildPageCountSql(string where)
        {
            var sql = string.Format("SELECT COUNT(*) AS DATA_COUNT FROM {0} {1}", this.TABLE_NAME, where);
            return sql;
        }
        #endregion

        #region Basic Db access

        public virtual List<T> ListModels(int pageSize, int index)
        {
            string whereSql = " order by KEY asc ";
            var sql = this.BuildPageSql(whereSql, index, pageSize);
            return SqlMapper.Query<T>(ConnectionFactory.Current.GetSessionConnection(), sql).ToList();

        }

        public virtual int GetModelCount()
        {
            string whereSql = " order by KEY asc ";
            var sql = this.BuildPageCountSql(whereSql);
            var command = ConnectionFactory.Current.GetSessionConnection().CreateCommand();
            command.CommandText = sql;
            command.CommandType = System.Data.CommandType.Text;
            return Convert.ToInt32(command.ExecuteScalar());
        }

        public void Delete(T obj)
        {
            Int32 i = SqlMapper.Execute(ConnectionFactory.Current.GetSessionConnection(), this.BuildDeleteSql(), GetParam(obj));
            System.Diagnostics.Debug.WriteLine("Delete:" + i.ToString());
        }

        public int Save(T obj)
        {
            Int32 i = SqlMapper.Execute(ConnectionFactory.Current.GetSessionConnection(), BuildInsertSql(), GetParam(obj));
            System.Diagnostics.Debug.WriteLine("Save:" + i.ToString());
            return i;
        }

        public void Update(T obj)
        {
            int i = SqlMapper.Execute(ConnectionFactory.Current.GetSessionConnection(), BuildUpdateSql(), GetParam(obj));
            System.Diagnostics.Debug.WriteLine("Update:" + i.ToString());
        }

        public T Get(T obj)
        {
            return SqlMapper.Query<T>(ConnectionFactory.Current.GetSessionConnection(), BuildGetSql(), GetParam(obj)).FirstOrDefault();
        }
        #endregion
    }
}