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

   1 //
   2 // ReadPostgresData.cs
   3 //
   4 // Uses the PostgresLibrary to retrieve a recordset.
   5 // This is not meant to be used in Production, but as a
   6 // learning aid in coding class System.Data.SqlClient.SqlDataReader.
   7 //
   8 // Author:
   9 //      Daniel Morgan <danmorg@sc.rr.com>
  10 //
  11 // (C) 2002 Daniel Morgan
  12 //
  13
  14 using System;
  15 using System.Data;
  16 using System.Runtime.InteropServices;
  17 using System.Diagnostics;
  18
  19 namespace LearnToCreateSqlDataReader
  20 {
  21         sealed public class PostgresHelper {
  22
  23                 public static object OidTypeToSystem (int oid, string value) {
  24                         object obj = null;
  25
  26                         switch(oid) {
  27                         case 1043: // varchar
  28                                 Console.WriteLine("oid 1023 varchar ==> String found");
  29                                 obj = (object) String.Copy(value); // String
  30                                 break;
  31                         case 25: // text
  32                                 Console.WriteLine("oid 25 text ==> String found");
  33                                 obj = (object) String.Copy(value); // String
  34                                 break;
  35                         case 18: // char
  36                                 Console.WriteLine("oid 18 char ==> String found");
  37                                 obj = (object) String.Copy(value); // String
  38                                 break;
  39                         case 16: // bool
  40                                 Console.WriteLine("oid 16 bool ==> Boolean found");
  41                                 obj = (object) Boolean.Parse(value);
  42                                 break;
  43                         case 21: // int2\r
  44                                 Console.WriteLine("oid 21 int2 ==> Int16 found");\r
  45                                 obj = (object) Int16.Parse(value);\r
  46                                 break;\r
  47                         case 23: // int4\r
  48                                 Console.WriteLine("oid 23 int4 ==> Int32 found");\r
  49                                 obj = (object) Int32.Parse(value);\r
  50                                 break;\r
  51                         case 20: // int8\r
  52                                 Console.WriteLine("oid 20 int8 ==> Int64 found");\r
  53                                 obj = (object) Int64.Parse(value);\r
  54                                 break;\r
  55                         default:\r
  56                                 Console.WriteLine("OidTypeToSystem Not Done Yet: oid: " +\r
  57                                         oid + " Value: " + value);\r
  58                                 break;\r
  59 \r
  60                         }
  61
  62                         return obj;
  63                 }
  64
  65                 public static Type OidToType (int oid) {
  66                         Type typ = null;
  67
  68                         switch(oid) {
  69                         case 1043: // varchar
  70                         case 25: // text
  71                         case 18: // char
  72                                 typ = typeof(String);
  73                                 break;
  74                         case 16: // bool
  75                                 typ = typeof(Boolean);
  76                                 break;
  77                         case 21: // int2\r
  78                                 typ = typeof(Int16);\r
  79                                 break;\r
  80                         case 23: // int4\r
  81                                 typ = typeof(Int32);\r
  82                                 break;\r
  83                         case 20: // int8\r
  84                                 typ = typeof(Int64);\r
  85                                 break;\r
  86                         default:\r
  87                                 throw new NotImplementedException(\r
  88                                         "PGNI2: PostgreSQL oid type " + oid +\r
  89                                         " not mapped to .NET System Type.");\r
  90                         }
  91                         return typ;
  92                 }
  93
  94         }
  95
  96         sealed public class PostgresLibrary {
  97
  98                 public enum ConnStatusType {
  99                         CONNECTION_OK,
 100                         CONNECTION_BAD,
 101                         CONNECTION_STARTED,
 102                         CONNECTION_MADE,
 103                         CONNECTION_AWAITING_RESPONSE,
 104                         CONNECTION_AUTH_OK,
 105                         CONNECTION_SETENV
 106                 }
 107
 108                 public enum PostgresPollingStatusType {
 109                         PGRES_POLLING_FAILED = 0,
 110                         PGRES_POLLING_READING,
 111                         PGRES_POLLING_WRITING,
 112                         PGRES_POLLING_OK,
 113                         PGRES_POLLING_ACTIVE
 114                 }
 115
 116                 public enum ExecStatusType {
 117                         PGRES_EMPTY_QUERY = 0,
 118                         PGRES_COMMAND_OK,
 119                         PGRES_TUPLES_OK,
 120                         PGRES_COPY_OUT,
 121                         PGRES_COPY_IN,
 122                         PGRES_BAD_RESPONSE,
 123                         PGRES_NONFATAL_ERROR,
 124                         PGRES_FATAL_ERROR
 125                 }
 126
 127
 128                 [DllImport("pq")]
 129                 public static extern string PQerrorMessage (IntPtr conn);
 130                 // char *PQerrorMessage(const PGconn *conn);
 131
 132                 [DllImport("pq")]
 133                 public static extern IntPtr PQconnectdb(String conninfo);
 134                 // PGconn *PQconnectdb(const char *conninfo)
 135
 136                 [DllImport("pq")]
 137                 public static extern void PQfinish(IntPtr conn);
 138                 // void PQfinish(PGconn *conn)
 139
 140                 [DllImport("pq")]
 141                 public static extern IntPtr PQexec(IntPtr conn,
 142                         String query);
 143                 // PGresult *PQexec(PGconn *conn,       const char *query);
 144
 145                 [DllImport("pq")]
 146                 public static extern int PQntuples (IntPtr res);
 147                 // int PQntuples(const PGresult *res);
 148
 149                 [DllImport("pq")]
 150                 public static extern int PQnfields (IntPtr res);
 151                 // int PQnfields(const PGresult *res);
 152
 153                 [DllImport("pq")]
 154                 public static extern ConnStatusType PQstatus (IntPtr conn);
 155                 // ConnStatusType PQstatus(const PGconn *conn);
 156                 [DllImport("pq")]
 157                 public static extern ExecStatusType PQresultStatus (IntPtr res);
 158                 // ExecStatusType PQresultStatus(const PGresult *res);
 159
 160                 [DllImport("pq")]
 161                 public static extern string PQresStatus (ExecStatusType status);
 162                 // char *PQresStatus(ExecStatusType status);
 163
 164                 [DllImport("pq")]
 165                 public static extern string PQresultErrorMessage (IntPtr res);
 166                 // char *PQresultErrorMessage(const PGresult *res);
 167
 168                 [DllImport("pq")]
 169                 public static extern int PQbinaryTuples (IntPtr res);
 170                 // int PQbinaryTuples(const PGresult *res);
 171
 172                 [DllImport("pq")]
 173                 public static extern string PQfname (IntPtr res,
 174                         int field_num);
 175                 // char *PQfname(const PGresult *res,
 176                 //      int field_num);
 177
 178                 [DllImport("pq")]
 179                 public static extern int PQfnumber (IntPtr res,
 180                         string field_name);
 181                 // int PQfnumber(const PGresult *res,
 182                 //      const char *field_name);
 183
 184
 185                 [DllImport("pq")]
 186                 public static extern int PQfmod (IntPtr res, int field_num);
 187                 // int PQfmod(const PGresult *res, int field_num);
 188
 189                 [DllImport("pq")]
 190                 public static extern int PQftype (IntPtr res,
 191                         int field_num);
 192                 // Oid PQftype(const PGresult *res,
 193                 //      int field_num);
 194
 195                 [DllImport("pq")]
 196                 public static extern int PQfsize (IntPtr res,
 197                         int field_num);
 198                 // int PQfsize(const PGresult *res,
 199                 //      int field_num);
 200
 201                 [DllImport("pq")]
 202                 public static extern string PQcmdStatus (IntPtr res);
 203                 // char *PQcmdStatus(PGresult *res);
 204
 205                 [DllImport("pq")]
 206                 public static extern string PQoidStatus (IntPtr res);
 207                 // char *PQoidStatus(const PGresult *res);
 208
 209                 [DllImport("pq")]
 210                 public static extern int PQoidValue (IntPtr res);
 211                 // Oid PQoidValue(const PGresult *res);
 212
 213                 [DllImport("pq")]
 214                 public static extern string PQcmdTuples (IntPtr res);
 215                 // char *PQcmdTuples(PGresult *res);
 216
 217                 [DllImport("pq")]
 218                 public static extern string PQgetvalue (IntPtr res,
 219                         int tup_num, int field_num);
 220                 // char *PQgetvalue(const PGresult *res,
 221                 //      int tup_num, int field_num);
 222
 223                 [DllImport("pq")]
 224                 public static extern int PQgetlength (IntPtr res,
 225                         int tup_num, int field_num);
 226                 // int PQgetlength(const PGresult *res,
 227                 //      int tup_num, int field_num);
 228
 229                 [DllImport("pq")]
 230                 public static extern int PQgetisnull (IntPtr res,
 231                         int tup_num, int field_num);
 232                 // int PQgetisnull(const PGresult *res,
 233                 //      int tup_num, int field_num);
 234
 235                 [DllImport("pq")]
 236                 public static extern void PQclear (IntPtr res);
 237                 // void PQclear(PGresult *res);
 238
 239
 240         }
 241
 242         public class ReadPostgresData
 243         {
 244
 245                 static void Test() {
 246                         String errorMessage;
 247
 248                         IntPtr pgConn;
 249                         String sConnInfo;
 250                         PostgresLibrary.ConnStatusType connStatus;
 251
 252                         String sQuery;
 253                         IntPtr pgResult;
 254
 255                         sConnInfo = "host=localhost dbname=test user=danmorg password=viewsonic";
 256
 257                         sQuery =
 258                                 "select tid, tdesc " +
 259                                 "from sometable ";
 260
 261                         pgConn = PostgresLibrary.PQconnectdb (sConnInfo);
 262
 263                         connStatus = PostgresLibrary.PQstatus (pgConn);
 264                         if(connStatus ==
 265                                 PostgresLibrary.
 266                                 ConnStatusType.CONNECTION_OK) {
 267
 268                                 Console.WriteLine("CONNECTION_OK");
 269
 270                                 pgResult = PostgresLibrary.PQexec(pgConn, sQuery);
 271
 272                                 PostgresLibrary.ExecStatusType execStatus;
 273
 274                                 execStatus = PostgresLibrary.
 275                                         PQresultStatus (pgResult);
 276
 277                                 if(execStatus ==
 278                                         PostgresLibrary.
 279                                         ExecStatusType.PGRES_TUPLES_OK)
 280                                 {
 281                                         Console.WriteLine("PGRES_TUPLES_OK");
 282
 283                                         int nRows = PostgresLibrary.
 284                                                 PQntuples(pgResult);
 285                                         Console.WriteLine("Rows: " + nRows);
 286
 287                                         int nFields = PostgresLibrary.
 288                                                 PQnfields(pgResult);
 289                                         Console.WriteLine("Columns: " + nFields);
 290
 291
 292                                         String fieldName;
 293
 294                                         // get meta data fromm result set (schema)
 295                                         // for each column (field)
 296                                         for(int fieldIndex = 0;
 297                                                 fieldIndex < nFields;
 298                                                 fieldIndex ++) {
 299
 300                                                 // get column name
 301                                                 fieldName = PostgresLibrary.
 302                                                         PQfname(pgResult, fieldIndex);
 303
 304                                                 Console.WriteLine("Field " +
 305                                                         fieldIndex + ": " +
 306                                                         fieldName);
 307
 308                                                 int oid;
 309                                                 // get PostgreSQL data type (OID)
 310                                                 oid = PostgresLibrary.
 311                                                         PQftype(pgResult, fieldIndex);
 312
 313                                                 Console.WriteLine("Data Type oid: " + oid);
 314
 315                                                 int definedSize;
 316                                                 // get defined size of column
 317                                                 definedSize = PostgresLibrary.
 318                                                         PQfsize(pgResult, fieldIndex);
 319
 320                                                 Console.WriteLine("definedSize: " +
 321                                                         definedSize);
 322                                         }
 323
 324                                         // for each row and column, get the data value
 325                                         for(int row = 0;
 326                                                 row < nRows;
 327                                                 row++) {
 328
 329                                                 for(int col = 0;
 330                                                         col < nFields;
 331                                                         col++) {
 332
 333                                                         String value;
 334                                                         // get data value
 335                                                         value = PostgresLibrary.
 336                                                                         PQgetvalue(
 337                                                                                 pgResult,
 338                                                                                 row, col);
 339
 340                                                         Console.WriteLine("Row: " + row +
 341                                                                         " Col: " + col);
 342                                                         Console.WriteLine("Value: " +
 343                                                                         value);
 344
 345                                                         int columnIsNull;
 346                                                         // is column NULL?
 347                                                         columnIsNull = PostgresLibrary.
 348                                                                 PQgetisnull(pgResult,
 349                                                                         row, col);
 350
 351                                                         Console.WriteLine("Data is " +
 352                                                                 (columnIsNull == 0 ? "NOT NULL" : "NULL"));
 353
 354
 355                                                         int actualLength;
 356                                                         // get Actual Length
 357                                                         actualLength = PostgresLibrary.
 358                                                                 PQgetlength(pgResult,
 359                                                                         row, col);
 360
 361                                                         Console.WriteLine("Actual Length: " +
 362                                                                 actualLength);
 363                                                 }
 364                                         }
 365
 366                                         // close result set
 367                                         PostgresLibrary.PQclear (pgResult);
 368                                 }
 369                                 else {
 370                                         // display execution error
 371                                         errorMessage = PostgresLibrary.
 372                                                 PQresStatus(execStatus);
 373
 374                                         errorMessage += " " + PostgresLibrary.
 375                                                 PQresultErrorMessage(pgResult);
 376
 377                                         Console.WriteLine(errorMessage);
 378                                 }
 379
 380                                 // close database conneciton
 381                                 PostgresLibrary.PQfinish(pgConn);
 382
 383                         }
 384                         else {
 385                                 errorMessage = PostgresLibrary.
 386                                         PQerrorMessage (pgConn);
 387                                 errorMessage += ": Could not connect to database.";
 388                                 Console.WriteLine(errorMessage);
 389                         }
 390
 391                 }
 392
 393                 public static object ExecuteScalar(string sql) {
 394                         object obj = null; // return
 395
 396                         int nRow;
 397                         int nCol;
 398
 399                         String errorMessage;
 400
 401                         IntPtr pgConn;
 402                         String sConnInfo;
 403                         PostgresLibrary.ConnStatusType connStatus;
 404
 405                         String sQuery;
 406                         IntPtr pgResult;
 407
 408                         sConnInfo = "host=localhost dbname=test user=danmorg password=viewsonic";
 409
 410                         sQuery = sql;
 411
 412                         pgConn = PostgresLibrary.PQconnectdb (sConnInfo);
 413
 414                         connStatus = PostgresLibrary.PQstatus (pgConn);
 415                         if(connStatus ==
 416                                 PostgresLibrary.
 417                                 ConnStatusType.CONNECTION_OK) {
 418
 419                                 Console.WriteLine("CONNECTION_OK");
 420
 421                                 pgResult = PostgresLibrary.PQexec(pgConn, sQuery);
 422
 423                                 PostgresLibrary.ExecStatusType execStatus;
 424
 425                                 execStatus = PostgresLibrary.
 426                                         PQresultStatus (pgResult);
 427
 428                                 if(execStatus ==
 429                                         PostgresLibrary.
 430                                         ExecStatusType.PGRES_TUPLES_OK) {
 431
 432                                         Console.WriteLine("PGRES_TUPLES_OK");
 433
 434                                         int nRows = PostgresLibrary.
 435                                                 PQntuples(pgResult);
 436                                         Console.WriteLine("Rows: " + nRows);
 437
 438                                         int nFields = PostgresLibrary.
 439                                                 PQnfields(pgResult);
 440                                         Console.WriteLine("Columns: " + nFields);
 441                                         if(nRows > 0 && nFields > 0) {
 442                                                 nRow = 0;
 443                                                 nCol = 0;
 444
 445                                                 // get column name
 446                                                 String fieldName;
 447                                                 fieldName = PostgresLibrary.
 448                                                         PQfname(pgResult, nCol);
 449
 450                                                 Console.WriteLine("Field " +
 451                                                         nCol + ": " +
 452                                                         fieldName);
 453
 454                                                 int oid;
 455
 456                                                 // get PostgreSQL data type (OID)
 457                                                 oid = PostgresLibrary.
 458                                                         PQftype(pgResult, nCol);
 459
 460                                                 Console.WriteLine("Data Type oid: " + oid);
 461
 462                                                 int definedSize;
 463                                                 // get defined size of column
 464                                                 definedSize = PostgresLibrary.
 465                                                         PQfsize(pgResult, nCol);
 466
 467                                                 Console.WriteLine("DefinedSize: " +
 468                                                         definedSize);
 469
 470                                                 String value;
 471                                                 // get data value
 472                                                 value = PostgresLibrary.
 473                                                         PQgetvalue(
 474                                                         pgResult,
 475                                                         nRow, nCol);
 476
 477                                                 Console.WriteLine("Row: " + nRow +
 478                                                         " Col: " + nCol);
 479                                                 Console.WriteLine("Value: " + value);
 480
 481                                                 int columnIsNull;
 482                                                 // is column NULL?
 483                                                 columnIsNull = PostgresLibrary.
 484                                                         PQgetisnull(pgResult,
 485                                                         nRow, nCol);
 486
 487                                                 Console.WriteLine("Data is " +
 488                                                         (columnIsNull == 0 ? "NOT NULL" : "NULL"));
 489
 490                                                 int actualLength;
 491                                                 // get Actual Length
 492                                                 actualLength = PostgresLibrary.
 493                                                         PQgetlength(pgResult,
 494                                                         nRow, nCol);
 495
 496                                                 Console.WriteLine("Actual Length: " +
 497                                                         actualLength);
 498
 499                                                 obj = PostgresHelper.
 500                                                         OidTypeToSystem (oid, value);
 501                                         }
 502
 503                                         // close result set
 504                                         PostgresLibrary.PQclear (pgResult);
 505                                 }
 506                                 else {
 507                                         // display execution error
 508                                         errorMessage = PostgresLibrary.
 509                                                 PQresStatus(execStatus);
 510
 511                                         errorMessage += " " + PostgresLibrary.
 512                                                 PQresultErrorMessage(pgResult);
 513
 514                                         Console.WriteLine(errorMessage);
 515                                 }
 516
 517                                 // close database conneciton
 518                                 PostgresLibrary.PQfinish(pgConn);
 519
 520                         }
 521                         else {
 522                                 errorMessage = PostgresLibrary.
 523                                         PQerrorMessage (pgConn);
 524                                 errorMessage += ": Could not connect to database.";
 525                                 Console.WriteLine(errorMessage);
 526                         }
 527
 528                         return obj;
 529                 }
 530
 531                 static void TestExecuteScalar() {
 532                         String selectStatement;
 533
 534                         try {
 535                                 selectStatement =
 536                                         "select count(*) " +
 537                                         "from sometable";
 538                                 Int64 myCount = (Int64) ExecuteScalar(selectStatement);
 539                                 Console.WriteLine("Count: " + myCount);
 540
 541                                 selectStatement =
 542                                         "select max(tdesc) " +
 543                                         "from sometable";
 544                                 string myMax = (string) ExecuteScalar(selectStatement);
 545                                 Console.WriteLine("Max: " + myMax);
 546                         }
 547                         catch(Exception e) {
 548                                 Console.WriteLine(e);
 549                         }
 550
 551                 }
 552
 553                 [STAThread]
 554                 static void Main(string[] args)
 555                 {
 556                         Test();
 557
 558                         TestExecuteScalar();
 559
 560                         Type t;
 561                         int oid;
 562
 563                         oid = 1043;
 564                         t = PostgresHelper.OidToType(oid); // varchar ==> String
 565                         Console.WriteLine("OidToType varchar oid: " + oid +
 566                                 " ==> t: " + t.ToString());
 567
 568                         oid = 23;
 569                         t = PostgresHelper.OidToType(oid);  // int4 ==> Int32
 570                         Console.WriteLine("OidToType int4 oid: " + oid +
 571                                 " ==> t: " + t.ToString());
 572
 573                 }
 574         }
 575 }