1 /* PostgresTest.cs - based on the postgres-test.c in libgda
3 * Copyright (C) 2002 Gonzalo Paniagua Javier
4 * Copyright (C) 2002 Daniel Morgan
7 * Gonzalo Paniagua Javier <gonzalo@gnome-db.org>
8 * PORTING FROM C TO C# AUTHOR:
9 * Daniel Morgan <danmorg@sc.rr.com>
11 * Permission was given from the original author, Gonzalo Paniagua Javier,
12 * to port and include his original work in Mono.
14 * The original work falls under the LGPL, but the port to C# falls
15 * under the X11 license.
17 * This program is free software; you can redistribute it and/or
18 * modify it under the terms of the GNU General Public License as
19 * published by the Free Software Foundation; either version 2 of the
20 * License, or (at your option) any later version.
22 * This program is distributed in the hope that it will be useful,
23 * but WITHOUT ANY WARRANTY; without even the implied warranty of
24 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
25 * Library General Public License for more details.
27 * You should have received a copy of the GNU General Public
28 * License along with this program; see the file COPYING. If not,
29 * write to the Free Software Foundation, Inc., 59 Temple Place - Suite 330,
30 * Boston, MA 02111-1307, USA.
35 using Mono.Data.PostgreSqlClient;
37 namespace Test.Mono.Data.PostgreSqlClient {
41 // execute SQL CREATE TABLE Command using ExecuteNonQuery()
42 static void CreateTable (IDbConnection cnc) {
44 IDbCommand createCommand = cnc.CreateCommand();
46 createCommand.CommandText =
47 "create table mono_postgres_test (" +
48 "boolean_value boolean, " +
49 "int2_value smallint, " +
50 "int4_value integer, " +
51 "bigint_value bigint, " +
52 "float_value real, " +
53 "double_value double precision, " +
54 "numeric_value numeric(15, 3), " +
55 "char_value char(50), " +
56 "varchar_value varchar(20), " +
58 "point_value point, " +
61 "timestamp_value timestamp, " +
62 "null_boolean_value boolean, " +
63 "null_int2_value smallint, " +
64 "null_int4_value integer, " +
65 "null_bigint_value bigint, " +
66 "null_float_value real, " +
67 "null_double_value double precision, " +
68 "null_numeric_value numeric(15, 3), " +
69 "null_char_value char(50), " +
70 "null_varchar_value varchar(20), " +
71 "null_text_value text, " +
72 "null_point_value point, " +
73 "null_time_value time, " +
74 "null_date_value date, " +
75 "null_timestamp_value timestamp " +
78 createCommand.ExecuteNonQuery ();
81 // execute SQL DROP TABLE Command using ExecuteNonQuery
82 static void DropTable (IDbConnection cnc) {
84 IDbCommand dropCommand = cnc.CreateCommand ();
86 dropCommand.CommandText =
87 "drop table mono_postgres_test";
89 dropCommand.ExecuteNonQuery ();
92 // execute stored procedure using ExecuteScalar()
93 static object CallStoredProcedure (IDbConnection cnc) {
95 IDbCommand callStoredProcCommand = cnc.CreateCommand ();
98 callStoredProcCommand.CommandType =
99 CommandType.StoredProcedure;
100 callStoredProcCommand.CommandText =
103 data = callStoredProcCommand.ExecuteScalar ();
108 // execute SQL INSERT Command using ExecuteNonQuery()
109 static void InsertData (IDbConnection cnc) {
111 IDbCommand insertCommand = cnc.CreateCommand();
113 insertCommand.CommandText =
114 "insert into mono_postgres_test (" +
127 "timestamp_value, " +
133 "123456789012345, " +
135 "3.1415926969696, " +
136 "123456789012.345, " +
137 "'This is a char', " +
138 "'This is a varchar', " +
139 "'This is a text', " +
142 "'2004-02-29 14:00:11.31', " +
146 insertCommand.ExecuteNonQuery ();
149 // execute a SQL SELECT Query using ExecuteReader() to retrieve
150 // a IDataReader so we retrieve data
151 static IDataReader SelectData (IDbConnection cnc) {
153 IDbCommand selectCommand = cnc.CreateCommand();
156 // FIXME: System.Data classes need to handle NULLs
157 // this would be done by System.DBNull ?
158 // FIXME: System.Data needs to handle more data types
160 selectCommand.CommandText =
162 "from mono_postgres_test";
165 selectCommand.CommandText =
180 "timestamp_value, " +
181 "null_boolean_value, " +
182 "null_int2_value, " +
183 "null_int4_value, " +
184 "null_bigint_value, " +
185 "null_float_value, " +
186 "null_double_value, " +
187 "null_numeric_value, " +
188 "null_char_value, " +
189 "null_varchar_value, " +
190 "null_text_value, " +
191 "null_point_value, " +
192 "null_time_value, " +
193 "null_date_value, " +
194 "null_timestamp_value " +
195 "from mono_postgres_test";
197 reader = selectCommand.ExecuteReader ();
202 // Tests a SQL Command (INSERT, UPDATE, DELETE)
203 // executed via ExecuteReader
204 static IDataReader SelectDataUsingInsertCommand (IDbConnection cnc) {
206 IDbCommand selectCommand = cnc.CreateCommand();
209 // This is a SQL INSERT Command, not a Query
210 selectCommand.CommandText =
211 "insert into mono_postgres_test (" +
224 "timestamp_value, " +
230 "123456789012345, " +
232 "3.1415926969696, " +
233 "123456789012.345, " +
234 "'This is a char', " +
235 "'This is a varchar', " +
236 "'This is a text', " +
239 "'2004-02-29 14:00:11.31', " +
243 reader = selectCommand.ExecuteReader ();
248 // Tests a SQL Command not (INSERT, UPDATE, DELETE)
249 // executed via ExecuteReader
250 static IDataReader SelectDataUsingCommand (IDbConnection cnc) {
252 IDbCommand selectCommand = cnc.CreateCommand();
255 // This is a SQL Command, not a Query
256 selectCommand.CommandText =
257 "SET DATESTYLE TO 'ISO'";
259 reader = selectCommand.ExecuteReader ();
265 // execute an SQL UPDATE Command using ExecuteNonQuery()
266 static void UpdateData (IDbConnection cnc) {
268 IDbCommand updateCommand = cnc.CreateCommand();
270 updateCommand.CommandText =
271 "update mono_postgres_test " +
273 "boolean_value = 'F', " +
276 "bigint_value = 9, " +
277 "char_value = 'Mono.Data!' , " +
278 "varchar_value = 'It was not me!', " +
279 "text_value = 'We got data!' " +
280 "where int2_value = -22";
282 updateCommand.ExecuteNonQuery ();
285 // used to do a min(), max(), count(), sum(), or avg()
286 // execute SQL SELECT Query using ExecuteScalar
287 static object SelectAggregate (IDbConnection cnc, String agg) {
289 IDbCommand selectCommand = cnc.CreateCommand();
292 Console.WriteLine("Aggregate: " + agg);
294 selectCommand.CommandType = CommandType.Text;
295 selectCommand.CommandText =
297 "from mono_postgres_test";
299 data = selectCommand.ExecuteScalar ();
301 Console.WriteLine("Agg Result: " + data);
306 // used internally by ReadData() to read each result set
307 static void ReadResult(IDataReader rdr, DataTable dt) {
309 // number of columns in the table
\r
310 Console.WriteLine(" Total Columns: " +
\r
313 // display the schema
\r
314 foreach (DataRow schemaRow in dt.Rows) {
\r
315 foreach (DataColumn schemaCol in dt.Columns)
\r
316 Console.WriteLine(schemaCol.ColumnName +
\r
318 schemaRow[schemaCol]);
\r
319 Console.WriteLine();
\r
324 string output, metadataValue, dataValue;
\r
325 // Read and display the rows
\r
326 Console.WriteLine("Gonna do a Read() now...");
\r
327 while(rdr.Read()) {
\r
328 Console.WriteLine(" Row " + nRows + ": ");
\r
330 for(c = 0; c < rdr.FieldCount; c++) {
\r
331 // column meta data
\r
332 DataRow dr = dt.Rows[c];
\r
339 if(rdr.IsDBNull(c) == true)
\r
340 dataValue = " is NULL";
\r
346 // display column meta data and data
\r
347 output = metadataValue + dataValue;
\r
348 Console.WriteLine(output);
\r
352 Console.WriteLine(" Total Rows Retrieved: " +
\r
356 // Used to read data from IDataReader after calling IDbCommand:ExecuteReader()
357 static void ReadData(IDataReader rdr) {
362 Console.WriteLine("IDataReader has a Null Reference.");
366 DataTable dt = rdr.GetSchemaTable();
\r
367 if(rdr.RecordsAffected != -1) {
\r
369 // SQL INSERT, UPDATE, DELETE Commands
\r
370 // have RecordsAffected >= 0
\r
371 Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
\r
373 else if(dt == null)
\r
375 // SQL Commands not INSERT, UPDATE, nor DELETE
\r
376 // have RecordsAffected == -1
\r
377 // and GetSchemaTable() returns a null reference
\r
378 Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
\r
381 // SQL SELECT Queries
\r
382 // have RecordsAffected = -1
\r
383 // and GetSchemaTable() returns a reference to a DataTable
\r
384 Console.WriteLine("Result is from a SELECT SQL Query. Records Affected: " + rdr.RecordsAffected);
\r
387 Console.WriteLine("Result Set " + results + "...");
\r
389 ReadResult(rdr, dt);
\r
392 } while(rdr.NextResult());
\r
393 Console.WriteLine("Total Result sets: " + results);
\r
399 /* Postgres provider tests */
400 static void DoPostgresTest (IDbConnection cnc) {
405 Console.WriteLine ("\tPostgres provider specific tests...\n");
407 /* Drops the gda_postgres_test table. */
408 Console.WriteLine ("\t\tDrop table: ");
411 Console.WriteLine ("OK");
413 catch (PgSqlException e) {
414 Console.WriteLine("Error (don't worry about this one)" + e);
418 /* Creates a table with all supported data types */
419 Console.WriteLine ("\t\tCreate table with all supported types: ");
421 Console.WriteLine ("OK");
424 Console.WriteLine ("\t\tInsert values for all known types: ");
426 Console.WriteLine ("OK");
429 Console.WriteLine ("\t\tUpdate values: ");
431 Console.WriteLine ("OK");
434 Console.WriteLine ("\t\tInsert values for all known types: ");
436 Console.WriteLine ("OK");
438 /* Select aggregates */
439 SelectAggregate (cnc, "count(*)");
440 // FIXME: still having a problem with avg()
441 // because it returns a decimal.
442 // It may have something to do
443 // with culture not being set
445 //SelectAggregate (cnc, "avg(int4_value)");
446 SelectAggregate (cnc, "min(text_value)");
447 SelectAggregate (cnc, "max(int4_value)");
448 SelectAggregate (cnc, "sum(int4_value)");
451 Console.WriteLine ("\t\tSelect values from the database: ");
452 reader = SelectData (cnc);
455 /* SQL Command via ExecuteReader/SqlDataReader */
456 /* Command is not INSERT, UPDATE, or DELETE */
457 Console.WriteLine("\t\tCall ExecuteReader with a SQL Command. (Not INSERT,UPDATE,DELETE).");
458 reader = SelectDataUsingCommand(cnc);
461 /* SQL Command via ExecuteReader/SqlDataReader */
462 /* Command is INSERT, UPDATE, or DELETE */
463 Console.WriteLine("\t\tCall ExecuteReader with a SQL Command. (Is INSERT,UPDATE,DELETE).");
464 reader = SelectDataUsingInsertCommand(cnc);
467 // Call a Stored Procedure named Version()
\r
468 Console.WriteLine("\t\tCalling stored procedure version()");
\r
469 object obj = CallStoredProcedure(cnc);
\r
470 Console.WriteLine("Result: " + obj);
\r
472 Console.WriteLine("Database Server Version: " +
\r
473 ((PgSqlConnection)cnc).ServerVersion);
\r
476 Console.WriteLine ("Clean up...");
477 Console.WriteLine ("\t\tDrop table...");
479 Console.WriteLine("OK");
482 Console.WriteLine("Exception caught: " + e);
487 static void Main(string[] args) {
\r
488 Console.WriteLine("Tests Start.");
\r
489 Console.WriteLine("Creating PgSqlConnectioin...");
\r
490 PgSqlConnection cnc = new PgSqlConnection ();
\r
492 // possible PostgreSQL Provider ConnectionStrings
\r
493 //string connectionString =
494 // "Server=hostname;" +
495 // "Database=database;" +
496 // "User ID=userid;" +
497 // "Password=password";
\r
499 //string connectionString =
500 // "host=hostname;" +
501 // "dbname=database;" +
503 // "password=password";
505 string connectionString =
510 Console.WriteLine("Setting ConnectionString: " +
\r
512 cnc.ConnectionString = connectionString;
\r
514 Console.WriteLine("Opening database connection...");
\r
517 Console.WriteLine("Do Tests....");
\r
520 Console.WriteLine("Close database connection...");
523 Console.WriteLine("Tests Done.");