public static bool ExecuteBatchScript(
string connectionString,
string pathToScriptFile)
{
FbScript script = new FbScript(pathToScriptFile);
if (script.Parse() > 0)
{
using (FbConnection connection = new FbConnection(connectionString))
{
connection.Open();
try
{
FbBatchExecution batch = new FbBatchExecution(connection, script);
batch.Execute(true);
}
catch (FbException ex)
{
log.Error(ex);
throw new Exception(pathToScriptFile, ex);
}
finally
{
connection.Close();
}
}
}
return true;
}
/// <summary>
///
/// </summary>
/// <param name="pathScript"></param>
private void CreateEmbeddedDb(string pathScript)
{
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.Charset = "UTF8";
csb.ServerType = FbServerType.Embedded;
csb.Database = databaseName;
csb.UserID = "SYSDBA";
csb.Password = "******";
try {
if (!File.Exists(databaseName)) {
OpenFileDialog filedialog = new OpenFileDialog();
DialogResult res = filedialog.ShowDialog();
filedialog.Title = "Wybierz skrypt sql do utworzenia bazy";
FbConnection.CreateDatabase(csb.ToString());
// parse the SQL script
FbScript script = new FbScript(filedialog.FileName);
script.Parse();
// execute the SQL script
using (FbConnection c = new FbConnection(csb.ToString())) {
FbBatchExecution fbe = new FbBatchExecution(c);
foreach (string cmd in script.Results) {
fbe.SqlStatements.Add(cmd);
}
fbe.Execute();
}
}
}
catch (Exception ex) {
MessageBox.Show(ex.Message, "Błąd", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
public override IDatabase Execute()
{
var db = Database;
FbScript script;
using (var s = GetType().Assembly.GetManifestResourceStream(ScriptResourceName))
{
using (var r = new StreamReader(s, Encoding.UTF8))
{
script = new FbScript(r.ReadToEnd());
script.Parse();
}
}
try
{
using (var con = new FbConnection(db.ConnectionString))
{
con.Open();
}
}
catch
{
FbConnection.CreateDatabase(db.ConnectionString);
}
using (var con = new FbConnection(db.ConnectionString))
{
var be = new FbBatchExecution(con);
be.AppendSqlStatements(script);
be.Execute();
}
return db;
}
private static void ExecuteDbCreationScript()
{
using (var connection = new FbConnection(GetConnectionString()))
{
var batchExecution = new FbBatchExecution(connection);
var script = new FbScript(DbCreationScript ?? Resources.create_db);
script.Parse();
batchExecution.AppendSqlStatements(script);
batchExecution.Execute();
}
}
public static void CleanTables(FbConnection connection)
{
if (connection == null) throw new ArgumentNullException("connection");
var script = GetStringResource(
typeof(FirebirdTestObjectsInitializer).Assembly,
"Hangfire.Firebird.Tests.Clean.sql");
FbScript fbScript = new FbScript(script);
fbScript.Parse();
FbBatchExecution fbBatch = new FbBatchExecution(connection, fbScript);
fbBatch.Execute(true);
}
public static void Install(FbConnection connection)
{
if (connection == null) throw new ArgumentNullException("connection");
Log.Info("Start installing Hangfire SQL objects...");
int version = 1;
bool scriptFound = true;
do
{
try
{
var script = GetStringResource(
typeof(FirebirdObjectsInstaller).Assembly,
string.Format("Hangfire.Firebird.Install.v{0}.sql",
version.ToString(CultureInfo.InvariantCulture)));
if (!VersionAlreadyApplied(connection, version))
{
FbScript fbScript = new FbScript(script);
fbScript.Parse();
FbBatchExecution fbBatch = new FbBatchExecution(connection, fbScript);
fbBatch.Execute(true);
UpdateVersion(connection, version);
}
}
catch (FbException)
{
throw;
}
catch (Exception)
{
scriptFound = false;
}
version++;
} while (scriptFound);
Log.Info("Hangfire SQL objects installed.");
}
/// <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();
}
public static bool ExecuteBatchScript(
string connectionString,
string pathToScriptFile)
{
// http://stackoverflow.com/questions/9259034/the-type-of-the-sql-statement-could-not-be-determinated
//FbScript script = new FbScript(pathToScriptFile);
FbScript script;
using (StreamReader sr = File.OpenText(pathToScriptFile))
{
script = new FbScript(sr.ReadToEnd());
}
FbBatchExecution batch;
if (script.Parse() > 0)
{
using (FbConnection connection = new FbConnection(connectionString))
{
connection.Open();
try
{
batch = new FbBatchExecution(connection, script);
batch.Execute(true);
}
catch (FbException ex)
{
//log.Error(ex);
throw new Exception(pathToScriptFile, ex);
}
}
}
return true;
}
private void ExecuteDDL(FbConnection db, String fbDDL)
{
MemoryStream memStream = new MemoryStream();
byte[] data = Encoding.ASCII.GetBytes(fbDDL);
memStream.Write(data, 0, data.Length);
memStream.Position = 0;
TextReader txtReader = new StreamReader(memStream);
// parse the SQL script
FbScript script = new FbScript(txtReader);
int i = script.Parse();
FbBatchExecution fbe = new FbBatchExecution(db);
foreach (string cmd in script.Results)
{
fbe.SqlStatements.Add(cmd);
}
if (fbe.SqlStatements.Count > 0) fbe.Execute();
}
private void ExecuteScript(FbConnection myConnection, string scriptPath)
{
try
{
using (FbTransaction myTransaction = myConnection.BeginTransaction())
{
if (!File.Exists(scriptPath))
throw new FileNotFoundException("Script not found", scriptPath);
string script = File.ReadAllText(scriptPath);
// use FbScript to parse all statements
FbScript fbs = new FbScript(script);
fbs.Parse();
// execute all statements
FbBatchExecution fbe = new FbBatchExecution(myConnection, fbs);
fbe.CommandExecuting += (sender, args) => args.SqlCommand.Transaction = myTransaction;
fbe.Execute(true);
}
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
throw;
}
}
void CreateDb(string ConnStr, string ScriptPath)
{
var assembly = Assembly.GetExecutingAssembly();
var stream = assembly.GetManifestResourceStream(typeof(FbDatabase).Namespace + ".struct.sql");
string script = new StreamReader(stream, System.Text.Encoding.ASCII).ReadToEnd();
FbScript fbs = new FbScript(script);
fbs.Parse();
FbConnection.CreateDatabase(_conw.ConnectionString);
using (var c = new FbConnection(ConnStr))
{
c.Open();
try
{
using (FbTransaction myTransaction = c.BeginTransaction())
{
FbBatchExecution fbe = new FbBatchExecution(c, fbs);
fbe.CommandExecuting += (sender, args) => args.SqlCommand.Transaction = myTransaction;
fbe.Execute(true);
}
}
finally
{
c.Close();
}
}
}
public override void RecreateDataBase()
{
// ConnectionString Builder
FbConnectionStringBuilder csb = new FbConnectionStringBuilder();
csb.DataSource = "localhost";
csb.Dialect = 3;
csb.Charset = "UTF8";
csb.Pooling = false;
csb.UserID = "SYSDBA"; // default user
csb.Password = "******"; // default password
string serverConnectionString = csb.ToString();
csb.Database = csb.Database = FQDBFile;
string databaseConnectionString = csb.ToString();
Console.WriteLine("-------------------------");
Console.WriteLine("Using Firebird Database ");
Console.WriteLine("-------------------------");
base.RecreateDataBase();
// Create simple user
FbSecurity security = new FbSecurity();
security.ConnectionString = serverConnectionString;
var userData = security.DisplayUser(FbUserName);
if (userData == null)
{
userData = new FbUserData();
userData.UserName = FbUserName;
userData.UserPassword = FbUserPass;
security.AddUser(userData);
}
// Try to shutdown & delete database
if (File.Exists(FQDBFile))
{
FbConfiguration configuration = new FbConfiguration();
configuration.ConnectionString = databaseConnectionString;
try
{
configuration.DatabaseShutdown(FbShutdownMode.Forced, 0);
Thread.Sleep(1000);
}
finally
{
File.Delete(FQDBFile);
}
}
// Create the new DB
FbConnection.CreateDatabase(databaseConnectionString, 4096, true, true);
if (!File.Exists(FQDBFile)) throw new Exception("Database failed to create");
// Create the Schema
string script = @"
CREATE TABLE Users(
UserId integer PRIMARY KEY NOT NULL,
Name varchar(200),
Age integer,
DateOfBirth timestamp,
Savings decimal(10,5),
Is_Male smallint,
UniqueId char(38),
TimeSpan time,
TestEnum varchar(10),
HouseId integer,
SupervisorId integer
);
CREATE TABLE ExtraUserInfos(
ExtraUserInfoId integer PRIMARY KEY NOT NULL,
UserId integer NOT NULL,
Email varchar(200),
Children integer
);
CREATE TABLE Houses(
HouseId integer PRIMARY KEY NOT NULL,
Address varchar(200)
);
CREATE TABLE CompositeObjects(
Key1ID integer PRIMARY KEY NOT NULL,
Key2ID integer NOT NULL,
Key3ID integer NOT NULL,
TextData varchar(512),
DateEntered timestamp NOT NULL,
DateUpdated timestamp
);
CREATE GENERATOR USERS_USERID_GEN;
CREATE GENERATOR EXTRAUSERINFOS_ID_GEN;
CREATE GENERATOR HOUSES_HOUSEID_GEN;
SET TERM ^ ;
CREATE TRIGGER BI_USERS_USERID FOR USERS
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
IF (NEW.USERID IS NULL) THEN
NEW.USERID = GEN_ID(USERS_USERID_GEN, 1);
END^
CREATE TRIGGER BI_EXTRAUSERINFOS_ID1 FOR EXTRAUSERINFOS
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
IF (NEW.EXTRAUSERINFOID IS NULL) THEN
NEW.EXTRAUSERINFOID = GEN_ID(EXTRAUSERINFOS_ID_GEN, 1);
END^
CREATE TRIGGER BI_HOUSES_HOUSEID FOR HOUSES
ACTIVE BEFORE INSERT
POSITION 0
AS
BEGIN
IF (NEW.HOUSEID IS NULL) THEN
NEW.HOUSEID = GEN_ID(HOUSES_HOUSEID_GEN, 1);
END^
SET TERM ; ^
CREATE ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON Users TO ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON ExtraUserInfos TO ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON Houses TO ROLE %role%;
GRANT SELECT, UPDATE, INSERT, DELETE ON CompositeObjects TO ROLE %role%;
GRANT %role% TO %user%;
".Replace("%role%", FbRole).Replace("%user%", FbUserName);
/*
* Using new connection so that when a transaction is bound to Connection if it rolls back
* it doesn't blow away the tables
*/
using (var conn = new FbConnection(databaseConnectionString))
{
FbScript fbScript = new FbScript(script);
fbScript.Parse();
FbBatchExecution fbBatch = new FbBatchExecution(conn, fbScript);
fbBatch.Execute(true);
conn.Open();
Console.WriteLine("Tables (CreateDB): " + Environment.NewLine);
var dt = conn.GetSchema("Tables", new[] {null, null, null, "TABLE"});
foreach (DataRow row in dt.Rows)
{
Console.WriteLine(row[2]);
}
conn.Close();
}
}
protected override void DbCreateDatabase(DbConnection connection, int? commandTimeout,
#pragma warning disable 3001
StoreItemCollection storeItemCollection)
#pragma warning restore 3001
{
FbConnection.CreateDatabase(connection.ConnectionString, 16384, true, false);
string script = DbCreateDatabaseScript(GetDbProviderManifestToken(connection), storeItemCollection);
FbScript fbScript = new FbScript(script);
fbScript.Parse();
using (var fbConnection = new FbConnection(connection.ConnectionString))
{
var execution = new FbBatchExecution(fbConnection);
execution.AppendSqlStatements(fbScript);
execution.Execute();
}
}
/// <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();
}
private bool DoDirtyWork(string db, StreamReader sr)
{
FbScript script = new FbScript(sr);
script.Parse();
FbConnectionStringBuilder fbcs = new FbConnectionStringBuilder(db);
fbcs.Dialect = 1;
// execute the SQL script
using (FbConnection c = new FbConnection(fbcs.ConnectionString))
{
c.Open();
FbBatchExecution fbe = new FbBatchExecution(c);
fbe.CommandExecuted += new CommandExecutedEventHandler(fbe_CommandExecuted);
foreach (string cmd in script.Results)
{
if (!cmd.Contains("commit work"))
fbe.SqlStatements.Add(cmd);
}
try
{
fbe.Execute();
}
catch (Exception ex)
{
PutLog(string.Format("Work Error: {0}", ex.Message));
ThreadExceptionHandler.SendErrorMessage(string.Format("Work Error: {0}", ex.Message));
c.Close();
return false;
}
c.Close(); while (c.State != ConnectionState.Closed) ;
FbConnection.ClearAllPools();
}
return true;
}
/// <summary>
/// ExecuteScript
/// </summary>
private void ExecuteScript(TextReader textReader)
{
try
{
FbScript fbScript = new FbScript(textReader);
fbScript.Parse();
FbBatchExecution fbBatchExection = new FbBatchExecution(this.Connection);
foreach (string cmd in fbScript.Results)
{
fbBatchExection.SqlStatements.Add(cmd);
}
fbBatchExection.Execute();
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
