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

   1 //
   2 // TestSqlParameters.cs - test parameters for the PostgreSQL .NET Data Provider in Mono
   3 //                        using PgSqlParameter and PgSqlParameterCollection
   4 //
   5 // Note: it currently only tests input parameters.  Output is next on the list.
   6 //       Then output/input and return parameters.
   7 //
   8 // Author:
   9 //     Daniel Morgan <danmorg@sc.rr.com>
  10 //
  11 // (c)copyright 2002 Daniel Morgan
  12 //
  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
  37 using System;
  38 using System.Collections;
  39 using System.Data;
  40 using Mono.Data.PostgreSqlClient;
  41
  42 namespace TestSystemDataPgSqlClient {
  43
  44         public class TestParameters {
  45                 public static void Main() {
  46                         Console.WriteLine("** Start Test...");
  47
  48                         String connectionString = null;
  49                         connectionString =
  50                                 "host=localhost;" +
  51                                 "dbname=test;" +
  52                                 "user=postgres";
  53
  54                         PgSqlConnection con;
  55                         Console.WriteLine("** Creating connection...");
  56                         con = new PgSqlConnection(connectionString);
  57                         Console.WriteLine("** opening connection...");
  58                         con.Open();
  59
  60                         string tableName = "pg_type";
  61
  62                         string sql;
  63                         sql = "SELECT * FROM PG_TABLES WHERE TABLENAME = :inTableName";
  64
  65                         Console.WriteLine("** Creating command...");
  66                         PgSqlCommand cmd = new PgSqlCommand(sql, con);
  67
  68                         // add parameter for inTableName
  69                         Console.WriteLine("** Create parameter...");
  70                         PgSqlParameter parm = new PgSqlParameter("inTableName", DbType.String);
  71
  72                         Console.WriteLine("** set dbtype of parameter to string");
  73                         parm.DbType = DbType.String;
  74
  75                         Console.WriteLine("** set direction of parameter to input");
  76                         parm.Direction = ParameterDirection.Input;
  77
  78                         Console.WriteLine("** set value to the tableName string...");
  79                         parm.Value = tableName;
  80
  81                         Console.WriteLine("** add parameter to parameters collection in the command...");
  82                         cmd.Parameters.Add(parm);
  83
  84                         PgSqlDataReader rdr;
  85                         Console.WriteLine("** ExecuteReader()...");
  86
  87                         rdr = cmd.ExecuteReader();
  88
  89                         Console.WriteLine("[][] And now we are going to our results [][]...");
  90                         int c;
  91                         int results = 0;
  92                         do {
  93                                 results++;
  94                                 Console.WriteLine("Result Set " + results + "...");
  95
  96                                 // get the DataTable that holds
  97                                 // the schema
  98                                 DataTable dt = rdr.GetSchemaTable();
  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                                 string output, metadataValue, dataValue;
 114                                 int nRows = 0;
 115
 116                                 // Read and display the rows
 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                         } while(rdr.NextResult());
 145                         Console.WriteLine("Total Result sets: " + results);
 146
 147                         con.Close();
 148                 }
 149         }
 150 }