w5rgL5Tk

1using System.Data;
2using System.Data.SQLite;
3using System.IO;
4using System.Windows.Forms;
5
6namespace timelord
7{
8    /// <summary>
9    /// Manages the tasks database
10    /// </summary>
11    class Timesheet
12    {
13        private string filePath;
14        private SQLiteConnection sqlite;
15        private SQLiteDataAdapter adapter;
16        private SQLiteCommandBuilder builder;
17        private DataTable datatable;
18
19        /// <summary>
20        /// Creates a timesheet object that determines if the filepath exists.
21        /// If the filepath exists then it opens a database.
22        /// If it does not exist it creates a database.
23        /// </summary>
24        /// <param name="filePath">The path to open or create a database file at.</param>
25        public Timesheet(string filePath)
26        {
27            this.filePath = filePath;
28
29            if (File.Exists(filePath))
30            {
31                openDatabase();
32
33            }else
34            {
35                createDatabase();
36
37                createSchema();
38            }
39
40            prepareQueries();
41
42            this.datatable = new DataTable();
43
44            this.adapter.Fill(this.datatable);
45        }
46
47        /// <summary>
48        /// Calls the methods that create the database file, connection, and schema
49        /// </summary>
50        private void createDatabase()
51        {
52            SQLiteConnection.CreateFile(filePath);
53
54            openDatabase();
55        }
56
57        /// <summary>
58        /// Creates the database connection
59        /// </summary>
60        private void openDatabase()
61        {
62            sqlite = new SQLiteConnection("Data Source=" + this.filePath + ";Version=3;");
63            try
64            {
65                sqlite.Open();
66            }
67            catch(SQLiteException e)
68            {
69                MessageBox.Show(e.Message);
70            }
71        }
72
73        /// <summary>
74        /// Creates the schema for all tables nessicary
75        /// </summary>
76        private void createSchema()
77        {
78            string query = "CREATE TABLE task (id INTEGER PRIMARY KEY AUTOINCREMENT, description TEXT, begindate TEXT, enddate TEXT, status INTEGER default 0)";
79
80            SQLiteCommand cmd = new SQLiteCommand(query, sqlite);
81
82            cmd.ExecuteNonQuery();
83
84        }
85
86        /// <summary>
87        /// Prepares the queries used to manipulate the timesheet
88        /// </summary>
89        private void prepareQueries()
90        {
91            adapter = new SQLiteDataAdapter("select id,description,begindate,enddate,status from task", sqlite);
92
93            builder = new SQLiteCommandBuilder(adapter);
94
95            adapter.UpdateCommand = builder.GetUpdateCommand();
96            adapter.DeleteCommand = builder.GetDeleteCommand();
97            adapter.InsertCommand = builder.GetInsertCommand();
98        }
99
100        /// <summary>
101        /// Return the task table
102        /// </summary>
103        /// <returns></returns>
104        public DataTable Tasks()
105        {
106            return this.datatable;
107        }
108
109        public void Update()
110        {
111            adapter.Update(datatable);
112
113            datatable.Clear();
114
115            adapter.Fill(datatable);
116        }
117
118        /// <summary>
119        /// Closes the database connection
120        /// </summary>
121        public void close()
122        {
123            sqlite.Close();
124        }
125    }
126}
127	
128using (MySqlConnection con = new MySqlConnection(ConnectionString))
129 {
130   using(MysqlCommand cmd = new MySqlCommand(Command, con);   
131   {
132     //Add parameters and execute
133   }
134 }
135	
136internal class Timesheet
137{
138    private const string SelectTaskQuery = "SELECT id, description, begindate, enddate, status FROM task";
139
140    private readonly string _filePath;
141
142    public Timesheet(string filePath)
143    {
144        _filePath = filePath;
145
146        InitializeDb();
147    }
148
149    private DataTable Tasks() => ExecuteWithConnection(Tasks);
150
151    private void InitializeDb() => ExecuteWithConnection(connection =>
152    {
153        using (var command = connection.CreateCommand())
154            CreateSchemaIfNotExists(command);
155    });
156
157    private SQLiteConnection GetConnection() => new SQLiteConnection("Data Source=" + _filePath + ";Version=3;");
158
159    private void ExecuteWithConnection(Action<SQLiteConnection> action)
160    {
161        using (var connection = GetConnection())
162        {
163            connection.Open();
164
165            action(connection);
166        }
167    }
168
169    private T ExecuteWithConnection<T>(Func<SQLiteConnection, T> action)
170    {
171        using (var connection = GetConnection())
172            return action(connection);
173    }
174
175    private static DataTable Tasks(SQLiteConnection connection)
176    {
177        var adapter = new SQLiteDataAdapter(SelectTaskQuery, connection);
178
179        var datatable = new DataTable();
180
181        adapter.Fill(datatable);
182
183        return datatable;
184    }
185
186    public void Update(DataTable dataTable)
187    {
188        ExecuteWithConnection(connection =>
189        {
190            var adapter = new SQLiteDataAdapter(SelectTaskQuery, connection);
191
192            var builder = new SQLiteCommandBuilder(adapter);
193
194            adapter.UpdateCommand = builder.GetUpdateCommand();
195
196            adapter.Update(dataTable);
197        });
198    }
199
200    private static void CreateSchemaIfNotExists(IDbCommand command)
201    {
202        const string query = "CREATE TABLE IF NOT EXISTS task (id INTEGER PRIMARY KEY AUTOINCREMENT, description TEXT, begindate TEXT, enddate TEXT, status INTEGER default 0)";
203
204        command.CommandText = query;
205
206        command.ExecuteNonQuery();
207    }
208}