/// <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);
}
public COASqlData <T> GenerateInsertQuery()
{
var insertQuery = new StringBuilder($"INSERT INTO {TableName} ");
insertQuery.Append("(");
var properties = Types.GenerateListOfProperties(GetProperties);
properties.ForEach(prop => { if (prop != PrimaryKeyName)
{
insertQuery.Append($"[{prop}],");
}
});
insertQuery
.Remove(insertQuery.Length - 1, 1)
.Append(") VALUES (");
properties.ForEach(prop =>
{
if (prop != PrimaryKeyName)
{
if (!Oracle)
{
insertQuery.Append($"@{prop},");
}
else
{
insertQuery.Append($":{prop},");
}
}
});
insertQuery
.Remove(insertQuery.Length - 1, 1)
.Append(")");
return(new COASqlData <T>()
{
SqlQuery = insertQuery.ToString(), Lenght = insertQuery.ToString().Length, TableName = TableName, Oracle = Oracle, PrimaryKeyName = PrimaryKeyName
});
}
public COASqlSelectData <T> GenerateSelectQuery()
{
return(selectQuery("*", Types.GenerateListOfProperties(GetProperties)));
}