public static string GetSqlForTotalBuilder(SelectBuilderData data) { StringBuilder sql = new StringBuilder(); sql.Append("select count(0) as result"); sql.AppendFormat(" from {0}", data.From); if (data.WhereSql.Length > 0) { sql.Append(" where "); sql.Append(data.WhereSql); } return(sql.ToString()); }
public static string GetSqlForSelectBuilder(SelectBuilderData data) { var sql = new StringBuilder(); if (data.PagingCurrentPage == 1) { if (data.PagingItemsPerPage == 0) { sql.Append("select"); } else // support distinct start //sql.Append("select top " + data.PagingItemsPerPage.ToString()); if (data.Select.ToLower().Trim().StartsWith("distinct")) { sql.Append("select distinct top " + data.PagingItemsPerPage.ToString()); data.Select = data.Select.Trim().Substring(8); } else { sql.Append("select top " + data.PagingItemsPerPage.ToString()); } //modify end sql.Append(" " + data.Select); sql.Append(" from " + data.From); if (data.WhereSql.Length > 0) { sql.Append(" where " + data.WhereSql); } if (data.GroupBy.Length > 0) { sql.Append(" group by " + data.GroupBy); } if (data.Having.Length > 0) { sql.Append(" having " + data.Having); } if (data.OrderBy.Length > 0) { sql.Append(" order by " + data.OrderBy); } return(sql.ToString()); } else { sql.Append(" from " + data.From); if (data.WhereSql.Length > 0) { sql.Append(" where " + data.WhereSql); } if (data.GroupBy.Length > 0) { sql.Append(" group by " + data.GroupBy); } if (data.Having.Length > 0) { sql.Append(" having " + data.Having); } var pagedSql = string.Format(@"with PagedPersons as ( select top 100 percent {0}, row_number() over (order by {1}) as FLUENTDATA_ROWNUMBER {2} ) select * from PagedPersons where fluentdata_RowNumber between {3} and {4}" , data.Select, data.OrderBy, sql, ((data.PagingCurrentPage * data.PagingItemsPerPage) - data.PagingItemsPerPage + 1), (data.PagingCurrentPage * data.PagingItemsPerPage)); return(pagedSql); } }