wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 // $Header$
2 using System;
3 using System.Collections;
4 using System.Data;
5  
6 namespace Community.CsharpSqlite
7 {
8  
9 using sqlite = Sqlite3.sqlite3;
10 using Vdbe = Sqlite3.Vdbe;
11 /// <summary>
12 /// C#-SQLite wrapper with functions for opening, closing and executing queries.
13 /// </summary>
14 public class SQLiteDatabase
15 {
16 // pointer to database
17 private sqlite db;
18  
19 /// <summary>
20 /// Creates new instance of SQLiteBase class with no database attached.
21 /// </summary>
22 public SQLiteDatabase()
23 {
24 db = null;
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 { } while ( ReadNextRow( statement.VirtualMachine(), table ) == Sqlite3.SQLITE_ROW );
139 // finalize executing this query
140 statement.Close();
141  
142 // returns table
143 return table;
144 }
145  
146 // private function for reading rows and creating table and columns
147 private int ReadNextRow( Vdbe vm, DataTable table )
148 {
149 int columnCount = table.Columns.Count;
150 if ( columnCount == 0 )
151 {
152 if ( ( columnCount = ReadColumnNames( vm, table ) ) == 0 ) return Sqlite3.SQLITE_ERROR;
153 }
154  
155 int resultType;
156 if ( ( resultType =
157 #if NET_35
158 Sqlite3.Step
159 #else
160 Sqlite3.sqlite3_step
161 #endif
162 ( vm ) ) == Sqlite3.SQLITE_ROW )
163 {
164 object[] columnValues = new object[columnCount];
165  
166 for ( int i = 0; i < columnCount; i++ )
167 {
168 int columnType =
169 #if NET_35
170 Sqlite3.ColumnType
171 #else
172 Sqlite3.sqlite3_column_type
173 #endif
174 ( vm, i );
175 switch ( columnType )
176 {
177 case Sqlite3.SQLITE_INTEGER:
178 {
179 table.Columns[i].DataType = typeof( Int64 );
180 columnValues[i] =
181 #if NET_35
182 Sqlite3.ColumnInt
183 #else
184 Sqlite3.sqlite3_column_int
185 #endif
186 ( vm, i );
187 break;
188 }
189 case Sqlite3.SQLITE_FLOAT:
190 {
191 table.Columns[i].DataType = typeof( Double );
192 columnValues[i] =
193 #if NET_35
194 Sqlite3.ColumnDouble
195 #else
196 Sqlite3.sqlite3_column_double
197 #endif
198 ( vm, i );
199 break;
200 }
201 case Sqlite3.SQLITE_TEXT:
202 {
203 table.Columns[i].DataType = typeof( String );
204 columnValues[i] =
205 #if NET_35
206 Sqlite3.ColumnText
207 #else
208 Sqlite3.sqlite3_column_text
209 #endif
210 ( vm, i );
211 break;
212 }
213 case Sqlite3.SQLITE_BLOB:
214 {
215 table.Columns[i].DataType = typeof( Byte[] );
216 columnValues[i] =
217 #if NET_35
218 Sqlite3.ColumnBlob
219 #else
220 Sqlite3.sqlite3_column_blob
221 #endif
222 ( vm, i );
223 break;
224 }
225 default:
226 {
227 table.Columns[i].DataType = null;
228 columnValues[i] = "";
229 break;
230 }
231 }
232 }
233 table.Rows.Add( columnValues );
234 }
235 return resultType;
236 }
237 // private function for creating Column Names
238 // Return number of colums read
239 private int ReadColumnNames( Vdbe vm, DataTable table )
240 {
241  
242 String columnName = "";
243 int columnType = 0;
244 // returns number of columns returned by statement
245 int columnCount =
246 #if NET_35
247 Sqlite3.ColumnCount
248 #else
249 Sqlite3.sqlite3_column_count
250 #endif
251 ( vm );
252 object[] columnValues = new object[columnCount];
253  
254 try
255 {
256 // reads columns one by one
257 for ( int i = 0; i < columnCount; i++ )
258 {
259 columnName =
260 #if NET_35
261 Sqlite3.ColumnName
262 #else
263 Sqlite3.sqlite3_column_name
264 #endif
265 ( vm, i );
266 columnType =
267 #if NET_35
268 Sqlite3.ColumnType
269 #else
270 Sqlite3.sqlite3_column_type
271 #endif
272 ( vm, i );
273  
274 switch ( columnType )
275 {
276 case Sqlite3.SQLITE_INTEGER:
277 {
278 // adds new integer column to table
279 table.Columns.Add( columnName, Type.GetType( "System.Int64" ) );
280 break;
281 }
282 case Sqlite3.SQLITE_FLOAT:
283 {
284 table.Columns.Add( columnName, Type.GetType( "System.Double" ) );
285 break;
286 }
287 case Sqlite3.SQLITE_TEXT:
288 {
289 table.Columns.Add( columnName, Type.GetType( "System.String" ) );
290 break;
291 }
292 case Sqlite3.SQLITE_BLOB:
293 {
294 table.Columns.Add( columnName, Type.GetType( "System.byte[]" ) );
295 break;
296 }
297 default:
298 {
299 table.Columns.Add( columnName, Type.GetType( "System.String" ) );
300 break;
301 }
302 }
303 }
304 }
305 catch
306 {
307 return 0;
308 }
309 return table.Columns.Count;
310 }
311  
312 }
313 }