/// <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; } }
/// <summary> /// Inserts the given objects into the given table. The first object "id" is ignored due to the auto increment, /// <para/> /// </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, ConcurrentDictionary <string, Table> tables, List <List <object> > rows) { try { 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.Tags(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); } catch (Exception e) { throw e; } }