/// <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:
case SqlStatementType.AlterView:
// 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.RecreateProcedure:
case SqlStatementType.RecreateTable:
case SqlStatementType.RecreateTrigger:
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:
throw new NotImplementedException();
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();
}
/// <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 = true)
{
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.SetAutoDDL ||
statementType == SqlStatementType.SetDatabase ||
statementType == SqlStatementType.SetNames ||
statementType == SqlStatementType.SetSQLDialect))
{
this.ProvideCommand();
this.sqlCommand.CommandText = sqlStatement;
if (this.sqlTransaction == null && !(statementType == SqlStatementType.Commit || statementType == SqlStatementType.Rollback))
{
this.sqlTransaction = this.sqlConnection.BeginTransaction();
}
this.sqlCommand.Transaction = this.sqlTransaction;
}
try
{
switch (statementType)
{
case SqlStatementType.AlterDatabase:
case SqlStatementType.AlterDomain:
case SqlStatementType.AlterException:
case SqlStatementType.AlterIndex:
case SqlStatementType.AlterProcedure:
case SqlStatementType.AlterRole:
case SqlStatementType.AlterTable:
case SqlStatementType.AlterTrigger:
case SqlStatementType.AlterView:
this.OnCommandExecuting(this.sqlCommand);
rowsAffected = this.ExecuteCommand(autoCommit);
this.requiresNewConnection = false;
this.OnCommandExecuted(sqlStatement, null, rowsAffected);
break;
case SqlStatementType.Commit:
this.OnCommandExecuting(null);
this.CommitTransaction();
this.OnCommandExecuted(sqlStatement, null, -1);
break;
case SqlStatementType.Connect:
this.OnCommandExecuting(null);
this.ConnectToDatabase(sqlStatement);
this.requiresNewConnection = false;
this.OnCommandExecuted(sqlStatement, null, -1);
break;
case SqlStatementType.CreateDatabase:
this.OnCommandExecuting(null);
this.CreateDatabase(sqlStatement);
this.requiresNewConnection = false;
this.OnCommandExecuted(sqlStatement, null, -1);
break;
case SqlStatementType.CommentOn:
case SqlStatementType.CreateCollation:
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:
this.OnCommandExecuting(this.sqlCommand);
rowsAffected = this.ExecuteCommand(autoCommit);
this.requiresNewConnection = false;
this.OnCommandExecuted(sqlStatement, null, rowsAffected);
break;
case SqlStatementType.Describe:
break;
case SqlStatementType.Disconnect:
this.OnCommandExecuting(null);
this.sqlConnection.Close();
FbConnection.ClearPool(this.sqlConnection);
this.requiresNewConnection = false;
this.OnCommandExecuted(sqlStatement, null, -1);
break;
case SqlStatementType.DropDatabase:
this.OnCommandExecuting(null);
FbConnection.DropDatabase(this.connectionString.ToString());
this.requiresNewConnection = true;
this.OnCommandExecuted(sqlStatement, null, -1);
break;
case SqlStatementType.DropCollation:
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;
this.OnCommandExecuting(this.sqlCommand);
rowsAffected = this.ExecuteCommand(autoCommit);
this.requiresNewConnection = false;
this.OnCommandExecuted(sqlStatement, null, rowsAffected);
break;
case SqlStatementType.ExecuteBlock:
this.ProvideCommand().CommandText = sqlStatement;
this.OnCommandExecuting(this.sqlCommand);
dataReader = this.sqlCommand.ExecuteReader();
this.requiresNewConnection = false;
this.OnCommandExecuted(sqlStatement, dataReader, -1);
if (!dataReader.IsClosed)
{
dataReader.Close();
}
break;
case SqlStatementType.Fetch:
break;
case SqlStatementType.Grant:
case SqlStatementType.Insert:
case SqlStatementType.InsertCursor:
case SqlStatementType.Open:
case SqlStatementType.Prepare:
case SqlStatementType.Revoke:
this.OnCommandExecuting(this.sqlCommand);
rowsAffected = this.ExecuteCommand(autoCommit);
this.requiresNewConnection = false;
this.OnCommandExecuted(sqlStatement, null, rowsAffected);
break;
case SqlStatementType.RecreateProcedure:
case SqlStatementType.RecreateTable:
case SqlStatementType.RecreateTrigger:
case SqlStatementType.RecreateView:
this.OnCommandExecuting(this.sqlCommand);
rowsAffected = this.ExecuteCommand(autoCommit);
this.requiresNewConnection = false;
this.OnCommandExecuted(sqlStatement, null, rowsAffected);
break;
case SqlStatementType.Rollback:
this.OnCommandExecuting(null);
this.RollbackTransaction();
this.OnCommandExecuted(sqlStatement, null, -1);
break;
case SqlStatementType.Select:
this.ProvideCommand().CommandText = sqlStatement;
this.OnCommandExecuting(this.sqlCommand);
dataReader = this.sqlCommand.ExecuteReader();
this.requiresNewConnection = false;
this.OnCommandExecuted(sqlStatement, dataReader, -1);
if (!dataReader.IsClosed)
{
dataReader.Close();
}
break;
case SqlStatementType.SetAutoDDL:
this.OnCommandExecuting(null);
this.SetAutoDdl(sqlStatement, ref autoCommit);
this.requiresNewConnection = false;
this.OnCommandExecuted(sqlStatement, null, -1);
break;
case SqlStatementType.SetGenerator:
case SqlStatementType.AlterSequence:
this.OnCommandExecuting(this.sqlCommand);
rowsAffected = this.ExecuteCommand(autoCommit);
this.requiresNewConnection = false;
this.OnCommandExecuted(sqlStatement, null, rowsAffected);
break;
case SqlStatementType.SetNames:
this.OnCommandExecuting(null);
this.SetNames(sqlStatement);
this.requiresNewConnection = true;
this.OnCommandExecuted(sqlStatement, null, -1);
break;
case SqlStatementType.SetSQLDialect:
this.OnCommandExecuting(null);
this.SetSqlDialect(sqlStatement);
this.requiresNewConnection = true;
this.OnCommandExecuted(sqlStatement, null, -1);
break;
case SqlStatementType.SetDatabase:
case SqlStatementType.SetStatistics:
case SqlStatementType.SetTransaction:
case SqlStatementType.ShowSQLDialect:
throw new NotImplementedException();
case SqlStatementType.Update:
case SqlStatementType.Whenever:
this.OnCommandExecuting(this.sqlCommand);
rowsAffected = this.ExecuteCommand(autoCommit);
this.requiresNewConnection = false;
this.OnCommandExecuted(sqlStatement, null, rowsAffected);
break;
}
}
catch (Exception ex)
{
this.RollbackTransaction();
throw new FbException(string.Format("An exception was thrown when executing command: {1}.{0}Batch execution aborted.{0}The returned message was: {2}.",
Environment.NewLine,
sqlStatement,
ex.Message));
}
}
this.CommitTransaction();
this.sqlConnection.Close();
}