/// <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(); }