wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 //
2 // Community.CsharpSqlite.SQLiteClient.SqliteCommand.cs
3 //
4 // Represents a Transact-SQL statement or stored procedure to execute against
5 // a Sqlite database file.
6 //
7 // Author(s): Vladimir Vukicevic <vladimir@pobox.com>
8 // Everaldo Canuto <everaldo_canuto@yahoo.com.br>
9 // Chris Turchin <chris@turchin.net>
10 // Jeroen Zwartepoorte <jeroen@xs4all.nl>
11 // Thomas Zoechling <thomas.zoechling@gmx.at>
12 // Joshua Tauberer <tauberer@for.net>
13 // Noah Hart <noah.hart@gmail.com>
14 //
15 // Copyright (C) 2002 Vladimir Vukicevic
16 //
17 // Permission is hereby granted, free of charge, to any person obtaining
18 // a copy of this software and associated documentation files (the
19 // "Software"), to deal in the Software without restriction, including
20 // without limitation the rights to use, copy, modify, merge, publish,
21 // distribute, sublicense, and/or sell copies of the Software, and to
22 // permit persons to whom the Software is furnished to do so, subject to
23 // the following conditions:
24 //
25 // The above copyright notice and this permission notice shall be
26 // included in all copies or substantial portions of the Software.
27 //
28 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
29 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
30 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
31 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
32 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
33 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
34 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
35 //
36  
37 using System;
38 using System.Text;
39 using System.Data;
40 using System.Data.Common;
41 using Community.CsharpSqlite;
42 using System.Globalization;
43  
44 namespace Community.CsharpSqlite.SQLiteClient
45 {
46 public class SqliteCommand : DbCommand, ICloneable
47 {
48 #region Fields
49  
50 private SqliteConnection parent_conn;
51 private SqliteTransaction transaction;
52 private string sql;
53 private int timeout;
54 private CommandType type;
55 #if !SQLITE_SILVERLIGHT
56 private UpdateRowSource upd_row_source;
57 #endif
58 private SqliteParameterCollection sql_params;
59 private bool prepared = false;
60 private bool _designTimeVisible = true;
61  
62 #endregion
63  
64 #region Constructors and destructors
65  
66 public SqliteCommand()
67 {
68 sql = string.Empty;
69 }
70  
71 public SqliteCommand( string sqlText )
72 {
73 sql = sqlText;
74 }
75  
76 public SqliteCommand( string sqlText, SqliteConnection dbConn )
77 {
78 sql = sqlText;
79 parent_conn = dbConn;
80 }
81  
82 public SqliteCommand( string sqlText, SqliteConnection dbConn, SqliteTransaction trans )
83 {
84 sql = sqlText;
85 parent_conn = dbConn;
86 transaction = trans;
87 }
88  
89 #endregion
90  
91 #region Properties
92  
93 public override string CommandText
94 {
95 get
96 {
97 return sql;
98 }
99 set
100 {
101 sql = value;
102 prepared = false;
103 }
104 }
105  
106 public override int CommandTimeout
107 {
108 get
109 {
110 return timeout;
111 }
112 set
113 {
114 timeout = value;
115 }
116 }
117  
118 public override CommandType CommandType
119 {
120 get
121 {
122 return type;
123 }
124 set
125 {
126 type = value;
127 }
128 }
129  
130 protected override DbConnection DbConnection
131 {
132 get { return parent_conn; }
133 set { parent_conn = (SqliteConnection)value; }
134 }
135  
136 public new SqliteConnection Connection
137 {
138 get
139 {
140 return parent_conn;
141 }
142 set
143 {
144 parent_conn = (SqliteConnection)value;
145 }
146 }
147  
148 public new SqliteParameterCollection Parameters
149 {
150 get
151 {
152 if ( sql_params == null )
153 sql_params = new SqliteParameterCollection();
154 return sql_params;
155 }
156 }
157  
158 protected override DbParameterCollection DbParameterCollection
159 {
160 get { return Parameters; }
161 }
162  
163 protected override DbTransaction DbTransaction
164 {
165 get
166 {
167 return transaction;
168 }
169 set
170 {
171 transaction = (SqliteTransaction)value;
172 }
173 }
174  
175 public override bool DesignTimeVisible
176 {
177 get { return _designTimeVisible; }
178 set { _designTimeVisible = value; }
179 }
180 #if !SQLITE_SILVERLIGHT
181 public override UpdateRowSource UpdatedRowSource
182 {
183 get
184 {
185 return upd_row_source;
186 }
187 set
188 {
189 upd_row_source = value;
190 }
191 }
192 #endif
193  
194 #endregion
195  
196 #region Internal Methods
197  
198 internal int NumChanges()
199 {
200 //if (parent_conn.Version == 3)
201 return Sqlite3.sqlite3_changes( parent_conn.Handle2 );
202 //else
203 // return Sqlite.sqlite_changes(parent_conn.Handle);
204 }
205  
206 private void BindParameters3( Sqlite3.Vdbe pStmt )
207 {
208 if ( sql_params == null )
209 return;
210 if ( sql_params.Count == 0 )
211 return;
212  
213 int pcount = Sqlite3.sqlite3_bind_parameter_count( pStmt );
214  
215 for ( int i = 1; i <= pcount; i++ )
216 {
217 String name = Sqlite3.sqlite3_bind_parameter_name( pStmt, i );
218  
219 SqliteParameter param = null;
220 if ( !string.IsNullOrEmpty( name ) )
221 param = sql_params[name] as SqliteParameter;
222 else
223 param = sql_params[i - 1] as SqliteParameter;
224  
225 if ( param.Value == null )
226 {
227 Sqlite3.sqlite3_bind_null( pStmt, i );
228 continue;
229 }
230  
231 Type ptype = param.Value.GetType();
232 if ( ptype.IsEnum )
233 ptype = Enum.GetUnderlyingType( ptype );
234  
235 SqliteError err;
236  
237 if ( ptype.Equals( typeof( String ) ) )
238 {
239 String s = (String)param.Value;
240 err = (SqliteError)Sqlite3.sqlite3_bind_text( pStmt, i, s, -1, null );
241 } else if ( ptype.Equals( typeof( DBNull ) ) )
242 {
243 err = (SqliteError)Sqlite3.sqlite3_bind_null( pStmt, i );
244 } else if ( ptype.Equals( typeof( Boolean ) ) )
245 {
246 bool b = (bool)param.Value;
247 err = (SqliteError)Sqlite3.sqlite3_bind_int( pStmt, i, b ? 1 : 0 );
248 } else if ( ptype.Equals( typeof( Byte ) ) )
249 {
250 err = (SqliteError)Sqlite3.sqlite3_bind_int( pStmt, i, (Byte)param.Value );
251 } else if ( ptype.Equals( typeof( Char ) ) )
252 {
253 err = (SqliteError)Sqlite3.sqlite3_bind_int( pStmt, i, (Char)param.Value );
254 } else if ( ptype.IsEnum )
255 {
256 err = (SqliteError)Sqlite3.sqlite3_bind_int( pStmt, i, (Int32)param.Value );
257 } else if ( ptype.Equals( typeof( Int16 ) ) )
258 {
259 err = (SqliteError)Sqlite3.sqlite3_bind_int( pStmt, i, (Int16)param.Value );
260 } else if ( ptype.Equals( typeof( Int32 ) ) )
261 {
262 err = (SqliteError)Sqlite3.sqlite3_bind_int( pStmt, i, (Int32)param.Value );
263 } else if ( ptype.Equals( typeof( SByte ) ) )
264 {
265 err = (SqliteError)Sqlite3.sqlite3_bind_int( pStmt, i, (SByte)param.Value );
266 } else if ( ptype.Equals( typeof( UInt16 ) ) )
267 {
268 err = (SqliteError)Sqlite3.sqlite3_bind_int( pStmt, i, (UInt16)param.Value );
269 } else if ( ptype.Equals( typeof( DateTime ) ) )
270 {
271 DateTime dt = (DateTime)param.Value;
272 err = (SqliteError)Sqlite3.sqlite3_bind_text( pStmt, i, dt.ToString( "yyyy-MM-dd HH:mm:ss.fff" ), -1, null );
273 } else if ( ptype.Equals( typeof( Decimal ) ) )
274 {
275 string val = ( (Decimal)param.Value ).ToString( CultureInfo.InvariantCulture );
276 err = (SqliteError)Sqlite3.sqlite3_bind_text( pStmt, i, val, val.Length, null );
277 } else if ( ptype.Equals( typeof( Double ) ) )
278 {
279 err = (SqliteError)Sqlite3.sqlite3_bind_double( pStmt, i, (Double)param.Value );
280 } else if ( ptype.Equals( typeof( Single ) ) )
281 {
282 err = (SqliteError)Sqlite3.sqlite3_bind_double( pStmt, i, (Single)param.Value );
283 } else if ( ptype.Equals( typeof( UInt32 ) ) )
284 {
285 err = (SqliteError)Sqlite3.sqlite3_bind_int64( pStmt, i, (UInt32)param.Value );
286 } else if ( ptype.Equals( typeof( Int64 ) ) )
287 {
288 err = (SqliteError)Sqlite3.sqlite3_bind_int64( pStmt, i, (Int64)param.Value );
289 } else if ( ptype.Equals( typeof( Byte[] ) ) )
290 {
291 err = (SqliteError)Sqlite3.sqlite3_bind_blob( pStmt, i, (byte[])param.Value, ( (byte[])param.Value ).Length, null );
292 } else if ( ptype.Equals( typeof( Guid ) ) )
293 {
294 err = (SqliteError)Sqlite3.sqlite3_bind_text( pStmt, i, param.Value.ToString(), param.Value.ToString().Length, null );
295 } else
296 {
297 throw new ApplicationException( "Unknown Parameter Type" );
298 }
299 if ( err != SqliteError.OK )
300 {
301 throw new ApplicationException( "Sqlite error in bind " + err );
302 }
303 }
304 }
305  
306 private void GetNextStatement( string pzStart, ref string pzTail, ref Sqlite3.Vdbe pStmt )
307 {
308 SqliteError err = (SqliteError)Sqlite3.sqlite3_prepare_v2( parent_conn.Handle2, pzStart, pzStart.Length, ref pStmt, ref pzTail );
309 if ( err != SqliteError.OK )
310 throw new SqliteSyntaxException( parent_conn.Handle2.errCode, GetError3() );
311 }
312  
313 // Executes a statement and ignores its result.
314 private void ExecuteStatement( Sqlite3.Vdbe pStmt )
315 {
316 int cols;
317 IntPtr pazValue, pazColName;
318 ExecuteStatement( pStmt, out cols, out pazValue, out pazColName );
319 }
320  
321 // Executes a statement and returns whether there is more data available.
322 internal bool ExecuteStatement( Sqlite3.Vdbe pStmt, out int cols, out IntPtr pazValue, out IntPtr pazColName )
323 {
324 SqliteError err;
325  
326 //if (parent_conn.Version == 3)
327 //{
328 err = (SqliteError)Sqlite3.sqlite3_step( pStmt );
329  
330 if ( err == SqliteError.ERROR )
331 throw new SqliteExecutionException(parent_conn.Handle2.errCode, GetError3() + "\n" + pStmt.zErrMsg );
332  
333 pazValue = IntPtr.Zero;
334 pazColName = IntPtr.Zero; // not used for v=3
335 cols = Sqlite3.sqlite3_column_count( pStmt );
336  
337 /*
338 }
339 else
340 {
341 err = (SqliteError)Sqlite3.sqlite3_step(pStmt, out cols, out pazValue, out pazColName);
342 if (err == SqliteError.ERROR)
343 throw new SqliteExecutionException ();
344 }
345 */
346 if ( err == SqliteError.BUSY )
347 throw new SqliteBusyException();
348  
349 if ( err == SqliteError.MISUSE )
350 throw new SqliteExecutionException();
351  
352 // err is either ROW or DONE.
353 return err == SqliteError.ROW;
354 }
355  
356 #endregion
357  
358 #region Public Methods
359  
360 object ICloneable.Clone()
361 {
362 return new SqliteCommand( sql, parent_conn, transaction );
363 }
364  
365 public override void Cancel()
366 {
367 }
368  
369 public override void Prepare()
370 {
371 // There isn't much we can do here. If a table schema
372 // changes after preparing a statement, Sqlite bails,
373 // so we can only compile statements right before we
374 // want to run them.
375  
376 if ( prepared )
377 return;
378 prepared = true;
379 }
380  
381 protected override DbParameter CreateDbParameter ()
382 {
383 return new SqliteParameter();
384 }
385  
386 public override int ExecuteNonQuery()
387 {
388 int rows_affected;
389 ExecuteReader( CommandBehavior.Default, false, out rows_affected );
390 return rows_affected;
391 }
392  
393 public override object ExecuteScalar()
394 {
395 SqliteDataReader r = (SqliteDataReader)ExecuteReader();
396 if ( r == null || !r.Read() )
397 {
398 return null;
399 }
400 object o = r[0];
401 r.Close();
402 return o;
403 }
404  
405 public new SqliteDataReader ExecuteReader( CommandBehavior behavior )
406 {
407 int r;
408 return ExecuteReader( behavior, true, out r );
409 }
410  
411 public new SqliteDataReader ExecuteReader ()
412 {
413 return ExecuteReader (CommandBehavior.Default);
414 }
415  
416 protected override DbDataReader ExecuteDbDataReader (CommandBehavior behavior)
417 {
418 return ExecuteReader (behavior);
419 }
420  
421 public SqliteDataReader ExecuteReader( CommandBehavior behavior, bool want_results, out int rows_affected )
422 {
423 Prepare();
424  
425 // The SQL string may contain multiple sql commands, so the main
426 // thing to do is have Sqlite iterate through the commands.
427 // If want_results, only the last command is returned as a
428 // DataReader. Otherwise, no command is returned as a
429 // DataReader.
430  
431 //IntPtr psql; // pointer to SQL command
432  
433 // Sqlite 2 docs say this: By default, SQLite assumes that all data uses a fixed-size 8-bit
434 // character (iso8859). But if you give the --enable-utf8 option to the configure script, then the
435 // library assumes UTF-8 variable sized characters. This makes a difference for the LIKE and GLOB
436 // operators and the LENGTH() and SUBSTR() functions. The static string sqlite_encoding will be set
437 // to either "UTF-8" or "iso8859" to indicate how the library was compiled. In addition, the sqlite.h
438 // header file will define one of the macros SQLITE_UTF8 or SQLITE_ISO8859, as appropriate.
439 //
440 // We have no way of knowing whether Sqlite 2 expects ISO8859 or UTF-8, but ISO8859 seems to be the
441 // default. Therefore, we need to use an ISO8859(-1) compatible encoding, like ANSI.
442 // OTOH, the user may want to specify the encoding of the bytes stored in the database, regardless
443 // of what Sqlite is treating them as,
444  
445 // For Sqlite 3, we use the UTF-16 prepare function, so we need a UTF-16 string.
446 /*
447 if (parent_conn.Version == 2)
448 psql = Sqlite.StringToHeap (sql.Trim(), parent_conn.Encoding);
449 else
450 psql = Marshal.StringToHGlobalUni (sql.Trim());
451 */
452 string pzTail = sql.Trim();
453  
454 parent_conn.StartExec();
455  
456 rows_affected = 0;
457  
458 try
459 {
460 while ( true )
461 {
462 Sqlite3.Vdbe pStmt = null;
463  
464 string queryval = pzTail;
465 GetNextStatement( queryval, ref pzTail, ref pStmt );
466  
467 if ( pStmt == null )
468 throw new Exception();
469  
470 // pzTail is positioned after the last byte in the
471 // statement, which will be the NULL character if
472 // this was the last statement.
473 bool last = pzTail.Length == 0;
474  
475 try
476 {
477 if ( parent_conn.Version == 3 )
478 BindParameters3( pStmt );
479  
480 if ( last && want_results )
481 return new SqliteDataReader( this, pStmt, parent_conn.Version );
482  
483 ExecuteStatement( pStmt );
484  
485 if ( last ) // rows_affected is only used if !want_results
486 rows_affected = NumChanges();
487  
488 }
489 finally
490 {
491 //if (parent_conn.Version == 3)
492 Sqlite3.sqlite3_finalize( pStmt );
493 //else
494 // Sqlite.sqlite_finalize (pStmt, out errMsgPtr);
495 }
496  
497 if ( last )
498 break;
499 }
500  
501 return null;
502 }
503 //alxwest: Console.WriteLine in shared functionality.
504 //catch ( Exception ex )
505 //{
506 // Console.WriteLine( ex.Message );
507 // return null;
508 //}
509 finally
510 {
511 parent_conn.EndExec();
512 //Marshal.FreeHGlobal (psql);
513 }
514 }
515  
516 public int LastInsertRowID()
517 {
518 return parent_conn.LastInsertRowId;
519 }
520 public string GetLastError()
521 {
522 return Sqlite3.sqlite3_errmsg( parent_conn.Handle2 );
523 }
524 private string GetError3()
525 {
526 return Sqlite3.sqlite3_errmsg( parent_conn.Handle2 );
527 //return Marshal.PtrToStringUni (Sqlite.sqlite3_errmsg16 (parent_conn.Handle));
528 }
529 #endregion
530 }
531 }