/// <summary>
/// Fetches all row data which matches to statements from database
/// </summary>
/// <typeparam name="T">The type of entity class</typeparam>
/// <param name="query">The query which applied to SQL</param>
/// <returns></returns>
public static List <T> FetchList <T>(this IQuery <T> query)
{
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append($"SELECT { (query.TakeCount.HasValue ? "TOP " + query.TakeCount.Value.ToString() : "") }");
if (query.Selects.PropertyAssignments.Any())
{
for (int i = 0; i < query.Selects.PropertyAssignments.Count; i++)
{
sbQuery.Append(query.Selects.PropertyAssignments[i].FromType.Name.ToLower());
sbQuery.Append(".");
sbQuery.Append(query.Selects.PropertyAssignments[i].FromProperty);
sbQuery.Append(" AS ");
sbQuery.Append(query.Selects.PropertyAssignments[i].PropertyName);
if (i < query.Selects.PropertyAssignments.Count - 1)
{
sbQuery.Append(", ");
}
else
{
sbQuery.Append(" ");
}
}
}
else
{
sbQuery.Append(" * ");
}
sbQuery.Append(query.FromStatement);
sbQuery.Append(query.JoinStatement);
sbQuery.Append(" WHERE ");
sbQuery.Append(query.WhereStatement);
sbQuery.Append(" ORDER BY ");
sbQuery.Append(query.OrderByAscendingStatement);
sbQuery.Append(" ");
sbQuery.Append(query.OrderByDescendingStatement);
if (query.SkipCount.HasValue && query.TakeCount.HasValue)
{
sbQuery.Append($" OFFSET {query.SkipCount.Value} ROWS FETCH NEXT {query.TakeCount.Value} ROWS ONLY");
}
MsSQLDataProvider <T> dataProvider = new MsSQLDataProvider <T>();
return(dataProvider.GetList(sbQuery.ToString(), query.ConnectionString));
}
/// <summary>
/// Fetches 1 row data from database
/// </summary>
/// <typeparam name="T">The type of entity class</typeparam>
/// <param name="query">The query which applied to SQL</param>
/// <returns></returns>
public static T FetchFirst <T>(this IQuery <T> query)
{
StringBuilder sbQuery = new StringBuilder();
sbQuery.Append($"SELECT TOP 1 ");
if (query.Selects.PropertyAssignments.Any())
{
for (int i = 0; i < query.Selects.PropertyAssignments.Count; i++)
{
sbQuery.Append(query.Selects.PropertyAssignments[i].FromType.Name.ToLower());
sbQuery.Append(".");
sbQuery.Append(query.Selects.PropertyAssignments[i].FromProperty);
sbQuery.Append(" AS ");
sbQuery.Append(query.Selects.PropertyAssignments[i].PropertyName);
if (i < query.Selects.PropertyAssignments.Count - 1)
{
sbQuery.Append(", ");
}
else
{
sbQuery.Append(" ");
}
}
}
else
{
sbQuery.Append(" * ");
}
sbQuery.Append(query.FromStatement);
sbQuery.Append(query.JoinStatement);
sbQuery.Append(" WHERE ");
sbQuery.Append(query.WhereStatement);
sbQuery.Append(" ORDER BY ");
sbQuery.Append(query.OrderByAscendingStatement);
sbQuery.Append(" ");
sbQuery.Append(query.OrderByDescendingStatement);
MsSQLDataProvider <T> dataProvider = new MsSQLDataProvider <T>();
return(dataProvider.Get(sbQuery.ToString(), query.ConnectionString));
}
