mcs/class/System.Data/Test/TestSqlException.cs

   1 //
   2 // TestPgSqlInsert.cs
   3 //
   4 // To Test PgSqlConnection and PgSqlCommand by connecting
   5 // to a PostgreSQL database
   6 // and then executing an INSERT SQL statement
   7 //
   8 // To use:
   9 //   change strings to your database, userid, tables, etc...:
  10 //        connectionString
  11 //        insertStatement
  12 //
  13 // To test:
  14 //   mcs TestPgSqlInsert.cs -r System.Data
  15 //   mint TestPgSqlInsert.exe
  16 //
  17 // Author:
  18 //   Rodrigo Moya (rodrigo@ximian.com)
  19 //   Daniel Morgan (danmorg@sc.rr.com)
  20 //
  21 // (C) Ximian, Inc 2002
  22 //
  23
  24 using System;
  25 using System.Data;
  26 using Mono.Data.PostgreSqlClient;
  27
  28 namespace TestSystemDataPgSqlClient
  29 {
  30         class TestPgSqlInsert
  31         {
  32                 [STAThread]
  33                 static void Main(string[] args) {
  34                         PgSqlConnection conn = null;
  35                         PgSqlCommand cmd = null;
  36                         PgSqlTransaction trans = null;
  37
  38                         int rowsAffected = -1;
  39
  40                         String connectionString = "";
  41                         String insertStatement = "";
  42                         String deleteStatement = "";
  43
  44                         connectionString =
  45                                 "host=localhost;" +
  46                                 "dbname=test;" +
  47                                 "user=postgres";
  48
  49                         insertStatement =
  50                                 "insert into NoSuchTable " +
  51                                 "(tid, tdesc) " +
  52                                 "values ('beer', 'Beer for All!') ";
  53
  54                         deleteStatement =
  55                                 "delete from sometable " +
  56                                 "where tid = 'beer' ";
  57
  58                         try {
  59                                 // Connect to a PostgreSQL database
  60                                 Console.WriteLine ("Connect to database...");
  61                                 conn = new PgSqlConnection(connectionString);
  62                                 conn.Open();
  63
  64                                 // begin transaction
  65                                 Console.WriteLine ("Begin Transaction...");
  66                                 trans = conn.BeginTransaction();
  67
  68                                 // create SQL DELETE command
  69                                 Console.WriteLine ("Create Command initializing " +
  70                                         "with an DELETE statement...");
  71                                 cmd = new PgSqlCommand (deleteStatement, conn);
  72
  73                                 // execute the DELETE SQL command
  74                                 Console.WriteLine ("Execute DELETE SQL Command...");
  75                                 rowsAffected = cmd.ExecuteNonQuery();
  76                                 Console.WriteLine ("Rows Affected: " + rowsAffected);
  77
  78                                 // change the SQL command to an SQL INSERT Command
  79                                 Console.WriteLine ("Now use INSERT SQL Command...");
  80                                 cmd.CommandText = insertStatement;
  81
  82                                 // execute the INSERT SQL command
  83                                 Console.WriteLine ("Execute INSERT SQL Command...");
  84                                 rowsAffected = cmd.ExecuteNonQuery();
  85                                 Console.WriteLine ("Rows Affected: " + rowsAffected);
  86
  87                                 // if successfull at INSERT, commit the transaction,
  88                                 // otherwise, do a rollback the transaction using
  89                                 // trans.Rollback();
  90                                 Console.WriteLine ("Commit transaction...");
  91                                 trans.Commit();
  92
  93                                 // Close connection to database
  94                                 Console.WriteLine ("Close database connection...");
  95                                 conn.Close();
  96
  97                                 Console.WriteLine ("Assuming everything " +
  98                                         "was successful.");
  99                                 Console.WriteLine ("Verify data in database to " +
 100                                         "see if row is there.");
 101                         }
 102                         catch(PgSqlException e) {
 103                                 // Display the SQL Errors and Rollback the database
 104                                 Console.WriteLine("PgSqlException caught: " +
 105                                         e.ToString());
 106                                 if(trans != null) {
 107                                         trans.Rollback();
 108                                         Console.WriteLine("Database has been Rolled back!");
 109                                 }
 110                         }
 111                         finally {
 112                                 if(conn != null)
 113                                         if(conn.State == ConnectionState.Open)
 114                                                 conn.Close();
 115                         }
 116                 }
 117         }
 118 }