How to write variable DateTime value in SQLite database through C#?

Question

I'm very new to C# and SQLite database and have some variables which to be stored in SQLite database along with TimeStamp. Here is my code:

    DateTime now = DateTime.Now;
    m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
    m_dbConnection.Open();
    var sql = string.Format("insert into Table (Timestamp) values ({0})", now);
    SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
    command.ExecuteNonQuery();

But I'm getting error. I guess, SQLite doesn't support DateTime. I tried converting DateTime now to string but it still doesn't work.

    DateTime now1 = DateTime.Now;
    var now = now1.ToString("hh.mm.ss.fff");
    m_dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
    m_dbConnection.Open();
    var sql = string.Format("insert into Table (Timestamp) values ({0})", now);
    SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
    command.ExecuteNonQuery();

Am I doing something wrong?

Yes, you are doing something wrong. Use Parametrized queries and you'll avoid this kind of problems and many more. See [here](http://stackoverflow.com/q/809246/579895) for an example — Pikoh, Feb 28 '17 at 15:33

apomene · Answer 1 · 2017-03-01T08:39:02.250

2

Use parameters to avoid SQL injection:

var sql = string.Format("insert into Table (Timestamp) values (@now)", now);
SQLiteCommand command = new SQLiteCommand(sql, m_dbConnection);
command.Parameters.AddWithValue("@now",now);

edited Mar 01 '17 at 08:39

answered Feb 28 '17 at 15:34

apomene

14,282
9
46
72

3

@Polyfun although i agree that you should always be using parameters, I think editing other's answer is not the way to go. I'd rather make a comment suggesting him to change it – Pikoh Feb 28 '17 at 15:44
I'm getting following error because of the line `command.Parameters.Add("@now", now);` CS1503 Argument 2: cannot convert from 'string' to 'System.Data.DbType' – Keshav Mar 01 '17 at 08:37
@Keshav, Edited, try with AddWithValue – apomene Mar 01 '17 at 08:39
1

@apomene Yes, it worked. Could you tell me the difference as well? – Keshav Mar 02 '17 at 15:18
2

Add also expects the sql DBType provided as an argument – apomene Mar 02 '17 at 15:19

How to write variable DateTime value in SQLite database through C#?

1 Answers1

Linked