internal class Timesheet
{
private const string SelectTaskQuery = "SELECT id, description, begindate, enddate, status FROM task";
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(tasksQuerySelectTaskQuery, connection);
var datatable = new DataTable();
adapter.Fill(datatable);
return datatable;
}
public void Update(DataTable dataTable)
{
ExecuteWithConnection(connection =>
{
var adapter = new SQLiteDataAdapter(SelectTaskQuery, connection.CreateCommand);
var builder = new SQLiteCommandBuilder(adapter);
adapter.UpdateCommand = builder.GetUpdateCommand();
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();
}
}
