public void Commit(Enlistment enlistment)
{
if (_transaction != null && !_transaction.IsUpdated)
{
_transaction.Commit();
_transaction = null;
if (Completed != null)
{
Completed(this, new EventArgs());
}
if (_connection != null)
{
if (!_connection.Options.Pooling && (_connection.OwningConnection == null || _connection.OwningConnection.IsClosed))
{
_connection.Disconnect();
}
}
_connection = null;
_systemTransaction = null;
// Declare done on the enlistment
enlistment.Done();
}
}
public static bool abrir()
{
conexao = new FbConnection(stringConexao);
conexao.Open();
transacao = conexao.BeginTransaction();
return true;
}
public int? Add(FbConnection conn, FbTransaction trans, int collectionId)
{
string query = "SELECT * FROM sp_add_object(@collectionId)";
int? ret = null;
try
{
using (FbCommand cmd = new FbCommand(query, conn, trans))
{
cmd.Parameters.AddWithValue("collectionId", collectionId);
using (FbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
ret = !reader.IsDBNull(0) ? reader.GetInt32(0) as int? : null;
}
}
}
}
catch (Exception ex)
{
throw new Exception(String.Format("Ошибка SQL запроса. {0}", ex.Message));
}
return ret;
}
protected List<Dictionary<string, string>> _executeReader(FbConnection conn, FbTransaction trans, string query, Dictionary<string, object> prms)
{
List<Dictionary<string, string>> ret = new List<Dictionary<string, string>>();
using (FbCommand cmd = new FbCommand(query, conn, trans))
{
foreach (var prm in prms)
cmd.Parameters.AddWithValue(prm.Key, prm.Value);
using (FbDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Dictionary<string, string> row = new Dictionary<string, string>();
Object[] values = new Object[reader.FieldCount];
reader.GetValues(values);
for (int jj = 0; jj < reader.FieldCount; jj++)
row.Add(reader.GetName(jj).ToLower(), values[jj].ToString());
ret.Add(row);
}
}
}
return ret;
}
public int? Get(FbConnection conn, FbTransaction trans, int objectId, string code, bool createIfNotExist)
{
string query = "SELECT * FROM sp_get_collection(@objectid, @collectioncode, @createifnotexist)";
int? ret = null;
try
{
using (FbCommand cmd = new FbCommand(query, conn, trans))
{
cmd.Parameters.AddWithValue("@objectid", objectId);
cmd.Parameters.AddWithValue("@collectioncode", code);
cmd.Parameters.AddWithValue("@createifnotexist", createIfNotExist);
using (FbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
ret = !reader.IsDBNull(0) ? reader.GetInt32(0) as int? : null;
}
}
}
}
catch (Exception ex)
{
throw new Exception(String.Format("Ошибка SQL запроса. {0}", ex.Message));
}
return ret;
}
public void Commit(Enlistment enlistment)
{
if (this.transaction != null && !this.transaction.IsUpdated)
{
this.transaction.Commit();
this.transaction = null;
if (this.Completed != null)
{
this.Completed(this, new EventArgs());
}
if (this.connection != null)
{
if (!this.connection.Pooled && (this.connection.OwningConnection == null || this.connection.OwningConnection.IsClosed))
{
this.connection.Disconnect();
}
}
this.connection = null;
this.systemTransaction = null;
// Declare done on the enlistment
enlistment.Done();
}
}
private void Init(FirebirdProcessor processor, IEnumerable<string> columnDefinitions)
{
Connection = (FbConnection)processor.Connection;
Transaction = (FbTransaction)processor.Transaction;
Create(columnDefinitions);
}
public IModel Select(FbConnection conn, FbTransaction trans, string code)
{
string query = "SELECT * FROM models WHERE modelcode = UPPER(@modelcode)";
IModel ret = null;
try
{
using (FbCommand cmd = new FbCommand(query, conn, trans))
{
cmd.Parameters.AddWithValue("modelcode", code.ToUpper());
using (FbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
ret = new Model
{
Id = reader.GetInt32(0),
Code = reader.GetString(1),
ObjectIdRoot = reader.GetInt32(2)
};
}
}
}
}
catch (Exception ex)
{
throw new Exception(String.Format("Ошибка SQL запроса. {0}", ex.Message));
}
return ret;
}
public List<int> Find(FbConnection conn, FbTransaction trans, int ownerObjectId, string collectionName, string attrName, string attrValue)
{
string query = "SELECT objectid FROM sp_find_objects(@ownerObjectId, @collectionName, @attrName, '=', @attrValue)";
List<int> ret = new List<int>();
try
{
using (FbCommand cmd = new FbCommand(query, conn, trans))
{
cmd.Parameters.AddWithValue("ownerObjectId", ownerObjectId);
cmd.Parameters.AddWithValue("collectionName", collectionName);
cmd.Parameters.AddWithValue("attrName", attrName);
cmd.Parameters.AddWithValue("attrValue", attrValue);
using (FbDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
ret.Add(reader.GetInt32(0));
}
}
}
}
catch (Exception ex)
{
throw new Exception(String.Format("Ошибка SQL запроса. {0}", ex.Message));
}
return ret;
}
protected string _executeNonQuery(FbConnection conn, FbTransaction trans, string query, Dictionary<string, object> prms, bool isReturning)
{
string ret = "";
using (FbCommand cmd = new FbCommand(query, conn, trans))
{
foreach (var prm in prms)
cmd.Parameters.AddWithValue(prm.Key, prm.Value);
if (isReturning)
{
FbParameter outparam = new FbParameter("@out", FbDbType.VarChar)
{
Direction = ParameterDirection.Output
};
cmd.Parameters.Add(outparam);
cmd.ExecuteNonQuery();
ret = outparam.Value as string;
}
else
cmd.ExecuteNonQuery();
}
return ret;
}
public FbEnlistmentNotification(FbConnectionInternal connection, Transaction systemTransaction)
{
this.connection = connection;
this.transaction = connection.BeginTransaction(systemTransaction.IsolationLevel);
this.systemTransaction = systemTransaction;
this.systemTransaction.EnlistVolatile(this, System.Transactions.EnlistmentOptions.None);
}
/// <summary>
/// 指定されたパラメタでインスタンスを生成します。
/// </summary>
/// <param name="connectionSubstance">接続実体オブジェクト</param>
internal TransactionSubstance(ConnectionSubstance connectionSubstance)
{
this.count = 0;
this.isRollback = false;
this.connectionSubstance = connectionSubstance;
this.transaction = this.connectionSubstance.FbConnection.BeginTransaction(Transaction.FbTransactionOption);
this.commandList = new List<Command>();
this.disposed = false;
}
/// <summary>
/// Удаление пользователя
/// </summary>
/// <param name="user">Пользователь</param>
/// <param name="connection">Соединение</param>
/// <param name="transaction">Транзакция</param>
public static void Delete(UserData user, FbConnection connection,
FbTransaction transaction)
{
// создаем команду
using (FbCommand command = new FbCommand("delete from users where id = @id",
connection, transaction))
{
// параметры команды
command.Parameters.Add("@id", user.Id);
// выполняем команду
command.ExecuteNonQuery();
}
}
/// <summary>
/// Проверка существования записи в базе
/// </summary>
/// <param name="user">Пользователь</param>
/// <param name="connection">Соединение</param>
/// <param name="transaction">Транзакция</param>
public static Boolean Exists(UserData user, FbConnection connection,
FbTransaction transaction)
{
// создаем команду
using (FbCommand command = new FbCommand("select id from users where id = @id",
connection, transaction))
{
// параметры команды
command.Parameters.Add("@id", user.Id);
// выполняем команду
using (FbDataReader reader = command.ExecuteReader())
{
// возвращаем результат поиска
return reader.Read();
}
}
}
public void Delete(FbConnection conn, FbTransaction trans, int ownerObjectId, string code)
{
string query = "DELETE FROM collections WHERE owner_objectid = @owner_objectid AND collectioncode = UPPER(@collectioncode)";
try
{
using (FbCommand cmd = new FbCommand(query, conn, trans))
{
cmd.Parameters.AddWithValue("@owner_objectid", ownerObjectId);
cmd.Parameters.AddWithValue("@collectioncode", code);
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(String.Format("Ошибка SQL запроса. {0}", ex.Message));
}
}
public void Set(FbConnection conn, FbTransaction trans, int objectId, string code, string val)
{
string query = "EXECUTE PROCEDURE sp_set_attribute(@objectId, @attributeCode, @val)";
try
{
using (FbCommand cmd = new FbCommand(query, conn, trans))
{
cmd.Parameters.AddWithValue("objectId", objectId);
cmd.Parameters.AddWithValue("attributeCode", code);
cmd.Parameters.AddWithValue("val", val);
cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(String.Format("Ошибка SQL запроса. {0}", ex.Message));
}
}
public int Delete(FbConnection conn, FbTransaction trans, int id)
{
string query = "EXECUTE PROCEDURE sp_delete_object(@id)";
int ret = -1;
try
{
using (FbCommand cmd = new FbCommand(query, conn, trans))
{
cmd.Parameters.AddWithValue("id", id);
ret = cmd.ExecuteNonQuery();
}
}
catch (Exception ex)
{
throw new Exception(String.Format("Ошибка SQL запроса. {0}", ex.Message));
}
return ret;
}
/// <summary>
/// Добавление пользователя
/// </summary>
/// <param name="user">Пользователь</param>
/// <param name="connection">Соединение</param>
/// <param name="transaction">Транзакция</param>
public static void Insert(UserData user, FbConnection connection,
FbTransaction transaction)
{
// текст команды
StringBuilder commandText = new StringBuilder();
commandText.Append("insert into users values ");
commandText.Append("(@id, @name, @password, @group, @can_close, @cashier_mode_only)");
// создаем команду
using (FbCommand command = new FbCommand(commandText.ToString(), connection, transaction))
{
// параметры
command.Parameters.Add("@id", user.Id);
command.Parameters.Add("@name", user.Name);
command.Parameters.Add("@password", user.Password);
command.Parameters.Add("@group", (Int32)user.SecurityGroup);
command.Parameters.Add("@can_close", user.CanClose ? 1 : 0);
command.Parameters.Add("@cashier_mode_only", user.CashierModeOnly ? 1 : 0);
// выполняем команду
command.ExecuteNonQuery();
}
}
public void Commit(Enlistment enlistment)
{
if (_transaction != null && !_transaction.IsCompleted)
{
_transaction.Commit();
_transaction = null;
Completed?.Invoke(this, new EventArgs());
if (_connection != null)
{
if (!_connection.Options.Pooling && (_connection.OwningConnection == null || _connection.OwningConnection.IsClosed))
{
_connection.Disconnect(new AsyncWrappingCommonArgs(false)).GetAwaiter().GetResult();
}
}
_connection = null;
_systemTransaction = null;
// Declare done on the enlistment
enlistment.Done();
}
}
public void Rollback(Enlistment enlistment)
{
if (_transaction != null && !_transaction.IsCompleted)
{
_transaction.Rollback();
_transaction = null;
Completed?.Invoke(this, new EventArgs());
if (_connection != null)
{
if (!_connection.Options.Pooling && (_connection.OwningConnection == null || _connection.OwningConnection.IsClosed))
{
_connection.Disconnect();
}
}
_connection = null;
_systemTransaction = null;
// Declare done on the enlistment
enlistment.Done();
}
}
public FbCommand(string cmdText, FbConnection connection, FbTransaction transaction)
{
_namedParameters = new List <string>();
_updatedRowSource = UpdateRowSource.Both;
_commandType = CommandType.Text;
_designTimeVisible = true;
_commandTimeout = 30;
_fetchSize = 200;
_commandText = string.Empty;
if (connection != null)
{
_fetchSize = connection.ConnectionOptions.FetchSize;
}
if (cmdText != null)
{
CommandText = cmdText;
}
Connection = connection;
_transaction = transaction;
}
public async Task <FbTransaction> BeginTransaction(FbTransactionOptions options, string transactionName, AsyncWrappingCommonArgs async)
{
EnsureActiveTransaction();
try
{
_activeTransaction = new FbTransaction(_owningConnection, IsolationLevel.Unspecified);
await _activeTransaction.BeginTransaction(options, async).ConfigureAwait(false);
if (transactionName != null)
{
_activeTransaction.Save(transactionName);
}
}
catch (IscException ex)
{
await DisposeTransaction(async).ConfigureAwait(false);
throw FbException.Create(ex);
}
return(_activeTransaction);
}
private async Task DisposeHelper(AsyncWrappingCommonArgs async)
{
if (!_disposed)
{
_disposed = true;
await Release(async).ConfigureAwait(false);
_commandTimeout = 0;
_fetchSize = 0;
_implicitTransaction = false;
_commandText = null;
_connection = null;
_transaction = null;
_parameters = null;
_statement = null;
_activeReader = null;
if (_namedParameters != null)
{
_namedParameters.Clear();
_namedParameters = null;
}
}
}
protected override void Dispose(bool disposing)
{
lock (this)
{
if (!this.disposed)
{
try
{
// Release any unmanaged resources
this.Release();
// release any managed resources
this.commandTimeout = 0;
this.fetchSize = 0;
this.implicitTransaction = false;
this.commandText = null;
this.connection = null;
this.transaction = null;
this.parameters = null;
this.statement = null;
this.activeReader = null;
if (this.namedParameters != null)
{
this.namedParameters.Clear();
this.namedParameters = null;
}
this.disposed = true;
}
finally
{
base.Dispose(disposing);
}
}
}
}
protected override void Dispose(bool disposing)
{
lock (this)
{
if (!_disposed)
{
try
{
// Release any unmanaged resources
Release();
// release any managed resources
_commandTimeout = 0;
_fetchSize = 0;
_implicitTransaction = false;
_commandText = null;
_connection = null;
_transaction = null;
_parameters = null;
_statement = null;
_activeReader = null;
if (_namedParameters != null)
{
_namedParameters.Clear();
_namedParameters = null;
}
_disposed = true;
}
finally
{
base.Dispose(disposing);
}
}
}
}
public FbCommand(string cmdText, FbConnection connection, FbTransaction transaction)
: base()
{
this.namedParameters = new List <string>();
this.updatedRowSource = UpdateRowSource.Both;
this.commandType = CommandType.Text;
this.designTimeVisible = true;
this.commandTimeout = 30;
this.fetchSize = 200;
this.commandText = string.Empty;
if (connection != null)
{
this.fetchSize = connection.ConnectionOptions.FetchSize;
}
if (cmdText != null)
{
this.CommandText = cmdText;
}
this.Connection = connection;
this.transaction = transaction;
}
public FbCommand(string cmdText, FbConnection connection, FbTransaction transaction)
: base()
{
this.namedParameters = new List<string>();
this.updatedRowSource = UpdateRowSource.Both;
this.commandType = CommandType.Text;
this.designTimeVisible = true;
this.commandTimeout = 30;
this.fetchSize = 200;
this.commandText = string.Empty;
if (connection != null)
{
this.fetchSize = connection.ConnectionOptions.FetchSize;
}
if (cmdText != null)
{
this.CommandText = cmdText;
}
this.Connection = connection;
this.transaction = transaction;
}
public FbTransaction BeginTransaction(IsolationLevel level, string transactionName)
{
if (HasActiveTransaction)
{
throw new InvalidOperationException("A transaction is currently active. Parallel transactions are not supported.");
}
try
{
_activeTransaction = new FbTransaction(_owningConnection, level);
_activeTransaction.BeginTransaction();
if (transactionName != null)
{
_activeTransaction.Save(transactionName);
}
}
catch (IscException ex)
{
throw new FbException(ex.Message, ex);
}
return(_activeTransaction);
}
private void CheckCommand()
{
if (_transaction != null && _transaction.IsCompleted)
{
_transaction = null;
}
if (_connection == null ||
_connection.State != ConnectionState.Open)
{
throw new InvalidOperationException("Connection must be valid and open");
}
if (_activeReader != null)
{
throw new InvalidOperationException("There is already an open DataReader associated with this Command which must be closed first.");
}
if (_transaction == null &&
_connection.InnerConnection.HasActiveTransaction &&
!_connection.InnerConnection.IsEnlisted)
{
throw new InvalidOperationException("Execute requires the Command object to have a Transaction object when the Connection object assigned to the command is in a pending local transaction. The Transaction property of the Command has not been initialized.");
}
if (_transaction != null && !_transaction.IsCompleted &&
!_connection.Equals(_transaction.Connection))
{
throw new InvalidOperationException("Command Connection is not equal to Transaction Connection.");
}
if (_commandText == null || _commandText.Length == 0)
{
throw new InvalidOperationException("The command text for this Command has not been set.");
}
}
internal void CommitImplicitTransaction()
{
if (this.HasImplicitTransaction &&
this.transaction != null &&
this.transaction.Transaction != null)
{
try
{
this.transaction.Commit();
}
catch
{
this.RollbackImplicitTransaction();
throw;
}
finally
{
if (this.transaction != null)
{
this.transaction.Dispose();
this.transaction = null;
this.implicitTransaction = false;
}
if (this.statement != null)
{
this.statement.Transaction = null;
}
}
}
}
private void Prepare(bool returnsSet)
{
LogCommand();
FbConnectionInternal innerConn = this.connection.InnerConnection;
// Check if we have a valid transaction
if (this.transaction == null)
{
if (innerConn.IsEnlisted)
{
this.transaction = innerConn.ActiveTransaction;
}
else
{
this.implicitTransaction = true;
this.transaction = new FbTransaction(this.connection, this.connection.ConnectionOptions.IsolationLevel);
this.transaction.BeginTransaction();
// Update Statement transaction
if (this.statement != null)
{
this.statement.Transaction = this.transaction.Transaction;
}
}
}
// Check if we have a valid statement handle
if (this.statement == null)
{
this.statement = innerConn.Database.CreateStatement(this.transaction.Transaction);
}
// Prepare the statement if needed
if (!this.statement.IsPrepared)
{
// Close the inner DataReader if needed
this.CloseReader();
// Reformat the SQL statement if needed
string sql = this.commandText;
if (this.commandType == CommandType.StoredProcedure)
{
sql = this.BuildStoredProcedureSql(sql, returnsSet);
}
try
{
// Try to prepare the command
this.statement.Prepare(this.ParseNamedParameters(sql));
}
catch
{
// Release the statement and rethrow the exception
this.statement.Release();
this.statement = null;
throw;
}
// Add this command to the active command list
innerConn.AddPreparedCommand(this);
}
else
{
// Close statement for subsequently executions
this.Close();
}
}
private void CheckCommand()
{
if (this.transaction != null && this.transaction.IsUpdated)
{
this.transaction = null;
}
if (this.connection == null ||
this.connection.State != ConnectionState.Open)
{
throw new InvalidOperationException("Connection must be valid and open");
}
if (this.activeReader != null)
{
throw new InvalidOperationException("There is already an open DataReader associated with this Command which must be closed first.");
}
if (this.transaction == null &&
this.connection.InnerConnection.HasActiveTransaction &&
!this.connection.InnerConnection.IsEnlisted)
{
throw new InvalidOperationException("Execute requires the Command object to have a Transaction object when the Connection object assigned to the command is in a pending local transaction. The Transaction property of the Command has not been initialized.");
}
if (this.transaction != null && !this.transaction.IsUpdated &&
!this.connection.Equals(transaction.Connection))
{
throw new InvalidOperationException("Command Connection is not equal to Transaction Connection.");
}
if (this.commandText == null || this.commandText.Length == 0)
{
throw new InvalidOperationException("The command text for this Command has not been set.");
}
}
public virtual void SetUp()
{
string cs = this.BuildConnectionString();
CreateDatabase(cs);
CreateTables(cs);
InsertTestData(cs);
CreateProcedures(cs);
CreateTriggers(cs);
this.connection = new FbConnection(cs);
this.connection.Open();
if (this.withTransaction)
{
this.transaction = this.connection.BeginTransaction();
}
}
private string transacaoImportacaoIniciar()
{
string msg = "";
Transacao = D.Bd.Con.BeginTransaction();
return msg;
}
private void Prepare(bool returnsSet)
{
LogCommand();
var innerConn = _connection.InnerConnection;
// Check if we have a valid transaction
if (_transaction == null)
{
if (innerConn.IsEnlisted)
{
_transaction = innerConn.ActiveTransaction;
}
else
{
_implicitTransaction = true;
_transaction = new FbTransaction(_connection, _connection.ConnectionOptions.IsolationLevel);
_transaction.BeginTransaction();
// Update Statement transaction
if (_statement != null)
{
_statement.Transaction = _transaction.Transaction;
}
}
}
// Check if we have a valid statement handle
if (_statement == null)
{
_statement = innerConn.Database.CreateStatement(_transaction.Transaction);
}
// Prepare the statement if needed
if (!_statement.IsPrepared)
{
// Close the inner DataReader if needed
DisposeReader();
// Reformat the SQL statement if needed
var sql = _commandText;
if (_commandType == CommandType.StoredProcedure)
{
sql = BuildStoredProcedureSql(sql, returnsSet);
}
try
{
// Try to prepare the command
_statement.Prepare(ParseNamedParameters(sql));
}
catch
{
// Release the statement and rethrow the exception
_statement.Release();
_statement = null;
throw;
}
// Add this command to the active command list
innerConn.AddPreparedCommand(this);
}
else
{
// Close statement for subsequently executions
Close();
}
}
internal void RollbackImplicitTransaction()
{
if (this.HasImplicitTransaction && this.transaction != null && this.transaction.Transaction != null)
{
int transactionCount = this.Connection.InnerConnection.Database.TransactionCount;
try
{
this.transaction.Rollback();
}
catch
{
if (this.Connection.InnerConnection.Database.TransactionCount == transactionCount)
{
this.Connection.InnerConnection.Database.TransactionCount--;
}
}
finally
{
this.transaction.Dispose();
this.transaction = null;
this.implicitTransaction = false;
if (this.statement != null)
{
this.statement.Transaction = null;
}
}
}
}
protected override void Dispose(bool disposing)
{
lock (this)
{
if (!this.disposed)
{
try
{
// Release any unmanaged resources
this.Release();
// release any managed resources
this.commandTimeout = 0;
this.fetchSize = 0;
this.implicitTransaction = false;
this.commandText = null;
this.connection = null;
this.transaction = null;
this.parameters = null;
this.statement = null;
this.activeReader = null;
if (this.namedParameters != null)
{
this.namedParameters.Clear();
this.namedParameters = null;
}
this.disposed = true;
}
finally
{
base.Dispose(disposing);
}
}
}
}
private void InitTransaction()
{
switch (DatabaseManager.DatabaseConnectionType.ToLower())
{
case "firebird":
CommandFireBird = Client.CreateNewCommandFireBird();
_transactionfirebird = Client.GetTransactionFireBird();
CommandFireBird.Transaction = _transactionfirebird;
CommandFireBird.Connection = _transactionfirebird.Connection;
break;
case "ingres":
case "ingress":
CommandIngress = Client.CreateNewCommandIngress();
_transactioningress = Client.GetTransactionIngress();
CommandIngress.Transaction = _transactioningress;
break;
case "pgsql":
CommandPgSql = Client.CreateNewCommandPgSql();
_transactionpgsql = Client.GetTransactionPgSql();
CommandPgSql.Transaction = _transactionpgsql;
CommandPgSql.Connection = _transactionpgsql.Connection;
break;
default:
CommandMySql = Client.CreateNewCommandMySql();
_transactionmysql = Client.GetTransactionMySql();
CommandMySql.Transaction = _transactionmysql;
CommandMySql.Connection = _transactionmysql.Connection;
break;
}
_finishedTransaction = false;
}
public static void setTransacao(FbTransaction pfbTransaction)
{
fbTransaction = pfbTransaction;
}
/// <summary>
/// Releases the resources used by this object.
/// </summary>
private void Dispose(bool isDisposing)
{
// Check to see if Dispose has already been called.
if (this.isDisposed == false)
{
if (isDisposing)
{
// Dispose managed resources.
if (this.sqlTran != null)
{
this.sqlTran.Dispose();
this.sqlTran = null;
}
// Closing the connection will abort (rollback) any pending transactions.
if (this.sqlConn != null)
{
this.sqlConn.Close();
this.sqlConn.Dispose();
this.sqlConn = null;
}
}
}
this.isDisposed = true;
}
/// <summary>
/// Starts the ordered execution of the SQL statements that are in <see cref="SqlStatements"/> collection.
/// </summary>
/// <param name="autoCommit">Specifies if the transaction should be committed after a DDL command execution</param>
public void Execute(bool autoCommit)
{
if (this.SqlStatements == null || this.SqlStatements.Count == 0)
{
throw new InvalidOperationException("There are no commands for execution.");
}
foreach (string sqlStatement in this.SqlStatements)
{
if (string.IsNullOrEmpty(sqlStatement))
{
continue;
}
// initializate outputs to default
int rowsAffected = -1;
FbDataReader dataReader = null;
SqlStatementType statementType = FbBatchExecution.GetStatementType(sqlStatement);
if (!(statementType == SqlStatementType.Connect ||
statementType == SqlStatementType.CreateDatabase ||
statementType == SqlStatementType.Disconnect ||
statementType == SqlStatementType.DropDatabase ||
statementType == SqlStatementType.SetDatabase ||
statementType == SqlStatementType.SetNames ||
statementType == SqlStatementType.SetSQLDialect))
{
// Update command configuration
this.ProvideCommand();
this.sqlCommand.CommandText = sqlStatement;
// Check how transactions are going to be handled
if (statementType == SqlStatementType.Insert ||
statementType == SqlStatementType.Update ||
statementType == SqlStatementType.Delete)
{
// DML commands should be inside a transaction
if (this.sqlTransaction == null)
{
this.sqlTransaction = this.sqlConnection.BeginTransaction();
}
this.sqlCommand.Transaction = this.sqlTransaction;
}
else if (this.sqlTransaction != null && !(statementType == SqlStatementType.Commit || statementType == SqlStatementType.Rollback))
{
// Non DML Statements should be executed using
// implicit transaction support
this.sqlTransaction.Commit();
this.sqlTransaction = null;
}
}
try
{
switch (statementType)
{
case SqlStatementType.AlterDatabase:
case SqlStatementType.AlterDomain:
case SqlStatementType.AlterException:
case SqlStatementType.AlterIndex:
case SqlStatementType.AlterProcedure:
case SqlStatementType.AlterTable:
case SqlStatementType.AlterTrigger:
// raise the event
this.OnCommandExecuting(this.sqlCommand);
rowsAffected = this.ExecuteCommand(this.sqlCommand, autoCommit);
this.requiresNewConnection = false;
// raise the event
this.OnCommandExecuted(sqlStatement, null, rowsAffected);
break;
case SqlStatementType.Commit:
// raise the event
this.OnCommandExecuting(null);
this.sqlTransaction.Commit();
this.sqlTransaction = null;
// raise the event
this.OnCommandExecuted(sqlStatement, null, -1);
break;
case SqlStatementType.Connect:
// raise the event
this.OnCommandExecuting(null);
this.ConnectToDatabase(sqlStatement);
this.requiresNewConnection = false;
// raise the event
this.OnCommandExecuted(sqlStatement, null, -1);
break;
case SqlStatementType.CreateDatabase:
// raise the event
this.OnCommandExecuting(null);
this.CreateDatabase(sqlStatement);
this.requiresNewConnection = false;
// raise the event
this.OnCommandExecuted(sqlStatement, null, -1);
break;
case SqlStatementType.CommentOn:
case SqlStatementType.CreateDomain:
case SqlStatementType.CreateException:
case SqlStatementType.CreateGenerator:
case SqlStatementType.CreateIndex:
case SqlStatementType.CreateProcedure:
case SqlStatementType.CreateRole:
case SqlStatementType.CreateSequence:
case SqlStatementType.CreateShadow:
case SqlStatementType.CreateTable:
case SqlStatementType.CreateTrigger:
case SqlStatementType.CreateView:
case SqlStatementType.DeclareCursor:
case SqlStatementType.DeclareExternalFunction:
case SqlStatementType.DeclareFilter:
case SqlStatementType.DeclareStatement:
case SqlStatementType.DeclareTable:
case SqlStatementType.Delete:
// raise the event
this.OnCommandExecuting(this.sqlCommand);
rowsAffected = this.ExecuteCommand(this.sqlCommand, autoCommit);
this.requiresNewConnection = false;
// raise the event
this.OnCommandExecuted(sqlStatement, null, rowsAffected);
break;
case SqlStatementType.Describe:
break;
case SqlStatementType.Disconnect:
// raise the event
this.OnCommandExecuting(null);
this.sqlConnection.Close();
FbConnection.ClearPool(this.sqlConnection);
this.requiresNewConnection = false;
// raise the event
this.OnCommandExecuted(sqlStatement, null, -1);
break;
case SqlStatementType.DropDatabase:
// raise the event
this.OnCommandExecuting(null);
FbConnection.DropDatabase(this.connectionString.ToString());
//this.sqlConnection = null;
this.requiresNewConnection = true;
// raise the event
this.OnCommandExecuted(sqlStatement, null, -1);
break;
case SqlStatementType.DropDomain:
case SqlStatementType.DropException:
case SqlStatementType.DropExternalFunction:
case SqlStatementType.DropFilter:
case SqlStatementType.DropGenerator:
case SqlStatementType.DropIndex:
case SqlStatementType.DropProcedure:
case SqlStatementType.DropSequence:
case SqlStatementType.DropRole:
case SqlStatementType.DropShadow:
case SqlStatementType.DropTable:
case SqlStatementType.DropTrigger:
case SqlStatementType.DropView:
case SqlStatementType.EventInit:
case SqlStatementType.EventWait:
case SqlStatementType.Execute:
case SqlStatementType.ExecuteImmediate:
case SqlStatementType.ExecuteProcedure:
this.ProvideCommand().CommandText = sqlStatement;
// raise the event
this.OnCommandExecuting(this.sqlCommand);
rowsAffected = this.ExecuteCommand(this.sqlCommand, autoCommit);
this.requiresNewConnection = false;
// raise the event
this.OnCommandExecuted(sqlStatement, null, rowsAffected);
break;
case SqlStatementType.Fetch:
break;
case SqlStatementType.Grant:
case SqlStatementType.Insert:
case SqlStatementType.InsertCursor:
case SqlStatementType.Open:
case SqlStatementType.Prepare:
case SqlStatementType.Revoke:
// raise the event
this.OnCommandExecuting(this.sqlCommand);
rowsAffected = this.ExecuteCommand(this.sqlCommand, autoCommit);
this.requiresNewConnection = false;
// raise the event
this.OnCommandExecuted(sqlStatement, null, rowsAffected);
break;
case SqlStatementType.RecereateProcedure:
case SqlStatementType.RecreateTable:
case SqlStatementType.RecreateView:
// raise the event
this.OnCommandExecuting(this.sqlCommand);
rowsAffected = this.ExecuteCommand(this.sqlCommand, autoCommit);
this.requiresNewConnection = false;
// raise the event
this.OnCommandExecuted(sqlStatement, null, rowsAffected);
break;
case SqlStatementType.Rollback:
// raise the event
this.OnCommandExecuting(null);
this.sqlTransaction.Rollback();
this.sqlTransaction = null;
// raise the event
this.OnCommandExecuted(sqlStatement, null, -1);
break;
case SqlStatementType.Select:
this.ProvideCommand().CommandText = sqlStatement;
// raise the event
this.OnCommandExecuting(this.sqlCommand);
dataReader = this.sqlCommand.ExecuteReader();
this.requiresNewConnection = false;
// raise the event
this.OnCommandExecuted(sqlStatement, dataReader, -1);
if (!dataReader.IsClosed)
{
dataReader.Close();
}
break;
case SqlStatementType.SetGenerator:
case SqlStatementType.AlterSequence:
// raise the event
this.OnCommandExecuting(this.sqlCommand);
rowsAffected = this.ExecuteCommand(this.sqlCommand, autoCommit);
this.requiresNewConnection = false;
// raise the event
this.OnCommandExecuted(sqlStatement, null, rowsAffected);
break;
case SqlStatementType.SetDatabase:
case SqlStatementType.SetNames:
case SqlStatementType.SetSQLDialect:
case SqlStatementType.SetStatistics:
case SqlStatementType.SetTransaction:
case SqlStatementType.ShowSQLDialect:
#if (!NET_CF)
throw new NotImplementedException();
#else
throw new NotSupportedException();
#endif
case SqlStatementType.Update:
case SqlStatementType.Whenever:
// raise the event
this.OnCommandExecuting(this.sqlCommand);
rowsAffected = this.ExecuteCommand(this.sqlCommand, autoCommit);
this.requiresNewConnection = false;
// raise the event
this.OnCommandExecuted(sqlStatement, null, rowsAffected);
break;
}
}
catch (Exception ex)
{
if (this.sqlTransaction != null)
{
this.sqlTransaction.Rollback();
this.sqlTransaction = null;
}
throw new FbException(string.Format(CultureInfo.CurrentUICulture, "An exception was thrown when executing command: {0}{2}Batch execution aborted{2}The returned message was: {1}", sqlStatement, ex.Message, Environment.NewLine));
}
}
if (this.sqlTransaction != null)
{
// commit root transaction
this.sqlTransaction.Commit();
this.sqlTransaction = null;
}
this.sqlConnection.Close();
}
private void Prepare(bool returnsSet)
{
#if (DEBUG)
System.Diagnostics.Debug.WriteLine(string.Format("Command:\n{0}", commandText));
if (this.parameters != null)
{
foreach (FbParameter item in this.parameters)
{
System.Diagnostics.Debug.WriteLine(string.Format("Name:{0} \t Type:{1} \t Value:{2}", item.InternalParameterName, item.FbDbType, item.InternalValue));
}
}
#endif
FbConnectionInternal innerConn = this.connection.InnerConnection;
// Check if we have a valid transaction
if (this.transaction == null)
{
if (innerConn.IsEnlisted)
{
this.transaction = innerConn.ActiveTransaction;
}
else
{
this.implicitTransaction = true;
this.transaction = new FbTransaction(this.connection, this.connection.ConnectionOptions.IsolationLevel);
this.transaction.BeginTransaction();
// Update Statement transaction
if (this.statement != null)
{
this.statement.Transaction = this.transaction.Transaction;
}
}
}
// Check if we have a valid statement handle
if (this.statement == null)
{
this.statement = innerConn.Database.CreateStatement(this.transaction.Transaction);
}
// Prepare the statement if needed
if (!this.statement.IsPrepared)
{
// Close the inner DataReader if needed
this.CloseReader();
// Reformat the SQL statement if needed
string sql = this.commandText;
if (this.commandType == CommandType.StoredProcedure)
{
sql = this.BuildStoredProcedureSql(sql, returnsSet);
}
try
{
// Try to prepare the command
this.statement.Prepare(this.ParseNamedParameters(sql));
}
catch
{
// Release the statement and rethrow the exception
this.statement.Release();
this.statement = null;
throw;
}
// Add this command to the active command list
innerConn.AddPreparedCommand(this);
}
else
{
// Close statement for subsequently executions
this.Close();
}
}
