Need some precision about PGSQLSCRIPT

Need some precision about PGSQLSCRIPT

Postby baba » Fri 22 Jan 2010 13:17

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
baba
 
Posts: 22
Joined: Thu 14 Apr 2005 16:40

Postby Shalex » Mon 25 Jan 2010 10:45

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();
        }
      }
Shalex
Devart Team
 
Posts: 7792
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for PostgreSQL