public string GetSqlForSelectBuilder(SelectBuilderData data)
{
var sql = "";
sql = "select " + data.Select;
sql += " from " + data.From;
if (data.WhereSql.Length > 0)
{
sql += " where " + data.WhereSql;
}
if (data.GroupBy.Length > 0)
{
sql += " group by " + data.GroupBy;
}
if (data.Having.Length > 0)
{
sql += " having " + data.Having;
}
if (data.OrderBy.Length > 0)
{
sql += " order by " + data.OrderBy;
}
if (data.PagingItemsPerPage > 0 &&
data.PagingCurrentPage > 0)
{
sql += string.Format(" limit {0}, {1}", data.GetFromItems() - 1, data.GetToItems());
}
return(sql);
}
public string GetSqlForSelectBuilder(SelectBuilderData data)
{
var sql = "";
sql = "select " + data.Select;
sql += " from " + data.From;
if (data.WhereSql.Length > 0)
{
sql += " where " + data.WhereSql;
}
if (data.GroupBy.Length > 0)
{
sql += " group by " + data.GroupBy;
}
if (data.Having.Length > 0)
{
sql += " having " + data.Having;
}
if (data.OrderBy.Length > 0)
{
sql += " order by " + data.OrderBy;
}
if (data.PagingItemsPerPage > 0)
{
sql += " offset " + (data.GetFromItems() - 1) + " rows";
if (data.PagingItemsPerPage > 0)
{
sql += " fetch next " + data.PagingItemsPerPage + " rows only";
}
}
return(sql);
}
public string GetSqlForSelectBuilder(SelectBuilderData data)
{
var sql = "";
if (data.PagingItemsPerPage == 0)
{
sql = "select " + data.Select;
sql += " from " + data.From;
if (data.WhereSql.Length > 0)
{
sql += " where " + data.WhereSql;
}
if (data.GroupBy.Length > 0)
{
sql += " group by " + data.GroupBy;
}
if (data.Having.Length > 0)
{
sql += " having " + data.Having;
}
if (data.OrderBy.Length > 0)
{
sql += " order by " + data.OrderBy;
}
}
else if (data.PagingItemsPerPage > 0)
{
sql += " from " + data.From;
if (data.WhereSql.Length > 0)
{
sql += " where " + data.WhereSql;
}
if (data.GroupBy.Length > 0)
{
sql += " group by " + data.GroupBy;
}
if (data.Having.Length > 0)
{
sql += " having " + data.Having;
}
sql = string.Format(@"select * from
(
select {0},
row_number() over (order by {1}) FLUENTDATA_ROWNUMBER
{2}
)
where fluentdata_RowNumber between {3} and {4}
order by fluentdata_RowNumber" ,
data.Select,
data.OrderBy,
sql,
data.GetFromItems(),
data.GetToItems());
}
return(sql);
}
public string GetSqlForSelectBuilder(SelectBuilderData data)
{
var sql = new StringBuilder();
if (data.PagingCurrentPage == 1)
{
if (data.PagingItemsPerPage == 0)
{
sql.Append("select");
}
else
//modify by liuhuisheng on 2013-08-28 for 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.Union);
if (data.Union.Length > 0)
{
sql.Append(data.Union + " union " + " select " + data.Select);
}
else
{
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.GetFromItems(),
data.GetToItems());
return(pagedSql);
}
}