|
| 1 | +using System; |
| 2 | +using System.Data; |
| 3 | +using System.Collections; |
| 4 | + |
| 5 | +namespace System.Data.SQLite |
| 6 | +{ |
| 7 | + using sqlite = Sqlite3.sqlite3; |
| 8 | + using Vdbe = Sqlite3.Vdbe; |
| 9 | + |
| 10 | + /// <summary> |
| 11 | + /// C#-SQLite wrapper with functions for opening, closing and executing queries. |
| 12 | + /// </summary> |
| 13 | + public class SQLiteDatabase |
| 14 | + { |
| 15 | + // pointer to database |
| 16 | + private sqlite db; |
| 17 | + |
| 18 | + /// <summary> |
| 19 | + /// Creates new instance of SQLiteBase class with no database attached. |
| 20 | + /// </summary> |
| 21 | + public SQLiteDatabase() |
| 22 | + { |
| 23 | + db = null; |
| 24 | + } |
| 25 | + |
| 26 | + /// <summary> |
| 27 | + /// Creates new instance of SQLiteDatabase class and opens database with given name. |
| 28 | + /// </summary> |
| 29 | + /// <param name="DatabaseName">Name (and path) to SQLite database file</param> |
| 30 | + public SQLiteDatabase(String DatabaseName) |
| 31 | + { |
| 32 | + OpenDatabase(DatabaseName); |
| 33 | + } |
| 34 | + |
| 35 | + /// <summary> |
| 36 | + /// Opens database. |
| 37 | + /// </summary> |
| 38 | + /// <param name="DatabaseName">Name of database file</param> |
| 39 | + public void OpenDatabase(String DatabaseName) |
| 40 | + { |
| 41 | + // opens database |
| 42 | + if( |
| 43 | +#if NET_35 |
| 44 | + Sqlite3.Open |
| 45 | +#else |
| 46 | +Sqlite3.sqlite3_open |
| 47 | +#endif |
| 48 | +(DatabaseName, out db) != Sqlite3.SQLITE_OK) |
| 49 | + { |
| 50 | + // if there is some error, database pointer is set to 0 and exception is throws |
| 51 | + db = null; |
| 52 | + throw new Exception("Error with opening database " + DatabaseName + "!"); |
| 53 | + } |
| 54 | + } |
| 55 | + |
| 56 | + /// <summary> |
| 57 | + /// Closes opened database. |
| 58 | + /// </summary> |
| 59 | + public void CloseDatabase() |
| 60 | + { |
| 61 | + // closes the database if there is one opened |
| 62 | + if(db != null) |
| 63 | + { |
| 64 | +#if NET_35 |
| 65 | + Sqlite3.Close |
| 66 | +#else |
| 67 | + Sqlite3.sqlite3_close |
| 68 | +#endif |
| 69 | +(db); |
| 70 | + } |
| 71 | + } |
| 72 | + |
| 73 | + /// <summary> |
| 74 | + /// Returns connection |
| 75 | + /// </summary> |
| 76 | + public sqlite Connection() |
| 77 | + { |
| 78 | + return db; |
| 79 | + } |
| 80 | + |
| 81 | + /// <summary> |
| 82 | + /// Returns the list of tables in opened database. |
| 83 | + /// </summary> |
| 84 | + /// <returns></returns> |
| 85 | + public ArrayList GetTables() |
| 86 | + { |
| 87 | + // executes query that select names of all tables in master table of the database |
| 88 | + String query = "SELECT name FROM sqlite_master " + |
| 89 | + "WHERE type = 'table'" + |
| 90 | + "ORDER BY 1"; |
| 91 | + DataTable table = ExecuteQuery(query); |
| 92 | + |
| 93 | + // Return all table names in the ArrayList |
| 94 | + ArrayList list = new ArrayList(); |
| 95 | + foreach(DataRow row in table.Rows) |
| 96 | + { |
| 97 | + list.Add(row.ItemArray[0].ToString()); |
| 98 | + } |
| 99 | + return list; |
| 100 | + } |
| 101 | + |
| 102 | + /// <summary> |
| 103 | + /// Executes query that does not return anything (e.g. UPDATE, INSERT, DELETE). |
| 104 | + /// </summary> |
| 105 | + /// <param name="query"></param> |
| 106 | + public void ExecuteNonQuery(String query) |
| 107 | + { |
| 108 | + // calles SQLite function that executes non-query |
| 109 | + Sqlite3.exec(db, query, 0, 0, 0); |
| 110 | + // if there is error, excetion is thrown |
| 111 | + if(db.errCode != Sqlite3.SQLITE_OK) |
| 112 | + throw new Exception("Error with executing non-query: \"" + query + "\"!\n" + |
| 113 | +#if NET_35 |
| 114 | + Sqlite3.Errmsg |
| 115 | +#else |
| 116 | + Sqlite3.sqlite3_errmsg |
| 117 | +#endif |
| 118 | +(db)); |
| 119 | + } |
| 120 | + |
| 121 | + /// <summary> |
| 122 | + /// Executes query that does return something (e.g. SELECT). |
| 123 | + /// </summary> |
| 124 | + /// <param name="query"></param> |
| 125 | + /// <returns></returns> |
| 126 | + public DataTable ExecuteQuery(String query) |
| 127 | + { |
| 128 | + // compiled query |
| 129 | + SQLiteVdbe statement = new SQLiteVdbe(this, query); |
| 130 | + |
| 131 | + // table for result of query |
| 132 | + DataTable table = new DataTable(); |
| 133 | + |
| 134 | + // create new instance of DataTable with name "resultTable" |
| 135 | + table = new DataTable("resultTable"); |
| 136 | + |
| 137 | + // reads rows |
| 138 | + do |
| 139 | + { |
| 140 | + } while ( ReadNextRow( statement.VirtualMachine(), table ) == Sqlite3.SQLITE_ROW ); |
| 141 | + // finalize executing this query |
| 142 | + statement.Close(); |
| 143 | + |
| 144 | + // returns table |
| 145 | + return table; |
| 146 | + } |
| 147 | + // private function for reading rows and creating table and columns |
| 148 | + private int ReadNextRow(Vdbe vm, DataTable table) |
| 149 | + { |
| 150 | + int columnCount = table.Columns.Count; |
| 151 | + if(columnCount == 0) |
| 152 | + { |
| 153 | + if((columnCount = ReadColumnNames(vm, table)) == 0) |
| 154 | + return Sqlite3.SQLITE_ERROR; |
| 155 | + } |
| 156 | + |
| 157 | + int resultType; |
| 158 | + if((resultType = |
| 159 | +#if NET_35 |
| 160 | + Sqlite3.Step |
| 161 | +#else |
| 162 | +Sqlite3.sqlite3_step |
| 163 | +#endif |
| 164 | +(vm)) == Sqlite3.SQLITE_ROW) |
| 165 | + { |
| 166 | + object[] columnValues = new object[columnCount]; |
| 167 | + |
| 168 | + for(int i = 0; i < columnCount; i++) |
| 169 | + { |
| 170 | + int columnType = |
| 171 | +#if NET_35 |
| 172 | + Sqlite3.ColumnType |
| 173 | +#else |
| 174 | +Sqlite3.sqlite3_column_type |
| 175 | +#endif |
| 176 | +(vm, i); |
| 177 | + switch(columnType) |
| 178 | + { |
| 179 | + case Sqlite3.SQLITE_INTEGER: |
| 180 | + { |
| 181 | + table.Columns[i].DataType = typeof(Int64); |
| 182 | + columnValues[i] = |
| 183 | +#if NET_35 |
| 184 | + Sqlite3.ColumnInt |
| 185 | +#else |
| 186 | +Sqlite3.sqlite3_column_int |
| 187 | +#endif |
| 188 | +(vm, i); |
| 189 | + break; |
| 190 | + } |
| 191 | + case Sqlite3.SQLITE_FLOAT: |
| 192 | + { |
| 193 | + table.Columns[i].DataType = typeof(Double); |
| 194 | + columnValues[i] = |
| 195 | +#if NET_35 |
| 196 | + Sqlite3.ColumnDouble |
| 197 | +#else |
| 198 | +Sqlite3.sqlite3_column_double |
| 199 | +#endif |
| 200 | +(vm, i); |
| 201 | + break; |
| 202 | + } |
| 203 | + case Sqlite3.SQLITE_TEXT: |
| 204 | + { |
| 205 | + table.Columns[i].DataType = typeof(String); |
| 206 | + columnValues[i] = |
| 207 | +#if NET_35 |
| 208 | + Sqlite3.ColumnText |
| 209 | +#else |
| 210 | +Sqlite3.sqlite3_column_text |
| 211 | +#endif |
| 212 | +(vm, i); |
| 213 | + break; |
| 214 | + } |
| 215 | + case Sqlite3.SQLITE_BLOB: |
| 216 | + { |
| 217 | + table.Columns[i].DataType = typeof(Byte[]); |
| 218 | + columnValues[i] = |
| 219 | +#if NET_35 |
| 220 | + Sqlite3.ColumnBlob |
| 221 | +#else |
| 222 | +Sqlite3.sqlite3_column_blob |
| 223 | +#endif |
| 224 | +(vm, i); |
| 225 | + break; |
| 226 | + } |
| 227 | + default: |
| 228 | + { |
| 229 | + table.Columns[i].DataType = null; |
| 230 | + columnValues[i] = ""; |
| 231 | + break; |
| 232 | + } |
| 233 | + } |
| 234 | + } |
| 235 | + table.Rows.Add(columnValues); |
| 236 | + } |
| 237 | + return resultType; |
| 238 | + } |
| 239 | + // private function for creating Column Names |
| 240 | + // Return number of colums read |
| 241 | + private int ReadColumnNames(Vdbe vm, DataTable table) |
| 242 | + { |
| 243 | + |
| 244 | + String columnName = ""; |
| 245 | + int columnType = 0; |
| 246 | + // returns number of columns returned by statement |
| 247 | + int columnCount = |
| 248 | +#if NET_35 |
| 249 | + Sqlite3.ColumnCount |
| 250 | +#else |
| 251 | +Sqlite3.sqlite3_column_count |
| 252 | +#endif |
| 253 | +(vm); |
| 254 | + object[] columnValues = new object[columnCount]; |
| 255 | + |
| 256 | + try |
| 257 | + { |
| 258 | + // reads columns one by one |
| 259 | + for(int i = 0; i < columnCount; i++) |
| 260 | + { |
| 261 | + columnName = |
| 262 | +#if NET_35 |
| 263 | + Sqlite3.ColumnName |
| 264 | +#else |
| 265 | +Sqlite3.sqlite3_column_name |
| 266 | +#endif |
| 267 | +(vm, i); |
| 268 | + columnType = |
| 269 | +#if NET_35 |
| 270 | + Sqlite3.ColumnType |
| 271 | +#else |
| 272 | +Sqlite3.sqlite3_column_type |
| 273 | +#endif |
| 274 | +(vm, i); |
| 275 | + |
| 276 | + switch(columnType) |
| 277 | + { |
| 278 | + case Sqlite3.SQLITE_INTEGER: |
| 279 | + { |
| 280 | + // adds new integer column to table |
| 281 | + table.Columns.Add(columnName, Type.GetType("System.Int64")); |
| 282 | + break; |
| 283 | + } |
| 284 | + case Sqlite3.SQLITE_FLOAT: |
| 285 | + { |
| 286 | + table.Columns.Add(columnName, Type.GetType("System.Double")); |
| 287 | + break; |
| 288 | + } |
| 289 | + case Sqlite3.SQLITE_TEXT: |
| 290 | + { |
| 291 | + table.Columns.Add(columnName, Type.GetType("System.String")); |
| 292 | + break; |
| 293 | + } |
| 294 | + case Sqlite3.SQLITE_BLOB: |
| 295 | + { |
| 296 | + table.Columns.Add(columnName, Type.GetType("System.byte[]")); |
| 297 | + break; |
| 298 | + } |
| 299 | + default: |
| 300 | + { |
| 301 | + table.Columns.Add(columnName, Type.GetType("System.String")); |
| 302 | + break; |
| 303 | + } |
| 304 | + } |
| 305 | + } |
| 306 | + } |
| 307 | + catch |
| 308 | + { |
| 309 | + return 0; |
| 310 | + } |
| 311 | + return table.Columns.Count; |
| 312 | + } |
| 313 | + } |
| 314 | +} |
0 commit comments