/// <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 virtual 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 (sqlStatement == null || sqlStatement.Length == 0)
{
continue;
}
// initializate outputs to default
int rowsAffected = -1;
FbDataReader dataReader = null;
SqlStatementType statementType = FbBatchExecution.GetStatementType(sqlStatement);
// Update command configuration
this.ProvideCommand().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);
requiresNewConnection = false;
// raise the event
this.OnCommandExecuted(sqlStatement, null, -1);
break;
case SqlStatementType.CreateDatabase:
#if (!NETCF)
throw new NotImplementedException();
#else
throw new NotSupportedException();
#endif
case SqlStatementType.CreateDomain:
case SqlStatementType.CreateException:
case SqlStatementType.CreateGenerator:
case SqlStatementType.CreateIndex:
case SqlStatementType.CreateProcedure:
case SqlStatementType.CreateRole:
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);
requiresNewConnection = false;
// raise the event
this.OnCommandExecuted(sqlStatement, null, rowsAffected);
break;
case SqlStatementType.Describe:
break;
case SqlStatementType.Disconnect:
this.sqlConnection.Close();
this.requiresNewConnection = false;
break;
case SqlStatementType.DropDatabase:
#if (!NETCF)
throw new NotImplementedException();
#else
throw new NotSupportedException();
#endif
case SqlStatementType.DropDomain:
case SqlStatementType.DropException:
case SqlStatementType.DropExternalFunction:
case SqlStatementType.DropFilter:
case SqlStatementType.DropGenerator:
case SqlStatementType.DropIndex:
case SqlStatementType.DropProcedure:
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:
// 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.ExecuteProcedure:
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.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:
// 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:
// 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 (!NETCF)
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.CurrentCulture, "An exception was thrown when executing command: {0}\nBatch execution aborted\nThe returned message was: {1}", sqlStatement, ex.Message));
}
}
if (this.sqlTransaction != null)
{
// commit root transaction
this.sqlTransaction.Commit();
this.sqlTransaction = null;
}
this.sqlConnection.Close();
}
public void InitCatalog()
{
//Create the database, replacing if it exists
Hashtable parms = new Hashtable();
parms["User"] = "******";
parms["Password"] = "******";
parms["Database"] = "mercury.fdb";
parms["ServerType"] = 1;
parms["Overwrite"] = true;
parms["Dialect"] = 3;
parms["Charset"] = "UNICODE_FSS";
FbConnection.CreateDatabase(parms);
//Open a connection to the new database, and populate it with the schema elements
FbConnection conn = GetConnection();
FbBatchExecution fbe = new FbBatchExecution(conn);
Assembly asm = Assembly.GetExecutingAssembly();
//Load and run the script to create the database
TextReader tr = new StreamReader(asm.GetManifestResourceStream("Mercury.MercDb.sql"));
FbScript fbs = new FbScript(tr);
fbs.Parse();
fbe.SqlStatements = fbs.Results;
fbe.Execute();
tr.Close();
tr = new StreamReader(asm.GetManifestResourceStream("Mercury.MercDbProcs.sql"));
fbs = new FbScript(tr);
fbs.Parse();
fbe.SqlStatements = fbs.Results;
fbe.Execute();
tr.Close();
conn.Close();
}
