Skip to content

Commit 45eb488

Browse files
author
Jayson Ragasa
committed
refactored Database project
1 parent 701d259 commit 45eb488

File tree

6 files changed

+185
-253
lines changed

6 files changed

+185
-253
lines changed

‎Database/Controllers/Database.cs

Lines changed: 79 additions & 140 deletions
Original file line numberDiff line numberDiff line change
@@ -5,119 +5,99 @@
55

66
namespace Database
77
{
8-
public class Database
8+
public class SQLiteDataSource
99
{
1010
string _connectionString = string.Empty;
11-
string db_name = string.Empty;
11+
string _db_name = string.Empty;
1212

13-
SQLiteConnection _connection = new SQLiteConnection();
14-
SQLiteCommand _command = new SQLiteCommand();
13+
public SQLiteConnection Connection { get; set; } = new SQLiteConnection();
14+
public SQLiteCommand Command { get; set; } = new SQLiteCommand();
15+
public SQLiteDataReader Reader { get; set; } = null;
1516

16-
public Database()
17+
static Lazy<SQLiteDataSource> _database = new Lazy<SQLiteDataSource>(() =>
1718
{
18-
db_name = ConfigurationManager.AppSettings["DatbaseFilepath"].ToString();
19-
db_name = System.IO.Path.Combine(System.Windows.Forms.Application.StartupPath, db_name);
19+
var db = new SQLiteDataSource();
20+
db.Initialize();
2021

21-
this._connectionString = ConfigurationManager.AppSettings["connection"].ToString();
22-
this._connectionString = string.Format(_connectionString, db_name);
22+
return db;
23+
});
2324

24-
this._connection.ConnectionString = this._connectionString;
25+
public static SQLiteDataSource I => _database.Value;
2526

26-
//if (!System.IO.File.Exists(db_name))
27-
//{
28-
// SQLiteConnection.CreateFile(db_name);
29-
// _connection.Open();
30-
// _connection.Close();
31-
//}
32-
}
33-
34-
public void Delete(bool all)
27+
public void Initialize()
3528
{
36-
string dbpath = System.IO.Path.GetDirectoryName(db_name);
37-
try
38-
{
39-
foreach (string f in System.IO.Directory.GetFiles(dbpath))
40-
{
41-
if (!all)
42-
{
43-
if (f.ToLower() != this.db_name.ToLower())
44-
{
45-
System.IO.File.Delete(f);
46-
}
47-
}
48-
else
49-
{
50-
System.IO.File.Delete(f);
51-
}
52-
}
53-
}
54-
catch (Exception ex)
55-
{
56-
System.Diagnostics.Debug.WriteLine(ex.Message);
57-
}
29+
_db_name = ConfigurationManager.AppSettings["DatbaseFilepath"].ToString();
30+
_db_name = System.IO.Path.Combine(System.Windows.Forms.Application.StartupPath, _db_name);
31+
32+
_connectionString = ConfigurationManager.AppSettings["connection"].ToString();
33+
_connectionString = string.Format(_connectionString, _db_name);
34+
35+
this.Connection.ConnectionString = _connectionString;
5836
}
5937

60-
public void ResetDatabase()
38+
public void CloseConnection()
6139
{
62-
Delete(true);
63-
64-
System.Diagnostics.Debug.WriteLine("reseting database");
65-
ExecuteNonQuery(DefaultDataAndSchema.sql, null);
66-
System.Diagnostics.Debug.WriteLine("done");
40+
this.Reader.Close();
41+
this.Command.Dispose();
42+
this.Connection.Close();
6743
}
44+
}
6845

