Need some precision about PGSQLSCRIPT

[baba]

Hello,

I want to execute a script as a transaction. If that transaction succeed, then i want to do something else, else nothing.

First block as a transaction :

UPDATE ..... ;
Update something else...;
UPdate some more ;

Then :


some code...

another code


I would like to do all of that in the script if possible, for speed.

How would you write this script ?
I code in C#
If this cannot be done in only one script, can you please give the script, to be executed as a single transaction, the way to detect if the transaction commited, or rolled-back ?

Seb

[Shalex]

Here is a possible way you can play with the try...catch block in the scope of the PgSqlTransaction object:

Code: Select all

      using (PgSqlTransaction trans = pgConnection.BeginTransaction()) {
        try {
          try {
            pgSqlScript.ScriptText = "1st script";
            pgSqlScript.Execute();
          }
          catch (PgSqlException ex) {
            // if 1st script failed
            pgSqlScript.ScriptText = "2nd script";
            pgSqlScript.Execute();
            trans.Commit();
            throw new Exception("2nd script executed successfully");
          }
          //if 1st script succeded
          pgSqlScript.ScriptText = "3d script";
          pgSqlScript.Execute();
          trans.Commit();
        }
        catch (Exception e) {
          if (e.Message != "2nd script executed successfully")
            trans.Rollback();
        }
      }