/// <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 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>
/// <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.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);
if (!ascending)
{
sqb.OrderBY().AddValue(DateTimeColumnName).Desc();
}
List <List <object> > results = ReadQuery(sqb.ToString(), GenerateOutputValuesFromTable(table));
return(results);
}
catch (Exception)
{
throw;
}
}
/// <summary>
/// Updates the given columns with the given id in the first column.
/// Each row of rowsToUpdate must have the same size as rowsData.
/// </summary>
/// <param name="tableName">The table where rows should be updated.</param>
/// <param name="rowsToUpdate">The rows with the name and data type to update.</param>
/// <param name="rowsData">The rows with all column data which should be updated.</param>
public override void UpdateTable(string tableName, List <Dictionary <string, Type> > rowsToUpdate, List <List <object> > rowsData)
{
int rowIter = 0;
List <string> queryList = new List <string>();
foreach (Dictionary <string, Type> row in rowsToUpdate)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
List <object> columnData = rowsData[rowIter];
List <string> columns = new List <string>();
if (!row.Count.Equals(columnData.Count))
{
throw new InvalidDataException(rowIter.ToString() + ". row size from rowsToUpdate doesn't match current row size from rowsData");
}
int columnIter = 0;
foreach (KeyValuePair <string, Type> column in row)
{
if (columnIter.Equals(0))
{
columnIter++;
continue;
}
if (!columnData[columnIter].GetType().Equals(column.Value))
{
throw new TypeLoadException("Type of the data doesn't match the columns type!");
}
if (column.Value == typeof(int))
{
sqb.AddValue(columnData[columnIter].ToString());
}
else if (column.Value == typeof(string))
{
sqb.Apostrophe(sqb.AddValue(columnData[columnIter].ToString()).Flush());
}
else if (column.Value == typeof(double))
{
sqb.AddValue(columnData[columnIter].ToString().Replace(',', '.'));
}
else if (column.Value == typeof(float))
{
sqb.AddValue(columnData[columnIter].ToString().Replace(',', '.'));
}
else if (column.Value == typeof(DateTime))
{
DateTime convertTime = (DateTime)columnData[columnIter];
sqb.Apostrophe(sqb.AddValue(convertTime.ToString(_stringFormat)).Flush());
}
else
{
throw new NotSupportedException(column.Value.Name + " Datatype not supported");
}
string value = sqb.Flush();
sqb.AddValue(column.Key).Equal().AddValue(value);
columns.Add(sqb.Flush());
columnIter++;
}
sqb.Update().AddValue(tableName).Set().AddValues(columns).Where().AddValue(row.First().Key).Equal().AddValue(columnData[0].ToString());
queryList.Add(sqb.ToString());
rowIter++;
}
CommitBatchQuery(queryList);
}
/// <summary>
/// Inserts the given objects into the given table. The first object "id" is ignored due to the auto increment,
/// <para/>
/// SQL query Example: <para/>
/// INSERT INTO table3 (Name, Date, value) VALUES ('John Doe', '2018-12-06 12:01:16.767', 22.5);
/// </summary>
/// <param name="tableName">The name of the table to insert rows to.</param>
/// <param name="rows">A list of rows with all column objects to insert.</param>
/// <param name="tables">All saved tables.</param>
public override void InsertIntoTable(string tableName, Dictionary <string, Table> tables, List <List <object> > rows)
{
List <string> queryList = new List <string>();
foreach (List <object> row in rows)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
List <string> firstBracket = new List <string>();
List <string> secondBracket = new List <string>();
int listIter = 0;
foreach (KeyValuePair <string, Type> column in tables[tableName].Columns)
{
if (listIter.Equals(0))
{
listIter++;
continue;
}
if (!row[listIter].GetType().Equals(column.Value))
{
throw new TypeLoadException("Type of the data doesn't match the columns type!");
}
firstBracket.Add(sqb.AddValue(column.Key).Flush());
if (column.Value == typeof(int))
{
sqb.AddValue(row[listIter].ToString());
}
else if (column.Value == typeof(string))
{
sqb.Apostrophe(sqb.AddValue(row[listIter].ToString()).Flush());
}
else if (column.Value == typeof(double))
{
sqb.AddValue(row[listIter].ToString().Replace(',', '.'));
}
else if (column.Value == typeof(float))
{
sqb.AddValue(row[listIter].ToString().Replace(',', '.'));
}
else if (column.Value == typeof(DateTime))
{
DateTime convertTime = (DateTime)row[listIter];
sqb.Apostrophe(sqb.AddValue(convertTime.ToString(_stringFormat)).Flush());
}
else
{
throw new NotSupportedException(column.Value.Name + " Datatype not supported");
}
secondBracket.Add(sqb.Flush());
listIter++;
}
string columnNames = sqb.Brackets_Multiple(firstBracket, false).Flush();
string columnValues = sqb.Brackets_Multiple(secondBracket, false).Flush();
sqb.InsertInto().AddValue(tableName).AddValue(columnNames).Values().AddValue(columnValues);
queryList.Add(sqb.ToString());
}
CommitBatchQuery(queryList);
}