/******************************************************** * ADO.NET 2.0 Data Provider for SQLite Version 3.X * Written by Robert Simpson (robert@blackcastlesoft.com) * * Released to the public domain, use at your own risk! ********************************************************/ namespace System.Data.SQLite { using System; using System.Data; using System.Data.Common; using System.Collections.Generic; using System.ComponentModel; /// /// SQLite implementation of DbCommand. /// #if !PLATFORM_COMPACTFRAMEWORK [Designer("SQLite.Designer.SQLiteCommandDesigner, SQLite.Designer, Version=1.0.38.1, Culture=neutral, PublicKeyToken=db937bc2d44ff139"), ToolboxItem(true)] #endif public sealed class SQLiteCommand : DbCommand, ICloneable { /// /// The command text this command is based on /// private string _commandText; /// /// The connection the command is associated with /// private SQLiteConnection _cnn; /// /// The version of the connection the command is associated with /// private long _version; /// /// Indicates whether or not a DataReader is active on the command. /// private WeakReference _activeReader; /// /// The timeout for the command, kludged because SQLite doesn't support per-command timeout values /// internal int _commandTimeout; /// /// Designer support /// private bool _designTimeVisible; /// /// Used by DbDataAdapter to determine updating behavior /// private UpdateRowSource _updateRowSource; /// /// The collection of parameters for the command /// private SQLiteParameterCollection _parameterCollection; /// /// The SQL command text, broken into individual SQL statements as they are executed /// internal List _statementList; /// /// Unprocessed SQL text that has not been executed /// internal string _remainingText; /// /// Transaction associated with this command /// private SQLiteTransaction _transaction; /// /// Constructs a new SQLiteCommand /// /// /// Default constructor /// public SQLiteCommand() :this(null, null) { } /// /// Initializes the command with the given command text /// /// The SQL command text public SQLiteCommand(string commandText) : this(commandText, null, null) { } /// /// Initializes the command with the given SQL command text and attach the command to the specified /// connection. /// /// The SQL command text /// The connection to associate with the command public SQLiteCommand(string commandText, SQLiteConnection connection) : this(commandText, connection, null) { } /// /// Initializes the command and associates it with the specified connection. /// /// The connection to associate with the command public SQLiteCommand(SQLiteConnection connection) : this(null, connection, null) { } private SQLiteCommand(SQLiteCommand source) : this(source.CommandText, source.Connection, source.Transaction) { CommandTimeout = source.CommandTimeout; DesignTimeVisible = source.DesignTimeVisible; UpdatedRowSource = source.UpdatedRowSource; foreach (SQLiteParameter param in source._parameterCollection) { Parameters.Add(param.Clone()); } } /// /// Initializes a command with the given SQL, connection and transaction /// /// The SQL command text /// The connection to associate with the command /// The transaction the command should be associated with public SQLiteCommand(string commandText, SQLiteConnection connection, SQLiteTransaction transaction) { _commandTimeout = 30; _parameterCollection = new SQLiteParameterCollection(this); _designTimeVisible = true; _updateRowSource = UpdateRowSource.None; if (commandText != null) CommandText = commandText; if (connection != null) { DbConnection = connection; _commandTimeout = connection.DefaultTimeout; } if (transaction != null) Transaction = transaction; } /// /// Disposes of the command and clears all member variables /// /// Whether or not the class is being explicitly or implicitly disposed protected override void Dispose(bool disposing) { base.Dispose(disposing); if (disposing) { // If a reader is active on this command, don't destroy the command, instead let the reader do it SQLiteDataReader reader = null; if (_activeReader != null) { try { reader = _activeReader.Target as SQLiteDataReader; } catch(InvalidOperationException) { } } if (reader != null) { reader._disposeCommand = true; _activeReader = null; return; } Connection = null; _parameterCollection.Clear(); _commandText = null; } } /// /// Clears and destroys all statements currently prepared /// internal void ClearCommands() { if (_activeReader != null) { SQLiteDataReader reader = null; try { reader = _activeReader.Target as SQLiteDataReader; } catch(InvalidOperationException) { } if (reader != null) reader.Close(); _activeReader = null; } if (_statementList == null) return; int x = _statementList.Count; for (int n = 0; n < x; n++) _statementList[n].Dispose(); _statementList = null; _parameterCollection.Unbind(); } /// /// Builds an array of prepared statements for each complete SQL statement in the command text /// internal SQLiteStatement BuildNextCommand() { SQLiteStatement stmt = null; try { if (_statementList == null) _remainingText = _commandText; stmt = _cnn._sql.Prepare(_cnn, _remainingText, (_statementList == null) ? null : _statementList[_statementList.Count - 1], (uint)(_commandTimeout * 1000), out _remainingText); if (stmt != null) { stmt._command = this; if (_statementList == null) _statementList = new List(); _statementList.Add(stmt); _parameterCollection.MapParameters(stmt); stmt.BindParameters(); } return stmt; } catch (Exception) { if (stmt != null) { if (_statementList.Contains(stmt)) _statementList.Remove(stmt); stmt.Dispose(); } // If we threw an error compiling the statement, we cannot continue on so set the remaining text to null. _remainingText = null; throw; } } internal SQLiteStatement GetStatement(int index) { // Haven't built any statements yet if (_statementList == null) return BuildNextCommand(); // If we're at the last built statement and want the next unbuilt statement, then build it if (index == _statementList.Count) { if (String.IsNullOrEmpty(_remainingText) == false) return BuildNextCommand(); else return null; // No more commands } SQLiteStatement stmt = _statementList[index]; stmt.BindParameters(); return stmt; } /// /// Not implemented /// public override void Cancel() { if (_activeReader != null) { SQLiteDataReader reader = _activeReader.Target as SQLiteDataReader; if (reader != null) reader.Cancel(); } } /// /// The SQL command text associated with the command /// #if !PLATFORM_COMPACTFRAMEWORK [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")] #endif public override string CommandText { get { return _commandText; } set { if (_commandText == value) return; if (_activeReader != null && _activeReader.IsAlive) { throw new InvalidOperationException("Cannot set CommandText while a DataReader is active"); } ClearCommands(); _commandText = value; if (_cnn == null) return; } } /// /// The amount of time to wait for the connection to become available before erroring out /// #if !PLATFORM_COMPACTFRAMEWORK [DefaultValue((int)30)] #endif public override int CommandTimeout { get { return _commandTimeout; } set { _commandTimeout = value; } } /// /// The type of the command. SQLite only supports CommandType.Text /// #if !PLATFORM_COMPACTFRAMEWORK [RefreshProperties(RefreshProperties.All), DefaultValue(CommandType.Text)] #endif public override CommandType CommandType { get { return CommandType.Text; } set { if (value != CommandType.Text) { throw new NotSupportedException(); } } } /// /// Forwards to the local CreateParameter() function /// /// protected override DbParameter CreateDbParameter() { return CreateParameter(); } /// /// Create a new parameter /// /// public new SQLiteParameter CreateParameter() { return new SQLiteParameter(); } /// /// The connection associated with this command /// #if !PLATFORM_COMPACTFRAMEWORK [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")] #endif public new SQLiteConnection Connection { get { return _cnn; } set { if (_activeReader != null && _activeReader.IsAlive) throw new InvalidOperationException("Cannot set Connection while a DataReader is active"); if (_cnn != null) { ClearCommands(); //_cnn.RemoveCommand(this); } _cnn = value; if (_cnn != null) _version = _cnn._version; //if (_cnn != null) // _cnn.AddCommand(this); } } /// /// Forwards to the local Connection property /// protected override DbConnection DbConnection { get { return Connection; } set { Connection = (SQLiteConnection)value; } } /// /// Returns the SQLiteParameterCollection for the given command /// #if !PLATFORM_COMPACTFRAMEWORK [DesignerSerializationVisibility(DesignerSerializationVisibility.Content)] #endif public new SQLiteParameterCollection Parameters { get { return _parameterCollection; } } /// /// Forwards to the local Parameters property /// protected override DbParameterCollection DbParameterCollection { get { return Parameters; } } /// /// The transaction associated with this command. SQLite only supports one transaction per connection, so this property forwards to the /// command's underlying connection. /// #if !PLATFORM_COMPACTFRAMEWORK [Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)] #endif public new SQLiteTransaction Transaction { get { return _transaction; } set { if (_cnn != null) { if (_activeReader != null && _activeReader.IsAlive) throw new InvalidOperationException("Cannot set Transaction while a DataReader is active"); if (value != null) { if (value._cnn != _cnn) throw new ArgumentException("Transaction is not associated with the command's connection"); } _transaction = value; } else { if (value != null) Connection = value.Connection; _transaction = value; } } } /// /// Forwards to the local Transaction property /// protected override DbTransaction DbTransaction { get { return Transaction; } set { Transaction = (SQLiteTransaction)value; } } /// /// This function ensures there are no active readers, that we have a valid connection, /// that the connection is open, that all statements are prepared and all parameters are assigned /// in preparation for allocating a data reader. /// private void InitializeForReader() { if (_activeReader != null && _activeReader.IsAlive) throw new InvalidOperationException("DataReader already active on this command"); if (_cnn == null) throw new InvalidOperationException("No connection associated with this command"); if (_cnn.State != ConnectionState.Open) throw new InvalidOperationException("Database is not open"); // If the version of the connection has changed, clear out any previous commands before starting if (_cnn._version != _version) { _version = _cnn._version; ClearCommands(); } // Map all parameters for statements already built _parameterCollection.MapParameters(null); //// Set the default command timeout //_cnn._sql.SetTimeout(_commandTimeout * 1000); } /// /// Creates a new SQLiteDataReader to execute/iterate the array of SQLite prepared statements /// /// The behavior the data reader should adopt /// Returns a SQLiteDataReader object protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior) { return ExecuteReader(behavior); } /// /// Overrides the default behavior to return a SQLiteDataReader specialization class /// /// The flags to be associated with the reader /// A SQLiteDataReader public new SQLiteDataReader ExecuteReader(CommandBehavior behavior) { InitializeForReader(); SQLiteDataReader rd = new SQLiteDataReader(this, behavior); _activeReader = new WeakReference(rd, false); return rd; } /// /// Overrides the default behavior of DbDataReader to return a specialized SQLiteDataReader class /// /// A SQLiteDataReader public new SQLiteDataReader ExecuteReader() { return ExecuteReader(CommandBehavior.Default); } /// /// Called by the SQLiteDataReader when the data reader is closed. /// internal void ClearDataReader() { _activeReader = null; } /// /// Execute the command and return the number of rows inserted/updated affected by it. /// /// public override int ExecuteNonQuery() { using (SQLiteDataReader reader = ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult)) { while (reader.NextResult()) ; return reader.RecordsAffected; } } /// /// Execute the command and return the first column of the first row of the resultset /// (if present), or null if no resultset was returned. /// /// The first column of the first row of the first resultset from the query public override object ExecuteScalar() { using (SQLiteDataReader reader = ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult)) { if (reader.Read()) return reader[0]; } return null; } /// /// Does nothing. Commands are prepared as they are executed the first time, and kept in prepared state afterwards. /// public override void Prepare() { } /// /// Sets the method the SQLiteCommandBuilder uses to determine how to update inserted or updated rows in a DataTable. /// [DefaultValue(UpdateRowSource.None)] public override UpdateRowSource UpdatedRowSource { get { return _updateRowSource; } set { _updateRowSource = value; } } /// /// Determines if the command is visible at design time. Defaults to True. /// #if !PLATFORM_COMPACTFRAMEWORK [DesignOnly(true), Browsable(false), DefaultValue(true), EditorBrowsable(EditorBrowsableState.Never)] #endif public override bool DesignTimeVisible { get { return _designTimeVisible; } set { _designTimeVisible = value; #if !PLATFORM_COMPACTFRAMEWORK TypeDescriptor.Refresh(this); #endif } } /// /// Clones a command, including all its parameters /// /// A new SQLiteCommand with the same commandtext, connection and parameters public object Clone() { return new SQLiteCommand(this); } } }