69-
public string ExecuteQuery(string sql_query, SQLiteParameter[] parameters, out SQLiteDataReader reader)
46+
public class SQLiteDataController
47+
{
48+
public SQLiteDataSource Database => SQLiteDataSource.I;
49+
50+
//public void Delete(bool all)
51+
//{
52+
// string dbpath = System.IO.Path.GetDirectoryName(db_name);
53+
// try
54+
// {
55+
// foreach (string f in System.IO.Directory.GetFiles(dbpath))
56+
// {
57+
// if (!all)
58+
// {
59+
// if (f.ToLower() != this.db_name.ToLower())
60+
// {
61+
// System.IO.File.Delete(f);
62+
// }
63+
// }
64+
// else
65+
// {
66+
// System.IO.File.Delete(f);
67+
// }
68+
// }
69+
// }
70+
// catch (Exception ex)
71+
// {
72+
// System.Diagnostics.Debug.WriteLine(ex.Message);
73+
// }
74+
//}
75+
76+
//public void ResetDatabase()
77+
//{
78+
// Delete(true);
79+
80+
// System.Diagnostics.Debug.WriteLine("reseting database");
81+
// ExecuteNonQuery(DefaultDataAndSchema.sql, null);
82+
// System.Diagnostics.Debug.WriteLine("done");
83+
//}
84+
85+
public string ExecuteQuery(string sql_query, SQLiteParameter[] parameters)
7086
{
7187
string res = string.Empty;
7288

73-
reader = null;
74-
75-
//using(SQLiteConnection conn = new SQLiteConnection(this._connectionString))
76-
//{
77-
// conn.Open();
78-
// using (SQLiteCommand comm = conn.CreateCommand())
79-
// {
80-
// comm.CommandText = sql_query;
81-
82-
// if(parameters != null)
83-
// {
84-
// comm.Parameters.AddRange(parameters);
85-
// }
86-
87-
// try
88-
// {
89-
// this._command = comm;
90-
91-
// reader = this._command.ExecuteReader(CommandBehavior.CloseConnection);
92-
// }
93-
// catch (Exception ex)
94-
// {
95-
// res = CreateExceptionMessage(ex);
96-
// }
97-
// }
98-
//}
99-
100-
reader = null;
101-
102-
//if (_connection.State == ConnectionState.Open)
103-
//{
104-
// CloseConnection();
105-
//}
106-
107-
_connection = new SQLiteConnection(this._connectionString);
108-
_connection.Open();
109-
_command = _connection.CreateCommand();
110-
_command.CommandText = sql_query;
89+
this.Database.Connection.Open();
90+
91+
this.Database.Command = this.Database.Connection.CreateCommand();
92+
this.Database.Command.CommandText = sql_query;
93+
this.Database.Command.Parameters.Clear();
11194

11295
if (parameters != null)
113-
{
114-
_command.Parameters.Clear();
115-
_command.Parameters.AddRange(parameters);
116-
}
96+
this.Database.Command.Parameters.AddRange(parameters);
11797

11898
try
11999
{
120-
reader = _command.ExecuteReader(CommandBehavior.CloseConnection);
100+
this.Database.Reader = this.Database.Command.ExecuteReader(CommandBehavior.CloseConnection);
121101
}
122102
catch (Exception ex)
123103
{
@@ -131,71 +111,30 @@ public string ExecuteNonQuery(string sql_query, SQLiteParameter[] parameters)
131111
{
132112
string res = string.Empty;
133113

134-
//using (SQLiteConnection conn = new SQLiteConnection(this._connectionString))
135-
//{
136-
// conn.Open();
137-
// using (SQLiteCommand comm = conn.CreateCommand())
138-
// {
139-
// comm.CommandText = sql_query;
140-
141-
// if (parameters != null)
142-
// {
143-
// comm.Parameters.AddRange(parameters);
144-
// }
145-
146-
// try
147-
// {
148-
// comm.ExecuteNonQuery();
149-
// }
150-
// catch (Exception ex)
151-
// {
152-
// res = CreateExceptionMessage(ex);
153-
// }
154-
// }
155-
//}
156-
157-
//if (_connection.State == ConnectionState.Open)
158-
//{
159-
// CloseConnection();
160-
//}
161-
162-
_connection = new SQLiteConnection(this._connectionString);
163-
_connection.Open();
164-
_command = _connection.CreateCommand();
165-
_command.CommandText = sql_query;
114+
this.Database.Connection.Open();
115+
116+
this.Database.Command = this.Database.Connection.CreateCommand();
117+
this.Database.Command.CommandText = sql_query;
118+
this.Database.Command.Parameters.Clear();
166119

167120
if (parameters != null)
168-
{
169-
_command.Parameters.AddRange(parameters);
170-
}
121+
this.Database.Command.Parameters.AddRange(parameters);
171122

172123
try
173124
{
174-
_command.ExecuteNonQuery();
125+
this.Database.Command.ExecuteNonQuery();
175126
}
176127
catch (Exception ex)
177128
{
178129
System.Diagnostics.Debug.WriteLine("ERROR! " + ex.Message + "\r\n" + ex.StackTrace);
179130
res = CreateExceptionMessage(ex);
180131
}
181132

182-
_connection.Close();
133+
//this.Database.CloseConnection();
183134

184135
return res;
185136
}
186137

187-
public void CloseConnection()
188-
{
189-
_command.Dispose();
190-
191-
if (_connection != null)
192-
{
193-
_connection.Close();
194-
_connection.Dispose();
195-
_connection = null;
196-
}
197-
}
198-
199138
private string CreateExceptionMessage(Exception ex)
200139
{
201140
string err = string.Empty;

0 commit comments

Comments
 (0)