T0320jZk

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	
128internal class Timesheet
129{
130    private const string SelectTaskQuery = "SELECT id, description, begindate, enddate, status FROM task";
131
132    private readonly string _filePath;
133
134    public Timesheet(string filePath)
135    {
136        _filePath = filePath;
137
138        InitializeDb();
139    }
140
141    private DataTable Tasks() => ExecuteWithConnection(Tasks);
142
143    private void InitializeDb() => ExecuteWithConnection(connection =>
144    {
145        using (var command = connection.CreateCommand())
146            CreateSchemaIfNotExists(command);
147    });
148
149    private SQLiteConnection GetConnection() => new SQLiteConnection("Data Source=" + _filePath + ";Version=3;");
150
151    private void ExecuteWithConnection(Action<SQLiteConnection> action)
152    {
153        using (var connection = GetConnection())
154        {
155            connection.Open();
156
157            action(connection);
158        }
159    }
160
161    private T ExecuteWithConnection<T>(Func<SQLiteConnection, T> action)
162    {
163        using (var connection = GetConnection())
164        {
165            connection.Open();
166
167            return action(connection);
168        }
169    }
170
171    private static DataTable Tasks(SQLiteConnection connection)
172    {
173        var adapter = new SQLiteDataAdapter(SelectTaskQuery, connection);
174
175        var datatable = new DataTable();
176
177        adapter.Fill(datatable);
178
179        return datatable;
180    }
181
182    public void Update(DataTable dataTable)
183    {
184        ExecuteWithConnection(connection =>
185        {
186            var adapter = new SQLiteDataAdapter(SelectTaskQuery, connection);
187
188            var builder = new SQLiteCommandBuilder(adapter);
189
190            adapter.Update(dataTable);
191        });
192    }
193
194    private static void CreateSchemaIfNotExists(IDbCommand command)
195    {
196        const string query = "CREATE TABLE IF NOT EXISTS task (id INTEGER PRIMARY KEY AUTOINCREMENT, description TEXT, begindate TEXT, enddate TEXT, status INTEGER default 0)";
197
198        command.CommandText = query;
199
200        command.ExecuteNonQuery();
201    }
202}
203	
204using (MySqlConnection con = new MySqlConnection(ConnectionString))
205 {
206   using(MysqlCommand cmd = new MySqlCommand(Command, con);   
207   {
208     //Add parameters and execute
209   }
210 }