I am developing a gym membership program on visual studio using C# and sqlite. On every employee-log in attempt, I want the program to check for expired customers where ExpiryDate(Attribute in the table Customer) is less than today's date. this is the piece of code I used:
string sql2 = "delete from Customer where ExpiryDate<' " + DateTime.Today + " ' ";
SQLiteCommand command2 = new SQLiteCommand(sql2, m_dbConnection);
command2.ExecuteNonQuery();
Why not use SQLite built-in functions?
delete from Customer where ExpiryDate<DATE('NOW')
Aren't you missing something like
delete from Customer where ExpiryDate<DATE('NOW') AND Costumer_id=?
Use a parameterized query and your problems in converting a date to a string will evaporate
string sql2 = "delete from Customer where ExpiryDate < @td";
SQLiteCommand command2 = new SQLiteCommand(sql2, m_dbConnection);
command2.Parameters.AddWithValue("@td", DateTime.Today);
command2.ExecuteNonQuery();
Of course this is also the recommended way to avoid Sql Injections but in this case is not your main concern.
By the way, I agree with comments in your question above. Probably it is better to allow a bit of flexibility in the calculation of the deadlines. For example you could add a configuration option that set the maximum number of days allowed after the deadline and add it to the DateTime.Today value.
