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

   1 //\r
   2 // TestSqlParameters.cs - test parameters for the PostgreSQL .NET Data Provider in Mono\r
   3 //                        using PgSqlParameter and PgSqlParameterCollection\r
   4 //\r
   5 // Note: it currently only tests input parameters.  Output is next on the list.\r
   6 //       Then output/input and return parameters.\r
   7 //\r
   8 // Author: \r
   9 //     Daniel Morgan <danmorg@sc.rr.com>\r
  10 //\r
  11 // (c)copyright 2002 Daniel Morgan\r
  12 //\r
  13
  14 //
  15 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
  16 //
  17 // Permission is hereby granted, free of charge, to any person obtaining
  18 // a copy of this software and associated documentation files (the
  19 // "Software"), to deal in the Software without restriction, including
  20 // without limitation the rights to use, copy, modify, merge, publish,
  21 // distribute, sublicense, and/or sell copies of the Software, and to
  22 // permit persons to whom the Software is furnished to do so, subject to
  23 // the following conditions:
  24 //
  25 // The above copyright notice and this permission notice shall be
  26 // included in all copies or substantial portions of the Software.
  27 //
  28 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  29 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  30 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  31 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  32 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  33 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  34 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  35 //
  36 \r
  37 using System;\r
  38 using System.Collections;\r
  39 using System.Data;\r
  40 using Mono.Data.PostgreSqlClient;\r
  41 \r
  42 namespace TestSystemDataPgSqlClient {\r
  43 \r
  44         public class TestParameters {\r
  45                 public static void Main() {\r
  46                         Console.WriteLine("** Start Test...");\r
  47                         \r
  48                         String connectionString = null;\r
  49                         connectionString =
  50                                 "host=localhost;" +
  51                                 "dbname=test;" +
  52                                 "user=postgres";
  53                                                 \r
  54                         PgSqlConnection con;\r
  55                         Console.WriteLine("** Creating connection...");\r
  56                         con = new PgSqlConnection(connectionString);\r
  57                         Console.WriteLine("** opening connection...");\r
  58                         con.Open();\r
  59                 \r
  60                         string tableName = "pg_type";\r
  61 \r
  62                         string sql;\r
  63                         sql = "SELECT * FROM PG_TABLES WHERE TABLENAME = :inTableName";\r
  64                                                 \r
  65                         Console.WriteLine("** Creating command...");\r
  66                         PgSqlCommand cmd = new PgSqlCommand(sql, con);\r
  67                         \r
  68                         // add parameter for inTableName\r
  69                         Console.WriteLine("** Create parameter...");\r
  70                         PgSqlParameter parm = new PgSqlParameter("inTableName", DbType.String);\r
  71                         \r
  72                         Console.WriteLine("** set dbtype of parameter to string");\r
  73                         parm.DbType = DbType.String;\r
  74                         \r
  75                         Console.WriteLine("** set direction of parameter to input");\r
  76                         parm.Direction = ParameterDirection.Input;\r
  77                         \r
  78                         Console.WriteLine("** set value to the tableName string...");\r
  79                         parm.Value = tableName;\r
  80                         \r
  81                         Console.WriteLine("** add parameter to parameters collection in the command...");\r
  82                         cmd.Parameters.Add(parm);\r
  83                         \r
  84                         PgSqlDataReader rdr;\r
  85                         Console.WriteLine("** ExecuteReader()...");\r
  86                         \r
  87                         rdr = cmd.ExecuteReader();\r
  88                         \r
  89                         Console.WriteLine("[][] And now we are going to our results [][]...");\r
  90                         int c;\r
  91                         int results = 0;\r
  92                         do {\r
  93                                 results++;\r
  94                                 Console.WriteLine("Result Set " + results + "...");\r
  95 \r
  96                                 // get the DataTable that holds\r
  97                                 // the schema\r
  98                                 DataTable dt = rdr.GetSchemaTable();\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                                 string output, metadataValue, dataValue;\r
 114                                 int nRows = 0;\r
 115 \r
 116                                 // Read and display the rows\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                         } while(rdr.NextResult());\r
 145                         Console.WriteLine("Total Result sets: " + results);\r
 146 \r
 147                         con.Close();\r
 148                 }\r
 149         }\r
 150 }\r