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

   1 //\r
   2 // Test/SqlDataReader.cs - to test Mono.Data.PostgreSqlClient/PgSqlDataReader.cs\r
   3 //\r
   4 // Test to do read a simple forward read only record set.\r
   5 // Using PgSqlCommand.ExecuteReader() to return a PgSqlDataReader\r
   6 // which can be used to Read a row\r
   7 // and Get a String or Int32.\r
   8 //\r
   9 // Author:\r
  10 //      Daniel Morgan <danmorg@sc.rr.com>\r
  11 //\r
  12 // (C) 2002 Daniel Morgan\r
  13 //\r
  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 \r
  38 using System;\r
  39 using System.Data;\r
  40 using Mono.Data.PostgreSqlClient;\r
  41 \r
  42 namespace Test.Mono.Data.PostgreSqlClient {\r
  43         class TestPgSqlDataReader {\r
  44 \r
  45                 static void Test(PgSqlConnection con, string sql, \r
  46                                 CommandType cmdType, CommandBehavior behavior,\r
  47                                 string testDesc) \r
  48                 { \r
  49                         PgSqlCommand cmd = null;\r
  50                         PgSqlDataReader rdr = null;\r
  51                         \r
  52                         int c;\r
  53                         int results = 0;\r
  54 \r
  55                         Console.WriteLine("Test: " + testDesc);\r
  56                         Console.WriteLine("[BEGIN SQL]");\r
  57                         Console.WriteLine(sql);\r
  58                         Console.WriteLine("[END SQL]");\r
  59 \r
  60                         cmd = new PgSqlCommand(sql, con);\r
  61                         cmd.CommandType = cmdType;\r
  62                                                 \r
  63                         Console.WriteLine("ExecuteReader...");\r
  64                         rdr = cmd.ExecuteReader(behavior);\r
  65 \r
  66                         if(rdr == null) {
  67
  68                                 Console.WriteLine("IDataReader has a Null Reference.");
  69                         }
  70                         else {
  71 \r
  72                                 do {\r
  73                                         // get the DataTable that holds\r
  74                                         // the schema\r
  75                                         DataTable dt = rdr.GetSchemaTable();\r
  76 \r
  77                                         if(rdr.RecordsAffected != -1) {\r
  78                                                 // Results for \r
  79                                                 // SQL INSERT, UPDATE, DELETE Commands \r
  80                                                 // have RecordsAffected >= 0\r
  81                                                 Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE).  Records Affected: " + rdr.RecordsAffected);\r
  82                                         }\r
  83                                         else if (dt == null)\r
  84                                                 Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE).   Records Affected: " + rdr.RecordsAffected);\r
  85                                         else {\r
  86                                                 // Results for\r
  87                                                 // SQL not INSERT, UPDATE, nor DELETE\r
  88                                                 // have RecordsAffected = -1\r
  89                                                 Console.WriteLine("Result is from a SQL SELECT Query.  Records Affected: " + rdr.RecordsAffected);\r
  90                         \r
  91                                                 // Results for a SQL Command (CREATE TABLE, SET, etc)\r
  92                                                 // will have a null reference returned from GetSchemaTable()\r
  93                                                 // \r
  94                                                 // Results for a SQL SELECT Query\r
  95                                                 // will have a DataTable returned from GetSchemaTable()\r
  96 \r
  97                                                 results++;\r
  98                                                 Console.WriteLine("Result Set " + results + "...");\r
  99                                                 \r
 100                                                 // number of columns in the table\r
 101                                                 Console.WriteLine("   Total Columns: " +\r
 102                                                         dt.Columns.Count);\r
 103 \r
 104                                                 // display the schema\r
 105                                                 foreach (DataRow schemaRow in dt.Rows) {\r
 106                                                         foreach (DataColumn schemaCol in dt.Columns)\r
 107                                                                 Console.WriteLine(schemaCol.ColumnName + \r
 108                                                                         " = " + \r
 109                                                                         schemaRow[schemaCol]);\r
 110                                                         Console.WriteLine();\r
 111                                                 }\r
 112 \r
 113                                                 int nRows = 0;\r
 114                                                 string output, metadataValue, dataValue;\r
 115                                                 // Read and display the rows\r
 116                                                 Console.WriteLine("Gonna do a Read() now...");\r
 117                                                 while(rdr.Read()) {\r
 118                                                         Console.WriteLine("   Row " + nRows + ": ");\r
 119                                         \r
 120                                                         for(c = 0; c < rdr.FieldCount; c++) {\r
 121                                                                 // column meta data \r
 122                                                                 DataRow dr = dt.Rows[c];\r
 123                                                                 metadataValue = \r
 124                                                                         "    Col " + \r
 125                                                                         c + ": " + \r
 126                                                                         dr["ColumnName"];\r
 127                                                 \r
 128                                                                 // column data\r
 129                                                                 if(rdr.IsDBNull(c) == true)\r
 130                                                                         dataValue = " is NULL";\r
 131                                                                 else\r
 132                                                                         dataValue = \r
 133                                                                                 ": " + \r
 134                                                                                 rdr.GetValue(c);\r
 135                                         \r
 136                                                                 // display column meta data and data\r
 137                                                                 output = metadataValue + dataValue;                                     \r
 138                                                                 Console.WriteLine(output);\r
 139                                                         }\r
 140                                                         nRows++;\r
 141                                                 }\r
 142                                                 Console.WriteLine("   Total Rows: " + \r
 143                                                         nRows);\r
 144                                         }       \r
 145                                 } while(rdr.NextResult());\r
 146                                 Console.WriteLine("Total Result sets: " + results);\r
 147                         \r
 148                                 rdr.Close();\r
 149                         }\r
 150                                         \r
 151                 }\r
 152 \r
 153                 [STAThread]\r
 154                 static void Main(string[] args) {\r
 155                         String connectionString = null;\r
 156                         connectionString =
 157                                 "host=localhost;" +
 158                                 "dbname=test;" +
 159                                 "user=postgres";
 160                                                 \r
 161                         PgSqlConnection con;\r
 162                         con = new PgSqlConnection(connectionString);\r
 163                         con.Open();\r
 164 \r
 165                         string sql;\r
 166 \r
 167                         // Text - only has one query (single query behavior)\r
 168                         sql = "select * from pg_tables";\r
 169                         Test(con, sql, CommandType.Text, \r
 170                                 CommandBehavior.SingleResult, "Text1");\r
 171 \r
 172                         // Text - only has one query (default behavior)\r
 173                         sql = "select * from pg_tables";\r
 174                         Test(con, sql, CommandType.Text, \r
 175                                 CommandBehavior.Default, "Text2");\r
 176                         \r
 177                         // Text - has three queries\r
 178                         sql =\r
 179                                 "select * from pg_user;" + \r
 180                                 "select * from pg_tables;" + \r
 181                                 "select * from pg_database";\r
 182                         Test(con, sql, CommandType.Text, \r
 183                                 CommandBehavior.Default, "Text3Queries");\r
 184                         \r
 185                         // Table Direct\r
 186                         sql = "pg_tables";\r
 187                         Test(con, sql, CommandType.TableDirect, \r
 188                                 CommandBehavior.Default, "TableDirect1");\r
 189 \r
 190                         // Stored Procedure\r
 191                         sql = "version";\r
 192                         Test(con, sql, CommandType.StoredProcedure, \r
 193                                 CommandBehavior.Default, "SP1");\r
 194 \r
 195                         // Text - test a SQL Command (default behavior)\r
 196                         // Note: this not a SQL Query\r
 197                         sql = "SET DATESTYLE TO 'ISO'";\r
 198                         Test(con, sql, CommandType.Text, \r
 199                                 CommandBehavior.Default, "TextCmd1");\r
 200 \r
 201                         con.Close();\r
 202                 }\r
 203         }\r
 204 }\r