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 \r
  15 using System;\r
  16 using System.Data;\r
  17 using Mono.Data.PostgreSqlClient;\r
  18 \r
  19 namespace Test.Mono.Data.PostgreSqlClient {\r
  20         class TestPgSqlDataReader {\r
  21 \r
  22                 static void Test(PgSqlConnection con, string sql, \r
  23                                 CommandType cmdType, CommandBehavior behavior,\r
  24                                 string testDesc) \r
  25                 { \r
  26                         PgSqlCommand cmd = null;\r
  27                         PgSqlDataReader rdr = null;\r
  28                         \r
  29                         int c;\r
  30                         int results = 0;\r
  31 \r
  32                         Console.WriteLine("Test: " + testDesc);\r
  33                         Console.WriteLine("[BEGIN SQL]");\r
  34                         Console.WriteLine(sql);\r
  35                         Console.WriteLine("[END SQL]");\r
  36 \r
  37                         cmd = new PgSqlCommand(sql, con);\r
  38                         cmd.CommandType = cmdType;\r
  39                                                 \r
  40                         Console.WriteLine("ExecuteReader...");\r
  41                         rdr = cmd.ExecuteReader(behavior);\r
  42 \r
  43                         if(rdr == null) {
  44
  45                                 Console.WriteLine("IDataReader has a Null Reference.");
  46                         }
  47                         else {
  48 \r
  49                                 do {\r
  50                                         // get the DataTable that holds\r
  51                                         // the schema\r
  52                                         DataTable dt = rdr.GetSchemaTable();\r
  53 \r
  54                                         if(rdr.RecordsAffected != -1) {\r
  55                                                 // Results for \r
  56                                                 // SQL INSERT, UPDATE, DELETE Commands \r
  57                                                 // have RecordsAffected >= 0\r
  58                                                 Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE).  Records Affected: " + rdr.RecordsAffected);\r
  59                                         }\r
  60                                         else if (dt == null)\r
  61                                                 Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE).   Records Affected: " + rdr.RecordsAffected);\r
  62                                         else {\r
  63                                                 // Results for\r
  64                                                 // SQL not INSERT, UPDATE, nor DELETE\r
  65                                                 // have RecordsAffected = -1\r
  66                                                 Console.WriteLine("Result is from a SQL SELECT Query.  Records Affected: " + rdr.RecordsAffected);\r
  67                         \r
  68                                                 // Results for a SQL Command (CREATE TABLE, SET, etc)\r
  69                                                 // will have a null reference returned from GetSchemaTable()\r
  70                                                 // \r
  71                                                 // Results for a SQL SELECT Query\r
  72                                                 // will have a DataTable returned from GetSchemaTable()\r
  73 \r
  74                                                 results++;\r
  75                                                 Console.WriteLine("Result Set " + results + "...");\r
  76                                                 \r
  77                                                 // number of columns in the table\r
  78                                                 Console.WriteLine("   Total Columns: " +\r
  79                                                         dt.Columns.Count);\r
  80 \r
  81                                                 // display the schema\r
  82                                                 foreach (DataRow schemaRow in dt.Rows) {\r
  83                                                         foreach (DataColumn schemaCol in dt.Columns)\r
  84                                                                 Console.WriteLine(schemaCol.ColumnName + \r
  85                                                                         " = " + \r
  86                                                                         schemaRow[schemaCol]);\r
  87                                                         Console.WriteLine();\r
  88                                                 }\r
  89 \r
  90                                                 int nRows = 0;\r
  91                                                 string output, metadataValue, dataValue;\r
  92                                                 // Read and display the rows\r
  93                                                 Console.WriteLine("Gonna do a Read() now...");\r
  94                                                 while(rdr.Read()) {\r
  95                                                         Console.WriteLine("   Row " + nRows + ": ");\r
  96                                         \r
  97                                                         for(c = 0; c < rdr.FieldCount; c++) {\r
  98                                                                 // column meta data \r
  99                                                                 DataRow dr = dt.Rows[c];\r
 100                                                                 metadataValue = \r
 101                                                                         "    Col " + \r
 102                                                                         c + ": " + \r
 103                                                                         dr["ColumnName"];\r
 104                                                 \r
 105                                                                 // column data\r
 106                                                                 if(rdr.IsDBNull(c) == true)\r
 107                                                                         dataValue = " is NULL";\r
 108                                                                 else\r
 109                                                                         dataValue = \r
 110                                                                                 ": " + \r
 111                                                                                 rdr.GetValue(c);\r
 112                                         \r
 113                                                                 // display column meta data and data\r
 114                                                                 output = metadataValue + dataValue;                                     \r
 115                                                                 Console.WriteLine(output);\r
 116                                                         }\r
 117                                                         nRows++;\r
 118                                                 }\r
 119                                                 Console.WriteLine("   Total Rows: " + \r
 120                                                         nRows);\r
 121                                         }       \r
 122                                 } while(rdr.NextResult());\r
 123                                 Console.WriteLine("Total Result sets: " + results);\r
 124                         \r
 125                                 rdr.Close();\r
 126                         }\r
 127                                         \r
 128                 }\r
 129 \r
 130                 [STAThread]\r
 131                 static void Main(string[] args) {\r
 132                         String connectionString = null;\r
 133                         connectionString =
 134                                 "host=localhost;" +
 135                                 "dbname=test;" +
 136                                 "user=postgres";
 137                                                 \r
 138                         PgSqlConnection con;\r
 139                         con = new PgSqlConnection(connectionString);\r
 140                         con.Open();\r
 141 \r
 142                         string sql;\r
 143 \r
 144                         // Text - only has one query (single query behavior)\r
 145                         sql = "select * from pg_tables";\r
 146                         Test(con, sql, CommandType.Text, \r
 147                                 CommandBehavior.SingleResult, "Text1");\r
 148 \r
 149                         // Text - only has one query (default behavior)\r
 150                         sql = "select * from pg_tables";\r
 151                         Test(con, sql, CommandType.Text, \r
 152                                 CommandBehavior.Default, "Text2");\r
 153                         \r
 154                         // Text - has three queries\r
 155                         sql =\r
 156                                 "select * from pg_user;" + \r
 157                                 "select * from pg_tables;" + \r
 158                                 "select * from pg_database";\r
 159                         Test(con, sql, CommandType.Text, \r
 160                                 CommandBehavior.Default, "Text3Queries");\r
 161                         \r
 162                         // Table Direct\r
 163                         sql = "pg_tables";\r
 164                         Test(con, sql, CommandType.TableDirect, \r
 165                                 CommandBehavior.Default, "TableDirect1");\r
 166 \r
 167                         // Stored Procedure\r
 168                         sql = "version";\r
 169                         Test(con, sql, CommandType.StoredProcedure, \r
 170                                 CommandBehavior.Default, "SP1");\r
 171 \r
 172                         // Text - test a SQL Command (default behavior)\r
 173                         // Note: this not a SQL Query\r
 174                         sql = "SET DATESTYLE TO 'ISO'";\r
 175                         Test(con, sql, CommandType.Text, \r
 176                                 CommandBehavior.Default, "TextCmd1");\r
 177 \r
 178                         con.Close();\r
 179                 }\r
 180         }\r
 181 }\r