Return to Revisions

You don't need to create the database file if it doesn't exist, the SQLite driver will take care of it; to ensure your table does not exist on creation, you can just add IF NOT EXISTS to the query.

I would also close the connection every time, this has already been covered by other answers. In addition I would make the class stateless as it doesn't need a state if we open/close the connection every time (Being stateless brings a lot of other advantages).

I would also remove any logic from the constructor, to do this you will need a state or you will have to check on every command unfortunately so I left it to you.

Here is my 0.02:

internal class Timesheet
{
    private readonly string _filePath;

    public Timesheet(string filePath)
    {
        _filePath = filePath;

        InitializeDb();
    }

    private DataTable Tasks() => ExecuteWithConnection(Tasks);

    private void InitializeDb() => ExecuteWithConnection(connection =>
    {
        using (var command = connection.CreateCommand())
            CreateSchemaIfNotExists(command);
    });

    private SQLiteConnection GetConnection() => new SQLiteConnection("Data Source=" + _filePath + ";Version=3;");

    private void ExecuteWithConnection(Action<SQLiteConnection> action)
    {
        using (var connection = GetConnection())
        {
            connection.Open();

            action(connection);
        }
    }

    private T ExecuteWithConnection<T>(Func<SQLiteConnection, T> action)
    {
        using (var connection = GetConnection())
            return action(connection);
    }

    private static DataTable Tasks(SQLiteConnection connection)
    {
        const string tasksQuery = "SELECT id, description, begindate, enddate, status FROM task";

        var adapter = new SQLiteDataAdapter(tasksQuery, connection);

        var datatable = new DataTable();

        adapter.Fill(datatable);

        return datatable;
    }

    public void Update(DataTable dataTable)
    {
        ExecuteWithConnection(connection =>
        {
            var adapter = new SQLiteDataAdapter(connection.CreateCommand());

            adapter.Update(dataTable);
        });
    }

    private static void CreateSchemaIfNotExists(IDbCommand command)
    {
        const string query = "CREATE TABLE IF NOT EXISTS task (id INTEGER PRIMARY KEY AUTOINCREMENT, description TEXT, begindate TEXT, enddate TEXT, status INTEGER default 0)";

        command.CommandText = query;

        command.ExecuteNonQuery();
    }
}

I would also add a small ORM like Dapper and add statically typed entities for insert/update.