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

   1 /* PostgresTest.cs - based on the postgres-test.c in libgda
   2  *
   3  * Copyright (C) 2002 Gonzalo Paniagua Javier
   4  * Copyright (C) 2002 Daniel Morgan
   5  *
   6  * ORIGINAL AUTHOR:
   7  *      Gonzalo Paniagua Javier <gonzalo@gnome-db.org>
   8  * PORTING FROM C TO C# AUTHOR:
   9  *      Daniel Morgan <danmorg@sc.rr.com>
  10  *
  11  * Permission was given from the original author, Gonzalo Paniagua Javier,
  12  * to port and include his original work in Mono.
  13  *
  14  * The original work falls under the LGPL, but the port to C# falls
  15  * under the X11 license.
  16  *
  17  * This program is free software; you can redistribute it and/or
  18  * modify it under the terms of the GNU General Public License as
  19  * published by the Free Software Foundation; either version 2 of the
  20  * License, or (at your option) any later version.
  21  *
  22  * This program is distributed in the hope that it will be useful,
  23  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  24  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  25  * Library General Public License for more details.
  26  *
  27  * You should have received a copy of the GNU General Public
  28  * License along with this program; see the file COPYING.  If not,
  29  * write to the Free Software Foundation, Inc., 59 Temple Place - Suite 330,
  30  * Boston, MA 02111-1307, USA.
  31  */
  32
  33 using System;
  34 using System.Data;
  35 using System.Data.SqlClient;
  36
  37 namespace TestSystemDataSqlClient {
  38
  39         class PostgresTest {
  40
  41                 static void CreateTable (IDbConnection cnc) {
  42
  43                         IDbCommand createCommand = cnc.CreateCommand();
  44
  45                         createCommand.CommandText =
  46                                 "create table mono_postgres_test (" +
  47                                 "boolean_value boolean, " +
  48                                 "int2_value smallint, " +
  49                                 "int4_value integer, " +
  50                                 "bigint_value bigint, " +
  51                                 "float_value real, " +
  52                                 "double_value double precision, " +
  53                                 "numeric_value numeric(15, 3), " +
  54                                 "char_value char(50), " +
  55                                 "varchar_value varchar(20), " +
  56                                 "text_value text, " +
  57                                 "point_value point, " +
  58                                 "time_value time, " +
  59                                 "date_value date, " +
  60                                 "timestamp_value timestamp, " +
  61                                 "null_boolean_value boolean, " +
  62                                 "null_int2_value smallint, " +
  63                                 "null_int4_value integer, " +
  64                                 "null_bigint_value bigint, " +
  65                                 "null_float_value real, " +
  66                                 "null_double_value double precision, " +
  67                                 "null_numeric_value numeric(15, 3), " +
  68                                 "null_char_value char(50), " +
  69                                 "null_varchar_value varchar(20), " +
  70                                 "null_text_value text, " +
  71                                 "null_point_value point, " +
  72                                 "null_time_value time, " +
  73                                 "null_date_value date, " +
  74                                 "null_timestamp_value timestamp " +
  75                                 ")";
  76
  77                         createCommand.ExecuteNonQuery ();
  78                 }
  79
  80                 static void DropTable (IDbConnection cnc) {
  81
  82                         IDbCommand dropCommand = cnc.CreateCommand ();
  83
  84                         dropCommand.CommandText =
  85                                 "drop table mono_postgres_test";
  86
  87                         dropCommand.ExecuteNonQuery ();
  88                 }
  89
  90                 static object CallStoredProcedure (IDbConnection cnc) {
  91
  92                         IDbCommand callStoredProcCommand = cnc.CreateCommand ();
  93                         object data;
  94
  95                         callStoredProcCommand.CommandText =
  96                                 "select version()";
  97
  98                         data = callStoredProcCommand.ExecuteScalar ();
  99
 100                         return data;
 101                 }
 102
 103                 static void InsertData (IDbConnection cnc) {
 104
 105                         IDbCommand insertCommand = cnc.CreateCommand();
 106
 107                         insertCommand.CommandText =
 108                                 "insert into mono_postgres_test (" +
 109                                 "boolean_value, " +
 110                                 "int2_value, " +
 111                                 "int4_value, " +
 112                                 "bigint_value, " +
 113                                 "float_value, " +
 114                                 "double_value, " +
 115                                 "numeric_value, " +
 116                                 "char_value, " +
 117                                 "varchar_value, " +
 118                                 "text_value, " +
 119                                 "time_value, " +
 120                                 "date_value, " +
 121                                 "timestamp_value, " +
 122                                 "point_value " +
 123                                 ") values (" +
 124                                 "'T', " +
 125                                 "-22, " +
 126                                 "1048000, " +
 127                                 "123456789012345, " +
 128                                 "3.141592, " +
 129                                 "3.1415926969696, " +
 130                                 "123456789012.345, " +
 131                                 "'This is a char', " +
 132                                 "'This is a varchar', " +
 133                                 "'This is a text', " +
 134                                 "'21:13:14', " +
 135                                 "'2000-02-29', " +
 136                                 "'2004-02-29 14:00:11.31', " +
 137                                 "'(1,0)' " +
 138                                 ")";
 139
 140                         insertCommand.ExecuteNonQuery ();
 141                 }
 142
 143                 static IDataReader SelectData (IDbConnection cnc) {
 144
 145                         IDbCommand selectCommand = cnc.CreateCommand();
 146                         IDataReader reader;
 147
 148                         // FIXME: System.Data classes need to handle NULLs
 149                         // FIXME: System.Data needs to handle more data types
 150                         /*
 151                         selectCommand.CommandText =
 152                                 "select * " +
 153                                 "from mono_postgres_test";
 154                         */
 155
 156                         selectCommand.CommandText =
 157                                 "select " +
 158                                 "boolean_value, " +
 159                                 "int2_value, " +
 160                                 "int4_value, " +
 161                                 "bigint_value, " +
 162                                 "float_value, " +
 163                                 "double_value, " +
 164                                 "numeric_value, " +
 165                                 "char_value, " +
 166                                 "varchar_value, " +
 167                                 "text_value, " +
 168                                 "point_value, " +
 169                                 "time_value, " +
 170                                 "date_value, " +
 171                                 "timestamp_value, " +
 172                                 "null_boolean_value, " +
 173                                 "null_int2_value, " +
 174                                 "null_int4_value, " +
 175                                 "null_bigint_value, " +
 176                                 "null_float_value, " +
 177                                 "null_double_value, " +
 178                                 "null_numeric_value, " +
 179                                 "null_char_value, " +
 180                                 "null_varchar_value, " +
 181                                 "null_text_value, " +
 182                                 "null_point_value, " +
 183                                 "null_time_value, " +
 184                                 "null_date_value, " +
 185                                 "null_timestamp_value " +
 186                                 "from mono_postgres_test";
 187
 188                         reader = selectCommand.ExecuteReader ();
 189
 190                         return reader;
 191                 }
 192
 193                 static void UpdateData (IDbConnection cnc) {
 194
 195                         IDbCommand updateCommand = cnc.CreateCommand();
 196
 197                         updateCommand.CommandText =
 198                                 "update mono_postgres_test " +
 199                                 "set " +
 200                                         "boolean_value = 'F', " +
 201                                         "int2_value    = 5, " +
 202                                         "int4_value    = 3, " +
 203                                         "bigint_value  = 9, " +
 204                                         "char_value    = 'Mono.Data!'   , " +
 205                                         "varchar_value = 'It was not me!', " +
 206                                         "text_value    = 'We got data!'   " +
 207                                 "where int2_value = -22";
 208
 209                         updateCommand.ExecuteNonQuery ();
 210                 }
 211
 212                 // used to do a min(), max(), count(), sum(), or avg()
 213                 static object SelectAggregate (IDbConnection cnc, String agg) {
 214
 215                         IDbCommand selectCommand = cnc.CreateCommand();
 216                         object data;
 217
 218                         Console.WriteLine("Aggregate: " + agg);
 219
 220                         selectCommand.CommandText =
 221                                 "select " + agg +
 222                                 "from mono_postgres_test";
 223
 224                         data = selectCommand.ExecuteScalar ();
 225
 226                         Console.WriteLine("Agg Result: " + data);
 227
 228                         return data;
 229                 }
 230
 231                 /* Postgres provider tests */
 232                 static void DoPostgresTest (IDbConnection cnc) {
 233
 234                         IDataReader reader;
 235                         Object oDataValue;
 236
 237                         Console.WriteLine ("\tPostgres provider specific tests...\n");
 238
 239                         /* Drops the gda_postgres_test table. */
 240                         Console.WriteLine ("\t\tDrop table: ");
 241                         try {
 242                                 DropTable (cnc);
 243                                 Console.WriteLine ("OK");
 244                         }
 245                         catch (SqlException e) {
 246                                 Console.WriteLine("Error (don't worry about this one)" + e);
 247                         }
 248
 249                         try {
 250                                 /* Creates a table with all supported data types */
 251                                 Console.WriteLine ("\t\tCreate table with all supported types: ");
 252                                 CreateTable (cnc);
 253                                 Console.WriteLine ("OK");
 254
 255                                 /* Inserts values */
 256                                 Console.WriteLine ("\t\tInsert values for all known types: ");
 257                                 InsertData (cnc);
 258                                 Console.WriteLine ("OK");
 259
 260                                 /* Update values */
 261                                 Console.WriteLine ("\t\tUpdate values: ");
 262                                 UpdateData (cnc);
 263                                 Console.WriteLine ("OK");
 264
 265                                 /* Inserts values */
 266                                 Console.WriteLine ("\t\tInsert values for all known types: ");
 267                                 InsertData (cnc);
 268                                 Console.WriteLine ("OK");
 269
 270                                 /* Select aggregates */
 271                                 SelectAggregate (cnc, "count(*)");
 272                                 //SelectAggregate (cnc, "avg(int4_value)");
 273                                 SelectAggregate (cnc, "min(text_value)");
 274                                 SelectAggregate (cnc, "max(int4_value)");
 275                                 SelectAggregate (cnc, "sum(int4_value)");
 276
 277                                 /* Select values */
 278                                 Console.WriteLine ("\t\tSelect values from the database: ");
 279                                 reader = SelectData (cnc);
 280
 281                                 // get the DataTable that holds\r
 282                                 // the schema\r
 283                                 Console.WriteLine("\t\tGet Schema.");\r
 284                                 DataTable dt = reader.GetSchemaTable();\r
 285                         \r
 286                                 // number of columns in the table\r
 287                                 Console.WriteLine("dt.Columns.Count: " +\r
 288                                         dt.Columns.Count);\r
 289 \r
 290                                 int c;\r
 291                                 // display the schema\r
 292                                 for(c = 0; c < dt.Columns.Count; c++) {\r
 293                                         Console.WriteLine("* Column Name: " + \r
 294                                                 dt.Columns[c].ColumnName);\r
 295                                         Console.WriteLine("         MaxLength: " +\r
 296                                                 dt.Columns[c].MaxLength);\r
 297                                         Console.WriteLine("         Type: " +\r
 298                                                 dt.Columns[c].DataType);\r
 299                                 }\r
 300 \r
 301                                 int nRows = 0;\r
 302                                 string metadataValue;\r
 303                                 string dataValue;\r
 304                                 string output;\r
 305                                 // Read and display the rows\r
 306                                 while(reader.Read()) {\r
 307                                         Console.WriteLine ("Row " + nRows + ":");\r
 308                                         for(c = 0; c < reader.FieldCount; c++) {\r
 309                                                 \r
 310                                                 // column meta data \r
 311                                                 metadataValue = \r
 312                                                         "    Col " + \r
 313                                                         c + ": " + \r
 314                                                         dt.Columns[c].ColumnName;\r
 315                                                 \r
 316                                                 // column data\r
 317                                                 if(reader.IsDBNull(c) == true)\r
 318                                                         dataValue = " is NULL";\r
 319                                                 else\r
 320                                                         dataValue = \r
 321                                                                 ": " + \r
 322                                                                 reader.GetValue(c);\r
 323                                         \r
 324                                                 // display column meta data and data\r
 325                                                 output = metadataValue + dataValue;                                     \r
 326                                                 Console.WriteLine(output);\r
 327                                         }\r
 328         \r
 329                                         nRows++;\r
 330                                 }\r
 331                                 reader.Close();\r
 332                                 Console.WriteLine ("Rows: " + nRows);           \r
 333 \r
 334                                 // Call a Stored Procedure named Version()\r
 335                                 Console.WriteLine("\t\tCalling stored procedure version()");\r
 336                                 object obj = CallStoredProcedure(cnc);\r
 337                                 Console.WriteLine("Result: " + obj);\r
 338 \r
 339                                 Console.WriteLine("Database Server Version: " + \r
 340                                         ((SqlConnection)cnc).ServerVersion);\r
 341 \r
 342                                 /* Clean up */
 343                                 Console.WriteLine ("Clean up...");
 344                                 Console.WriteLine ("\t\tDrop table...");
 345                                 DropTable (cnc);
 346                                 Console.WriteLine("OK");
 347                         }
 348                         catch(Exception e) {
 349                                 Console.WriteLine("Exception caught: " + e);
 350                         }
 351                 }
 352
 353                 [STAThread]\r
 354                 static void Main(string[] args)\r
 355                 {\r
 356                         SqlConnection cnc = new SqlConnection ();\r
 357 \r
 358                         /*\r
 359                         string connectionString =
 360                                 "host=localhost;" +
 361                                 "dbname=test;" +
 362                                 "user=userid;" +
 363                                 "password=password";
 364                         */\r
 365 \r
 366                         string connectionString =
 367                                 "host=localhost;" +
 368                                 "dbname=test;" +
 369                                 "user=postgres;";
 370 \r
 371                         cnc.ConnectionString =  connectionString;\r
 372 \r
 373                         cnc.Open();\r
 374                         DoPostgresTest(cnc);
 375
 376                         cnc.Close();
 377                 }
 378         }
 379 }