/// <summary>
/// This is search property for your datas
/// </summary>
public static COASqlSelectData <T> Where <T>(this COASqlSelectData <T> data, Expression <Func <T, bool> > Where)
{
var str = Types.PredicateToString(Where, data.DataBaseType);
data.SqlQuery += str;
data.WhereQuery = str;
data.Lenght = data.SqlQuery.Length;
return(data);
}
/// <summary>
/// Set OrderByDescing option
/// </summary>
public static COASqlSelectData <T> OrderByDescing <T>(this COASqlSelectData <T> data, Expression <Func <T, object> > select)
{
string selectStr = " ORDER BY ";
var ForeachSorgu = Types.GetAllTypes(select);
ForeachSorgu.Name.ForEach(row => { selectStr += row + ","; });
selectStr = selectStr.Remove(selectStr.Length - 1, 1);
data.SqlQuery += selectStr + " DESC";
data.Lenght = data.SqlQuery.Length;
data.OrderQuery = selectStr + " DESC";
return(data);
}
/// <summary>
/// Skip - Take your list in database
/// </summary>
public static COASqlSelectData <T> Skip <T>(this COASqlSelectData <T> data, int skip, int take)
{
string SelectedColumns = "";
if (data.SelectedColumns.Count == 0 || data.SelectedColumns == null)
{
var properties = Types.GenerateListOfProperties(typeof(T).GetProperties());
properties.ForEach(props => { SelectedColumns += props + ","; data.SelectedColumns.Add(props); });
}
else
{
data.SelectedColumns.ForEach(colums => { SelectedColumns += colums + ","; });
}
SelectedColumns = SelectedColumns.Remove(SelectedColumns.Length - 1, 1);
var Ordrquery = "";
if (string.IsNullOrEmpty(data.OrderQuery))
{
Ordrquery = $"order by {data.PrimaryKeyName} asc";
}
else
{
Ordrquery = data.OrderQuery;
}
var str = "";
if (data.DataBaseType == COADataBaseTypes.Sql)
{
str = $"with dummyTable as (select ROW_NUMBER() over({Ordrquery}) as RowNumber,* from {data.TableName}{data.WhereQuery}) select top({take}) {SelectedColumns} from dummyTable";
if (take > 0)
{
str += $" WHERE RowNumber > ({skip})";
}
}
else
{
str = $"select {SelectedColumns} from(select a.*, rownum rnum from(select * from {data.TableName}{data.WhereQuery} {Ordrquery}) a where rownum <= {take + skip})where rnum > {skip}";
}
data.SqlQuery = str;
data.Lenght = data.SqlQuery.Length;
data.OrderQuery = Ordrquery;
return(data);
}
