1 /******************************************************** 2 * ADO.NET 2.0 Data Provider for SQLite Version 3.X 3 * Written by Robert Simpson (robert@blackcastlesoft.com) 4 * 5 * Released to the public domain, use at your own risk! 6 ********************************************************/ 7 8 namespace Mono.Data.Sqlite 9 { 10 using System; 11 using System.Data; 12 using System.Data.Common; 13 using System.Collections.Generic; 14 using System.ComponentModel; 15 16 /// <summary> 17 /// SQLite implementation of DbCommand. 18 /// </summary> 19 #if !PLATFORM_COMPACTFRAMEWORK 20 [Designer("SQLite.Designer.SqliteCommandDesigner, SQLite.Designer, Version=1.0.36.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139"), ToolboxItem(true)] 21 #endif 22 public sealed class SqliteCommand : DbCommand, ICloneable 23 { 24 /// <summary> 25 /// The command text this command is based on 26 /// </summary> 27 private string _commandText; 28 /// <summary> 29 /// The connection the command is associated with 30 /// </summary> 31 private SqliteConnection _cnn; 32 /// <summary> 33 /// The version of the connection the command is associated with 34 /// </summary> 35 private long _version; 36 /// <summary> 37 /// Indicates whether or not a DataReader is active on the command. 38 /// </summary> 39 private WeakReference _activeReader; 40 /// <summary> 41 /// The timeout for the command, kludged because SQLite doesn't support per-command timeout values 42 /// </summary> 43 internal int _commandTimeout; 44 /// <summary> 45 /// Designer support 46 /// </summary> 47 private bool _designTimeVisible; 48 /// <summary> 49 /// Used by DbDataAdapter to determine updating behavior 50 /// </summary> 51 private UpdateRowSource _updateRowSource; 52 /// <summary> 53 /// The collection of parameters for the command 54 /// </summary> 55 private SqliteParameterCollection _parameterCollection; 56 /// <summary> 57 /// The SQL command text, broken into individual SQL statements as they are executed 58 /// </summary> 59 internal List<SqliteStatement> _statementList; 60 /// <summary> 61 /// Unprocessed SQL text that has not been executed 62 /// </summary> 63 internal string _remainingText; 64 /// <summary> 65 /// Transaction associated with this command 66 /// </summary> 67 private SqliteTransaction _transaction; 68 69 ///<overloads> 70 /// Constructs a new SqliteCommand 71 /// </overloads> 72 /// <summary> 73 /// Default constructor 74 /// </summary> SqliteCommand()75 public SqliteCommand() :this(null, null) 76 { 77 } 78 79 /// <summary> 80 /// Initializes the command with the given command text 81 /// </summary> 82 /// <param name="commandText">The SQL command text</param> SqliteCommand(string commandText)83 public SqliteCommand(string commandText) 84 : this(commandText, null, null) 85 { 86 } 87 88 /// <summary> 89 /// Initializes the command with the given SQL command text and attach the command to the specified 90 /// connection. 91 /// </summary> 92 /// <param name="commandText">The SQL command text</param> 93 /// <param name="connection">The connection to associate with the command</param> SqliteCommand(string commandText, SqliteConnection connection)94 public SqliteCommand(string commandText, SqliteConnection connection) 95 : this(commandText, connection, null) 96 { 97 } 98 99 /// <summary> 100 /// Initializes the command and associates it with the specified connection. 101 /// </summary> 102 /// <param name="connection">The connection to associate with the command</param> SqliteCommand(SqliteConnection connection)103 public SqliteCommand(SqliteConnection connection) 104 : this(null, connection, null) 105 { 106 } 107 SqliteCommand(SqliteCommand source)108 private SqliteCommand(SqliteCommand source) : this(source.CommandText, source.Connection, source.Transaction) 109 { 110 CommandTimeout = source.CommandTimeout; 111 DesignTimeVisible = source.DesignTimeVisible; 112 UpdatedRowSource = source.UpdatedRowSource; 113 114 foreach (SqliteParameter param in source._parameterCollection) 115 { 116 Parameters.Add(param.Clone()); 117 } 118 } 119 120 /// <summary> 121 /// Initializes a command with the given SQL, connection and transaction 122 /// </summary> 123 /// <param name="commandText">The SQL command text</param> 124 /// <param name="connection">The connection to associate with the command</param> 125 /// <param name="transaction">The transaction the command should be associated with</param> SqliteCommand(string commandText, SqliteConnection connection, SqliteTransaction transaction)126 public SqliteCommand(string commandText, SqliteConnection connection, SqliteTransaction transaction) 127 { 128 _statementList = null; 129 _activeReader = null; 130 _commandTimeout = 30; 131 _parameterCollection = new SqliteParameterCollection(this); 132 _designTimeVisible = true; 133 _updateRowSource = UpdateRowSource.None; 134 _transaction = null; 135 136 if (commandText != null) 137 CommandText = commandText; 138 139 if (connection != null) 140 { 141 DbConnection = connection; 142 _commandTimeout = connection.DefaultTimeout; 143 } 144 145 if (transaction != null) 146 Transaction = transaction; 147 } 148 149 /// <summary> 150 /// Disposes of the command and clears all member variables 151 /// </summary> 152 /// <param name="disposing">Whether or not the class is being explicitly or implicitly disposed</param> Dispose(bool disposing)153 protected override void Dispose(bool disposing) 154 { 155 base.Dispose(disposing); 156 157 if (disposing) 158 { 159 // If a reader is active on this command, don't destroy the command, instead let the reader do it 160 SqliteDataReader reader = null; 161 if (_activeReader != null) 162 { 163 try 164 { 165 reader = _activeReader.Target as SqliteDataReader; 166 } 167 catch 168 { 169 } 170 } 171 172 if (reader != null) 173 { 174 reader._disposeCommand = true; 175 _activeReader = null; 176 return; 177 } 178 179 Connection = null; 180 _parameterCollection.Clear(); 181 _commandText = null; 182 } 183 } 184 185 /// <summary> 186 /// Clears and destroys all statements currently prepared 187 /// </summary> ClearCommands()188 internal void ClearCommands() 189 { 190 if (_activeReader != null) 191 { 192 SqliteDataReader reader = null; 193 try 194 { 195 reader = _activeReader.Target as SqliteDataReader; 196 } 197 catch 198 { 199 } 200 201 if (reader != null) 202 reader.Close(); 203 204 _activeReader = null; 205 } 206 207 if (_statementList == null) return; 208 209 int x = _statementList.Count; 210 for (int n = 0; n < x; n++) 211 _statementList[n].Dispose(); 212 213 _statementList = null; 214 215 _parameterCollection.Unbind(); 216 } 217 218 /// <summary> 219 /// Builds an array of prepared statements for each complete SQL statement in the command text 220 /// </summary> BuildNextCommand()221 internal SqliteStatement BuildNextCommand() 222 { 223 SqliteStatement stmt = null; 224 225 try 226 { 227 if (_statementList == null) 228 _remainingText = _commandText; 229 230 stmt = _cnn._sql.Prepare(_cnn, _remainingText, (_statementList == null) ? null : _statementList[_statementList.Count - 1], (uint)(_commandTimeout * 1000), out _remainingText); 231 if (stmt != null) 232 { 233 stmt._command = this; 234 if (_statementList == null) 235 _statementList = new List<SqliteStatement>(); 236 237 _statementList.Add(stmt); 238 239 _parameterCollection.MapParameters(stmt); 240 stmt.BindParameters(); 241 } 242 return stmt; 243 } 244 catch (Exception) 245 { 246 if (stmt != null) 247 { 248 if (_statementList.Contains(stmt)) 249 _statementList.Remove(stmt); 250 251 stmt.Dispose(); 252 } 253 254 // If we threw an error compiling the statement, we cannot continue on so set the remaining text to null. 255 _remainingText = null; 256 257 throw; 258 } 259 } 260 GetStatement(int index)261 internal SqliteStatement GetStatement(int index) 262 { 263 // Haven't built any statements yet 264 if (_statementList == null) return BuildNextCommand(); 265 266 // If we're at the last built statement and want the next unbuilt statement, then build it 267 if (index == _statementList.Count) 268 { 269 if (String.IsNullOrEmpty(_remainingText) == false) return BuildNextCommand(); 270 else return null; // No more commands 271 } 272 273 SqliteStatement stmt = _statementList[index]; 274 stmt.BindParameters(); 275 276 return stmt; 277 } 278 279 /// <summary> 280 /// Not implemented 281 /// </summary> Cancel()282 public override void Cancel() 283 { 284 if (_activeReader != null) 285 { 286 SqliteDataReader reader = _activeReader.Target as SqliteDataReader; 287 if (reader != null) 288 reader.Cancel(); 289 } 290 } 291 292 /// <summary> 293 /// The SQL command text associated with the command 294 /// </summary> 295 #if !PLATFORM_COMPACTFRAMEWORK 296 [DefaultValue(""), RefreshProperties(RefreshProperties.All), Editor("Microsoft.VSDesigner.Data.SQL.Design.SqlCommandTextEditor, Microsoft.VSDesigner, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a", "System.Drawing.Design.UITypeEditor, System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")] 297 #endif 298 public override string CommandText 299 { 300 get 301 { 302 return _commandText; 303 } 304 set 305 { 306 if (_commandText == value) return; 307 308 if (_activeReader != null && _activeReader.IsAlive) 309 { 310 throw new InvalidOperationException("Cannot set CommandText while a DataReader is active"); 311 } 312 313 ClearCommands(); 314 _commandText = value; 315 316 if (_cnn == null) return; 317 } 318 } 319 320 /// <summary> 321 /// The amount of time to wait for the connection to become available before erroring out 322 /// </summary> 323 #if !PLATFORM_COMPACTFRAMEWORK 324 [DefaultValue((int)30)] 325 #endif 326 public override int CommandTimeout 327 { 328 get 329 { 330 return _commandTimeout; 331 } 332 set 333 { 334 _commandTimeout = value; 335 } 336 } 337 338 /// <summary> 339 /// The type of the command. SQLite only supports CommandType.Text 340 /// </summary> 341 #if !PLATFORM_COMPACTFRAMEWORK 342 [RefreshProperties(RefreshProperties.All), DefaultValue(CommandType.Text)] 343 #endif 344 public override CommandType CommandType 345 { 346 get 347 { 348 return CommandType.Text; 349 } 350 set 351 { 352 if (value != CommandType.Text) 353 { 354 throw new NotSupportedException(); 355 } 356 } 357 } 358 359 /// <summary> 360 /// Forwards to the local CreateParameter() function 361 /// </summary> 362 /// <returns></returns> CreateDbParameter()363 protected override DbParameter CreateDbParameter() 364 { 365 return CreateParameter(); 366 } 367 368 /// <summary> 369 /// Create a new parameter 370 /// </summary> 371 /// <returns></returns> CreateParameter()372 public new SqliteParameter CreateParameter() 373 { 374 return new SqliteParameter(); 375 } 376 377 /// <summary> 378 /// The connection associated with this command 379 /// </summary> 380 #if !PLATFORM_COMPACTFRAMEWORK 381 [DefaultValue((string)null), Editor("Microsoft.VSDesigner.Data.Design.DbConnectionEditor, Microsoft.VSDesigner, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a", "System.Drawing.Design.UITypeEditor, System.Drawing, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a")] 382 #endif 383 public new SqliteConnection Connection 384 { 385 get { return _cnn; } 386 set 387 { 388 if (_activeReader != null && _activeReader.IsAlive) 389 throw new InvalidOperationException("Cannot set Connection while a DataReader is active"); 390 391 if (_cnn != null) 392 { 393 ClearCommands(); 394 //_cnn.RemoveCommand(this); 395 } 396 397 _cnn = value; 398 if (_cnn != null) 399 _version = _cnn._version; 400 401 //if (_cnn != null) 402 // _cnn.AddCommand(this); 403 } 404 } 405 406 /// <summary> 407 /// Forwards to the local Connection property 408 /// </summary> 409 protected override DbConnection DbConnection 410 { 411 get 412 { 413 return Connection; 414 } 415 set 416 { 417 Connection = (SqliteConnection)value; 418 } 419 } 420 421 /// <summary> 422 /// Returns the SqliteParameterCollection for the given command 423 /// </summary> 424 #if !PLATFORM_COMPACTFRAMEWORK 425 [DesignerSerializationVisibility(DesignerSerializationVisibility.Content)] 426 #endif 427 public new SqliteParameterCollection Parameters 428 { 429 get { return _parameterCollection; } 430 } 431 432 /// <summary> 433 /// Forwards to the local Parameters property 434 /// </summary> 435 protected override DbParameterCollection DbParameterCollection 436 { 437 get 438 { 439 return Parameters; 440 } 441 } 442 443 /// <summary> 444 /// The transaction associated with this command. SQLite only supports one transaction per connection, so this property forwards to the 445 /// command's underlying connection. 446 /// </summary> 447 #if !PLATFORM_COMPACTFRAMEWORK 448 [Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)] 449 #endif 450 public new SqliteTransaction Transaction 451 { 452 get { return _transaction; } 453 set 454 { 455 if (_cnn != null) 456 { 457 if (_activeReader != null && _activeReader.IsAlive) 458 throw new InvalidOperationException("Cannot set Transaction while a DataReader is active"); 459 460 if (value != null) 461 { 462 if (value._cnn != _cnn) 463 throw new ArgumentException("Transaction is not associated with the command's connection"); 464 } 465 _transaction = value; 466 } 467 else 468 { 469 Connection = value.Connection; 470 _transaction = value; 471 } 472 } 473 } 474 475 /// <summary> 476 /// Forwards to the local Transaction property 477 /// </summary> 478 protected override DbTransaction DbTransaction 479 { 480 get 481 { 482 return Transaction; 483 } 484 set 485 { 486 Transaction = (SqliteTransaction)value; 487 } 488 } 489 490 /// <summary> 491 /// This function ensures there are no active readers, that we have a valid connection, 492 /// that the connection is open, that all statements are prepared and all parameters are assigned 493 /// in preparation for allocating a data reader. 494 /// </summary> InitializeForReader()495 private void InitializeForReader() 496 { 497 if (_activeReader != null && _activeReader.IsAlive) 498 throw new InvalidOperationException("DataReader already active on this command"); 499 500 if (_cnn == null) 501 throw new InvalidOperationException("No connection associated with this command"); 502 503 if (_cnn.State != ConnectionState.Open) 504 throw new InvalidOperationException("Database is not open"); 505 506 // If the version of the connection has changed, clear out any previous commands before starting 507 if (_cnn._version != _version) 508 { 509 _version = _cnn._version; 510 ClearCommands(); 511 } 512 513 // Map all parameters for statements already built 514 _parameterCollection.MapParameters(null); 515 516 //// Set the default command timeout 517 //_cnn._sql.SetTimeout(_commandTimeout * 1000); 518 } 519 520 /// <summary> 521 /// Creates a new SqliteDataReader to execute/iterate the array of SQLite prepared statements 522 /// </summary> 523 /// <param name="behavior">The behavior the data reader should adopt</param> 524 /// <returns>Returns a SqliteDataReader object</returns> ExecuteDbDataReader(CommandBehavior behavior)525 protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior) 526 { 527 return ExecuteReader(behavior); 528 } 529 530 /// <summary> 531 /// Overrides the default behavior to return a SqliteDataReader specialization class 532 /// </summary> 533 /// <param name="behavior">The flags to be associated with the reader</param> 534 /// <returns>A SqliteDataReader</returns> ExecuteReader(CommandBehavior behavior)535 public new SqliteDataReader ExecuteReader(CommandBehavior behavior) 536 { 537 InitializeForReader(); 538 539 SqliteDataReader rd = new SqliteDataReader(this, behavior); 540 _activeReader = new WeakReference(rd, false); 541 542 return rd; 543 } 544 545 /// <summary> 546 /// Overrides the default behavior of DbDataReader to return a specialized SqliteDataReader class 547 /// </summary> 548 /// <returns>A SqliteDataReader</returns> ExecuteReader()549 public new SqliteDataReader ExecuteReader() 550 { 551 return ExecuteReader(CommandBehavior.Default); 552 } 553 554 /// <summary> 555 /// Called by the SqliteDataReader when the data reader is closed. 556 /// </summary> ClearDataReader()557 internal void ClearDataReader() 558 { 559 _activeReader = null; 560 } 561 562 /// <summary> 563 /// Execute the command and return the number of rows inserted/updated affected by it. 564 /// </summary> 565 /// <returns></returns> ExecuteNonQuery()566 public override int ExecuteNonQuery() 567 { 568 using (SqliteDataReader reader = ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult)) 569 { 570 while (reader.NextResult()) ; 571 return reader.RecordsAffected; 572 } 573 } 574 575 /// <summary> 576 /// Execute the command and return the first column of the first row of the resultset 577 /// (if present), or null if no resultset was returned. 578 /// </summary> 579 /// <returns>The first column of the first row of the first resultset from the query</returns> ExecuteScalar()580 public override object ExecuteScalar() 581 { 582 using (SqliteDataReader reader = ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult)) 583 { 584 if (reader.Read()) 585 return reader[0]; 586 } 587 return null; 588 } 589 590 /// <summary> 591 /// Does nothing. Commands are prepared as they are executed the first time, and kept in prepared state afterwards. 592 /// </summary> Prepare()593 public override void Prepare() 594 { 595 } 596 597 /// <summary> 598 /// Sets the method the SqliteCommandBuilder uses to determine how to update inserted or updated rows in a DataTable. 599 /// </summary> 600 [DefaultValue(UpdateRowSource.None)] 601 public override UpdateRowSource UpdatedRowSource 602 { 603 get 604 { 605 return _updateRowSource; 606 } 607 set 608 { 609 _updateRowSource = value; 610 } 611 } 612 613 /// <summary> 614 /// Determines if the command is visible at design time. Defaults to True. 615 /// </summary> 616 #if !PLATFORM_COMPACTFRAMEWORK 617 [DesignOnly(true), Browsable(false), DefaultValue(true), EditorBrowsable(EditorBrowsableState.Never)] 618 #endif 619 public override bool DesignTimeVisible 620 { 621 get 622 { 623 return _designTimeVisible; 624 } 625 set 626 { 627 _designTimeVisible = value; 628 #if !PLATFORM_COMPACTFRAMEWORK 629 TypeDescriptor.Refresh(this); 630 #endif 631 } 632 } 633 634 /// <summary> 635 /// Clones a command, including all its parameters 636 /// </summary> 637 /// <returns>A new SqliteCommand with the same commandtext, connection and parameters</returns> Clone()638 public object Clone() 639 { 640 return new SqliteCommand(this); 641 } 642 } 643 }