source: Common/Database.cs

Last change on this file was 8, checked in by chronos, 6 years ago
  • Modified: Updated files.
  • Added: ExtTools class for external program and compare tool execution.
File size: 22.4 KB
Line 
1//#define SQLITE
2
3using System;
4using System.Collections.Generic;
5using System.ComponentModel;
6using System.Data;
7using System.Data.Common;
8using System.Data.SQLite;
9using System.Data.SqlClient;
10using System.Windows.Forms;
11using System.Diagnostics;
12using System.IO;
13using System.Threading;
14using System.Runtime.InteropServices;
15using System.Deployment.Application;
16using System.Text.RegularExpressions;
17using System.Xml;
18using System.Reflection;
19
20namespace Common
21{
22 enum DatabaseType { MsSql, SQLite };
23
24 class Database
25 {
26 public delegate void DatabaseInitializedHandler();
27 public static event DatabaseInitializedHandler DatabaseInitialized;
28 public delegate void LogMessageHandler(string text);
29 public static event LogMessageHandler LogMessage;
30 public static string databaseName = "";
31 public static string serverName = "";
32 public static string sqliteFileName;
33 private static Progress progressVar;
34 public static DatabaseType type = DatabaseType.MsSql;
35
36 public static bool InitDb()
37 {
38 bool result = true;
39
40 // Connect to server
41 if (type == DatabaseType.SQLite)
42 {
43 Directory.CreateDirectory(Path.GetDirectoryName(sqliteFileName));
44 databaseName = null;
45 }
46 using (DbConnection connection = GetConnection())
47 {
48 try
49 {
50 connection.Open();
51 connection.Close();
52 }
53 catch (DbException ex)
54 {
55 if ((ex is SqlException) && ((ex as SqlException).Number == 4060))
56 {
57 // database does not exist, try to create it
58 ExecuteNonQuery("CREATE DATABASE " + databaseName);
59
60 // Need to wait for the new DB startup in order to continue and be sure it can be accessed
61 if (connection is SqlConnection) SqlConnection.ClearAllPools();
62 }
63 else
64 {
65 result = false;
66 Log("When connecting to DB: " + ex.Message);
67 throw new Exception("When connecting to DB: " + ex.Message);
68 }
69 }
70
71 }
72 // Check if tables with settings exist, if not create them and fill them with default settings
73 if (result)
74 {
75 if (DatabaseInitialized != null)
76 DatabaseInitialized();
77 }
78
79 return result;
80 }
81
82 public static string GetConnectionString()
83 {
84 string connectionString = "";
85 if (type == DatabaseType.MsSql)
86 {
87 connectionString = "server=" + serverName + "; Trusted_Connection=yes; database=" + databaseName + "; connection timeout=5";
88 }
89 else
90 if (type == DatabaseType.SQLite)
91 {
92 connectionString = "Data Source=" + sqliteFileName + "; Version=3";
93 }
94 return connectionString;
95 }
96
97 private static void Log(string message)
98 {
99 if (LogMessage != null) LogMessage(message);
100 }
101
102 public static DbProviderFactory GetFactory()
103 {
104 DbProviderFactory factory = null;
105 if (type == DatabaseType.MsSql) factory = System.Data.SqlClient.SqlClientFactory.Instance;
106 else if (type == DatabaseType.SQLite) factory = System.Data.SQLite.SQLiteFactory.Instance;
107 return factory;
108 }
109
110 public static DbConnection GetConnection()
111 {
112 DbConnection connection = GetFactory().CreateConnection();
113 connection.ConnectionString = GetConnectionString();
114 return connection;
115 }
116
117 public static DbParameter CreateParameter()
118 {
119 return GetFactory().CreateParameter();
120 }
121
122 private static DbDataAdapter CreateDataAdapter(DbCommand cmd)
123 {
124 DbDataAdapter adapter;
125 adapter = GetFactory().CreateDataAdapter();
126 adapter.SelectCommand = cmd;
127 return adapter;
128 }
129
130 public static bool Connected()
131 {
132 using (DbConnection connection = GetConnection())
133 {
134 try
135 {
136 connection.Open();
137 connection.Close();
138 return true;
139 }
140 catch (DbException)
141 {
142 return false;
143 }
144 }
145 }
146
147 public static bool TableExists(string tableName)
148 {
149 bool result = false;
150 using (DbConnection connection = GetConnection())
151 {
152 connection.Open();
153 DataTable table = connection.GetSchema("Tables", new string[] { databaseName, null, tableName });
154 result = table.Rows.Count > 0;
155 }
156 return result;
157 }
158
159 public static bool TableColumnExists(string tableName, string columnName)
160 {
161 bool result = false;
162 using (DbConnection connection = GetConnection())
163 {
164 connection.Open();
165 DataTable table = connection.GetSchema("Columns", new string[] { databaseName, null, tableName, columnName});
166 result = table.Rows.Count > 0;
167 }
168 return result;
169 }
170
171 public static bool TableIndexExists(string tableName, string indexName)
172 {
173 bool result = false;
174 using (DbConnection connection = GetConnection())
175 {
176 connection.Open();
177 DataTable table = connection.GetSchema("Indexes", new string[] { databaseName, null, tableName });
178 foreach (DataRow row in table.Rows)
179 {
180 if (row.ItemArray[0].ToString() == indexName)
181 {
182 result = true;
183 break;
184 }
185 }
186 }
187 return result;
188 }
189
190 public static void TableDrop(string tableName)
191 {
192 if (TableExists(tableName)) ExecuteNonQuery("DROP TABLE " + tableName);
193 }
194
195 public static void CopyFile(string sourceFileName, string destFileName, Progress progress)
196 {
197 byte[] buffer = new byte[1024 * 1024]; // 1MB buffer
198
199 using (FileStream source = new FileStream(sourceFileName, FileMode.Open, FileAccess.Read))
200 {
201 progress.total = 1000;
202 using (FileStream dest = new FileStream(destFileName, FileMode.OpenOrCreate, FileAccess.Write))
203 {
204 long totalBytes = 0;
205 int currentBlockSize = 0;
206
207 while ((currentBlockSize = source.Read(buffer, 0, buffer.Length)) > 0)
208 {
209 totalBytes += currentBlockSize;
210 dest.Write(buffer, 0, currentBlockSize);
211 progress.current = (int)((float)totalBytes / source.Length * progress.total);
212 if (progress.terminated) break;
213 }
214 }
215 }
216 if (progress.terminated) File.Delete(destFileName);
217 }
218
219 public static void Backup(string backUpPath, Progress progress)
220 {
221 if (type == DatabaseType.MsSql)
222 {
223 progressVar = progress;
224 progressVar.total = 100;
225 using (DbConnection connection = GetConnection())
226 {
227 if (connection is SqlConnection)
228 {
229 (connection as SqlConnection).FireInfoMessageEventOnUserErrors = true;
230 (connection as SqlConnection).InfoMessage += OnInfoMessage;
231 }
232 connection.Open();
233
234 using (DbCommand cmd = connection.CreateCommand())
235 {
236 cmd.CommandText = @"BACKUP DATABASE [" + databaseName + "] TO DISK = N'" + backUpPath + @"' WITH FORMAT, NAME = N'Requirements Flowdown Backup', STATS = 1";
237 cmd.CommandTimeout = 5 * 60;
238 cmd.ExecuteNonQuery();
239 }
240 }
241 } else
242 if (type == DatabaseType.SQLite)
243 {
244 SQLiteConnection.ClearAllPools();
245 CopyFile(sqliteFileName, backUpPath, progress);
246 } else
247 throw new Exception("Operation not supported.");
248 }
249
250 public static void Restore(string backUpPath, Progress progress)
251 {
252 if (type == DatabaseType.MsSql)
253 {
254 progressVar = progress;
255 progressVar.total = 100;
256 using (DbConnection connection = GetConnection())
257 {
258 if (connection is SqlConnection)
259 {
260 (connection as SqlConnection).FireInfoMessageEventOnUserErrors = true;
261 (connection as SqlConnection).InfoMessage += OnInfoMessage;
262 }
263 connection.Open();
264
265 using (DbCommand cmd = connection.CreateCommand())
266 {
267 cmd.CommandText = "USE master";
268 cmd.ExecuteNonQuery();
269 }
270
271 using (DbCommand cmd = connection.CreateCommand())
272 {
273 cmd.CommandText = @"ALTER DATABASE [" + databaseName + "] SET Single_User WITH Rollback Immediate";
274 cmd.ExecuteNonQuery();
275 }
276
277 using (DbCommand cmd = connection.CreateCommand())
278 {
279 cmd.CommandText = @"RESTORE DATABASE [" + databaseName + "] FROM DISK = N'" + backUpPath + @"' WITH REPLACE, FILE = 1, NOUNLOAD, STATS = 1";
280 cmd.CommandTimeout = 5 * 60;
281 cmd.ExecuteNonQuery();
282 }
283
284 using (DbCommand cmd = connection.CreateCommand())
285 {
286 cmd.CommandText = @"ALTER DATABASE [" + databaseName + "] SET Multi_User";
287 cmd.ExecuteNonQuery();
288 }
289 }
290 } else
291 if (type == DatabaseType.SQLite)
292 {
293 SQLiteConnection.ClearAllPools();
294 CopyFile(backUpPath, sqliteFileName, progress);
295 } else
296 throw new Exception("Operation not supported.");
297
298 }
299
300 static private void OnInfoMessage(object sender, SqlInfoMessageEventArgs e)
301 {
302 foreach (SqlError error in e.Errors)
303 if ((error.Class > 10) && (error.Number != 3013))
304 {
305 Log(error.ToString());
306 throw new Exception(error.ToString());
307 }
308
309 Match match = Regex.Match(e.Message, "(\\d{1,3}) percent");
310 if (match.Success)
311 {
312 string[] split = Regex.Split(e.Message, "(\\d{1,3}) percent");
313 progressVar.current = Int32.Parse(split[1]);
314 }
315 }
316
317 public static DataTable ExecuteQuery(string query)
318 {
319 DataTable dbTable = null;
320 try
321 {
322 using (DbConnection connection = GetConnection())
323 using (DbCommand cmd = connection.CreateCommand())
324 {
325 connection.Open();
326 cmd.CommandText = query;
327 using (DbDataAdapter adapter = CreateDataAdapter(cmd))
328 {
329 dbTable = new DataTable();
330 adapter.Fill(dbTable);
331 }
332 }
333 }
334 catch (DbException ex)
335 {
336 Log("When executing query sql command:\n" + query + "\nException text: " + ex.Message);
337 throw new Exception("When executing query sql command:\n" + query + "\nException text: " + ex.Message);
338 }
339 return dbTable;
340 }
341
342 public static void ExecuteTransaction(Action<DbTransaction> callback)
343 {
344 try
345 {
346 using (DbConnection connection = GetConnection())
347 {
348 connection.Open();
349 using (var transaction = connection.BeginTransaction())
350 {
351 try
352 {
353 callback(transaction);
354 transaction.Commit();
355 }
356 catch (Exception ex)
357 {
358 transaction.Rollback();
359 throw new Exception("When executing sql transaction:\nException text: " + ex.Message);
360 }
361 }
362 }
363 }
364 catch (DbException ex)
365 {
366 Log("When executing sql transaction:\nException text: " + ex.Message);
367 throw new Exception("When executing sql transaction:\nException text: " + ex.Message);
368 }
369 }
370
371 public static void ExecuteTransactionCommand(DbTransaction transaction, string query, List<DbParameter> parameters = null )
372 {
373 using (DbCommand cmd = transaction.Connection.CreateCommand())
374 {
375 cmd.Transaction = transaction;
376 cmd.CommandText = query;
377 if (parameters != null)
378 {
379 cmd.Parameters.AddRange(parameters.ToArray());
380 }
381 cmd.ExecuteNonQuery();
382 }
383 }
384
385 public static int ExecuteScalar(string query, List<DbParameter> parameters = null)
386 {
387 int result = 0;
388 try
389 {
390 using (DbConnection connection = GetConnection())
391 using (DbCommand cmd = connection.CreateCommand())
392 {
393 cmd.CommandText = query;
394 if (parameters != null)
395 foreach (DbParameter parameter in parameters)
396 cmd.Parameters.Add(parameter);
397 connection.Open();
398 object resultObject = cmd.ExecuteScalar();
399 if (resultObject is Int64) result = (int)Convert.ToInt64(resultObject);
400 else result = (int)resultObject;
401 }
402 }
403 catch (DbException ex)
404 {
405 Log("When executing query sql command:\n" + query + "\nException text: " + ex.Message);
406 throw new Exception("When executing query sql command:\n" + query + "\nException text: " + ex.Message);
407 }
408 return result;
409 }
410
411 public static DbDataReader ExecuteQueryContent(string query)
412 {
413 DbDataReader dbReader = null;
414 try
415 {
416 using (DbConnection connection = GetConnection())
417 using (DbCommand cmd = connection.CreateCommand())
418 {
419 cmd.CommandText = query;
420 connection.Open();
421 dbReader = cmd.ExecuteReader();
422 }
423 }
424 catch (DbException ex)
425 {
426 Log("When executing query sql command:\n" + query + "\nException text: " + ex.Message);
427 throw new Exception("When executing query sql command:\n" + query + "\nException text: " + ex.Message);
428 }
429 return dbReader;
430 }
431
432 public static void ExecuteNonQuery(string query, DbParameter parameter)
433 {
434 ExecuteNonQuery(query, new List<DbParameter> { parameter });
435 }
436
437 public static void ExecuteNonQuery(string query, List<DbParameter> parameters = null)
438 {
439 try
440 {
441 using (DbConnection connection = GetConnection())
442 using (DbCommand cmd = connection.CreateCommand())
443 {
444 cmd.CommandText = query;
445 connection.Open();
446 if (parameters != null)
447 foreach (DbParameter param in parameters)
448 cmd.Parameters.Add(param);
449 int rowsAffected = cmd.ExecuteNonQuery();
450 }
451 }
452 catch (DbException ex)
453 {
454 Log("When executing non query sql command:\n" + query + "\nException text: " + ex.Message);
455 throw new Exception("When executing non query sql command:\n" + query + "\nException text: " + ex.Message);
456 }
457 }
458
459 public static int Insert(string tableName, Dictionary<string, string> values, List<DbParameter> parameters = null)
460 {
461 string columnsText = '"' + string.Join("\",\"", values.Keys) + '"';
462 string valuesText = string.Join(",", values.Values);
463 int result = -1;
464 if (type == DatabaseType.MsSql)
465 result = ExecuteScalar("INSERT INTO " + tableName + " (" + columnsText + ") OUTPUT INSERTED.ID VALUES (" + valuesText + ")", parameters);
466 if (type == DatabaseType.SQLite)
467 result = ExecuteScalar("INSERT INTO " + tableName + " (" + columnsText + ") VALUES (" + valuesText + "); SELECT last_insert_rowid()", parameters);
468 return result;
469 }
470
471 public static void Update(string tableName, string where, Dictionary<string, string> values, List<DbParameter> parameters = null)
472 {
473 string valuesText = "";
474 foreach (var value in values)
475 {
476 if (valuesText != "") valuesText += ", ";
477 valuesText += value.Key + '=' + value.Value;
478 }
479 ExecuteNonQuery("UPDATE " + tableName + " SET " + valuesText + " WHERE " + where, parameters);
480 }
481
482 public static string GetPrimaryKeyText()
483 {
484 string result = "";
485 if (type == DatabaseType.MsSql) result = "int IDENTITY(1, 1) NOT NULL";
486 else if (type == DatabaseType.SQLite) result = "INTEGER NOT NULL";
487 return result;
488 }
489
490 public static string GetVarCharMax()
491 {
492 string result = "";
493 if (type == DatabaseType.MsSql) result = "max";
494 else if (type == DatabaseType.SQLite) result = "2147483647";
495 return result;
496 }
497
498 public static string GetTopLimit(int limit)
499 {
500 string result = "";
501 if (type == DatabaseType.MsSql) result = "TOP " + limit.ToString();
502 else if (type == DatabaseType.SQLite) result = "";
503 return result;
504 }
505
506 public static string GetEndLimit(int limit)
507 {
508 string result = "";
509 if (type == DatabaseType.MsSql) result = "";
510 else if (type == DatabaseType.SQLite) result = " LIMIT " + limit.ToString();
511 return result;
512 }
513
514 public static void CreateIndexOnColumn(string table, string column, string indexName = null)
515 {
516 if (indexName == null) indexName = table + "_" + column;
517 ExecuteNonQuery("CREATE INDEX " + indexName + " ON " + table + " (" + column + ")");
518 }
519 }
520
521 public class Query
522 {
523 public string query;
524 public List<DbParameter> parameters;
525
526 public Query(string query, List<DbParameter> data)
527 {
528 this.query = query;
529 this.parameters = data;
530 }
531 }
532
533 public class DatabaseTransaction
534 {
535 public List<Query> queries = new List<Query>();
536 private List<Query> threadQueries = new List<Query>();
537 int queriesPerTransaction = 1000;
538 Thread dbThread;
539 bool threadTerminated;
540
541 public void ExecuteNonQueryNonBlocking(string query)
542 {
543 ExecuteNonQueryNonBlocking(query, null);
544 }
545
546 public void ExecuteNonQueryNonBlocking(string query, List<DbParameter> parameters = null)
547 {
548 queries.Add(new Query(query, parameters));
549 if (queries.Count > queriesPerTransaction)
550 {
551 ExecuteQueries();
552 }
553 }
554
555 public void ExecuteQueries()
556 {
557#if NO_TRANSACTION
558 foreach (Query item in queries)
559 {
560 Database.ExecuteNonQuery(item.query, item.parameters);
561 }
562#else
563 lock (threadQueries)
564 {
565 threadQueries.AddRange(queries);
566 Monitor.Pulse(threadQueries);
567 }
568#endif
569 queries.Clear();
570 }
571
572 public void ThreadStart()
573 {
574 threadTerminated = false;
575 dbThread = new Thread(y =>
576 {
577 List<Query> localQueries;
578 while (!threadTerminated)
579 {
580 lock (threadQueries)
581 {
582 Monitor.Wait(threadQueries);
583 localQueries = new List<Query>(threadQueries);
584 threadQueries.Clear();
585 }
586 Database.ExecuteTransaction(x =>
587 {
588 foreach (Query item in localQueries)
589 {
590 Database.ExecuteTransactionCommand(x, item.query, item.parameters);
591 }
592 });
593 }
594 });
595 dbThread.Start();
596 }
597
598 public void ThreadStop()
599 {
600 // Finish SQL insert thread
601 threadTerminated = true;
602 lock (threadQueries)
603 {
604 Monitor.Pulse(threadQueries);
605 }
606 dbThread.Join();
607 }
608 }
609}
Note: See TracBrowser for help on using the repository browser.