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

   1 //
   2 // TestSqlInsert.cs
   3 //
   4 // To Test SqlConnection and SqlCommand 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 TestSqlInsert.cs -r System.Data
  15 //   mint TestSqlInsert.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 System.Data.SqlClient;
  27
  28 namespace TestSystemDataSqlClient
  29 {
  30         class TestSqlInsert
  31         {
  32                 [STAThread]
  33                 static void Main(string[] args)
  34                 {
  35                         SqlConnection conn;
  36                         SqlCommand cmd;
  37                         SqlTransaction trans;
  38
  39                         int rowsAffected;
  40
  41                         String connectionString;
  42                         String insertStatement;
  43                         String deleteStatement;
  44
  45                         connectionString =
  46                                 "host=localhost;" +
  47                                 "dbname=test;" +
  48                                 "user=postgres";
  49
  50                         insertStatement =
  51                                 "insert into sometable " +
  52                                 "(tid, tdesc) " +
  53                                 "values ('beer', 'Beer for All!') ";
  54
  55                         deleteStatement =
  56                                 "delete from sometable " +
  57                                 "where tid = 'beer' ";
  58
  59                         // Connect to a PostgreSQL database
  60                         Console.WriteLine ("Connect to database...");
  61                         conn = new SqlConnection(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 SqlCommand (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                         // FIXME: need to have exceptions working in
  91                         //        SqlClient classes before you can do rollback
  92                         Console.WriteLine ("Commit transaction...");
  93                         trans.Commit();
  94
  95                         // Close connection to database
  96                         Console.WriteLine ("Close database connection...");
  97                         conn.Close();
  98
  99                         Console.WriteLine ("Assuming everything " +
 100                                 "was successful.");
 101                         Console.WriteLine ("Verify data in database to " +
 102                                 "see if row is there.");
 103                 }
 104         }
 105 }