I created .Net C# software using SQLite3.0 database. In the software, data is inserted almost per minute, every day. But very rare times (about once in a month), it loses its data like it never was.
My all tables have identity columns. To check the loss, I writes inserted time of new row in every table. When the loss occurred, I observed rows of an hour was lost. But the identity values were not skipped and continued finely. I checked my transactions but they were fine.
There is one connection created and opened when the program started. And that connection is used whole runtime without close and reopen. At runtime, there are many db actions such as insert, update, delete, select.
Can it be a reason for the loss?
Should I open and close a connection for every db actions?