Iam trying to join two tables in combination with a where clause.
The Table "Order" has a relation to the table "Todo" with the foreign key "order_id".
I want to have the "Order.name" stored as "Todo.order_name" for every "Todo" object which is "done== true" and its "date" is between "startDate" and "endDate".
So far my SQL String is working, iam getting "Todo" objects out of the database and the "order_name" is also correct loaded from the joined "Order" table. The problem is, that the "datetime" part of my SQL String isnt working and i dont know what iam doning wrong.
1) Do someone has a idea how correctly use the "startDate" and "endDate" DateTime values as SQLite parameters, so the "Todo.date" comparision is working?
2) I know my approach is vulnerable for SQL-Injection, but this is for now the first "join" approch which is working for me.
Can someone provide a better and safer approch, maybe whithout using a "SQL String" ?
Here is my code:
var startDateString = startDate.ToString("s", CultureInfo.InvariantCulture); var endDateString = endDate.ToString("s", CultureInfo.InvariantCulture); List<Todo> plannedTodos = await app.DbConnection.QueryAsync<Todo>("SELECT DISTINCT [Order].[name] AS [order_name], [Todo].[id], [Todo].[text], [Todo].[done], [Todo].[date] FROM [Order] INNER JOIN [Todo] ON [Todo].[order_id] = [Order].[id] WHERE [Todo].[done] = 1 AND [Todo].[date] BETWEEN datetime('"+ startDateString + "') AND datetime('"+ endDateString + "'");