cyf
644 lines
26 KiB
C#
644 lines
26 KiB
C#
/*
|
||
* Copyright 2010 www.Zhaizj.Framework.com
|
||
*
|
||
* Licensed under the Apache License, Version 2.0 (the "License");
|
||
* you may not use this file except in compliance with the License.
|
||
* You may obtain a copy of the License at
|
||
*
|
||
* http://www.apache.org/licenses/LICENSE-2.0
|
||
*
|
||
* Unless required by applicable law or agreed to in writing, software
|
||
* distributed under the License is distributed on an "AS IS" BASIS,
|
||
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
|
||
* See the License for the specific language governing permissions and
|
||
* limitations under the License.
|
||
*/
|
||
|
||
using System;
|
||
using System.Collections;
|
||
using System.Collections.Generic;
|
||
using System.Text;
|
||
|
||
using Zhaizj.Framework.Data;
|
||
using Zhaizj.Framework.Log;
|
||
using Zhaizj.Framework.ORM.Utils;
|
||
using Zhaizj.Framework.ORM.Operation;
|
||
|
||
namespace Zhaizj.Framework.ORM {
|
||
|
||
[Serializable]
|
||
internal class SqlBuilder {
|
||
|
||
private static readonly ILog logger = LogManager.GetLogger( typeof( SqlBuilder ) );
|
||
|
||
//-------------------------初始化-------------------------
|
||
|
||
private IList _propertyList;
|
||
private EntityInfo _entityInfo;
|
||
|
||
public SqlBuilder( Type type ) {
|
||
_entityInfo = Entity.GetInfo( type );
|
||
_propertyList = _entityInfo.SavedPropertyList;
|
||
}
|
||
|
||
public SqlBuilder( EntityInfo entityInfo ) {
|
||
_entityInfo = entityInfo;
|
||
_propertyList = _entityInfo.SavedPropertyList;
|
||
}
|
||
|
||
//-------------------------Sql语句-------------------------
|
||
|
||
public String GetFindConditionSql( String propertyString, String condition, Dictionary<String, Object> paramMap, int maxResults ) {
|
||
|
||
String[] arrConditonResult = getConditionResult( propertyString, condition, paramMap );
|
||
String sql = "";
|
||
if (maxResults > 0) {
|
||
sql = String.Format( "select {0} from {1}{2}", arrConditonResult[0], arrConditonResult[1], arrConditonResult[2] );
|
||
sql = _entityInfo.Dialect.GetLimit( sql, maxResults );
|
||
}
|
||
else {
|
||
sql = String.Format( "select {0} from {1}{2}", arrConditonResult[0], arrConditonResult[1], arrConditonResult[2] );
|
||
}
|
||
|
||
return sql;
|
||
}
|
||
|
||
public String GetFindById( int id, String propertyString ) {
|
||
String columnString = getColumnsByProperty( propertyString );
|
||
String sql = String.Format( "select {0} from {1} where Id={2}", columnString, _entityInfo.TableName, id );
|
||
logger.Info( String.Format( "{0}[{1}_FindById] {2}", LoggerUtil.SqlPrefix, _entityInfo.Name, sql ) );
|
||
return sql;
|
||
}
|
||
|
||
// TODO
|
||
public String GetFindById( String pkeyValue ) {
|
||
String pkeyName = ""; //_entityInfo.getPrimaryKeyName();
|
||
String sql = String.Format( "select * from {0} where {1}={2}", _entityInfo.TableName, pkeyName, pkeyValue );
|
||
logger.Info( String.Format( "{0}[{1}_FindById] {2}", LoggerUtil.SqlPrefix, _entityInfo.Name, sql ) );
|
||
return sql;
|
||
}
|
||
|
||
public String GetDeleteSql( String condition ) {
|
||
|
||
String where = condition.Trim().ToLower();
|
||
where = where.StartsWith( "where " ) ? where : "where "+ condition;
|
||
return string.Format( "delete from {0} {1}", _entityInfo.TableName, where );
|
||
}
|
||
|
||
public String GetCountSql( String condition ) {
|
||
String[] arrConditonResult = getConditionResult( "", condition, new Dictionary<String, Object>() );
|
||
return String.Format( "select count(*) from {0}{1}", arrConditonResult[1], arrConditonResult[2] );
|
||
}
|
||
|
||
public String GetPropertyList( String tbl, String selectColumn, String idlist ) {
|
||
String sql;
|
||
if (idlist.IndexOf( "," ) < 0) {
|
||
sql = "select " + selectColumn + " from " + tbl + " where Id=" + idlist;
|
||
}
|
||
else {
|
||
sql = "select " + selectColumn + " from " + tbl + " where Id in (" + idlist + ")";
|
||
}
|
||
logger.Info( LoggerUtil.SqlPrefix + "[" + _entityInfo.Name + "_FindPropertyList]" + sql );
|
||
return sql;
|
||
}
|
||
|
||
public String GetPageSql( PageCondition pc ) {
|
||
|
||
String columnString = getColumnsByProperty( pc.Property );
|
||
|
||
//------------------------ condition ------------------------------
|
||
String joinTable = "";
|
||
pc.ConditionStr = processCondition( pc.ConditionStr, new Dictionary<String, Object>(), ref joinTable );
|
||
|
||
Boolean isJoin = false;
|
||
if (strUtil.HasText( joinTable )) {
|
||
isJoin = true;
|
||
joinTable = _entityInfo.TableName + " t0, " + joinTable;
|
||
|
||
// columnString中的每个columnName都要加上t0前缀
|
||
columnString = addJoinTablePrefix( columnString, ',' );
|
||
}
|
||
else {
|
||
joinTable = _entityInfo.TableName;
|
||
}
|
||
joinTable = joinTable.Trim().TrimEnd( ',' );
|
||
|
||
|
||
if (strUtil.HasText( pc.ConditionStr )) {
|
||
if (pc.ConditionStr.ToLower().Trim().StartsWith( "and " )) {
|
||
|
||
//"and id=7" 剔除and开头
|
||
pc.ConditionStr = " where " + pc.ConditionStr.Trim().Substring( 3 );
|
||
}
|
||
//"order by id" 加上一个空格
|
||
else if (pc.ConditionStr.Trim().ToLower().StartsWith( "order" )) {
|
||
pc.ConditionStr = " " + pc.ConditionStr;
|
||
}
|
||
else {
|
||
//加上 where
|
||
pc.ConditionStr = " where " + pc.ConditionStr;
|
||
}
|
||
}
|
||
|
||
//------------------------ order ------------------------------
|
||
Boolean isCustomerOrder = false;
|
||
if (pc.ConditionStr.ToLower().IndexOf( " order " ) >= 0) isCustomerOrder = true;
|
||
|
||
String orderString = "";
|
||
pc.OrderStr = pc.OrderStr.ToLower();
|
||
|
||
// 默认排序方式：逆序
|
||
if (isCustomerOrder == false) {
|
||
|
||
if (pc.OrderStr == "asc")
|
||
orderString = " order by Id asc";
|
||
else
|
||
orderString = " order by Id desc";
|
||
|
||
if (isJoin) orderString = orderString.Replace( "Id", "t0.Id" );
|
||
|
||
}
|
||
else {
|
||
// 自定义排序方式
|
||
String newOrderString = "";
|
||
|
||
String tempString = pc.ConditionStr;
|
||
//此处conditionString已经不带order
|
||
pc.ConditionStr = pc.ConditionStr.Substring( 0, pc.ConditionStr.ToLower().IndexOf( "order " ) );
|
||
|
||
orderString = tempString.Replace( pc.ConditionStr, "" );
|
||
|
||
String[] arrOrder = orderString.Split( new char[] { ' ', ',' } );
|
||
foreach (String s in arrOrder) {
|
||
if (s.ToLower() == "asc" || s.ToLower() == "desc") {
|
||
newOrderString += s + ", ";
|
||
}
|
||
else if (s.ToLower() != "order" && s.ToLower() != "by" && s != null && s != "") {
|
||
if (isJoin)
|
||
newOrderString += "t0." + s + " ";
|
||
else
|
||
newOrderString += s + " ";
|
||
}
|
||
else {
|
||
newOrderString += s + " ";
|
||
}
|
||
}
|
||
|
||
orderString = newOrderString.Trim().TrimEnd( ',' );
|
||
}
|
||
|
||
String _condition_nowhere = pc.ConditionStr.Trim();
|
||
if (strUtil.HasText( _condition_nowhere ) && _condition_nowhere.ToLower().StartsWith( "where" )) {
|
||
_condition_nowhere = pc.ConditionStr.Trim().Substring( 5 );
|
||
_condition_nowhere = "and " + _condition_nowhere;
|
||
}
|
||
|
||
String sql = "";
|
||
|
||
String pageCountSql = "select count(*) from " + joinTable + pc.ConditionStr;
|
||
|
||
//callback执行beginCount方法
|
||
pc.CurrentPage = pc.beginCount( pageCountSql, pc.Pager, _entityInfo );
|
||
|
||
// TODO：最后一页优化
|
||
|
||
if (pc.CurrentPage == 1) {
|
||
sql = "select " + columnString + " from " + joinTable + pc.ConditionStr + orderString;
|
||
|
||
sql = _entityInfo.Dialect.GetLimit( sql, pc.Size );
|
||
}
|
||
else {
|
||
|
||
StringBuilder sb = new StringBuilder();
|
||
getPageSql( pc, columnString, joinTable, isJoin, isCustomerOrder, orderString, _condition_nowhere, sb );
|
||
|
||
sql = sb.ToString();
|
||
|
||
}
|
||
|
||
logger.Info( LoggerUtil.SqlPrefix + "[Page Sql] " + sql );
|
||
return sql;
|
||
}
|
||
|
||
private void getPageSql( PageCondition pc, String columnString, String joinTable, Boolean isJoin, Boolean isCustomerOrder, String orderString, String _condition_nowhere, StringBuilder sb ) {
|
||
|
||
if (_entityInfo.DbType == DatabaseType.MySql) {
|
||
|
||
int startRow = pc.Size * (pc.CurrentPage - 1);
|
||
|
||
sb.Append( "select " );
|
||
sb.Append( columnString );
|
||
sb.Append( " from " );
|
||
sb.Append( joinTable );
|
||
sb.Append( " " );
|
||
sb.Append( pc.ConditionStr );
|
||
sb.Append( " " );
|
||
sb.Append( orderString );
|
||
sb.Append( " limit " );
|
||
sb.Append( startRow );
|
||
sb.Append( ", " );
|
||
sb.Append( pc.Size );
|
||
|
||
return;
|
||
}
|
||
|
||
if (isCustomerOrder == false) {
|
||
|
||
sb.Append( "select top " );
|
||
sb.Append( pc.Size );
|
||
sb.Append( " " );
|
||
sb.Append( columnString );
|
||
sb.Append( " from " );
|
||
sb.Append( joinTable );
|
||
|
||
if (pc.OrderStr == "asc") {
|
||
if (isJoin)
|
||
sb.Append( " where t0.Id>(select max(Id) from (select top " );
|
||
else
|
||
sb.Append( " where Id>(select max(Id) from (select top " );
|
||
}
|
||
else {
|
||
if (isJoin)
|
||
sb.Append( " where t0.Id<(select min(Id) from (select top " );
|
||
else
|
||
sb.Append( " where Id<(select min(Id) from (select top " );
|
||
}
|
||
|
||
sb.Append( pc.Size * (pc.CurrentPage - 1) );
|
||
|
||
if (isJoin)
|
||
sb.Append( " t0.Id from " );
|
||
else
|
||
sb.Append( " Id from " );
|
||
|
||
sb.Append( joinTable );
|
||
sb.Append( pc.ConditionStr );
|
||
sb.Append( orderString );
|
||
sb.Append( ") as t) " );
|
||
//sb.Append( "and " );
|
||
sb.Append( _condition_nowhere );
|
||
sb.Append( orderString );
|
||
|
||
}
|
||
|
||
else if (_entityInfo.DbType == DatabaseType.Access || _entityInfo.DbType == DatabaseType.SqlServer2000) {
|
||
|
||
sb.Append( "select top " );
|
||
sb.Append( pc.Size );
|
||
sb.Append( " " );
|
||
sb.Append( columnString );
|
||
sb.Append( " from " );
|
||
sb.Append( joinTable );
|
||
|
||
if (strUtil.IsNullOrEmpty( pc.ConditionStr )) {
|
||
if (isJoin)
|
||
sb.Append( " where t0.Id not in ( select top " );
|
||
else
|
||
sb.Append( " where Id not in ( select top " );
|
||
|
||
}
|
||
else {
|
||
|
||
sb.Append( pc.ConditionStr );
|
||
|
||
if (isJoin)
|
||
sb.Append( " and t0.Id not in ( select top " );
|
||
else
|
||
sb.Append( " and Id not in ( select top " );
|
||
|
||
}
|
||
|
||
sb.Append( pc.Size * (pc.CurrentPage - 1) );
|
||
|
||
if (isJoin)
|
||
sb.Append( " t0.Id from " );
|
||
else
|
||
sb.Append( " Id from " );
|
||
|
||
sb.Append( joinTable );
|
||
sb.Append( pc.ConditionStr );
|
||
sb.Append( orderString );
|
||
sb.Append( " ) " );
|
||
//sb.Append( "and " );
|
||
sb.Append( _condition_nowhere );
|
||
sb.Append( " " );
|
||
sb.Append( orderString );
|
||
|
||
}
|
||
|
||
else {
|
||
|
||
int startRow = pc.Size * (pc.CurrentPage - 1);
|
||
int endRow = startRow + pc.Size;
|
||
|
||
sb.AppendFormat( "select {0} from ", columnString );
|
||
sb.Append( "(" );
|
||
sb.AppendFormat( "select ROW_NUMBER() over ( {0} ) as rowNumber, {1} from {2} {3}", orderString, columnString, joinTable, pc.ConditionStr );
|
||
sb.Append( ") as tmpTable " );
|
||
sb.AppendFormat( "where (rowNumber between {0} and {1}) {2}", (startRow + 1), endRow, _condition_nowhere );
|
||
|
||
}
|
||
}
|
||
|
||
//-------------------------帮助方法-------------------------
|
||
|
||
private String[] getConditionResult( String propertyString, String condition, Dictionary<String, Object> paramMap ) {
|
||
|
||
String columnString = getColumnsByProperty( propertyString );
|
||
|
||
String joinTable = "";
|
||
|
||
String conditionString = condition;
|
||
if (strUtil.HasText( conditionString )) {
|
||
conditionString = processCondition( conditionString, paramMap, ref joinTable ).Trim();
|
||
}
|
||
|
||
if (strUtil.HasText( joinTable )) {
|
||
joinTable = _entityInfo.TableName + " t0, " + joinTable;
|
||
// columnString中的每个columnName都要加上t0前缀
|
||
columnString = addJoinTablePrefix( columnString, ',' );
|
||
}
|
||
else {
|
||
joinTable = _entityInfo.TableName;
|
||
}
|
||
joinTable = joinTable.Trim().TrimEnd( ',' );
|
||
|
||
if (strUtil.HasText( conditionString )) {
|
||
if (conditionString.ToLower().StartsWith( "and" )) {
|
||
//"and id=7" 剔除and开头
|
||
conditionString = " where " + conditionString.Substring( 3 ).Trim();
|
||
}
|
||
//"order by id" 加上一个空格
|
||
else if (conditionString.ToLower().StartsWith( "order" )) {
|
||
conditionString = " " + conditionString.Trim();
|
||
}
|
||
else {
|
||
//加上 where
|
||
conditionString = " where " + conditionString.Trim();
|
||
}
|
||
}
|
||
return new String[] { columnString, joinTable, conditionString };
|
||
}
|
||
|
||
// 示例：Id,Title,Member.Id,Cat.Name
|
||
public String getColumnsByProperty( String propertyString ) {
|
||
|
||
if (!strUtil.HasText( propertyString ) || propertyString.Trim().Equals( "*" )) return "*";
|
||
|
||
//将 propertyString 与 当前实体的所有属性比较，得到 columnList
|
||
String[] arrProperty = propertyString.Split( ',' );
|
||
String result = "";
|
||
foreach (EntityPropertyInfo ep in _propertyList) {
|
||
foreach (String pString in arrProperty) {
|
||
|
||
if (isAddColumn( pString.Trim(), ep )) {
|
||
//实体属性的数据，这里不联表查询。而是获取相应id，通过id in(idlist) 得到数据再填充
|
||
result += ep.ColumnName + ",";
|
||
break;
|
||
}
|
||
|
||
}
|
||
}
|
||
return result.Trim().TrimEnd( ',' );
|
||
}
|
||
|
||
// 是否在sql中选择当前属性的column
|
||
private Boolean isAddColumn( String selectedProperty, EntityPropertyInfo ep ) {
|
||
|
||
//选择字符串和属性完全同名
|
||
if (selectedProperty.Equals( ep.Name )) return true;
|
||
|
||
//属性Member,selectProperty为Member.Name，只要前缀相同，即可选取属性的columnName
|
||
if (ep.IsEntity && selectedProperty.StartsWith( ep.Name + "." )) return true;
|
||
|
||
return false;
|
||
}
|
||
|
||
// 示例：Member.Id=6 and Cat.Id>7
|
||
// art.Find("Author=:author and Title=:title and Id>:id order by Id desc")
|
||
public String processCondition( String conditionString, Dictionary<String, Object> paramMap, ref String joinTable ) {
|
||
|
||
//logger.Info( "conditionString=>" + conditionString );
|
||
|
||
|
||
// 根据不同方言，替换参数，比如“Author=:author and Title=:title”需要替换成“Author=? and Title=?”
|
||
foreach (String key in paramMap.Keys) {
|
||
conditionString = conditionString.Replace( ":" + key, _entityInfo.Dialect.GetParameter( key ) );
|
||
}
|
||
|
||
//如果是实体属性在设置条件，还要负责把 Member.Id=:mid 转换成 TMemberId=?：不联表查询
|
||
// 1、当前实体具有的column ： _entityInfo.ColumnList
|
||
// 2、找到参数查询所对应的column
|
||
if (isConditionHasEntity( conditionString )) {
|
||
|
||
//是否联表查询
|
||
Boolean isJoinedQuery = false;
|
||
String _join_condition = "";
|
||
int _joined_Property_Count = 0;
|
||
|
||
// and (Member.Id=? and Cat.Name=?) order by Id desc
|
||
List<String[]> items = SqlUtil.getEntityProperties( conditionString );
|
||
foreach (String[] pair in items) {
|
||
|
||
String strItem = pair[0] + "." + pair[1];
|
||
|
||
if (!pair[1].ToLower().Equals( "id" )) isJoinedQuery = true;
|
||
|
||
if (!isJoinedQuery) {
|
||
conditionString = conditionString.Replace( strItem, _entityInfo.GetColumnName( pair[0] ) );
|
||
}
|
||
else {
|
||
|
||
_joined_Property_Count += 1;
|
||
|
||
EntityPropertyInfo _e_p = _entityInfo.GetProperty( pair[0] );
|
||
|
||
EntityPropertyInfo _sub_p = _e_p.EntityInfo.GetProperty( pair[1] );
|
||
String subColumn = (_sub_p == null ? pair[1] : _sub_p.ColumnName);
|
||
String _sub_column_name = "t" + _joined_Property_Count + "." + subColumn;
|
||
|
||
// joinTable
|
||
joinTable += _e_p.EntityInfo.TableName + " t" + _joined_Property_Count + ",";
|
||
|
||
// replace entity property
|
||
conditionString = conditionString.Replace( strItem, _sub_column_name );
|
||
_join_condition += "and t0." + _e_p.ColumnName + "=t" + _joined_Property_Count + ".Id ";
|
||
|
||
}
|
||
}
|
||
|
||
// add prefix "0" to all current entity property's column, especially in "order by id desc" etc.(=>order by t0.id desc)
|
||
if (isJoinedQuery) conditionString = addJoinTablePrefix( conditionString, ' ' );
|
||
|
||
//conditionString = _join_condition + conditionString;
|
||
conditionString = conditionString.Trim();
|
||
_join_condition = _join_condition.Trim();
|
||
if (conditionString.StartsWith( "and " ) == false && _join_condition.EndsWith( " and" ) == false) {
|
||
conditionString =_join_condition + " and " + conditionString;
|
||
}
|
||
else {
|
||
conditionString = _join_condition + " " + conditionString;
|
||
}
|
||
}
|
||
|
||
return conditionString;
|
||
}
|
||
|
||
// " and ( ControlPath = '~/Module/BlogMain.ascx' ) order by Id desc"
|
||
// " and Id>6 and Member.Name = 'zhang' "
|
||
// " and UnitPrice = 32.80 "
|
||
private Boolean isConditionHasEntity( String conditionString ) {
|
||
|
||
|
||
foreach (EntityPropertyInfo ep in this._entityInfo.EntityPropertyList) {
|
||
|
||
if (conditionString.IndexOf( " " + ep.Name + "." ) > -1) return true;
|
||
if (conditionString.StartsWith( ep.Name + "." )) return true;
|
||
if (conditionString.IndexOf( "(" + ep.Name + "." ) > -1) return true;
|
||
}
|
||
return false;
|
||
}
|
||
|
||
// 用于1)select columnNames
|
||
// 2)where 中，比如ReceiverId=1 and IsDelete=0
|
||
private String addJoinTablePrefix( String targetString, char seperator ) {
|
||
if (strUtil.IsNullOrEmpty( targetString )) return null;
|
||
|
||
String[] _arr_Str;
|
||
if (targetString.Equals( "*" )) {
|
||
_arr_Str = _entityInfo.ColumnList.Split( seperator );
|
||
}
|
||
else {
|
||
_arr_Str = targetString.Split( seperator );
|
||
}
|
||
|
||
String _result_string = "";
|
||
for (int i = 0; i < _arr_Str.Length; i++) {
|
||
String _str_item = _arr_Str[i].Trim(); //ReceiverId=1
|
||
if (!strUtil.HasText( _str_item )) continue;
|
||
|
||
if (_str_item.IndexOf( '=' ) > 0) {
|
||
|
||
String[] _arrItemPair = _str_item.Split( '=' );
|
||
|
||
_str_item = checkColumn( _arrItemPair[0] ) + "=" + _arrItemPair[1];
|
||
|
||
|
||
}
|
||
else {
|
||
_str_item = checkColumn( _str_item );
|
||
}
|
||
|
||
_result_string += _str_item + seperator;
|
||
}
|
||
return _result_string.TrimEnd( seperator ).Trim();
|
||
}
|
||
|
||
private String checkColumn( String orginalColumnName ) {
|
||
|
||
if (orginalColumnName.Equals( "=" ) ||
|
||
orginalColumnName.ToLower().Equals( "and" ) ||
|
||
orginalColumnName.ToLower().Equals( "or" ) ||
|
||
orginalColumnName.Equals( "?" ) ||
|
||
orginalColumnName.Equals( "(" ) ||
|
||
orginalColumnName.Equals( ")" ) ||
|
||
orginalColumnName.Equals( ">" ) ||
|
||
orginalColumnName.Equals( "<" ) ||
|
||
orginalColumnName.Equals( "order" ) ||
|
||
orginalColumnName.ToLower().Equals( "by" ) ||
|
||
orginalColumnName.ToLower().Equals( "asc" ) ||
|
||
orginalColumnName.ToLower().Equals( "desc" ) ||
|
||
orginalColumnName.Equals( "<>" ) ||
|
||
orginalColumnName.ToLower().Equals( "like" ))
|
||
|
||
return orginalColumnName;
|
||
|
||
if (orginalColumnName.IndexOf( "." ) > 0) return orginalColumnName;
|
||
|
||
foreach (EntityPropertyInfo ep in _propertyList) {
|
||
if (orginalColumnName.ToLower().Equals( ep.ColumnName.ToLower() )) {
|
||
return "t0." + orginalColumnName;
|
||
}
|
||
}
|
||
|
||
return orginalColumnName;
|
||
}
|
||
|
||
//String sql = "select Id,Name,ArticleCount from TCat";
|
||
//select top 1 Id,CategoryId,Name,FriendUrl,Gender,Pic,TemplateId from (note ： top 1)
|
||
public static String GetColumnString( String sql ) {
|
||
sql = sql.Trim();
|
||
int fromIndex = sql.ToLower().IndexOf( "from" );
|
||
sql = sql.Substring( 0, fromIndex ).Trim();
|
||
sql = sql.Substring( 7 ); // delete "select"
|
||
|
||
// TODO:database is mysql : don't use "top"
|
||
if (sql.ToLower().IndexOf( "top" ) > -1) {
|
||
String topOne = sql.Split( ',' )[0].Trim(); //=>top 1 Id
|
||
String[] arrTopOne = topOne.Split( ' ' );//=>["top","1","Id"]
|
||
String firstColumn = arrTopOne[arrTopOne.Length - 1];//=>Id
|
||
sql = sql.Replace( topOne, firstColumn );
|
||
}
|
||
|
||
sql = sql.Replace( " ", "" );
|
||
return sql;
|
||
}
|
||
|
||
//Id,Title,Member.Id,Cat.Name,Cat.ArticleCount,Board.Name
|
||
public static String GetIncludeProperty( String propertyString ) {
|
||
String[] _arr_property = propertyString.Trim().Split( ',' );
|
||
StringBuilder sb = new StringBuilder( "," );
|
||
foreach (String propertyItem in _arr_property) {
|
||
String p = propertyItem.Trim();
|
||
if (p.IndexOf( "." ) > 0) {
|
||
String propertyName = p.Split( '.' )[0];
|
||
if (sb.ToString().IndexOf( "," + propertyName + "," ) < 0) { //avoid repeat property
|
||
sb.Append( propertyName );
|
||
sb.Append( "," );
|
||
}
|
||
}
|
||
}
|
||
return sb.ToString().TrimStart( ',' ).TrimEnd( ',' );
|
||
}
|
||
|
||
// Id,Title,Member.Id,Cat.Name,Cat.ArticleCount,Board.Name
|
||
// => 3 hashtable
|
||
// Hashtable["Member"] = "Id", Hashtable["Cat"]="Name,ArticleCount", Hashtable["Board"]="Name"
|
||
public static Hashtable GetIncludeSelectColumn( String propertyString, IList entityPropertyList ) {
|
||
Hashtable result = new Hashtable();
|
||
if (propertyString.Equals( "*" )) return result;
|
||
|
||
String[] _arr_property = propertyString.Trim().Split( ',' );
|
||
|
||
foreach (String propertyItem in _arr_property) {
|
||
|
||
String p = propertyItem.Trim();
|
||
if (p.IndexOf( "." ) < 0) continue;
|
||
|
||
String[] _arr_p_item = p.Split( '.' ); //Cat.ArticleCount
|
||
String propertyName = _arr_p_item[0]; //Cat
|
||
String subPropertyName = _arr_p_item[1]; //ArticleCount
|
||
|
||
if (result[propertyName] == null) { //first time fill column
|
||
result[propertyName] = getColumnNameFromPropertyList( entityPropertyList, propertyName, subPropertyName ) + ",";
|
||
}
|
||
else {
|
||
result[propertyName] = result[propertyName].ToString() + getColumnNameFromPropertyList( entityPropertyList, propertyName, subPropertyName ) + ",";
|
||
}
|
||
}
|
||
|
||
return result;
|
||
}
|
||
|
||
private static String getColumnNameFromPropertyList( IList entityPropertyList, String propertyName, String subPropertyName ) {
|
||
foreach (EntityPropertyInfo p in entityPropertyList) {
|
||
if (p.Name.Equals( propertyName )) {
|
||
return p.EntityInfo.GetProperty( subPropertyName ).ColumnName;
|
||
}
|
||
}
|
||
return null;
|
||
}
|
||
|
||
}
|
||
}
|