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

   1 //
   2 // Test/SqlDataReader.cs - to test Mono.Data.PostgreSqlClient/PgSqlDataReader.cs
   3 //
   4 // Test to do read a simple forward read only record set.
   5 // Using PgSqlCommand.ExecuteReader() to return a PgSqlDataReader
   6 // which can be used to Read a row
   7 // and Get a String or Int32.
   8 //
   9 // Author:
  10 //      Daniel Morgan <danmorg@sc.rr.com>
  11 //
  12 // (C) 2002 Daniel Morgan
  13 //
  14
  15 //
  16 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
  17 //
  18 // Permission is hereby granted, free of charge, to any person obtaining
  19 // a copy of this software and associated documentation files (the
  20 // "Software"), to deal in the Software without restriction, including
  21 // without limitation the rights to use, copy, modify, merge, publish,
  22 // distribute, sublicense, and/or sell copies of the Software, and to
  23 // permit persons to whom the Software is furnished to do so, subject to
  24 // the following conditions:
  25 //
  26 // The above copyright notice and this permission notice shall be
  27 // included in all copies or substantial portions of the Software.
  28 //
  29 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  30 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  31 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  32 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  33 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  34 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  35 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  36 //
  37
  38 using System;
  39 using System.Data;
  40 using Mono.Data.PostgreSqlClient;
  41
  42 namespace Test.Mono.Data.PostgreSqlClient {
  43         class TestPgSqlDataReader {
  44
  45                 static void Test(PgSqlConnection con, string sql,
  46                                 CommandType cmdType, CommandBehavior behavior,
  47                                 string testDesc)
  48                 {
  49                         PgSqlCommand cmd = null;
  50                         PgSqlDataReader rdr = null;
  51
  52                         int c;
  53                         int results = 0;
  54
  55                         Console.WriteLine("Test: " + testDesc);
  56                         Console.WriteLine("[BEGIN SQL]");
  57                         Console.WriteLine(sql);
  58                         Console.WriteLine("[END SQL]");
  59
  60                         cmd = new PgSqlCommand(sql, con);
  61                         cmd.CommandType = cmdType;
  62
  63                         Console.WriteLine("ExecuteReader...");
  64                         rdr = cmd.ExecuteReader(behavior);
  65
  66                         if(rdr == null) {
  67
  68                                 Console.WriteLine("IDataReader has a Null Reference.");
  69                         }
  70                         else {
  71
  72                                 do {
  73                                         // get the DataTable that holds
  74                                         // the schema
  75                                         DataTable dt = rdr.GetSchemaTable();
  76
  77                                         if(rdr.RecordsAffected != -1) {
  78                                                 // Results for
  79                                                 // SQL INSERT, UPDATE, DELETE Commands
  80                                                 // have RecordsAffected >= 0
  81                                                 Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE).  Records Affected: " + rdr.RecordsAffected);
  82                                         }
  83                                         else if (dt == null)
  84                                                 Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE).   Records Affected: " + rdr.RecordsAffected);
  85                                         else {
  86                                                 // Results for
  87                                                 // SQL not INSERT, UPDATE, nor DELETE
  88                                                 // have RecordsAffected = -1
  89                                                 Console.WriteLine("Result is from a SQL SELECT Query.  Records Affected: " + rdr.RecordsAffected);
  90
  91                                                 // Results for a SQL Command (CREATE TABLE, SET, etc)
  92                                                 // will have a null reference returned from GetSchemaTable()
  93                                                 //
  94                                                 // Results for a SQL SELECT Query
  95                                                 // will have a DataTable returned from GetSchemaTable()
  96
  97                                                 results++;
  98                                                 Console.WriteLine("Result Set " + results + "...");
  99
 100                                                 // number of columns in the table
 101                                                 Console.WriteLine("   Total Columns: " +
 102                                                         dt.Columns.Count);
 103
 104                                                 // display the schema
 105                                                 foreach (DataRow schemaRow in dt.Rows) {
 106                                                         foreach (DataColumn schemaCol in dt.Columns)
 107                                                                 Console.WriteLine(schemaCol.ColumnName +
 108                                                                         " = " +
 109                                                                         schemaRow[schemaCol]);
 110                                                         Console.WriteLine();
 111                                                 }
 112
 113                                                 int nRows = 0;
 114                                                 string output, metadataValue, dataValue;
 115                                                 // Read and display the rows
 116                                                 Console.WriteLine("Gonna do a Read() now...");
 117                                                 while(rdr.Read()) {
 118                                                         Console.WriteLine("   Row " + nRows + ": ");
 119
 120                                                         for(c = 0; c < rdr.FieldCount; c++) {
 121                                                                 // column meta data
 122                                                                 DataRow dr = dt.Rows[c];
 123                                                                 metadataValue =
 124                                                                         "    Col " +
 125                                                                         c + ": " +
 126                                                                         dr["ColumnName"];
 127
 128                                                                 // column data
 129                                                                 if(rdr.IsDBNull(c) == true)
 130                                                                         dataValue = " is NULL";
 131                                                                 else
 132                                                                         dataValue =
 133                                                                                 ": " +
 134                                                                                 rdr.GetValue(c);
 135
 136                                                                 // display column meta data and data
 137                                                                 output = metadataValue + dataValue;
 138                                                                 Console.WriteLine(output);
 139                                                         }
 140                                                         nRows++;
 141                                                 }
 142                                                 Console.WriteLine("   Total Rows: " +
 143                                                         nRows);
 144                                         }
 145                                 } while(rdr.NextResult());
 146                                 Console.WriteLine("Total Result sets: " + results);
 147
 148                                 rdr.Close();
 149                         }
 150
 151                 }
 152
 153                 [STAThread]
 154                 static void Main(string[] args) {
 155                         String connectionString = null;
 156                         connectionString =
 157                                 "host=localhost;" +
 158                                 "dbname=test;" +
 159                                 "user=postgres";
 160
 161                         PgSqlConnection con;
 162                         con = new PgSqlConnection(connectionString);
 163                         con.Open();
 164
 165                         string sql;
 166
 167                         // Text - only has one query (single query behavior)
 168                         sql = "select * from pg_tables";
 169                         Test(con, sql, CommandType.Text,
 170                                 CommandBehavior.SingleResult, "Text1");
 171
 172                         // Text - only has one query (default behavior)
 173                         sql = "select * from pg_tables";
 174                         Test(con, sql, CommandType.Text,
 175                                 CommandBehavior.Default, "Text2");
 176
 177                         // Text - has three queries
 178                         sql =
 179                                 "select * from pg_user;" +
 180                                 "select * from pg_tables;" +
 181                                 "select * from pg_database";
 182                         Test(con, sql, CommandType.Text,
 183                                 CommandBehavior.Default, "Text3Queries");
 184
 185                         // Table Direct
 186                         sql = "pg_tables";
 187                         Test(con, sql, CommandType.TableDirect,
 188                                 CommandBehavior.Default, "TableDirect1");
 189
 190                         // Stored Procedure
 191                         sql = "version";
 192                         Test(con, sql, CommandType.StoredProcedure,
 193                                 CommandBehavior.Default, "SP1");
 194
 195                         // Text - test a SQL Command (default behavior)
 196                         // Note: this not a SQL Query
 197                         sql = "SET DATESTYLE TO 'ISO'";
 198                         Test(con, sql, CommandType.Text,
 199                                 CommandBehavior.Default, "TextCmd1");
 200
 201                         con.Close();
 202                 }
 203         }
 204 }