rw5Vm5CD

1using System.Data;
2using System.Data.SQLite;
3using System;
4
5namespace timelord
6{
7    /// <summary>
8    /// Manages the tasks database
9    /// </summary>
10    class Timesheet
11    {
12        private readonly string _filePath;
13        private const string createTaskTable = "CREATE TABLE IF NOT EXISTS task (id INTEGER PRIMARY KEY AUTOINCREMENT, description TEXT, begindate TEXT, enddate TEXT, status TEXT)";
14        private const string selectTaskQuery = "SELECT id,description,begindate,enddate,status FROM task";
15
16
17        public Timesheet(string filePath)
18        {
19            this._filePath = filePath;
20
21            ExecuteAction(c =>
22            {
23                CreateSchema(c);
24            });
25        }
26
27        private SQLiteConnection GetConnection()
28        {
29            return new SQLiteConnection("Data Source=" + this._filePath + ";Version=3;");
30        }
31
32        private void ExecuteAction(Action<SQLiteConnection> action)
33        {
34            using (SQLiteConnection c = GetConnection())
35            {
36                c.Open();
37
38                action(c);
39            }
40        }
41
42        private T ExecuteFunction<T>(Func<SQLiteConnection,T> function)
43        {
44            using (SQLiteConnection c = GetConnection())
45            {
46                c.Open();
47
48                return function(c);
49            }
50        }
51
52        private T ExecuteCommand<T>(Func<SQLiteDataAdapter, T> function)
53        {
54            return ExecuteFunction(c =>
55            {
56                using (SQLiteDataAdapter a = new SQLiteDataAdapter(selectTaskQuery, c))
57                {
58                    using (SQLiteCommandBuilder b = new SQLiteCommandBuilder(a))
59                    {
60                        a.UpdateCommand = b.GetUpdateCommand();
61                        a.DeleteCommand = b.GetDeleteCommand();
62                        a.InsertCommand = b.GetInsertCommand();
63                    }
64
65                    return function(a);
66                }
67            });
68        }
69
70        private void CreateSchema(SQLiteConnection c)
71        {
72            using (SQLiteCommand cmd = c.CreateCommand())
73            {
74                cmd.CommandText = createTaskTable;
75                cmd.ExecuteNonQuery();
76            }
77        }
78
79        public DataTable Tasks()
80        {
81            return ExecuteCommand(a =>
82            {
83                DataTable d = new DataTable("task");
84                a.Fill(d);
85                return d;
86            });
87        }
88
89        public int Update(DataTable d)
90        {
91            return ExecuteCommand(a =>{
92                return a.Update(d);
93            });
94        }
95
96    }
97}