/// <summary>
/// Generates a querie list to create tables.
/// <para/>
/// SQL query Example: <para/>
/// CREATE TABLE table1 (id INTEGER PRIMARY KEY, firstColumn TEXT NOT NULL, secondColumn REAL NOT NULL)
/// </summary>
/// <returns>Returns a list of queries to create the local tables.</returns>
private List <string> CreateTableQueries(Dictionary <string, Table> tables)
{
List <string> tableQueries = new List <string>();
foreach (KeyValuePair <string, Table> table in tables)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
List <string> paramList = new List <string>();
int iter = 0;
foreach (KeyValuePair <string, Type> column in table.Value.Columns)
{
sqb.AddValue(column.Key);
if (column.Value == typeof(int))
{
sqb.TypeInteger();
}
else if (column.Value == typeof(string))
{
sqb.TypeText();
}
else if (column.Value == typeof(double))
{
sqb.TypeReal();
}
else if (column.Value == typeof(float))
{
sqb.TypeReal();
}
else if (column.Value == typeof(DateTime))
{
sqb.TypeText();
}
else
{
throw new NotSupportedException(column.Value.Name + " Datatype not supported");
}
if (iter.Equals(0))
{
sqb.ParamPrimaryKey();
}
else
{
sqb.ParamNot().Null();
}
paramList.Add(sqb.Flush());
iter++;
}
string values = sqb.Brackets_Multiple(paramList, false).Flush();
sqb.Create().Table().IfNotExists().AddValue(table.Value.TableName).AddValue(values);
tableQueries.Add(sqb.ToString());
tableQueries.Add(CreateTriggerTable(table.Value.TableName));
tableQueries.Add(InsertStartValueTriggerTable(table.Value.TableName));
tableQueries.Add(CreateCounterAddTriger(table.Value.TableName));
tableQueries.Add(CreateCounterSubTriger(table.Value.TableName));
}
return(tableQueries);
}
/// <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);
}
/// <summary>
/// Generates a querie list to create tables.
/// <para/>
/// </summary>
/// <returns>Returns a list of queries to create the local tables.</returns>
private List <string> CreateTableQueries(ConcurrentDictionary <string, Table> tables)
{
try
{
List <string> tableQueries = new List <string>();
foreach (KeyValuePair <string, Table> table in tables)
{
SQLQueryBuilder sqb = new SQLQueryBuilder();
List <string> paramList = new List <string>();
int iter = 0;
foreach (KeyValuePair <string, Type> column in table.Value.Columns)
{
sqb.AddValue(column.Key);
if (iter.Equals(0))
{
sqb.AddValue("AUTOINCREMENT").ParamPrimaryKey();
paramList.Add(sqb.Flush());
iter++;
continue;
}
if (column.Value == typeof(int))
{
sqb.TypeInteger();
}
else if (column.Value == typeof(string))
{
sqb.TypeText();
}
else if (column.Value == typeof(double))
{
sqb.TypeReal();
}
else if (column.Value == typeof(float))
{
sqb.TypeReal();
}
else if (column.Value == typeof(DateTime))
{
sqb.TypeDateTime();
}
else
{
throw new NotSupportedException(column.Value.Name + " Datatype not supported");
}
sqb.ParamNot().Null();
paramList.Add(sqb.Flush());
iter++;
}
string values = sqb.Brackets_Multiple(paramList, false).Flush();
sqb.Create().Table().AddValue(table.Value.TableName).AddValue(values);
tableQueries.Add(sqb.ToString());
}
return(tableQueries);
}
catch (Exception e)
{
throw e;
}
}