/// <summary>
/// Gets all rows in the given id slot.
/// <para/>
/// SQL query Example: <para/>
/// SELECT * from table3 where id >= 0 and id -= 1
/// </summary>
/// <param name="table"></param>
/// <param name="start"></param>
/// <param name="end"></param>
/// <returns></returns>
public override List <List <object> > GetRowsFromTableWithIndex(Table table, int start, int end)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
sqb.Select().ALL().From().AddValue(table.TableName).Where().AddValue(table.Columns.First().Key);
sqb.GreaterThen().AddValue(start.ToString()).AND().AddValue(table.Columns.First().Key).LesserThen().AddValue(end.ToString());
List <List <object> > results = ReadQuery(sqb.ToString(), GenerateOutputValuesFromTable(table));
return(results);
}
/// <summary>
/// Gets all rows in the given DateTime slot.
/// <para/>
/// SQL query Example: <para/>
/// select * from table3 where Date >= "2018-12-06 11:10:32.632" and Date -= "2018-12-06 12:05:57.526";
/// </summary>
/// <param name="table">The name of the table to get the data from.</param>
/// <param name="DateTimeColumnName">The name of the column with the DateTime values.</param>
/// <param name="from">A DateTime object with the beginning of the timeslot.</param>
/// <param name="until">A DateTime object with the end of the timeslot.</param>
/// <returns></returns>
public override List <List <object> > GetRowsFromTableWithTime(Table table, string DateTimeColumnName, DateTime from, DateTime until)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
string stringFrom = sqb.Apostrophe(from.ToString(_stringFormat)).Flush();
string stringUntil = sqb.Apostrophe(until.ToString(_stringFormat)).Flush();
sqb.Select().ALL().From().AddValue(table.TableName).Where().AddValue(DateTimeColumnName);
sqb.GreaterThen().AddValue(stringFrom).AND().AddValue(DateTimeColumnName).LesserThen().AddValue(stringUntil);
List <List <object> > results = ReadQuery(sqb.ToString(), GenerateOutputValuesFromTable(table));
return(results);
}
/// <summary>
/// Gets all rows in the given id slot.
/// <para/>
/// </summary>
/// <param name="table"></param>
/// <param name="start"></param>
/// <param name="end"></param>
/// <param name="ascending">Ascending or descending by first param.</param>
/// <returns></returns>
public override List <List <object> > GetRowsFromTableWithIndex(Table table, int start, int end, bool ascending = true)
{
try
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
sqb.Select().ALL().From().AddValue(table.TableName).Where().AddValue(table.Columns.First().Key);
sqb.GreaterThen().AddValue(start.ToString()).AND().AddValue(table.Columns.First().Key).LesserThen().AddValue(end.ToString());
if (!ascending)
{
sqb.OrderBY().AddValue(table.Columns.First().Key).Desc();
}
List <List <object> > results = ReadQuery(sqb.ToString(), GenerateOutputValuesFromTable(table));
return(results);
}
catch (Exception e)
{
throw e;
}
}
/// <summary>
/// Gets all rows in the given DateTime slot.
/// <para/>
/// </summary>
/// <param name="table">The name of the table to get the data from.</param>
/// <param name="DateTimeColumnName">The name of the column with the DateTime values.</param>
/// <param name="from">A DateTime object with the beginning of the timeslot.</param>
/// <param name="until">A DateTime object with the end of the timeslot.</param>
/// <param name="ascending">Ascending or descending by DateTimeColumn param.</param>
/// <returns></returns>
public override List <List <object> > GetRowsFromTableWithTime(Table table, string DateTimeColumnName, DateTime from, DateTime until, bool ascending = true)
{
try
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
string stringFrom = sqb.Tags(from.ToString(_stringFormat)).Flush();
string stringUntil = sqb.Tags(until.ToString(_stringFormat)).Flush();
sqb.Select().ALL().From().AddValue(table.TableName).Where().AddValue(DateTimeColumnName);
sqb.GreaterThen().AddValue(stringFrom).AND().AddValue(DateTimeColumnName).LesserThen().AddValue(stringUntil);
if (!ascending)
{
sqb.OrderBY().AddValue(DateTimeColumnName).Desc();
}
List <List <object> > results = ReadQuery(sqb.ToString(), GenerateOutputValuesFromTable(table));
return(results);
}
catch (Exception e)
{
throw e;
}
}