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 \r
  14 using System;\r
  15 using System.Collections;\r
  16 using System.Data;\r
  17 using Mono.Data.PostgreSqlClient;\r
  18 \r
  19 namespace TestSystemDataPgSqlClient {\r
  20 \r
  21         public class TestParameters {\r
  22                 public static void Main() {\r
  23                         Console.WriteLine("** Start Test...");\r
  24                         \r
  25                         String connectionString = null;\r
  26                         connectionString =
  27                                 "host=localhost;" +
  28                                 "dbname=test;" +
  29                                 "user=postgres";
  30                                                 \r
  31                         PgSqlConnection con;\r
  32                         Console.WriteLine("** Creating connection...");\r
  33                         con = new PgSqlConnection(connectionString);\r
  34                         Console.WriteLine("** opening connection...");\r
  35                         con.Open();\r
  36                 \r
  37                         string tableName = "pg_type";\r
  38 \r
  39                         string sql;\r
  40                         sql = "SELECT * FROM PG_TABLES WHERE TABLENAME = :inTableName";\r
  41                                                 \r
  42                         Console.WriteLine("** Creating command...");\r
  43                         PgSqlCommand cmd = new PgSqlCommand(sql, con);\r
  44                         \r
  45                         // add parameter for inTableName\r
  46                         Console.WriteLine("** Create parameter...");\r
  47                         PgSqlParameter parm = new PgSqlParameter("inTableName", DbType.String);\r
  48                         \r
  49                         Console.WriteLine("** set dbtype of parameter to string");\r
  50                         parm.DbType = DbType.String;\r
  51                         \r
  52                         Console.WriteLine("** set direction of parameter to input");\r
  53                         parm.Direction = ParameterDirection.Input;\r
  54                         \r
  55                         Console.WriteLine("** set value to the tableName string...");\r
  56                         parm.Value = tableName;\r
  57                         \r
  58                         Console.WriteLine("** add parameter to parameters collection in the command...");\r
  59                         cmd.Parameters.Add(parm);\r
  60                         \r
  61                         PgSqlDataReader rdr;\r
  62                         Console.WriteLine("** ExecuteReader()...");\r
  63                         \r
  64                         rdr = cmd.ExecuteReader();\r
  65                         \r
  66                         Console.WriteLine("[][] And now we are going to our results [][]...");\r
  67                         int c;\r
  68                         int results = 0;\r
  69                         do {\r
  70                                 results++;\r
  71                                 Console.WriteLine("Result Set " + results + "...");\r
  72 \r
  73                                 // get the DataTable that holds\r
  74                                 // the schema\r
  75                                 DataTable dt = rdr.GetSchemaTable();\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                                 string output, metadataValue, dataValue;\r
  91                                 int nRows = 0;\r
  92 \r
  93                                 // Read and display the rows\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                         } while(rdr.NextResult());\r
 122                         Console.WriteLine("Total Result sets: " + results);\r
 123 \r
 124                         con.Close();\r
 125                 }\r
 126         }\r
 127 }\r