EleKitService/RDH.Data/SqlBuilder/SqlBuilder.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace RDH.Data.BLL
{
    /// <summary>
    /// 表示SQL语句拼接的帮助类
    /// </summary>
    public class SqlBuilder
    {
        private BaseBLL _dalMain;
        private List<TableJoinInfo> _listJoinInfo;
        private StringBuilder _whereBuilder;
        private StringBuilder _selectionBuilder;
        private StringBuilder _orderBuilder;

        public SqlBuilder(BaseBLL dal)
        {
            _dalMain = dal;
            _listJoinInfo = new List<TableJoinInfo>();
            _whereBuilder = new StringBuilder();
        }
        public Boolean DistinctFlag { get; set; }
        public Boolean DonotMapMainTableFlag { get; set; }

        public void AppendWherePhrases(String value) 
        {
            if (_whereBuilder.Length == 0) 
            {
                if (!value.Trim().StartsWith("WHERE", StringComparison.CurrentCultureIgnoreCase)) 
                {
                    _whereBuilder.Append(" WHERE ");
                }
            }
            else 
            {
                if (!value.Trim().StartsWith("AND", StringComparison.CurrentCultureIgnoreCase)
                    && !value.Trim().StartsWith("OR", StringComparison.CurrentCultureIgnoreCase))
                {
                    _whereBuilder.Append(" AND ");
                }
                else if (!value.StartsWith(" ")) 
                {
                    _whereBuilder.Append(" ");
                }
            }
            _whereBuilder.Append(value);
        }
        public void AppendWherePhrasesFormat(String value, params String[] paramInfo)
        {
            _whereBuilder.AppendFormat(value, paramInfo);
        }
        public void AppendOrderPhrases(String phrase) 
        {
            if (_orderBuilder == null) 
            {
                _orderBuilder = new StringBuilder();
                if (!phrase.Trim().ToLower().StartsWith("order")) 
                {
                    _orderBuilder.Append(" ORDER BY");
                }
            }
            if (_orderBuilder.Length > 9
                && !phrase.Trim().StartsWith(","))
            {
                _orderBuilder.Append(",");
            }
            else 
            {
                _orderBuilder.Append(" ");
            }
            _orderBuilder.Append(phrase);
        }
        public void AppendSelectionTable(TableJoinInfo joinInfo) 
        {
            if (joinInfo == null || joinInfo.LeftDal == null || joinInfo.RightDal == null) 
            {
                return;
            }
            _listJoinInfo.Add(joinInfo);
            joinInfo.CurrentSpiltor = "spiltor" + _listJoinInfo.Count.ToString();
        }
        public void AppendSelectionTable(TableJoinTypes join, BaseBLL rightDal, String leftColumn, String rightColumn) 
        {
            AppendSelectionTable(new TableJoinInfo()
            {
                LeftDal = _dalMain,
                RightDal = rightDal,
                LeftColumnName = leftColumn,
                RightColumnName = rightColumn,
                JoinType = join,
                
            });
        }
        public void AppendSelectionPhrases(String value) 
        {
            value = value.Trim();
            if (_selectionBuilder == null)
            {
                _selectionBuilder = new StringBuilder();
            }
            if (!value.StartsWith(","))
            {
                _selectionBuilder.Append(",");
            }
            _selectionBuilder.Append(value);
        }
        public String GetSpiltors() 
        {
            return _listJoinInfo.Count == 0 ? null
                : String.Join(",", _listJoinInfo.Where(x => !x.DonotMapFlag).Select(x => x.CurrentSpiltor));
        }
        public override string ToString()
        {
            Boolean beginSpiltor = false;
            StringBuilder builder = new StringBuilder();
            #region Build Select
            builder.Append("SELECT ");
            if (DistinctFlag) 
            {
                builder.Append("DISTINCT ");
            }
            //main table selection
            String mainTableName = String.IsNullOrEmpty(_dalMain.AliasTableName) ? _dalMain.TABLE_NAME : _dalMain.AliasTableName;
            if (!DonotMapMainTableFlag) 
            {
                foreach (KeyValuePair<String, String> pair in _dalMain.ColumnPropMaps)
                {
                    builder.AppendFormat("{0}.{1} {2},", mainTableName, pair.Key, pair.Value);
                }
                beginSpiltor = true;
            }
            //slave table selection
            if (_listJoinInfo.Count > 0)
            {
                foreach (TableJoinInfo info in _listJoinInfo)
                {
                    if (info.LeftTableAliasName == null) 
                    {
                        info.LeftTableAliasName = String.IsNullOrEmpty(info.LeftDal.AliasTableName) ? info.LeftDal.TABLE_NAME : info.LeftDal.AliasTableName;
                    }
                    if (info.RightTableAliasName == null) 
                    {
                        info.RightTableAliasName = String.IsNullOrEmpty(info.RightDal.AliasTableName) ? info.RightDal.TABLE_NAME : info.RightDal.AliasTableName;
                    }
                    if (info.DonotMapFlag)
                    {
                        continue;
                    }
                    if (beginSpiltor) 
                    {
                        builder.AppendFormat("'' {0},", info.CurrentSpiltor);
                    }
                    foreach (KeyValuePair<String, String> pair in info.RightDal.ColumnPropMaps)
                    {
                        builder.AppendFormat("{0}.{1} {2},",
                            info.RightTableAliasName, 
                            pair.Key, pair.Value);
                    }
                    if (info.CustomPropertiesMap != null) 
                    {
                        foreach (KeyValuePair<String, String> pair in info.CustomPropertiesMap) 
                        {
                            builder.AppendFormat("{0} {1},", pair.Value, pair.Key);
                        }
                    }
                    if (!beginSpiltor) 
                    {
                        beginSpiltor = true;
                    }
                }
            }
            builder.Remove(builder.Length - 1, 1);
            if (_selectionBuilder != null)
            {
                builder.Append(_selectionBuilder.ToString());
            }
            #endregion
            //from
            builder.AppendFormat(" FROM {0} {1} ", 
                _dalMain.TABLE_NAME, 
                mainTableName);
            #region Build Join
            if (_listJoinInfo.Count > 0)
            {
                foreach (TableJoinInfo info in _listJoinInfo)
                {
                    builder.AppendFormat(" {0} JOIN {1} {2} ", 
                        info.JoinType.ToString(), 
                        info.RightDal.TABLE_NAME, 
                        info.RightTableAliasName);
                    if (!String.IsNullOrEmpty(info.CustomJoinCondition))
                    {
                        builder.AppendFormat(info.CustomJoinCondition);
                    }
                    else
                    {
                        builder.AppendFormat("ON {0}.{1}={2}.{3}",
                            info.LeftTableAliasName, info.LeftColumnName,
                            info.RightTableAliasName, info.RightColumnName);
                    }
                }
            } 
            #endregion
            if (_whereBuilder.Length > 0) 
            {
                builder.Append(" ");
                builder.Append(_whereBuilder.ToString());
            }
            if (_orderBuilder != null) 
            {
                builder.Append(_orderBuilder.ToString());
            }
            return builder.ToString();
        }
    }
}