bU36udqD

1public static void CreateTables() // Create tables if they don't exist.
2        {
3            if (!OpenConnection())
4                return;
5
6            String query = String.Format("CREATE TABLE IF NOT EXISTS SerialNumbers (SerialNumber VARCHAR(30), ID INTEGER)");
7
8            sqCommand = sqConnection.CreateCommand();
9            sqCommand.CommandText = query;
10            sqCommand.ExecuteNonQuery();
11
12            query = String.Format("CREATE TABLE IF NOT EXISTS ESSInfo (ProductType VARCHAR(30), maxTemp INTEGER, minTemp INTEGER, cycles INTEGER, stayTime INTEGER, ID INTEGER PRIMARY KEY AUTOINCREMENT)");
13
14            sqCommand = sqConnection.CreateCommand();
15            sqCommand.CommandText = query;
16            sqCommand.ExecuteNonQuery();
17
18            query = String.Format("CREATE TABLE IF NOT EXISTS Temperatures (Temp VARCHAR(50), date VARCHAR(40), ID INTEGER, uniqueID INTEGER PRIMARY KEY AUTOINCREMENT)");
19
20            sqCommand = sqConnection.CreateCommand();
21            sqCommand.CommandText = query;
22            sqCommand.ExecuteNonQuery();
23
24            query = String.Format("CREATE TABLE IF NOT EXISTS InternalInfo (filePath VARCHAR(100), ID INTEGER)");
25
26            sqCommand = sqConnection.CreateCommand();
27            sqCommand.CommandText = query;
28            sqCommand.ExecuteNonQuery();
29
30            string sql = String.Format("SELECT * FROM InternalInfo WHERE ID = '{0}'", 1);
31            SQLiteCommand command = new SQLiteCommand(sql, sqConnection);
32            SQLiteDataReader reader = command.ExecuteReader();
33
34            if (reader.Read())
35            {
36                savedFilePath = reader["filePath"].ToString();
37            }
38            else
39            {
40                savedFilePath = @"R:\Analog Links\ESS Reports\";
41
42                sql = String.Format("INSERT INTO InternalInfo (filePath, ID) values ('{0}', '{1}')", "R:\\Analog Links\\ESS Reports\\", 1);
43                command = new SQLiteCommand(sql, sqConnection);
44                command.ExecuteNonQuery();
45            }
46
47            reader.Close();
48
49            CloseConnection();
50        }
51
52
53
54
55
56
57
58
59
60public static void InitESSDatabase() // Initialize database when the app opens. This is called on MainWindow's constructor.
61        {
62            CreateDBFile();
63            CreateTables();
64        }
65
66
67
68
69
70
71private static String dbPath = System.Environment.CurrentDirectory;
72private static String dbFilePath = dbPath + "\\ESS Database.db";
73
74
75
76
77
78public static void CreateDBFile() // Create database file if it doesn't exist.
79        {
80            if (!String.IsNullOrEmpty(dbPath) && !Directory.Exists(dbPath))
81            {
82                Directory.CreateDirectory(dbPath);
83            }
84
85            dbFilePath = dbPath + "\\ESS Database.db";
86
87            if (!File.Exists(dbFilePath))
88            {
89                SQLiteConnection.CreateFile(dbFilePath);
90            }
91        }
92
93
94
95
96
97
98
99
100
101
102
103
104public static bool OpenConnection() // Open connection to the database until explicitly closed.
105        {
106            bool openedSuccessfully = true;
107            String dbConn = string.Format("Data Source={0};", dbFilePath);
108
109            sqConnection = new SQLiteConnection(dbConn);
110
111            try
112            {
113                sqConnection.Open();
114            }
115            catch (Exception ex)
116            {
117                openedSuccessfully = false;
118                MessageBox.Show(ex.Message.ToString());
119            }
120
121            return openedSuccessfully;
122        }
123
124
125
126
127
128
129
130
131
132
133
134public static void CloseConnection() // Close connection to the database.
135        {
136            sqConnection.Close();
137            GC.Collect();
138        }
139
140
141
142
143
144
145
146
147
148
149
150
151
152public static void AddTemperature(double temp, long ID) // Add temperature sample to database. One sample at a time.
153        {
154            if (!OpenConnection())
155                return;
156
157            String sql = String.Format("INSERT INTO Temperatures (Temp, date, ID) values ('{0}', '{1}', '{2}')", temp.ToString(), DateTime.Now.ToString("g"), (int)ID);
158            SQLiteCommand command = new SQLiteCommand(sql, sqConnection);
159            command.ExecuteNonQuery();
160
161            CloseConnection();
162        }
163
164
165
166
167
168
169
170
171
172
173
174
175
176        public static void AddSerialNumber(String sn, long ID) // Add serial number to database. One serial number at a time.
177        {
178            if (!OpenConnection())
179                return;
180
181            String sql = String.Format("INSERT INTO SerialNumbers (SerialNumber, ID) values ('{0}', '{1}')", sn, (int)ID);
182            SQLiteCommand command = new SQLiteCommand(sql, sqConnection);
183            command.ExecuteNonQuery();
184
185            CloseConnection();
186        }
187
188
189
190
191
192
193
194
195public static void updateSavePath(string newPath)
196        {
197            if (!OpenConnection())
198            {
199                MessageBox.Show("Error: The database could not be accessed.");
200                return;
201            }
202
203            string sql = String.Format("UPDATE 'InternalInfo' SET filePath = '{0}' WHERE ID = {1}", @newPath, 1);
204            SQLiteCommand command = new SQLiteCommand(sql, sqConnection);
205            SQLiteDataReader reader = command.ExecuteReader();
206
207            CloseConnection();
208        }
209
210
211
212
213
214
215
216
217
218
219