· 9 years ago · Nov 29, 2016, 07:06 PM
1/*
2 * Created by SharpDevelop.
3 * User: A48248660
4 * Date: 28.11.2016
5 * Time: 08:54
6 *
7 * To change this template use Tools | Options | Coding | Edit Standard Headers.
8 */
9using System;
10using System.Collections.Generic;
11using System.Data.SQLite;
12
13namespace DBEngine
14{
15 /// <summary>
16 /// Description of cDbConnSqlite.
17 /// </summary>
18 public class cDbConnSqlite:cDbConn
19 {
20 SQLiteConnection _conn;
21
22 public cDbConnSqlite(string dbName)
23 {
24 _conn = new SQLiteConnection("Data Source=" + dbName + ".db;Version=3;");
25 }
26
27 public override void Connect()
28 {
29 _conn.Open();
30 }
31 public override void Disconnect()
32 {
33 _conn.Close();
34 }
35
36 public override bool TableExists(string tableName)
37 {
38 string query = "SELECT * FROM sqlite_master WHERE name ='" + tableName + "' and type='table';";
39
40 List<string[]> results = ExecuteQuery(query);
41
42 return results.Count > 0;
43 }
44 public override bool ColumnExistsInTable(string columnName, string tableName)
45 {
46 bool retVal = false;
47
48 if (TableExists(tableName))
49 {
50 string query = "PRAGMA table_info(" + tableName + ");";
51 List<string[]> results = ExecuteQuery(query);
52
53 for (int i = 0; i < results.Count-1; i++)
54 {
55 if (results[i][1].ToLower() == columnName.ToLower())
56 retVal = true;
57 }
58 }
59 return retVal;
60 }
61 public override void CreateTable(string tableName, string[] columns)
62 {
63 string command;
64
65 command = "CREATE TABLE IF NOT EXISTS ";
66 command += tableName + "(";
67
68 command += columns[0] + " INTEGER PRIMARY KEY, ";
69
70 for (int i = 1; i < columns.Length; i++)
71 {
72 command += columns[i] + ", ";
73 }
74
75 command = command.Remove(command.Length-2,2);
76 command += ")";
77
78 ExecuteNonQuery(command);
79
80 }
81 public override void ExecuteNonQuery(string command)
82 {
83 SQLiteCommand comm = new SQLiteCommand();
84 comm.Connection = _conn;
85 comm.CommandText = command;
86 comm.ExecuteNonQuery();
87 }
88 public override List<string[]> ExecuteQuery(string query)
89 {
90 SQLiteCommand comm = new SQLiteCommand();
91 comm.Connection = _conn;
92 comm.CommandText = query;
93 SQLiteDataReader results = comm.ExecuteReader();
94
95 return ConvertDataReaderToList(results);
96 }
97 public override void InsertInDB(string tableName, string[][] columnValues)
98 {
99 if (TableExists(tableName))
100 {
101 string command;
102 command = "INSERT INTO " + tableName + " (";
103
104 for (int i = 0; i < columnValues.Length; i++)
105 {
106 command += columnValues[i][0];
107 command += ", ";
108 }
109 command = command.Remove(command.Length-2,2);
110 command += ") VALUES(";
111
112 for (int i = 0; i < columnValues.Length; i++)
113 {
114 command += "'";
115 command += columnValues[i][1];
116 command += "', ";
117 }
118 command = command.Remove(command.Length-2,2);
119 command += ")";
120
121 ExecuteNonQuery(command);
122 }
123 }
124 public override List<string[]> SelectFromDB(string tableName, string[] columns, string[][] conditions)
125 {
126 if (!TableExists(tableName))
127 throw new ArgumentException(tableName + " nicht gefunden");
128 else
129 {
130 string query;
131 query = "SELECT ";
132
133 for (int i = 0; i < columns.Length; i++)
134 {
135 query += columns[i];
136 query += ", ";
137 }
138 query = query.Remove(query.Length-2,2);
139
140 query += " FROM " + tableName;
141
142 if (conditions.Length > 0)
143 {
144 query += " WHERE ";
145
146 for (int i = 0; i < conditions.Length; i++)
147 {
148 query += conditions[i][0];
149 query += " = ";
150 query += "'" + conditions[i][1] + "'";
151 query += " AND ";
152 }
153
154 query = query.Remove(query.Length-5,5);
155 }
156
157 return ExecuteQuery(query);
158 }
159
160 }
161 public override void UpdateInDB(string tableName, string[][] columnValues, string[][] conditions)
162 {
163 throw new NotImplementedException();
164 }
165
166 private List<string[]> ConvertDataReaderToList(SQLiteDataReader reader)
167 {
168 List<string[]> retVal = new List<string[]>();
169
170 if (reader.HasRows)
171 {
172 int rowCtr = 0;
173 while (reader.Read())
174 {
175 retVal.Add(new string[reader.FieldCount]);
176 for (int i = 0; i < reader.FieldCount; i++)
177 {
178 retVal[rowCtr][i] = Convert.ToString(reader.GetValue(i));
179 }
180
181 rowCtr++;
182 }
183 }
184
185 return retVal;
186 }
187 }
188}