Auto Deleting rows after expiration date in a SQLite database c#

Question

I have a program using a SQLite database which needs to remove a row after it's expiration date is lower than the current time. The table i am trying to interact with has a field with the id,name,expiration date,entrance date,code of costumers, each customer has a different expiration date and this data is retrieved by using:

DateTime.Now.AddDays(numberofdaysstaying)

and the current time is retrieved by using:

DateTime.Now

My sql code is executed in a different class named sqlManager and the function i use to auto-delete is:

        public void Autodelete()
    {
        using (SQLiteConnection sqlConn = new SQLiteConnection("Data Source=projecto.sqlite;Version=3;"))
        {
            sqlConn.Open();
            //create command
            SQLiteCommand sqlCommand = new SQLiteCommand("DELETE FROM cliente WHERE expirationDate < @td", sqlConn);
            sqlCommand.Parameters.AddWithValue(Datetime.Now));

            sqlCommand.ExecuteNonQuery();
        }

And this does nothing really, there is no exception or anything like that... it just doesnt delete a thing. Any tips on how to make this work?

If he choose *datetime* as column type the *real* column type is set by type affinity to *numeric* as documented https://www.sqlite.org/datatype3.html#section_3_2 — Sir Rufo, Sep 01 '16 at 18:10
@SirRufo yes i did choose datetime it is presented in the db as "30/08/2016 11:55:43" — wannabeLearner, Sep 01 '16 at 18:12
Store the dates using `ToString(u)" and they will evaluate as you want. They are still strings though, not dates — Ňɏssa Pøngjǣrdenlarp, Sep 01 '16 at 20:44

score 1 · Accepted Answer · edited May 23 '17 at 11:51

1

I would convert to unix time using date(expirationDate, unixepoch) then compare it to the time now in unix time. This should be easier because unix time is an integer. It is the time in seconds since 1970-01-01. I have linked below another question that answers how to get unix time in C#.

How to get the unix timestamp in C#

edited May 23 '17 at 11:51

Community

1
1

answered Sep 01 '16 at 18:41

Marc T

26
3

Worked for me although it makes some funny looking code. Thanks – wannabeLearner Sep 02 '16 at 13:18

Auto Deleting rows after expiration date in a SQLite database c#

1 Answers1