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.
9 // Daniel Morgan <danmorg@sc.rr.com>
11 // (C) 2002 Daniel Morgan
16 using System.Runtime.InteropServices;
17 using System.Diagnostics;
19 namespace LearnToCreateSqlDataReader
21 sealed public class PostgresHelper {
23 public static object OidTypeToSystem (int oid, string value) {
28 Console.WriteLine("oid 1023 varchar ==> String found");
29 obj = (object) String.Copy(value); // String
32 Console.WriteLine("oid 25 text ==> String found");
33 obj = (object) String.Copy(value); // String
36 Console.WriteLine("oid 18 char ==> String found");
37 obj = (object) String.Copy(value); // String
40 Console.WriteLine("oid 16 bool ==> Boolean found");
41 obj = (object) Boolean.Parse(value);
44 Console.WriteLine("oid 21 int2 ==> Int16 found");
\r
45 obj = (object) Int16.Parse(value);
\r
48 Console.WriteLine("oid 23 int4 ==> Int32 found");
\r
49 obj = (object) Int32.Parse(value);
\r
52 Console.WriteLine("oid 20 int8 ==> Int64 found");
\r
53 obj = (object) Int64.Parse(value);
\r
56 Console.WriteLine("OidTypeToSystem Not Done Yet: oid: " +
\r
57 oid + " Value: " + value);
\r
65 public static Type OidToType (int oid) {
75 typ = typeof(Boolean);
78 typ = typeof(Int16);
\r
81 typ = typeof(Int32);
\r
84 typ = typeof(Int64);
\r
87 throw new NotImplementedException(
\r
88 "PGNI2: PostgreSQL oid type " + oid +
\r
89 " not mapped to .NET System Type.");
\r
96 sealed public class PostgresLibrary {
98 public enum ConnStatusType {
103 CONNECTION_AWAITING_RESPONSE,
108 public enum PostgresPollingStatusType {
109 PGRES_POLLING_FAILED = 0,
110 PGRES_POLLING_READING,
111 PGRES_POLLING_WRITING,
116 public enum ExecStatusType {
117 PGRES_EMPTY_QUERY = 0,
123 PGRES_NONFATAL_ERROR,
129 public static extern string PQerrorMessage (IntPtr conn);
130 // char *PQerrorMessage(const PGconn *conn);
133 public static extern IntPtr PQconnectdb(String conninfo);
134 // PGconn *PQconnectdb(const char *conninfo)
137 public static extern void PQfinish(IntPtr conn);
138 // void PQfinish(PGconn *conn)
141 public static extern IntPtr PQexec(IntPtr conn,
143 // PGresult *PQexec(PGconn *conn, const char *query);
146 public static extern int PQntuples (IntPtr res);
147 // int PQntuples(const PGresult *res);
150 public static extern int PQnfields (IntPtr res);
151 // int PQnfields(const PGresult *res);
154 public static extern ConnStatusType PQstatus (IntPtr conn);
155 // ConnStatusType PQstatus(const PGconn *conn);
157 public static extern ExecStatusType PQresultStatus (IntPtr res);
158 // ExecStatusType PQresultStatus(const PGresult *res);
161 public static extern string PQresStatus (ExecStatusType status);
162 // char *PQresStatus(ExecStatusType status);
165 public static extern string PQresultErrorMessage (IntPtr res);
166 // char *PQresultErrorMessage(const PGresult *res);
169 public static extern int PQbinaryTuples (IntPtr res);
170 // int PQbinaryTuples(const PGresult *res);
173 public static extern string PQfname (IntPtr res,
175 // char *PQfname(const PGresult *res,
179 public static extern int PQfnumber (IntPtr res,
181 // int PQfnumber(const PGresult *res,
182 // const char *field_name);
186 public static extern int PQfmod (IntPtr res, int field_num);
187 // int PQfmod(const PGresult *res, int field_num);
190 public static extern int PQftype (IntPtr res,
192 // Oid PQftype(const PGresult *res,
196 public static extern int PQfsize (IntPtr res,
198 // int PQfsize(const PGresult *res,
202 public static extern string PQcmdStatus (IntPtr res);
203 // char *PQcmdStatus(PGresult *res);
206 public static extern string PQoidStatus (IntPtr res);
207 // char *PQoidStatus(const PGresult *res);
210 public static extern int PQoidValue (IntPtr res);
211 // Oid PQoidValue(const PGresult *res);
214 public static extern string PQcmdTuples (IntPtr res);
215 // char *PQcmdTuples(PGresult *res);
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);
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);
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);
236 public static extern void PQclear (IntPtr res);
237 // void PQclear(PGresult *res);
242 public class ReadPostgresData
250 PostgresLibrary.ConnStatusType connStatus;
255 sConnInfo = "host=localhost dbname=test user=danmorg password=viewsonic";
258 "select tid, tdesc " +
261 pgConn = PostgresLibrary.PQconnectdb (sConnInfo);
263 connStatus = PostgresLibrary.PQstatus (pgConn);
266 ConnStatusType.CONNECTION_OK) {
268 Console.WriteLine("CONNECTION_OK");
270 pgResult = PostgresLibrary.PQexec(pgConn, sQuery);
272 PostgresLibrary.ExecStatusType execStatus;
274 execStatus = PostgresLibrary.
275 PQresultStatus (pgResult);
279 ExecStatusType.PGRES_TUPLES_OK)
281 Console.WriteLine("PGRES_TUPLES_OK");
283 int nRows = PostgresLibrary.
285 Console.WriteLine("Rows: " + nRows);
287 int nFields = PostgresLibrary.
289 Console.WriteLine("Columns: " + nFields);
294 // get meta data fromm result set (schema)
295 // for each column (field)
296 for(int fieldIndex = 0;
297 fieldIndex < nFields;
301 fieldName = PostgresLibrary.
302 PQfname(pgResult, fieldIndex);
304 Console.WriteLine("Field " +
309 // get PostgreSQL data type (OID)
310 oid = PostgresLibrary.
311 PQftype(pgResult, fieldIndex);
313 Console.WriteLine("Data Type oid: " + oid);
316 // get defined size of column
317 definedSize = PostgresLibrary.
318 PQfsize(pgResult, fieldIndex);
320 Console.WriteLine("definedSize: " +
324 // for each row and column, get the data value
335 value = PostgresLibrary.
340 Console.WriteLine("Row: " + row +
342 Console.WriteLine("Value: " +
347 columnIsNull = PostgresLibrary.
348 PQgetisnull(pgResult,
351 Console.WriteLine("Data is " +
352 (columnIsNull == 0 ? "NOT NULL" : "NULL"));
357 actualLength = PostgresLibrary.
358 PQgetlength(pgResult,
361 Console.WriteLine("Actual Length: " +
367 PostgresLibrary.PQclear (pgResult);
370 // display execution error
371 errorMessage = PostgresLibrary.
372 PQresStatus(execStatus);
374 errorMessage += " " + PostgresLibrary.
375 PQresultErrorMessage(pgResult);
377 Console.WriteLine(errorMessage);
380 // close database conneciton
381 PostgresLibrary.PQfinish(pgConn);
385 errorMessage = PostgresLibrary.
386 PQerrorMessage (pgConn);
387 errorMessage += ": Could not connect to database.";
388 Console.WriteLine(errorMessage);
393 public static object ExecuteScalar(string sql) {
394 object obj = null; // return
403 PostgresLibrary.ConnStatusType connStatus;
408 sConnInfo = "host=localhost dbname=test user=danmorg password=viewsonic";
412 pgConn = PostgresLibrary.PQconnectdb (sConnInfo);
414 connStatus = PostgresLibrary.PQstatus (pgConn);
417 ConnStatusType.CONNECTION_OK) {
419 Console.WriteLine("CONNECTION_OK");
421 pgResult = PostgresLibrary.PQexec(pgConn, sQuery);
423 PostgresLibrary.ExecStatusType execStatus;
425 execStatus = PostgresLibrary.
426 PQresultStatus (pgResult);
430 ExecStatusType.PGRES_TUPLES_OK) {
432 Console.WriteLine("PGRES_TUPLES_OK");
434 int nRows = PostgresLibrary.
436 Console.WriteLine("Rows: " + nRows);
438 int nFields = PostgresLibrary.
440 Console.WriteLine("Columns: " + nFields);
441 if(nRows > 0 && nFields > 0) {
447 fieldName = PostgresLibrary.
448 PQfname(pgResult, nCol);
450 Console.WriteLine("Field " +
456 // get PostgreSQL data type (OID)
457 oid = PostgresLibrary.
458 PQftype(pgResult, nCol);
460 Console.WriteLine("Data Type oid: " + oid);
463 // get defined size of column
464 definedSize = PostgresLibrary.
465 PQfsize(pgResult, nCol);
467 Console.WriteLine("DefinedSize: " +
472 value = PostgresLibrary.
477 Console.WriteLine("Row: " + nRow +
479 Console.WriteLine("Value: " + value);
483 columnIsNull = PostgresLibrary.
484 PQgetisnull(pgResult,
487 Console.WriteLine("Data is " +
488 (columnIsNull == 0 ? "NOT NULL" : "NULL"));
492 actualLength = PostgresLibrary.
493 PQgetlength(pgResult,
496 Console.WriteLine("Actual Length: " +
499 obj = PostgresHelper.
500 OidTypeToSystem (oid, value);
504 PostgresLibrary.PQclear (pgResult);
507 // display execution error
508 errorMessage = PostgresLibrary.
509 PQresStatus(execStatus);
511 errorMessage += " " + PostgresLibrary.
512 PQresultErrorMessage(pgResult);
514 Console.WriteLine(errorMessage);
517 // close database conneciton
518 PostgresLibrary.PQfinish(pgConn);
522 errorMessage = PostgresLibrary.
523 PQerrorMessage (pgConn);
524 errorMessage += ": Could not connect to database.";
525 Console.WriteLine(errorMessage);
531 static void TestExecuteScalar() {
532 String selectStatement;
538 Int64 myCount = (Int64) ExecuteScalar(selectStatement);
539 Console.WriteLine("Count: " + myCount);
542 "select max(tdesc) " +
544 string myMax = (string) ExecuteScalar(selectStatement);
545 Console.WriteLine("Max: " + myMax);
548 Console.WriteLine(e);
554 static void Main(string[] args)
564 t = PostgresHelper.OidToType(oid); // varchar ==> String
565 Console.WriteLine("OidToType varchar oid: " + oid +
566 " ==> t: " + t.ToString());
569 t = PostgresHelper.OidToType(oid); // int4 ==> Int32
570 Console.WriteLine("OidToType int4 oid: " + oid +
571 " ==> t: " + t.ToString());