public ISelectBuilder <TEntity> OrderBy(string sql)
{
SelectBuilderData data = Data;
data.OrderBy += sql;
return(this);
}
public string GetSqlForSelectBuilder(SelectBuilderData data)
{
string text = "";
text = "select " + data.Select;
text = text + " from " + data.From;
if (data.WhereSql.Length > 0)
{
text = text + " where " + data.WhereSql;
}
if (data.GroupBy.Length > 0)
{
text = text + " group by " + data.GroupBy;
}
if (data.Having.Length > 0)
{
text = text + " having " + data.Having;
}
if (data.OrderBy.Length > 0)
{
text = text + " order by " + data.OrderBy;
}
if (data.PagingItemsPerPage > 0 && data.PagingCurrentPage > 0)
{
text += string.Format(" limit {0}, {1}", data.GetFromItems() - 1, data.GetToItems());
}
return(text);
}
public string GetSqlForSelectBuilder(SelectBuilderData data)
{
string text = "";
text = "select " + data.Select;
text = text + " from " + data.From;
if (data.WhereSql.Length > 0)
{
text = text + " where " + data.WhereSql;
}
if (data.GroupBy.Length > 0)
{
text = text + " group by " + data.GroupBy;
}
if (data.Having.Length > 0)
{
text = text + " having " + data.Having;
}
if (data.OrderBy.Length > 0)
{
text = text + " order by " + data.OrderBy;
}
if (data.PagingItemsPerPage > 0)
{
if (data.PagingItemsPerPage > 0)
{
text = text + " limit " + data.PagingItemsPerPage;
}
text = text + " offset " + (data.GetFromItems() - 1);
}
return(text);
}
public string GetSqlForSelectBuilder(SelectBuilderData data)
{
string text = "";
text = "select " + data.Select;
text = text + " from " + data.From;
if (data.WhereSql.Length > 0)
{
text = text + " where " + data.WhereSql;
}
if (data.GroupBy.Length > 0)
{
text = text + " group by " + data.GroupBy;
}
if (data.Having.Length > 0)
{
text = text + " having " + data.Having;
}
if (data.OrderBy.Length > 0)
{
text = text + " order by " + data.OrderBy;
}
if (data.PagingItemsPerPage > 0)
{
object obj = text;
text = obj + " offset " + (data.GetFromItems() - 1) + " rows";
if (data.PagingItemsPerPage > 0)
{
object obj2 = text;
text = obj2 + " fetch next " + data.PagingItemsPerPage + " rows only";
}
}
return(text);
}
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.PagingItemsPerPage);
}
return(sql);
}
public ISelectBuilder <TEntity> Where(string sql)
{
SelectBuilderData data = Data;
data.WhereSql += sql;
return(this);
}
public ISelectBuilder <TEntity> From(string sql)
{
SelectBuilderData data = Data;
data.From += sql;
return(this);
}
public ISelectBuilder <TEntity> Select(string sql)
{
SelectBuilderData data = Data;
data.Select += sql;
return(this);
}
public ISelectBuilder <TEntity> Having(string sql)
{
SelectBuilderData data = Data;
data.Having += sql;
return(this);
}
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 ISelectBuilder <TEntity> OrWhere(string sql)
{
if (Data.WhereSql.Length > 0)
{
SelectBuilderData data = Data;
data.WhereSql += " or ";
}
SelectBuilderData data2 = Data;
data2.WhereSql += sql;
return(this);
}
public string GetSqlForSelectBuilder(SelectBuilderData data)
{
StringBuilder stringBuilder = new StringBuilder();
if (data.PagingCurrentPage == 1)
{
if (data.PagingItemsPerPage == 0)
{
stringBuilder.Append("select");
}
else
{
stringBuilder.Append("select top " + data.PagingItemsPerPage.ToString());
}
stringBuilder.Append(" " + data.Select);
stringBuilder.Append(" from " + data.From);
if (data.WhereSql.Length > 0)
{
stringBuilder.Append(" where " + data.WhereSql);
}
if (data.GroupBy.Length > 0)
{
stringBuilder.Append(" group by " + data.GroupBy);
}
if (data.Having.Length > 0)
{
stringBuilder.Append(" having " + data.Having);
}
if (data.OrderBy.Length > 0)
{
stringBuilder.Append(" order by " + data.OrderBy);
}
return(stringBuilder.ToString());
}
stringBuilder.Append(" from " + data.From);
if (data.WhereSql.Length > 0)
{
stringBuilder.Append(" where " + data.WhereSql);
}
if (data.GroupBy.Length > 0)
{
stringBuilder.Append(" group by " + data.GroupBy);
}
if (data.Having.Length > 0)
{
stringBuilder.Append(" having " + data.Having);
}
return(string.Format("with PagedPersons as\r\n\t\t\t\t\t\t\t\t(\r\n\t\t\t\t\t\t\t\t\tselect top 100 percent {0}, row_number() over (order by {1}) as FLUENTDATA_ROWNUMBER\r\n\t\t\t\t\t\t\t\t\t{2}\r\n\t\t\t\t\t\t\t\t)\r\n\t\t\t\t\t\t\t\tselect *\r\n\t\t\t\t\t\t\t\tfrom PagedPersons\r\n\t\t\t\t\t\t\t\twhere fluentdata_RowNumber between {3} and {4}", data.Select, data.OrderBy, stringBuilder, data.GetFromItems(), data.GetToItems()));
}
public string GetSqlForSelectBuilder(SelectBuilderData data)
{
string text = "";
if (data.PagingItemsPerPage == 0)
{
text = "select " + data.Select;
text = text + " from " + data.From;
if (data.WhereSql.Length > 0)
{
text = text + " where " + data.WhereSql;
}
if (data.GroupBy.Length > 0)
{
text = text + " group by " + data.GroupBy;
}
if (data.Having.Length > 0)
{
text = text + " having " + data.Having;
}
if (data.OrderBy.Length > 0)
{
text = text + " order by " + data.OrderBy;
}
}
else if (data.PagingItemsPerPage > 0)
{
text = text + " from " + data.From;
if (data.WhereSql.Length > 0)
{
text = text + " where " + data.WhereSql;
}
if (data.GroupBy.Length > 0)
{
text = text + " group by " + data.GroupBy;
}
if (data.Having.Length > 0)
{
text = text + " having " + data.Having;
}
text = string.Format("select * from\r\n\t\t\t\t\t\t\t\t\t\t(\r\n\t\t\t\t\t\t\t\t\t\t\tselect {0}, \r\n\t\t\t\t\t\t\t\t\t\t\t\trow_number() over (order by {1}) FLUENTDATA_ROWNUMBER\r\n\t\t\t\t\t\t\t\t\t\t\t{2}\r\n\t\t\t\t\t\t\t\t\t\t)\r\n\t\t\t\t\t\t\t\t\t\twhere fluentdata_RowNumber between {3} and {4}\r\n\t\t\t\t\t\t\t\t\t\torder by fluentdata_RowNumber", data.Select, data.OrderBy, text, data.GetFromItems(), data.GetToItems());
}
return(text);
}
public string GetSqlForSelectBuilder(SelectBuilderData data)
{
var sql = "";
if (data.HasFoundRows)
{
sql = "select SQL_CALC_FOUND_ROWS " + data.Select;
}
else
{
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());
sql += string.Format(" limit {0}, {1}", data.GetFromItems() - 1, data.PagingItemsPerPage);
}
if (data.HasFoundRows)
{
sql += ";SELECT FOUND_ROWS();";
}
return(sql);
}
public string GetSqlForSelectBuilder(SelectBuilderData data)
{
throw new NotImplementedException();
}
public string GetSqlForSelectBuilder(SelectBuilderData data)
{
var sql = new StringBuilder();
if (data.PagingCurrentPage == 1)
{
if (data.PagingItemsPerPage == 0)
{
sql.Append("select");
}
else
{
sql.Append("select top " + data.PagingItemsPerPage.ToString());
}
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);
}
}
public SelectBuilder(IDbCommand command)
{
Data = new SelectBuilderData(command, "");
Actions = new ActionsHandler(Data);
}
public string GetSqlForSelectBuilder(SelectBuilderData data)
{
var sql = new StringBuilder();
if (data.PagingCurrentPage == 1)
{
if (data.PagingItemsPerPage == 0)
{
sql.Append("select");
}
else
{
sql.Append("select top " + data.PagingItemsPerPage.ToString());
}
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);
}
#region SqlServer 2012以上版本的分页(此效率更高,以上版本通用)
//if (!string.IsNullOrWhiteSpace(data.Select))
//{
// sql += "select " + data.Select;
//}
//if (!string.IsNullOrWhiteSpace(data.From))
//{
// sql += " from " + data.From;
//}
//if (!string.IsNullOrWhiteSpace(data.WhereSql))
//{
// sql += " where " + data.WhereSql;
//}
//if (!string.IsNullOrWhiteSpace(data.GroupBy))
//{
// sql += " group by " + data.GroupBy;
//}
//if (!string.IsNullOrWhiteSpace(data.Having))
//{
// sql += " having " + data.Having;
//}
//if (!string.IsNullOrWhiteSpace(data.OrderBy))
//{
// sql += " order by " + data.OrderBy;
//}
//if (data.PagingItemsPerPage > 0)
//{
// sql += " offset " + data.GetFormItemsRowsIndex + " rows";
// sql += " fetch next " + data.PagingItemsPerPage + " rows only";
//}
//return sql;
#endregion
}