/********************************************************
* 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.Diagnostics;
using System.Collections.Generic;
using System.ComponentModel;
using System.Globalization;
using System.Text;
/// <summary>
/// SQLite implementation of DbCommand.
/// </summary>
#if !PLATFORM_COMPACTFRAMEWORK
[Designer("SQLite.Designer.SQLiteCommandDesigner, SQLite.Designer, Version=" + SQLite3.DesignerVersion + ", Culture=neutral, PublicKeyToken=db937bc2d44ff139"), ToolboxItem(true)]
#endif
public sealed class SQLiteCommand : DbCommand, ICloneable
{
/// <summary>
/// These are the extra command behavior flags that should be used for all calls
/// into the <see cref="ExecuteNonQuery()" />, <see cref="ExecuteScalar()" />,
/// and <see cref="ExecuteReader()" /> methods.
/// </summary>
public static CommandBehavior? GlobalCommandBehaviors = null;
/// <summary>
/// The default connection string to be used when creating a temporary
/// connection to execute a command via the static
/// <see cref="Execute(string,SQLiteExecuteType,string,object[])" /> or
/// <see cref="Execute(string,SQLiteExecuteType,CommandBehavior,string,object[])" />
/// methods.
/// </summary>
internal static readonly string DefaultConnectionString = "Data Source=:memory:;";
/// <summary>
/// The command text this command is based on
/// </summary>
private string _commandText;
/// <summary>
/// The connection the command is associated with
/// </summary>
private SQLiteConnection _cnn;
/// <summary>
/// The version of the connection the command is associated with
/// </summary>
private int _version;
/// <summary>
/// Indicates whether or not a DataReader is active on the command.
/// </summary>
private WeakReference _activeReader;
/// <summary>
/// The timeout for the command, kludged because SQLite doesn't support per-command timeout values
/// </summary>
internal int _commandTimeout;
/// <summary>
/// The maximum amount of time to sleep when retrying a call to prepare or step for the current command.
/// </summary>
internal int _maximumSleepTime;
/// <summary>
/// Designer support
/// </summary>
private bool _designTimeVisible;
/// <summary>
/// Used by DbDataAdapter to determine updating behavior
/// </summary>
private UpdateRowSource _updateRowSource;
/// <summary>
/// The collection of parameters for the command
/// </summary>
private SQLiteParameterCollection _parameterCollection;
/// <summary>
/// The SQL command text, broken into individual SQL statements as they are executed
/// </summary>
internal List<SQLiteStatement> _statementList;
/// <summary>
/// Unprocessed SQL text that has not been executed
/// </summary>
internal string _remainingText;
/// <summary>
/// Transaction associated with this command
/// </summary>
private SQLiteTransaction _transaction;
static SQLiteCommand()
{
InitializeGlobalCommandBehaviors();
}
///<overloads>
/// Constructs a new SQLiteCommand
/// </overloads>
/// <summary>
/// Default constructor
/// </summary>
public SQLiteCommand() :this(null, null)
{
}
/// <summary>
/// Initializes the command with the given command text
/// </summary>
/// <param name="commandText">The SQL command text</param>
public SQLiteCommand(string commandText)
: this(commandText, null, null)
{
}
/// <summary>
/// Initializes the command with the given SQL command text and attach the command to the specified
/// connection.
/// </summary>
/// <param name="commandText">The SQL command text</param>
/// <param name="connection">The connection to associate with the command</param>
public SQLiteCommand(string commandText, SQLiteConnection connection)
: this(commandText, connection, null)
{
}
/// <summary>
/// Initializes the command and associates it with the specified connection.
/// </summary>
/// <param name="connection">The connection to associate with the command</param>
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());
}
}
/// <summary>
/// Initializes a command with the given SQL, connection and transaction
/// </summary>
/// <param name="commandText">The SQL command text</param>
/// <param name="connection">The connection to associate with the command</param>
/// <param name="transaction">The transaction the command should be associated with</param>
public SQLiteCommand(string commandText, SQLiteConnection connection, SQLiteTransaction transaction)
{
_commandTimeout = 30;
_maximumSleepTime = 150;
_parameterCollection = new SQLiteParameterCollection(this);
_designTimeVisible = true;
_updateRowSource = UpdateRowSource.None;
if (commandText != null)
CommandText = commandText;
if (connection != null)
{
DbConnection = connection;
_commandTimeout = connection.DefaultTimeout;
_maximumSleepTime = connection.DefaultMaximumSleepTime;
}
if (transaction != null)
Transaction = transaction;
if (SQLiteConnection.CanOnChanged(connection, false))
{
SQLiteConnection.OnChanged(connection, new ConnectionEventArgs(
SQLiteConnectionEventType.NewCommand, null, transaction, this,
null, null, null, null));
}
}
///////////////////////////////////////////////////////////////////////////////////////////////
[Conditional("CHECK_STATE")]
internal static void Check(SQLiteCommand command)
{
if (command == null)
throw new ArgumentNullException("command");
command.CheckDisposed();
SQLiteConnection.Check(command._cnn);
}
///////////////////////////////////////////////////////////////////////////////////////////////
#region IDisposable "Pattern" Members
private bool disposed;
private void CheckDisposed() /* throw */
{
#if THROW_ON_DISPOSED
if (disposed)
throw new ObjectDisposedException(typeof(SQLiteCommand).Name);
#endif
}
///////////////////////////////////////////////////////////////////////////////////////////////
/// <summary>
/// Disposes of the command and clears all member variables
/// </summary>
/// <param name="disposing">Whether or not the class is being explicitly or implicitly disposed</param>
protected override void Dispose(bool disposing)
{
if (SQLiteConnection.CanOnChanged(_cnn, false))
{
SQLiteConnection.OnChanged(_cnn, new ConnectionEventArgs(
SQLiteConnectionEventType.DisposingCommand, null, _transaction, this,
null, null, null, new object[] { disposing, disposed }));
}
bool skippedDispose = false;
try
{
if (!disposed)
{
if (disposing)
{
////////////////////////////////////
// dispose managed resources here...
////////////////////////////////////
//
// NOTE: If there is an active reader on
// us, let it perform the statement
// cleanup.
//
SQLiteDataReader reader = null;
if (_activeReader != null)
{
try
{
reader = _activeReader.Target as SQLiteDataReader;
}
catch (InvalidOperationException)
{
// do nothing.
}
}
if (reader != null)
{
reader._disposeCommand = true;
if (HelperMethods.HasFlags(reader._flags,
SQLiteConnectionFlags.AggressiveDisposal))
{
//
// HACK: Copy the statement list to our active reader,
// after adding a reference to each one of the
// valid statements.
//
if (_statementList != null)
{
List<SQLiteStatement> statements =
new List<SQLiteStatement>();
foreach (SQLiteStatement statement in _statementList)
{
if (statement == null) continue;
statement.AddReference();
statements.Add(statement);
}
reader._statementList = statements;
}
else
{
reader._statementList = null;
}
}
_activeReader = null;
skippedDispose = true;
return;
}
Connection = null;
_parameterCollection.Clear();
_commandText = null;
}
//////////////////////////////////////
// release unmanaged resources here...
//////////////////////////////////////
}
}
finally
{
if (!skippedDispose)
{
base.Dispose(disposing);
//
// NOTE: Everything should be fully disposed at this point.
//
disposed = true;
}
}
}
#endregion
///////////////////////////////////////////////////////////////////////////////////////////////
internal static SQLiteConnectionFlags GetFlags(
SQLiteCommand command
)
{
try
{
if (command != null)
{
SQLiteConnection cnn = command._cnn;
if (cnn != null)
return cnn.Flags;
}
}
catch (ObjectDisposedException)
{
// do nothing.
}
return SQLiteConnectionFlags.Default;
}
///////////////////////////////////////////////////////////////////////////////////////////////
internal static int GetPrepareRetries(
SQLiteCommand command
)
{
try
{
if (command != null)
{
SQLiteConnection cnn = command._cnn;
if (cnn != null)
return cnn.PrepareRetries;
}
}
catch (ObjectDisposedException)
{
// do nothing.
}
return SQLiteConnection.DefaultPrepareRetries;
}
///////////////////////////////////////////////////////////////////////////////////////////////
internal static int GetStepRetries(
SQLiteCommand command
)
{
try
{
if (command != null)
{
SQLiteConnection cnn = command._cnn;
if (cnn != null)
return cnn.StepRetries;
}
}
catch (ObjectDisposedException)
{
// do nothing.
}
return SQLiteConnection.DefaultStepRetries;
}
///////////////////////////////////////////////////////////////////////////////////////////////
internal static int GetMaximumSleepTime(
SQLiteCommand command
)
{
try
{
if (command != null)
return command._maximumSleepTime;
}
catch (ObjectDisposedException)
{
// do nothing.
}
return SQLiteConnection.DefaultConnectionMaximumSleepTime;
}
///////////////////////////////////////////////////////////////////////////////////////////////
/// <summary>
/// Modifies the specified <see cref="CommandBehavior" /> to include
/// the global command behavior flags, if any.
/// </summary>
/// <param name="behavior">
/// The <see cref="CommandBehavior" /> as it was originally passed into
/// the <see cref="ExecuteNonQuery()" />, <see cref="ExecuteScalar()" />,
/// or <see cref="ExecuteReader()" /> methods.
/// </param>
private void MaybeAddGlobalCommandBehaviors(
ref CommandBehavior behavior /* in, out */
)
{
CommandBehavior? globalBehaviors = GlobalCommandBehaviors;
if (globalBehaviors != null)
behavior |= (CommandBehavior)globalBehaviors;
}
///////////////////////////////////////////////////////////////////////////////////////////////
private static void InitializeGlobalCommandBehaviors()
{
string value = UnsafeNativeMethods.GetSettingValue(
"SQLite_GlobalCommandBehaviors", null);
if (value != null)
{
CommandBehavior? behavior;
string error = null;
behavior = CombineBehaviors(
GlobalCommandBehaviors, value, out error);
if (behavior != null)
{
GlobalCommandBehaviors = behavior;
}
#if !NET_COMPACT_20 && TRACE_WARNING
else
{
HelperMethods.Trace(HelperMethods.StringFormat(
CultureInfo.CurrentCulture,
"WARNING: Could not initialize global command behaviors: {0}",
error), TraceCategory.Warning);
}
#endif
}
}
///////////////////////////////////////////////////////////////////////////////////////////////
private void DisposeStatements()
{
DisposeStatements(true, ref _statementList);
}
///////////////////////////////////////////////////////////////////////////////////////////////
internal static void DisposeStatements(
bool force,
ref List<SQLiteStatement> statements
)
{
if (statements == null) return;
int count = statements.Count;
for (int index = 0; index < count; index++)
{
SQLiteStatement statement = statements[index];
if (statement == null) continue;
if ((statement.RemoveReference() <= 0) || force)
statement.Dispose();
}
statements.Clear();
statements = null;
}
///////////////////////////////////////////////////////////////////////////////////////////////
private void ClearDataReader()
{
if (_activeReader != null)
{
SQLiteDataReader reader = null;
try
{
reader = _activeReader.Target as SQLiteDataReader;
}
catch (InvalidOperationException)
{
// do nothing.
}
if (reader != null)
reader.Close(); /* Dispose */
_activeReader = null;
}
}
///////////////////////////////////////////////////////////////////////////////////////////////
/// <summary>
/// Clears and destroys all statements currently prepared
/// </summary>
internal void ClearCommands()
{
ClearDataReader();
DisposeStatements();
_parameterCollection.Unbind();
}
/// <summary>
/// Builds an array of prepared statements for each complete SQL statement in the command text
/// </summary>
internal SQLiteStatement BuildNextCommand()
{
SQLiteStatement stmt = null;
try
{
if ((_cnn != null) && (_cnn._sql != null))
{
if (_statementList == null)
_remainingText = _commandText;
stmt = _cnn._sql.Prepare(_cnn, this, _remainingText, (_statementList == null) ? null : _statementList[_statementList.Count - 1], (uint)(_commandTimeout * 1000), ref _remainingText);
if (stmt != null)
{
stmt.AddReference();
stmt._command = this;
if (_statementList == null)
_statementList = new List<SQLiteStatement>();
_statementList.Add(stmt);
_parameterCollection.MapParameters(stmt);
stmt.BindParameters();
}
}
return stmt;
}
catch (Exception)
{
if (stmt != null)
{
if ((_statementList != null) &&
_statementList.Contains(stmt))
{
_statementList.Remove(stmt);
}
if ((stmt.RemoveReference() <= 0) ||
!HelperMethods.HasFlags(
SQLiteConnection.GetFlags(_cnn),
SQLiteConnectionFlags.AggressiveDisposal))
{
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;
}
/// <summary>
/// Not implemented
/// </summary>
public override void Cancel()
{
CheckDisposed();
if (_activeReader != null)
{
SQLiteDataReader reader = _activeReader.Target as SQLiteDataReader;
if (reader != null)
reader.Cancel();
}
}
/// <summary>
/// The SQL command text associated with the command
/// </summary>
#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
{
CheckDisposed();
return _commandText;
}
set
{
CheckDisposed();
string newCommandText = value;
if (SQLiteConnection.CanOnChanged(_cnn, false))
{
ConnectionEventArgs previewEventArgs = new ConnectionEventArgs(
SQLiteConnectionEventType.SqlStringPreview,
null, null, null, null, null, null, null, null);
previewEventArgs.Result = newCommandText;
SQLiteConnection.OnChanged(_cnn, previewEventArgs);
newCommandText = previewEventArgs.Result;
}
if (_commandText == newCommandText) return;
if (_activeReader != null && _activeReader.IsAlive)
{
throw new InvalidOperationException("Cannot set CommandText while a DataReader is active");
}
ClearCommands();
_commandText = newCommandText;
if (_cnn == null) return;
}
}
/// <summary>
/// The amount of time to wait for the connection to become available before erroring out
/// </summary>
#if !PLATFORM_COMPACTFRAMEWORK
[DefaultValue((int)30)]
#endif
public override int CommandTimeout
{
get
{
CheckDisposed();
return _commandTimeout;
}
set
{
CheckDisposed();
_commandTimeout = value;
}
}
/// <summary>
/// The maximum amount of time to sleep when retrying a call to prepare or step for the
/// current command.
/// </summary>
#if !PLATFORM_COMPACTFRAMEWORK
[DefaultValue((int)150)]
#endif
public int MaximumSleepTime
{
get
{
CheckDisposed();
return _maximumSleepTime;
}
set
{
CheckDisposed();
_maximumSleepTime = value;
}
}
/// <summary>
/// The type of the command. SQLite only supports CommandType.Text
/// </summary>
#if !PLATFORM_COMPACTFRAMEWORK
[RefreshProperties(RefreshProperties.All), DefaultValue(CommandType.Text)]
#endif
public override CommandType CommandType
{
get
{
CheckDisposed();
return CommandType.Text;
}
set
{
CheckDisposed();
if (value != CommandType.Text)
{
throw new NotSupportedException();
}
}
}
/// <summary>
/// Forwards to the local CreateParameter() function
/// </summary>
/// <returns></returns>
protected override DbParameter CreateDbParameter()
{
return CreateParameter();
}
/// <summary>
/// Create a new parameter
/// </summary>
/// <returns></returns>
public new SQLiteParameter CreateParameter()
{
CheckDisposed();
return new SQLiteParameter(this);
}
/// <summary>
/// The connection associated with this command
/// </summary>
#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 { CheckDisposed(); return _cnn; }
set
{
CheckDisposed();
if (_activeReader != null && _activeReader.IsAlive)
throw new InvalidOperationException("Cannot set Connection while a DataReader is active");
if (Object.ReferenceEquals(_cnn, value))
return;
ClearCommands();
_cnn = value;
if (_cnn != null)
_version = _cnn._version;
}
}
/// <summary>
/// Forwards to the local Connection property
/// </summary>
protected override DbConnection DbConnection
{
get
{
return Connection;
}
set
{
Connection = (SQLiteConnection)value;
}
}
/// <summary>
/// Returns the SQLiteParameterCollection for the given command
/// </summary>
#if !PLATFORM_COMPACTFRAMEWORK
[DesignerSerializationVisibility(DesignerSerializationVisibility.Content)]
#endif
public new SQLiteParameterCollection Parameters
{
get { CheckDisposed(); return _parameterCollection; }
}
/// <summary>
/// Forwards to the local Parameters property
/// </summary>
protected override DbParameterCollection DbParameterCollection
{
get
{
return Parameters;
}
}
/// <summary>
/// The transaction associated with this command. SQLite only supports one transaction per connection, so this property forwards to the
/// command's underlying connection.
/// </summary>
#if !PLATFORM_COMPACTFRAMEWORK
[Browsable(false), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
#endif
public new SQLiteTransaction Transaction
{
get { CheckDisposed(); return _transaction; }
set
{
CheckDisposed();
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;
}
}
}
/// <summary>
/// Forwards to the local Transaction property
/// </summary>
protected override DbTransaction DbTransaction
{
get
{
return Transaction;
}
set
{
Transaction = (SQLiteTransaction)value;
}
}
/// <summary>
/// Verifies that all SQL queries associated with the current command text
/// can be successfully compiled. A <see cref="SQLiteException" /> will be
/// raised if any errors occur.
/// </summary>
public void VerifyOnly()
{
CheckDisposed();
SQLiteConnection connection = _cnn;
SQLiteConnection.Check(connection); /* throw */
SQLiteBase sqlBase = connection._sql;
if ((connection == null) || (sqlBase == null))
throw new SQLiteException("invalid or unusable connection");
List<SQLiteStatement> statements = null;
SQLiteStatement currentStatement = null;
try
{
string text = _commandText;
uint timeout = (uint)(_commandTimeout * 1000);
SQLiteStatement previousStatement = null;
while ((text != null) && (text.Length > 0))
{
currentStatement = sqlBase.Prepare(
connection, this, text, previousStatement,
timeout, ref text); /* throw */
previousStatement = currentStatement;
if (currentStatement != null)
{
if (statements == null)
statements = new List<SQLiteStatement>();
statements.Add(currentStatement);
currentStatement = null;
}
if (text == null) continue;
text = text.Trim();
}
}
finally
{
if (currentStatement != null)
{
currentStatement.Dispose();
currentStatement = null;
}
if (statements != null)
{
foreach (SQLiteStatement statement in statements)
{
if (statement == null)
continue;
statement.Dispose();
}
statements.Clear();
statements = null;
}
}
}
/// <summary>
/// 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.
/// </summary>
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);
}
/// <summary>
/// Creates a new SQLiteDataReader to execute/iterate the array of SQLite prepared statements
/// </summary>
/// <param name="behavior">The behavior the data reader should adopt</param>
/// <returns>Returns a SQLiteDataReader object</returns>
protected override DbDataReader ExecuteDbDataReader(CommandBehavior behavior)
{
return ExecuteReader(behavior);
}
/// <summary>
/// This method creates a new connection, executes the query using the given
/// execution type, closes the connection, and returns the results. If the
/// connection string is null, a temporary in-memory database connection will
/// be used.
/// </summary>
/// <param name="commandText">
/// The text of the command to be executed.
/// </param>
/// <param name="executeType">
/// The execution type for the command. This is used to determine which method
/// of the command object to call, which then determines the type of results
/// returned, if any.
/// </param>
/// <param name="connectionString">
/// The connection string to the database to be opened, used, and closed. If
/// this parameter is null, a temporary in-memory database will be used.
/// </param>
/// <param name="args">
/// The SQL parameter values to be used when building the command object to be
/// executed, if any.
/// </param>
/// <returns>
/// The results of the query -OR- null if no results were produced from the
/// given execution type.
/// </returns>
public static object Execute(
string commandText,
SQLiteExecuteType executeType,
string connectionString,
params object[] args
)
{
return Execute(
commandText, executeType, CommandBehavior.Default,
connectionString, args);
}
/// <summary>
/// This method creates a new connection, executes the query using the given
/// execution type and command behavior, closes the connection unless a data
/// reader is created, and returns the results. If the connection string is
/// null, a temporary in-memory database connection will be used.
/// </summary>
/// <param name="commandText">
/// The text of the command to be executed.
/// </param>
/// <param name="executeType">
/// The execution type for the command. This is used to determine which method
/// of the command object to call, which then determines the type of results
/// returned, if any.
/// </param>
/// <param name="commandBehavior">
/// The command behavior flags for the command.
/// </param>
/// <param name="connectionString">
/// The connection string to the database to be opened, used, and closed. If
/// this parameter is null, a temporary in-memory database will be used.
/// </param>
/// <param name="args">
/// The SQL parameter values to be used when building the command object to be
/// executed, if any.
/// </param>
/// <returns>
/// The results of the query -OR- null if no results were produced from the
/// given execution type.
/// </returns>
public static object Execute(
string commandText,
SQLiteExecuteType executeType,
CommandBehavior commandBehavior,
string connectionString,
params object[] args
)
{
SQLiteConnection connection = null;
try
{
if (connectionString == null)
connectionString = DefaultConnectionString;
using (connection = new SQLiteConnection(connectionString))
{
connection.Open();
using (SQLiteCommand command = connection.CreateCommand())
{
command.CommandText = commandText;
if (args != null)
{
foreach (object arg in args)
{
SQLiteParameter parameter = arg as SQLiteParameter;
if (parameter == null)
{
parameter = command.CreateParameter();
parameter.DbType = DbType.Object;
parameter.Value = arg;
}
command.Parameters.Add(parameter);
}
}
switch (executeType)
{
case SQLiteExecuteType.None:
{
//
// NOTE: Do nothing.
//
break;
}
case SQLiteExecuteType.NonQuery:
{
return command.ExecuteNonQuery(commandBehavior);
}
case SQLiteExecuteType.Scalar:
{
return command.ExecuteScalar(commandBehavior);
}
case SQLiteExecuteType.Reader:
{
bool success = true;
try
{
//
// NOTE: The CloseConnection flag is being added here.
// This should force the returned data reader to
// close the connection when it is disposed. In
// order to prevent the containing using block
// from disposing the connection prematurely,
// the innermost finally block sets the internal
// no-disposal flag to true. The outer finally
// block will reset the internal no-disposal flag
// to false so that the data reader will be able
// to (eventually) dispose of the connection.
//
return command.ExecuteReader(
commandBehavior | CommandBehavior.CloseConnection);
}
catch
{
success = false;
throw;
}
finally
{
//
// NOTE: If an exception was not thrown, that can only
// mean the data reader was successfully created
// and now owns the connection. Therefore, set
// the internal no-disposal flag (temporarily)
// in order to exit the containing using block
// without disposing it.
//
if (success)
connection._noDispose = true;
}
}
}
}
}
}
finally
{
//
// NOTE: Now that the using block has been exited, reset the
// internal disposal flag for the connection. This is
// always done if the connection was created because
// it will be harmless whether or not the data reader
// now owns it.
//
if (connection != null)
connection._noDispose = false;
}
return null;
}
/// <summary>
/// This method executes a query using the given execution type and command
/// behavior and returns the results.
/// </summary>
/// <param name="commandText">
/// The text of the command to be executed.
/// </param>
/// <param name="executeType">
/// The execution type for the command. This is used to determine which method
/// of the command object to call, which then determines the type of results
/// returned, if any.
/// </param>
/// <param name="commandBehavior">
/// The command behavior flags for the command.
/// </param>
/// <param name="connection">
/// The connection used to create and execute the command.
/// </param>
/// <param name="args">
/// The SQL parameter values to be used when building the command object to be
/// executed, if any.
/// </param>
/// <returns>
/// The results of the query -OR- null if no results were produced from the
/// given execution type.
/// </returns>
public static object Execute(
string commandText,
SQLiteExecuteType executeType,
CommandBehavior commandBehavior,
SQLiteConnection connection,
params object[] args
)
{
SQLiteConnection.Check(connection);
using (SQLiteCommand command = connection.CreateCommand())
{
command.CommandText = commandText;
if (args != null)
{
foreach (object arg in args)
{
SQLiteParameter parameter = arg as SQLiteParameter;
if (parameter == null)
{
parameter = command.CreateParameter();
parameter.DbType = DbType.Object;
parameter.Value = arg;
}
command.Parameters.Add(parameter);
}
}
switch (executeType)
{
case SQLiteExecuteType.None:
{
//
// NOTE: Do nothing.
//
break;
}
case SQLiteExecuteType.NonQuery:
{
return command.ExecuteNonQuery(commandBehavior);
}
case SQLiteExecuteType.Scalar:
{
return command.ExecuteScalar(commandBehavior);
}
case SQLiteExecuteType.Reader:
{
return command.ExecuteReader(commandBehavior);
}
}
}
return null;
}
/// <summary>
/// Overrides the default behavior to return a SQLiteDataReader specialization class
/// </summary>
/// <param name="behavior">The flags to be associated with the reader.</param>
/// <returns>A SQLiteDataReader</returns>
public new SQLiteDataReader ExecuteReader(CommandBehavior behavior)
{
CheckDisposed();
SQLiteConnection.Check(_cnn);
InitializeForReader();
MaybeAddGlobalCommandBehaviors(ref behavior);
SQLiteDataReader rd = new SQLiteDataReader(this, behavior);
_activeReader = new WeakReference(rd, false);
return rd;
}
/// <summary>
/// Overrides the default behavior of DbDataReader to return a specialized SQLiteDataReader class
/// </summary>
/// <returns>A SQLiteDataReader</returns>
public new SQLiteDataReader ExecuteReader()
{
CheckDisposed();
SQLiteConnection.Check(_cnn);
return ExecuteReader(CommandBehavior.Default);
}
/// <summary>
/// Called by the SQLiteDataReader when the data reader is closed.
/// </summary>
internal void ResetDataReader()
{
_activeReader = null;
}
/// <summary>
/// Execute the command and return the number of rows inserted/updated affected by it.
/// </summary>
/// <returns>The number of rows inserted/updated affected by it.</returns>
public override int ExecuteNonQuery()
{
CheckDisposed();
SQLiteConnection.Check(_cnn);
return ExecuteNonQuery(CommandBehavior.Default);
}
/// <summary>
/// Execute the command and return the number of rows inserted/updated affected by it.
/// </summary>
/// <param name="behavior">The flags to be associated with the reader.</param>
/// <returns>The number of rows inserted/updated affected by it.</returns>
public int ExecuteNonQuery(
CommandBehavior behavior
)
{
CheckDisposed();
SQLiteConnection.Check(_cnn);
MaybeAddGlobalCommandBehaviors(ref behavior);
using (SQLiteDataReader reader = ExecuteReader(behavior |
CommandBehavior.SingleRow | CommandBehavior.SingleResult))
{
//
// BUGFIX: See PrivateMaybeReadRemaining comments.
//
PrivateMaybeReadRemaining(reader, behavior);
while (reader.NextResult()) ;
return reader.RecordsAffected;
}
}
///////////////////////////////////////////////////////////////////////
/// <summary>
/// This integer value is used with <see cref="CommandBehavior" />
/// values. When set, extra <see cref="SQLiteDataReader.Read" />
/// calls are not performed within the <see cref="ExecuteScalar()" />
/// methods for write transactions. This value should be used with
/// extreme care because it can cause unusual behavior. It is
/// intended for use only by legacy applications that rely on the
/// old, incorrect behavior.
/// </summary>
public const CommandBehavior SkipExtraReads =
(CommandBehavior)0x10000000;
/// <summary>
/// This integer value is used with <see cref="CommandBehavior" />
/// values. When set, extra <see cref="SQLiteDataReader.Read" />
/// calls are performed within the <see cref="ExecuteScalar()" />
/// methods for all transactions. This value should be used with
/// extreme care because it can cause unusual behavior.
/// </summary>
public const CommandBehavior ForceExtraReads =
(CommandBehavior)0x20000000;
/// <summary>
/// Checks to see if the extra <see cref="SQLiteDataReader.Read" />
/// calls within the <see cref="ExecuteScalar()" /> methods should
/// be skipped.
/// </summary>
/// <param name="behavior">
/// The behavior flags, exactly as they were passed into the
/// <see cref="ExecuteScalar()" /> methods.
/// </param>
/// <returns>
/// Non-zero if the extra reads should be skipped; otherwise, zero.
/// </returns>
private bool ShouldSkipExtraReads(
CommandBehavior behavior /* in */
)
{
return (behavior & SkipExtraReads) == SkipExtraReads;
}
/// <summary>
/// Checks to see if the extra <see cref="SQLiteDataReader.Read" />
/// calls within the <see cref="ExecuteScalar()" /> methods should
/// be forced.
/// </summary>
/// <param name="behavior">
/// The behavior flags, exactly as they were passed into the
/// <see cref="ExecuteScalar()" /> methods.
/// </param>
/// <returns>
/// Non-zero if the extra reads should be forced; otherwise, zero.
/// </returns>
private bool ShouldForceExtraReads(
CommandBehavior behavior /* in */
)
{
return (behavior & ForceExtraReads) == ForceExtraReads;
}
///////////////////////////////////////////////////////////////////////
/// <summary>
/// Attempts to combine an original <see cref="CommandBehavior" />
/// value with a list of new <see cref="CommandBehavior" /> values.
/// </summary>
/// <param name="behavior">
/// The original <see cref="CommandBehavior" /> value, if any. If
/// this value is null, a suitable default value will be used.
/// </param>
/// <param name="flags">
/// The list of new <see cref="CommandBehavior" /> values delimited
/// by spaces or commas. Each value may have an optional prefix, a
/// '+' or '-' sign. If the prefix is a '+', the value is added to
/// the original <see cref="CommandBehavior" /> value. If the
/// prefix is a '-', the value is removed from the original
/// <see cref="CommandBehavior" /> value. In addition to the values
/// formally defined for <see cref="CommandBehavior" />, the extra
/// values "SkipExtraReads" and "ForceExtraReads" are recognized.
/// Other extra values may be added in the future.
/// </param>
/// <param name="error">
/// Upon success, this will be set to null. Upon failure, this will
/// be set to an appropriate error message.
/// </param>
/// <returns>
/// The resulting <see cref="CommandBehavior" /> value -OR- null
/// if it cannot be determined due to an error -OR- null if the
/// original <see cref="CommandBehavior" /> value and list of new
/// <see cref="CommandBehavior" /> value are both null. The way to
/// differentiate between these two null return scenarios is to
/// check the error message against null. If the error message is
/// not null, an error was encountered; otherwise, the null was the
/// natural return value.
/// </returns>
public static CommandBehavior? CombineBehaviors(
CommandBehavior? behavior, /* in: OPTIONAL */
string flags, /* in: OPTIONAL */
out string error /* out */
)
{
error = null;
if (String.IsNullOrEmpty(flags))
return behavior;
string[] parts = flags.Split(' ', ',');
if (parts == null)
{
error = "could not split flags into parts";
return null;
}
CommandBehavior localBehavior = (behavior != null) ?
(CommandBehavior)behavior : (CommandBehavior)0;
int length = parts.Length;
bool add = true;
for (int index = 0; index < length; index++)
{
string part = parts[index];
if (part != null)
part = part.Trim();
if (String.IsNullOrEmpty(part))
continue;
char subPart = part[0];
if ((subPart == '+') || (subPart == '-'))
{
add = (subPart == '+');
part = part.Substring(1).Trim();
}
if (String.IsNullOrEmpty(part))
continue;
object enumValue;
if (String.Equals(
part, "SkipExtraReads",
StringComparison.OrdinalIgnoreCase))
{
enumValue = SkipExtraReads;
}
else if (String.Equals(
part, "ForceExtraReads",
StringComparison.OrdinalIgnoreCase))
{
enumValue = ForceExtraReads;
}
else
{
try
{
enumValue = Enum.Parse( /* CLRv2+ */
typeof(CommandBehavior), part,
true); /* throw */
}
catch (Exception e)
{
error = e.ToString();
return null;
}
}
CommandBehavior flag = (CommandBehavior)enumValue;
if (add)
localBehavior |= flag;
else
localBehavior &= ~flag;
}
return localBehavior;
}
///////////////////////////////////////////////////////////////////////
/// <summary>
/// Checks if extra calls to the <see cref="SQLiteDataReader.Read" />
/// method are necessary. If so, it attempts to perform them. If
/// not, nothing is done.
/// </summary>
/// <param name="reader">
/// The data reader instance as it was received from one of the
/// <see cref="ExecuteReader()" /> methods.
/// </param>
/// <param name="behavior">
/// The original command behavior flags as passed into one of the
/// query execution methods.
/// </param>
/// <returns>
/// The number of extra calls to <see cref="SQLiteDataReader.Read" />
/// that were performed -OR- negative one to indicate they were not
/// enabled.
/// </returns>
public int MaybeReadRemaining(
SQLiteDataReader reader, /* in */
CommandBehavior behavior /* in */
)
{
CheckDisposed();
return PrivateMaybeReadRemaining(reader, behavior);
}
///////////////////////////////////////////////////////////////////////
/// <summary>
/// Checks if extra calls to the <see cref="SQLiteDataReader.Read" />
/// method are necessary. If so, it attempts to perform them. If
/// not, nothing is done.
/// </summary>
/// <param name="reader">
/// The data reader instance as it was received from one of the
/// <see cref="ExecuteReader()" /> methods.
/// </param>
/// <param name="behavior">
/// The original command behavior flags as passed into one of the
/// query execution methods.
/// </param>
/// <returns>
/// The number of extra calls to <see cref="SQLiteDataReader.Read" />
/// that were performed -OR- negative one to indicate they were not
/// enabled.
/// </returns>
private int PrivateMaybeReadRemaining(
SQLiteDataReader reader, /* in */
CommandBehavior behavior /* in */
)
{
//
// BUGFIX: There are SQL statements that cause a write transaction
// to be started and that always require more than one step
// to be successfully completed, e.g. INSERT with RETURNING
// clause. Therefore, if there is a write transaction in
// progress, keep stepping until done unless forbidden from
// doing so by the caller.
//
if (!ShouldSkipExtraReads(behavior) &&
(ShouldForceExtraReads(behavior) ||
MatchTransactionState(SQLiteTransactionState.SQLITE_TXN_WRITE)))
{
int count = 0;
while (reader.PrivateRead(true))
count++;
return count;
}
return -1;
}
///////////////////////////////////////////////////////////////////////
/// <summary>
/// Checks transaction state of the associated database connection.
/// </summary>
/// <param name="transactionState">
/// The desired transaction state.
/// </param>
/// <returns>
/// Non-zero if current transaction state of the associated database
/// connection matches the desired transaction state.
/// </returns>
private bool MatchTransactionState(
SQLiteTransactionState transactionState /* in */
)
{
//
// NOTE: The underlying sqlite3_txn_state() core library API
// is not available until release 3.34.1.
//
if (UnsafeNativeMethods.sqlite3_libversion_number() < 3034001)
return false;
SQLiteConnection cnn = _cnn;
if (cnn == null)
return false;
SQLiteBase sql = _cnn._sql;
if (sql == null)
return false;
return sql.GetTransactionState(null) == transactionState;
}
///////////////////////////////////////////////////////////////////////
/// <summary>
/// Execute the command and return the first column of the first row of the resultset
/// (if present), or null if no resultset was returned.
/// </summary>
/// <returns>The first column of the first row of the first resultset from the query.</returns>
public override object ExecuteScalar()
{
CheckDisposed();
SQLiteConnection.Check(_cnn);
return ExecuteScalar(CommandBehavior.Default);
}
/// <summary>
/// Execute the command and return the first column of the first row of the resultset
/// (if present), or null if no resultset was returned.
/// </summary>
/// <param name="behavior">The flags to be associated with the reader.</param>
/// <returns>The first column of the first row of the first resultset from the query.</returns>
public object ExecuteScalar(
CommandBehavior behavior
)
{
CheckDisposed();
SQLiteConnection.Check(_cnn);
MaybeAddGlobalCommandBehaviors(ref behavior);
object result = null;
using (SQLiteDataReader reader = ExecuteReader(behavior |
CommandBehavior.SingleRow | CommandBehavior.SingleResult))
{
if (reader.PrivateRead(false))
{
if (reader.FieldCount > 0)
result = reader[0];
//
// BUGFIX: See PrivateMaybeReadRemaining comments.
//
PrivateMaybeReadRemaining(reader, behavior);
}
}
return result;
}
/// <summary>
/// This method resets all the prepared statements held by this instance
/// back to their initial states, ready to be re-executed.
/// </summary>
public void Reset()
{
CheckDisposed();
SQLiteConnection.Check(_cnn);
Reset(true, false);
}
/// <summary>
/// This method resets all the prepared statements held by this instance
/// back to their initial states, ready to be re-executed.
/// </summary>
/// <param name="clearBindings">
/// Non-zero if the parameter bindings should be cleared as well.
/// </param>
/// <param name="ignoreErrors">
/// If this is zero, a <see cref="SQLiteException" /> may be thrown for
/// any unsuccessful return codes from the native library; otherwise, a
/// <see cref="SQLiteException" /> will only be thrown if the connection
/// or its state is invalid.
/// </param>
public void Reset(
bool clearBindings,
bool ignoreErrors
)
{
CheckDisposed();
SQLiteConnection.Check(_cnn);
if (clearBindings && (_parameterCollection != null))
_parameterCollection.Unbind();
ClearDataReader();
if (_statementList == null)
return;
SQLiteBase sqlBase = _cnn._sql;
SQLiteErrorCode rc;
foreach (SQLiteStatement item in _statementList)
{
if (item == null)
continue;
SQLiteStatementHandle stmt = item._sqlite_stmt;
if (stmt == null)
continue;
rc = sqlBase.Reset(item);
if ((rc == SQLiteErrorCode.Ok) && clearBindings &&
(SQLite3.SQLiteVersionNumber >= 3003007))
{
rc = UnsafeNativeMethods.sqlite3_clear_bindings(stmt);
}
if (!ignoreErrors && (rc != SQLiteErrorCode.Ok))
throw new SQLiteException(rc, sqlBase.GetLastError());
}
}
/// <summary>
/// Does nothing. Commands are prepared as they are executed the first time, and kept in prepared state afterwards.
/// </summary>
public override void Prepare()
{
CheckDisposed();
SQLiteConnection.Check(_cnn);
}
/// <summary>
/// Sets the method the SQLiteCommandBuilder uses to determine how to update inserted or updated rows in a DataTable.
/// </summary>
[DefaultValue(UpdateRowSource.None)]
public override UpdateRowSource UpdatedRowSource
{
get
{
CheckDisposed();
return _updateRowSource;
}
set
{
CheckDisposed();
_updateRowSource = value;
}
}
/// <summary>
/// Determines if the command is visible at design time. Defaults to True.
/// </summary>
#if !PLATFORM_COMPACTFRAMEWORK
[DesignOnly(true), Browsable(false), DefaultValue(true), EditorBrowsable(EditorBrowsableState.Never)]
#endif
public override bool DesignTimeVisible
{
get
{
CheckDisposed();
return _designTimeVisible;
}
set
{
CheckDisposed();
_designTimeVisible = value;
#if !PLATFORM_COMPACTFRAMEWORK
TypeDescriptor.Refresh(this);
#endif
}
}
/// <summary>
/// Clones a command, including all its parameters
/// </summary>
/// <returns>A new SQLiteCommand with the same commandtext, connection and parameters</returns>
public object Clone()
{
CheckDisposed();
return new SQLiteCommand(this);
}
/// <summary>
/// This method attempts to build and return a string containing diagnostic
/// information for use by the test suite. This method should not be used
/// by application code. It is designed for use by the test suite only and
/// may be modified or removed at any time.
/// </summary>
/// <returns>
/// A string containing information for use by the test suite -OR- null if
/// that information is not available.
/// </returns>
public string GetDiagnostics()
{
CheckDisposed();
StringBuilder builder = new StringBuilder();
if (_statementList != null)
{
for (int index = 0; index < _statementList.Count; index++)
{
SQLiteStatement statement = _statementList[index];
if (statement == null)
continue;
builder.AppendFormat(CultureInfo.CurrentCulture,
"#{0}, sql = {{{1}}}", index,
statement._sqlStatement);
if (statement._prepareSchemaRetries > 0)
{
builder.AppendFormat(CultureInfo.CurrentCulture,
", prepareSchemaRetries = {0}",
statement._prepareSchemaRetries);
}
if (statement._prepareLockRetries > 0)
{
builder.AppendFormat(CultureInfo.CurrentCulture,
", prepareLockRetries = {0}",
statement._prepareLockRetries);
}
if (statement._stepSchemaRetries > 0)
{
builder.AppendFormat(CultureInfo.CurrentCulture,
", stepSchemaRetries = {0}",
statement._stepSchemaRetries);
}
if (statement._stepLockRetries > 0)
{
builder.AppendFormat(CultureInfo.CurrentCulture,
", stepLockRetries = {0}",
statement._stepLockRetries);
}
#if !NET_COMPACT_20
builder.AppendLine();
#else
builder.AppendFormat(CultureInfo.CurrentCulture,
"\r\n");
#endif
}
}
return builder.ToString();
}
}
}
