internal static StringBuilder GetQueryableSql <T>(SqlSugar.Queryable <T> queryable) { StringBuilder sbSql = new StringBuilder(); string tableName = queryable.TableName.IsNullOrEmpty() ? queryable.TName : queryable.TableName; if (queryable.DB.Language.IsValuable() && queryable.DB.Language.Suffix.IsValuable()) { var viewNameList = LanguageHelper.GetLanguageViewNameList(queryable.DB); var isLanView = viewNameList.IsValuable() && viewNameList.Any(it => it == tableName); if (!queryable.DB.Language.Suffix.StartsWith(LanguageHelper.PreSuffix)) { queryable.DB.Language.Suffix = LanguageHelper.PreSuffix + queryable.DB.Language.Suffix; } //将视图变更为多语言的视图 if (isLanView) { tableName = typeof(T).Name + queryable.DB.Language.Suffix; } } if (queryable.DB.PageModel == PageModel.RowNumber) { #region rowNumber string withNoLock = queryable.DB.IsNoLock ? "WITH(NOLOCK)" : null; var order = queryable.OrderBy.IsValuable() ? (",row_index=ROW_NUMBER() OVER(ORDER BY " + queryable.OrderBy + " )") : null; sbSql.AppendFormat("SELECT " + queryable.Select.GetSelectFiles() + " {1} FROM {0} {2} WHERE 1=1 {3} {4} ", tableName, order, withNoLock, string.Join("", queryable.Where), queryable.GroupBy.GetGroupBy()); if (queryable.Skip == null && queryable.Take != null) { sbSql.Insert(0, "SELECT " + queryable.Select.GetSelectFiles() + " FROM ( "); sbSql.Append(") t WHERE t.row_index<=" + queryable.Take); } else if (queryable.Skip != null && queryable.Take == null) { sbSql.Insert(0, "SELECT " + queryable.Select.GetSelectFiles() + " FROM ( "); sbSql.Append(") t WHERE t.row_index>" + (queryable.Skip)); } else if (queryable.Skip != null && queryable.Take != null) { sbSql.Insert(0, "SELECT " + queryable.Select.GetSelectFiles() + " FROM ( "); sbSql.Append(") t WHERE t.row_index BETWEEN " + (queryable.Skip + 1) + " AND " + (queryable.Skip + queryable.Take)); } #endregion } else { #region offset string withNoLock = queryable.DB.IsNoLock ? "WITH(NOLOCK)" : null; var order = queryable.OrderBy.IsValuable() ? ("ORDER BY " + queryable.OrderBy + " ") : null; sbSql.AppendFormat("SELECT " + queryable.Select.GetSelectFiles() + " {1} FROM {0} {2} WHERE 1=1 {3} {4} ", tableName, "", withNoLock, string.Join("", queryable.Where), queryable.GroupBy.GetGroupBy()); sbSql.Append(order); sbSql.AppendFormat("OFFSET {0} ROW FETCH NEXT {1} ROWS ONLY", queryable.Skip, queryable.Take); #endregion } return(sbSql); }
internal static StringBuilder GetQueryableSql <T>(SqlSugar.Queryable <T> queryable) { string joinInfo = string.Join(" ", queryable.JoinTableValue); StringBuilder sbSql = new StringBuilder(); string tableName = queryable.TableName.IsNullOrEmpty() ? queryable.TName : queryable.TableName; if (queryable.DB.Language.IsValuable() && queryable.DB.Language.Suffix.IsValuable()) { var viewNameList = LanguageHelper.GetLanguageViewNameList(queryable.DB); var isLanView = viewNameList.IsValuable() && viewNameList.Any(it => it == tableName); if (!queryable.DB.Language.Suffix.StartsWith(LanguageHelper.PreSuffix)) { queryable.DB.Language.Suffix = LanguageHelper.PreSuffix + queryable.DB.Language.Suffix; } //将视图变更为多语言的视图 if (isLanView) { tableName = typeof(T).Name + queryable.DB.Language.Suffix; } } if (queryable.DB.PageModel == PageModel.RowNumber) { #region rowNumber string withNoLock = queryable.DB.IsNoLock ? "WITH(NOLOCK)" : null; sbSql.AppendFormat("SELECT " + queryable.SelectValue.GetSelectFiles() + " FROM {0} {4} {1} WHERE 1=1 {2} {3} ", tableName.GetTranslationSqlName(), withNoLock, string.Join("", queryable.WhereValue), queryable.GroupByValue.GetGroupBy(), joinInfo); string strTmp = string.Empty; string strOrder = string.Empty; string strSql = string.Empty; if (queryable.OrderByValue.IsValuable()) { strOrder = " order by " + queryable.OrderByValue; strTmp = queryable.OrderByValue.IndexOf("DESC") >= 0 ? " <(select min " : " >(select max "; } if (queryable.Skip == null && queryable.Take != null) { //if (joinInfo.IsValuable()) //{ // sbSql.Insert(0, "SELECT * FROM ( "); //} //else //{ // sbSql.Insert(0, "SELECT " + queryable.SelectValue.GetSelectFiles() + " FROM ( "); //} //sbSql.Append(") t WHERE t.row_index<=" + queryable.Take); strSql = string.Format("select top {0} {1} from {2} {3} {4} where 1=1 {5} {6} {7}", queryable.Take, queryable.SelectValue.GetSelectFiles(), tableName.GetTranslationSqlName(), joinInfo, withNoLock, string.Join("", queryable.WhereValue), queryable.GroupByValue.GetGroupBy(), strOrder); } else if (queryable.Skip != null && queryable.Take == null) { //if (joinInfo.IsValuable()) //{ // sbSql.Insert(0, "SELECT * FROM ( "); //} //else //{ // sbSql.Insert(0, "SELECT " + queryable.SelectValue.GetSelectFiles() + " FROM ( "); //} //sbSql.Append(") t WHERE t.row_index>" + (queryable.Skip)); strSql = string.Format("select {0} from {1}{2}{3} where 1=1 and [{5}]{6}([{5}]) from (select top {7} [{5}] from {1}{2}{3} where 1=1 {4}{9}) as tblTmp) {4} {8} {9}", queryable.SelectValue.GetSelectFiles(), tableName.GetTranslationSqlName(), joinInfo, withNoLock, string.Join("", queryable.WhereValue), queryable.OrderByField, strTmp, queryable.Skip, queryable.GroupByValue.GetGroupBy(), strOrder); } else if (queryable.Skip != null && queryable.Take != null) { if (queryable.Skip == 0) { strSql = string.Format("select top {0} {1} from {2} {3} {4} where 1=1 {5} {6} {7}", queryable.Take, queryable.SelectValue.GetSelectFiles(), tableName.GetTranslationSqlName(), joinInfo, withNoLock, string.Join("", queryable.WhereValue), queryable.GroupByValue.GetGroupBy(), strOrder); } else { strSql = string.Format("select top {0} {1} from {2}{3}{4} where 1=1 and [{6}]{7}([{6}]) from (select top {8} [{6}] from {2}{3}{4} where 1=1 {5}{10}) as tblTmp) {5} {9} {10}", queryable.Take, queryable.SelectValue.GetSelectFiles(), tableName.GetTranslationSqlName(), joinInfo, withNoLock, string.Join("", queryable.WhereValue), queryable.OrderByField, strTmp, queryable.Skip, queryable.GroupByValue.GetGroupBy(), strOrder); } sbSql.Clear(); sbSql.Append(strSql); //if (joinInfo.IsValuable()) //{ // sbSql.Insert(0, "SELECT * FROM ( "); //} //else //{ // sbSql.Insert(0, "SELECT " + queryable.SelectValue.GetSelectFiles() + " FROM ( "); //} //sbSql.Append(") t WHERE t.row_index BETWEEN " + (queryable.Skip + 1) + " AND " + (queryable.Skip + queryable.Take)); } #endregion } else { #region offset string withNoLock = queryable.DB.IsNoLock ? "WITH(NOLOCK)" : null; var order = queryable.OrderByValue.IsValuable() ? ("ORDER BY " + queryable.OrderByValue + " ") : null; sbSql.AppendFormat("SELECT " + queryable.SelectValue.GetSelectFiles() + " {1} FROM {0} {5} {2} WHERE 1=1 {3} {4} ", tableName.GetTranslationSqlName(), "", withNoLock, string.Join("", queryable.WhereValue), queryable.GroupByValue.GetGroupBy(), joinInfo); sbSql.Append(order); if (queryable.Skip != null || queryable.Take != null) { sbSql.AppendFormat("OFFSET {0} ROW FETCH NEXT {1} ROWS ONLY", Convert.ToInt32(queryable.Skip), Convert.ToInt32(queryable.Take)); } #endregion } return(sbSql); }