Joining Tables with QueryAsync, Problem with DateTime

Tom92Tom92 USMember ✭✭
edited September 2018 in Xamarin.Forms

Hi!

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 + "'");

Answers

  • ColeXColeX Member, Xamarin Team Xamurai

    Can you provide detailed explanation about "datetime" part of my SQL String isnt working ?

    Not be able to get the expected result ? Get error ?

  • Tom92Tom92 USMember ✭✭
    edited September 2018

    Iam not getting the expacted result (0 Todos, should be around 36) and no error.

    When Iam deleting the "AND [Todo].[date] ..." part of my SQL-String, Iam getting returned some Todo objects, so the join seams to work. As workaround Iam using Linq-WHERE to filter my "plannedTodos" by "startDate" and "endDate" for now. But I want to have it filtered "directly" on Datebase with SQLite.

    So I think Iam using the DateTime parameter in the SQL-String wrong?

    Do you know how to do the JOIN and filter with WHERE of two tables in C#-SQLite Query way (no SQLite-String)?

  • Tom92Tom92 USMember ✭✭

    Has anyone an idea how to solve this problem?

Sign In or Register to comment.