123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219 |
- 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();
- }
- }
- }
|