1 /********************************************************
\r
2 * ADO.NET 2.0 Data Provider for SQLite Version 3.X
\r
3 * Written by Robert Simpson (robert@blackcastlesoft.com)
\r
5 * Released to the public domain, use at your own risk!
\r
6 ********************************************************/
\r
8 namespace Mono.Data.Sqlite
\r
12 using System.Data.Common;
\r
13 using System.Collections.Generic;
\r
14 using System.ComponentModel;
\r
17 /// SQLite implementation of DbCommand.
\r
19 #if !PLATFORM_COMPACTFRAMEWORK
\r
20 [Designer("SQLite.Designer.SqliteCommandDesigner, SQLite.Designer, Version=1.0.36.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139"), ToolboxItem(true)]
\r
22 public sealed class SqliteCommand : DbCommand, ICloneable
\r
25 /// The command text this command is based on
\r
27 private string _commandText;
\r
29 /// The connection the command is associated with
\r
31 private SqliteConnection _cnn;
\r
33 /// The version of the connection the command is associated with
\r
35 private long _version;
\r
37 /// Indicates whether or not a DataReader is active on the command.
\r
39 private WeakReference _activeReader;
\r
41 /// The timeout for the command, kludged because SQLite doesn't support per-command timeout values
\r
43 internal int _commandTimeout;
\r
45 /// Designer support
\r
47 private bool _designTimeVisible;
\r
49 /// Used by DbDataAdapter to determine updating behavior
\r
51 private UpdateRowSource _updateRowSource;
\r
53 /// The collection of parameters for the command
\r
55 private SqliteParameterCollection _parameterCollection;
\r
57 /// The SQL command text, broken into individual SQL statements as they are executed
\r
59 internal List<SqliteStatement> _statementList;
\r
61 /// Unprocessed SQL text that has not been executed
\r
63 internal string _remainingText;
\r
65 /// Transaction associated with this command
\r
67 private SqliteTransaction _transaction;
\r
70 /// Constructs a new SqliteCommand
\r
73 /// Default constructor
\r
75 public SqliteCommand() :this(null, null)
\r
80 /// Initializes the command with the given command text
\r
82 /// <param name="commandText">The SQL command text</param>
\r
83 public SqliteCommand(string commandText)
\r
84 : this(commandText, null, null)
\r
89 /// Initializes the command with the given SQL command text and attach the command to the specified
\r
92 /// <param name="commandText">The SQL command text</param>
\r
93 /// <param name="connection">The connection to associate with the command</param>
\r
94 public SqliteCommand(string commandText, SqliteConnection connection)
\r
95 : this(commandText, connection, null)
\r
100 /// Initializes the command and associates it with the specified connection.
\r
102 /// <param name="connection">The connection to associate with the command</param>
\r
103 public SqliteCommand(SqliteConnection connection)
\r
104 : this(null, connection, null)
\r
108 private SqliteCommand(SqliteCommand source) : this(source.CommandText, source.Connection, source.Transaction)
\r
110 CommandTimeout = source.CommandTimeout;
\r
111 DesignTimeVisible = source.DesignTimeVisible;
\r
112 UpdatedRowSource = source.UpdatedRowSource;
\r
114 foreach (SqliteParameter param in source._parameterCollection)
\r
116 Parameters.Add(param.Clone());
\r
121 /// Initializes a command with the given SQL, connection and transaction
\r
123 /// <param name="commandText">The SQL command text</param>
\r
124 /// <param name="connection">The connection to associate with the command</param>
\r
125 /// <param name="transaction">The transaction the command should be associated with</param>
\r
126 public SqliteCommand(string commandText, SqliteConnection connection, SqliteTransaction transaction)
\r
128 _statementList = null;
\r
129 _activeReader = null;
\r
130 _commandTimeout = 30;
\r
131 _parameterCollection = new SqliteParameterCollection(this);
\r
132 _designTimeVisible = true;
\r
133 _updateRowSource = UpdateRowSource.None;
\r
134 _transaction = null;
\r
136 if (commandText != null)
\r
137 CommandText = commandText;
\r
139 if (connection != null)
\r
141 DbConnection = connection;
\r
142 _commandTimeout = connection.DefaultTimeout;
\r
145 if (transaction != null)
\r
146 Transaction = transaction;
\r
150 /// Disposes of the command and clears all member variables
\r
152 /// <param name="disposing">Whether or not the class is being explicitly or implicitly disposed</param>
\r
153 protected override void Dispose(bool disposing)
\r
155 base.Dispose(disposing);
\r
159 // If a reader is active on this command, don't destroy the command, instead let the reader do it
\r
160 SqliteDataReader reader = null;
\r
161 if (_activeReader != null)
\r
165 reader = _activeReader.Target as SqliteDataReader;
\r
172 if (reader != null)
\r
174 reader._disposeCommand = true;
\r
175 _activeReader = null;
\r
180 _parameterCollection.Clear();
\r
181 _commandText = null;
\r
186 /// Clears and destroys all statements currently prepared
\r
188 internal void ClearCommands()
\r
190 if (_activeReader != null)
\r
192 SqliteDataReader reader = null;
\r
195 reader = _activeReader.Target as SqliteDataReader;
\r
201 if (reader != null)
\r
204 _activeReader = null;
\r
207 if (_statementList == null) return;
\r
209 int x = _statementList.Count;
\r
210 for (int n = 0; n < x; n++)
\r
211 _statementList[n].Dispose();
\r
213 _statementList = null;
\r
215 _parameterCollection.Unbind();
\r
219 /// Builds an array of prepared statements for each complete SQL statement in the command text
\r
221 internal SqliteStatement BuildNextCommand()
\r
223 SqliteStatement stmt = null;
\r
227 if (_statementList == null)
\r
228 _remainingText = _commandText;
\r
230 stmt = _cnn._sql.Prepare(_cnn, _remainingText, (_statementList == null) ? null : _statementList[_statementList.Count - 1], (uint)(_commandTimeout * 1000), out _remainingText);
\r
233 stmt._command = this;
\r
234 if (_statementList == null)
\r
235 _statementList = new List<SqliteStatement>();
\r
237 _statementList.Add(stmt);
\r
239 _parameterCollection.MapParameters(stmt);
\r
240 stmt.BindParameters();
\r
248 if (_statementList.Contains(stmt))
\r
249 _statementList.Remove(stmt);
\r
254 // If we threw an error compiling the statement, we cannot continue on so set the remaining text to null.
\r
255 _remainingText = null;
\r
261 internal SqliteStatement GetStatement(int index)
\r
263 // Haven't built any statements yet
\r
264 if (_statementList == null) return BuildNextCommand();
\r
266 // If we're at the last built statement and want the next unbuilt statement, then build it
\r
267 if (index == _statementList.Count)
\r
269 if (String.IsNullOrEmpty(_remainingText) == false) return BuildNextCommand();
\r
270 else return null; // No more commands
\r
273 SqliteStatement stmt = _statementList[index];
\r
274 stmt.BindParameters();
\r
280 /// Not implemented
\r
282 public override void Cancel()
\r
284 if (_activeReader != null)
\r
286 SqliteDataReader reader = _activeReader.Target as SqliteDataReader;
\r
287 if (reader != null)
\r
293 /// The SQL command text associated with the command
\r
295 #if !PLATFORM_COMPACTFRAMEWORK
\r
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")]
\r
298 public override string CommandText
\r
302 return _commandText;
\r
306 if (_commandText == value) return;
\r
308 if (_activeReader != null && _activeReader.IsAlive)
\r
310 throw new InvalidOperationException("Cannot set CommandText while a DataReader is active");
\r
314 _commandText = value;
\r
316 if (_cnn == null) return;
\r
321 /// The amount of time to wait for the connection to become available before erroring out
\r
323 #if !PLATFORM_COMPACTFRAMEWORK
\r
324 [DefaultValue((int)30)]
\r
326 public override int CommandTimeout
\r
330 return _commandTimeout;
\r
334 _commandTimeout = value;
\r
339 /// The type of the command. SQLite only supports CommandType.Text
\r
341 #if !PLATFORM_COMPACTFRAMEWORK
\r
342 [RefreshProperties(RefreshProperties.All), DefaultValue(CommandType.Text)]
\r
344 public override CommandType CommandType
\r
348 return CommandType.Text;
\r
352 if (value != CommandType.Text)
\r
354 throw new NotSupportedException();
\r
360 /// Forwards to the local CreateParameter() function
\r
362 /// <returns></returns>
\r
363 protected override DbParameter CreateDbParameter()
\r
365 return CreateParameter();
\r
369 /// Create a new parameter
\r
371 /// <returns></returns>
\r
372 public new SqliteParameter CreateParameter()
\r
374 return new SqliteParameter();
\r
378 /// The connection associated with this command
\r
380 #if !PLATFORM_COMPACTFRAMEWORK
\r
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")]
\r
383 public new SqliteConnection Connection
\r
385 get { return _cnn; }
\r
388 if (_activeReader != null && _activeReader.IsAlive)
\r
389 throw new InvalidOperationException("Cannot set Connection while a DataReader is active");
\r
394 //_cnn.RemoveCommand(this);
\r
399 _version = _cnn._version;
\r
401 //if (_cnn != null)
\r
402 // _cnn.AddCommand(this);
\r
407 /// Forwards to the local Connection property
\r
409 protected override DbConnection DbConnection
\r
417 Connection = (SqliteConnection)value;
\r
422 /// Returns the SqliteParameterCollection for the given command
\r
424 #if !PLATFORM_COMPACTFRAMEWORK
\r
425 [DesignerSerializationVisibility(DesignerSerializationVisibility.Content)]
\r
427 public new SqliteParameterCollection Parameters
\r
429 get { return _parameterCollection; }
\r
433 /// Forwards to the local Parameters property
\r
435 protected override DbParameterCollection DbParameterCollection
\r
444 /// The transaction associated with this command. SQLite only supports one transaction per connection, so this property forwards to the
\r
445 /// command's underlying connection.
\r
447 #if !PLATFORM_COMPACTFRAMEWORK
\r
448 [Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
\r
450 public new SqliteTransaction Transaction
\r
452 get { return _transaction; }
\r
457 if (_activeReader != null && _activeReader.IsAlive)
\r
458 throw new InvalidOperationException("Cannot set Transaction while a DataReader is active");
\r
462 if (value._cnn != _cnn)
\r
463 throw new ArgumentException("Transaction is not associated with the command's connection");
\r
465 _transaction = value;
\r
469 Connection = value.Connection;
\r
470 _transaction = value;
\r
476 /// Forwards to the local Transaction property
\r
478 protected override DbTransaction DbTransaction
\r
482 return Transaction;
\r
486 Transaction = (SqliteTransaction)value;
\r
491 /// This function ensures there are no active readers, that we have a valid connection,
\r
492 /// that the connection is open, that all statements are prepared and all parameters are assigned
\r
493 /// in preparation for allocating a data reader.
\r
495 private void InitializeForReader()
\r
497 if (_activeReader != null && _activeReader.IsAlive)
\r
498 throw new InvalidOperationException("DataReader already active on this command");
\r
501 throw new InvalidOperationException("No connection associated with this command");
\r
503 if (_cnn.State != ConnectionState.Open)
\r
504 throw new InvalidOperationException("Database is not open");
\r
506 // If the version of the connection has changed, clear out any previous commands before starting
\r
507 if (_cnn._version != _version)
\r
509 _version = _cnn._version;
\r
513 // Map all parameters for statements already built
\r
514 _parameterCollection.MapParameters(null);
\r
516 //// Set the default command timeout
\r
517 //_cnn._sql.SetTimeout(_commandTimeout * 1000);
\r
521 /// Creates a new SqliteDataReader to execute/iterate the array of SQLite prepared statements
\r
523 /// <param name="behavior">The behavior the data reader should adopt</param>
\r
524 /// <returns>Returns a SqliteDataReader object</returns>
\r
525 protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior)
\r
527 return ExecuteReader(behavior);
\r
531 /// Overrides the default behavior to return a SqliteDataReader specialization class
\r
533 /// <param name="behavior">The flags to be associated with the reader</param>
\r
534 /// <returns>A SqliteDataReader</returns>
\r
535 public new SqliteDataReader ExecuteReader(CommandBehavior behavior)
\r
537 InitializeForReader();
\r
539 SqliteDataReader rd = new SqliteDataReader(this, behavior);
\r
540 _activeReader = new WeakReference(rd, false);
\r
546 /// Overrides the default behavior of DbDataReader to return a specialized SqliteDataReader class
\r
548 /// <returns>A SqliteDataReader</returns>
\r
549 public new SqliteDataReader ExecuteReader()
\r
551 return ExecuteReader(CommandBehavior.Default);
\r
555 /// Called by the SqliteDataReader when the data reader is closed.
\r
557 internal void ClearDataReader()
\r
559 _activeReader = null;
\r
563 /// Execute the command and return the number of rows inserted/updated affected by it.
\r
565 /// <returns></returns>
\r
566 public override int ExecuteNonQuery()
\r
568 using (SqliteDataReader reader = ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult))
\r
570 while (reader.NextResult()) ;
\r
571 return reader.RecordsAffected;
\r
576 /// Execute the command and return the first column of the first row of the resultset
\r
577 /// (if present), or null if no resultset was returned.
\r
579 /// <returns>The first column of the first row of the first resultset from the query</returns>
\r
580 public override object ExecuteScalar()
\r
582 using (SqliteDataReader reader = ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult))
\r
591 /// Does nothing. Commands are prepared as they are executed the first time, and kept in prepared state afterwards.
\r
593 public override void Prepare()
\r
598 /// Sets the method the SqliteCommandBuilder uses to determine how to update inserted or updated rows in a DataTable.
\r
600 [DefaultValue(UpdateRowSource.None)]
\r
601 public override UpdateRowSource UpdatedRowSource
\r
605 return _updateRowSource;
\r
609 _updateRowSource = value;
\r
614 /// Determines if the command is visible at design time. Defaults to True.
\r
616 #if !PLATFORM_COMPACTFRAMEWORK
\r
617 [DesignOnly(true), Browsable(false), DefaultValue(true), EditorBrowsable(EditorBrowsableState.Never)]
\r
619 public override bool DesignTimeVisible
\r
623 return _designTimeVisible;
\r
627 _designTimeVisible = value;
\r
628 #if !PLATFORM_COMPACTFRAMEWORK
\r
629 TypeDescriptor.Refresh(this);
\r
635 /// Clones a command, including all its parameters
\r
637 /// <returns>A new SqliteCommand with the same commandtext, connection and parameters</returns>
\r
638 public object Clone()
\r
640 return new SqliteCommand(this);
\r