/* PostgresTest.cs - based on the postgres-test.c in libgda
*
- * Copyright (C) 1998-2002 The GNOME Foundation
+ * Copyright (C) 2002 Gonzalo Paniagua Javier
* Copyright (C) 2002 Daniel Morgan
*
* ORIGINAL AUTHOR:
using System;
using System.Data;
-using System.Data.SqlClient;
+using Mono.Data.PostgreSqlClient;
-namespace TestSystemDataSqlClient {
+namespace Test.Mono.Data.PostgreSqlClient {
class PostgresTest {
+ // execute SQL CREATE TABLE Command using ExecuteNonQuery()
static void CreateTable (IDbConnection cnc) {
IDbCommand createCommand = cnc.CreateCommand();
"int2_value smallint, " +
"int4_value integer, " +
"bigint_value bigint, " +
- "float_value real, " +
+ "float_value real, " +
"double_value double precision, " +
"numeric_value numeric(15, 3), " +
"char_value char(50), " +
"time_value time, " +
"date_value date, " +
"timestamp_value timestamp, " +
- "null_value char(1) " +
+ "null_boolean_value boolean, " +
+ "null_int2_value smallint, " +
+ "null_int4_value integer, " +
+ "null_bigint_value bigint, " +
+ "null_float_value real, " +
+ "null_double_value double precision, " +
+ "null_numeric_value numeric(15, 3), " +
+ "null_char_value char(50), " +
+ "null_varchar_value varchar(20), " +
+ "null_text_value text, " +
+ "null_point_value point, " +
+ "null_time_value time, " +
+ "null_date_value date, " +
+ "null_timestamp_value timestamp " +
")";
createCommand.ExecuteNonQuery ();
}
+ // execute SQL DROP TABLE Command using ExecuteNonQuery
static void DropTable (IDbConnection cnc) {
IDbCommand dropCommand = cnc.CreateCommand ();
dropCommand.ExecuteNonQuery ();
}
+ // execute stored procedure using ExecuteScalar()
+ static object CallStoredProcedure (IDbConnection cnc) {
+
+ IDbCommand callStoredProcCommand = cnc.CreateCommand ();
+ object data;
+
+ callStoredProcCommand.CommandType =
+ CommandType.StoredProcedure;
+ callStoredProcCommand.CommandText =
+ "version";
+
+ data = callStoredProcCommand.ExecuteScalar ();
+
+ return data;
+ }
+
+ // execute SQL INSERT Command using ExecuteNonQuery()
static void InsertData (IDbConnection cnc) {
IDbCommand insertCommand = cnc.CreateCommand();
insertCommand.ExecuteNonQuery ();
}
+ // execute a SQL SELECT Query using ExecuteReader() to retrieve
+ // a IDataReader so we retrieve data
static IDataReader SelectData (IDbConnection cnc) {
IDbCommand selectCommand = cnc.CreateCommand();
IDataReader reader;
// FIXME: System.Data classes need to handle NULLs
+ // this would be done by System.DBNull ?
// FIXME: System.Data needs to handle more data types
/*
selectCommand.CommandText =
selectCommand.CommandText =
"select " +
- "int2_value, " +
- "int4_value, " +
- "bigint_value, " +
- "char_value, " +
- "varchar_value, " +
- "text_value " +
+ "boolean_value, " +
+ "int2_value, " +
+ "int4_value, " +
+ "bigint_value, " +
+ "float_value, " +
+ "double_value, " +
+ "numeric_value, " +
+ "char_value, " +
+ "varchar_value, " +
+ "text_value, " +
+ "point_value, " +
+ "time_value, " +
+ "date_value, " +
+ "timestamp_value, " +
+ "null_boolean_value, " +
+ "null_int2_value, " +
+ "null_int4_value, " +
+ "null_bigint_value, " +
+ "null_float_value, " +
+ "null_double_value, " +
+ "null_numeric_value, " +
+ "null_char_value, " +
+ "null_varchar_value, " +
+ "null_text_value, " +
+ "null_point_value, " +
+ "null_time_value, " +
+ "null_date_value, " +
+ "null_timestamp_value " +
"from mono_postgres_test";
reader = selectCommand.ExecuteReader ();
return reader;
}
+ // Tests a SQL Command (INSERT, UPDATE, DELETE)
+ // executed via ExecuteReader
+ static IDataReader SelectDataUsingInsertCommand (IDbConnection cnc) {
+
+ IDbCommand selectCommand = cnc.CreateCommand();
+ IDataReader reader;
+
+ // This is a SQL INSERT Command, not a Query
+ selectCommand.CommandText =
+ "insert into mono_postgres_test (" +
+ "boolean_value, " +
+ "int2_value, " +
+ "int4_value, " +
+ "bigint_value, " +
+ "float_value, " +
+ "double_value, " +
+ "numeric_value, " +
+ "char_value, " +
+ "varchar_value, " +
+ "text_value, " +
+ "time_value, " +
+ "date_value, " +
+ "timestamp_value, " +
+ "point_value " +
+ ") values (" +
+ "'T', " +
+ "-22, " +
+ "1048000, " +
+ "123456789012345, " +
+ "3.141592, " +
+ "3.1415926969696, " +
+ "123456789012.345, " +
+ "'This is a char', " +
+ "'This is a varchar', " +
+ "'This is a text', " +
+ "'21:13:14', " +
+ "'2000-02-29', " +
+ "'2004-02-29 14:00:11.31', " +
+ "'(1,0)' " +
+ ")";
+
+ reader = selectCommand.ExecuteReader ();
+
+ return reader;
+ }
+
+ // Tests a SQL Command not (INSERT, UPDATE, DELETE)
+ // executed via ExecuteReader
+ static IDataReader SelectDataUsingCommand (IDbConnection cnc) {
+
+ IDbCommand selectCommand = cnc.CreateCommand();
+ IDataReader reader;
+
+ // This is a SQL Command, not a Query
+ selectCommand.CommandText =
+ "SET DATESTYLE TO 'ISO'";
+
+ reader = selectCommand.ExecuteReader ();
+
+ return reader;
+ }
+
+
+ // execute an SQL UPDATE Command using ExecuteNonQuery()
+ static void UpdateData (IDbConnection cnc) {
+
+ IDbCommand updateCommand = cnc.CreateCommand();
+
+ updateCommand.CommandText =
+ "update mono_postgres_test " +
+ "set " +
+ "boolean_value = 'F', " +
+ "int2_value = 5, " +
+ "int4_value = 3, " +
+ "bigint_value = 9, " +
+ "char_value = 'Mono.Data!' , " +
+ "varchar_value = 'It was not me!', " +
+ "text_value = 'We got data!' " +
+ "where int2_value = -22";
+
+ updateCommand.ExecuteNonQuery ();
+ }
+
+ // used to do a min(), max(), count(), sum(), or avg()
+ // execute SQL SELECT Query using ExecuteScalar
+ static object SelectAggregate (IDbConnection cnc, String agg) {
+
+ IDbCommand selectCommand = cnc.CreateCommand();
+ object data;
+
+ Console.WriteLine("Aggregate: " + agg);
+
+ selectCommand.CommandType = CommandType.Text;
+ selectCommand.CommandText =
+ "select " + agg +
+ "from mono_postgres_test";
+
+ data = selectCommand.ExecuteScalar ();
+
+ Console.WriteLine("Agg Result: " + data);
+
+ return data;
+ }
+
+ // used internally by ReadData() to read each result set
+ static void ReadResult(IDataReader rdr, DataTable dt) {
+
+ // number of columns in the table
+ Console.WriteLine(" Total Columns: " +
+ dt.Rows.Count);
+
+ // display the schema
+ foreach (DataRow schemaRow in dt.Rows) {
+ foreach (DataColumn schemaCol in dt.Columns)
+ Console.WriteLine(schemaCol.ColumnName +
+ " = " +
+ schemaRow[schemaCol]);
+ Console.WriteLine();
+ }
+
+ int nRows = 0;
+ int c = 0;
+ string output, metadataValue, dataValue;
+ // Read and display the rows
+ Console.WriteLine("Gonna do a Read() now...");
+ while(rdr.Read()) {
+ Console.WriteLine(" Row " + nRows + ": ");
+
+ for(c = 0; c < rdr.FieldCount; c++) {
+ // column meta data
+ DataRow dr = dt.Rows[c];
+ metadataValue =
+ " Col " +
+ c + ": " +
+ dr["ColumnName"];
+
+ // column data
+ if(rdr.IsDBNull(c) == true)
+ dataValue = " is NULL";
+ else
+ dataValue =
+ ": " +
+ rdr.GetValue(c);
+
+ // display column meta data and data
+ output = metadataValue + dataValue;
+ Console.WriteLine(output);
+ }
+ nRows++;
+ }
+ Console.WriteLine(" Total Rows Retrieved: " +
+ nRows);
+ }
+
+ // Used to read data from IDataReader after calling IDbCommand:ExecuteReader()
+ static void ReadData(IDataReader rdr) {
+
+ int results = 0;
+ if(rdr == null) {
+
+ Console.WriteLine("IDataReader has a Null Reference.");
+ }
+ else {
+ do {
+ DataTable dt = rdr.GetSchemaTable();
+ if(rdr.RecordsAffected != -1) {
+ // Results for
+ // SQL INSERT, UPDATE, DELETE Commands
+ // have RecordsAffected >= 0
+ Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
+ }
+ else if(dt == null)
+ // Results for
+ // SQL Commands not INSERT, UPDATE, nor DELETE
+ // have RecordsAffected == -1
+ // and GetSchemaTable() returns a null reference
+ Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
+ else {
+ // Results for
+ // SQL SELECT Queries
+ // have RecordsAffected = -1
+ // and GetSchemaTable() returns a reference to a DataTable
+ Console.WriteLine("Result is from a SELECT SQL Query. Records Affected: " + rdr.RecordsAffected);
+
+ results++;
+ Console.WriteLine("Result Set " + results + "...");
+
+ ReadResult(rdr, dt);
+ }
+
+ } while(rdr.NextResult());
+ Console.WriteLine("Total Result sets: " + results);
+
+ rdr.Close();
+ }
+ }
+
/* Postgres provider tests */
static void DoPostgresTest (IDbConnection cnc) {
IDataReader reader;
+ Object oDataValue;
Console.WriteLine ("\tPostgres provider specific tests...\n");
DropTable (cnc);
Console.WriteLine ("OK");
}
- catch (SqlException e) {
- Console.WriteLine("Error (don't worry about this one): + e");
+ catch (PgSqlException e) {
+ Console.WriteLine("Error (don't worry about this one)" + e);
}
try {
Console.WriteLine ("\t\tInsert values for all known types: ");
InsertData (cnc);
Console.WriteLine ("OK");
-
+
+ /* Update values */
+ Console.WriteLine ("\t\tUpdate values: ");
+ UpdateData (cnc);
+ Console.WriteLine ("OK");
+
+ /* Inserts values */
+ Console.WriteLine ("\t\tInsert values for all known types: ");
+ InsertData (cnc);
+ Console.WriteLine ("OK");
+
+ /* Select aggregates */
+ SelectAggregate (cnc, "count(*)");
+ // FIXME: still having a problem with avg()
+ // because it returns a decimal.
+ // It may have something to do
+ // with culture not being set
+ // properly.
+ //SelectAggregate (cnc, "avg(int4_value)");
+ SelectAggregate (cnc, "min(text_value)");
+ SelectAggregate (cnc, "max(int4_value)");
+ SelectAggregate (cnc, "sum(int4_value)");
+
/* Select values */
Console.WriteLine ("\t\tSelect values from the database: ");
reader = SelectData (cnc);
+ ReadData(reader);
+
+ /* SQL Command via ExecuteReader/SqlDataReader */
+ /* Command is not INSERT, UPDATE, or DELETE */
+ Console.WriteLine("\t\tCall ExecuteReader with a SQL Command. (Not INSERT,UPDATE,DELETE).");
+ reader = SelectDataUsingCommand(cnc);
+ ReadData(reader);
+
+ /* SQL Command via ExecuteReader/SqlDataReader */
+ /* Command is INSERT, UPDATE, or DELETE */
+ Console.WriteLine("\t\tCall ExecuteReader with a SQL Command. (Is INSERT,UPDATE,DELETE).");
+ reader = SelectDataUsingInsertCommand(cnc);
+ ReadData(reader);
+
+ // Call a Stored Procedure named Version()
+ Console.WriteLine("\t\tCalling stored procedure version()");
+ object obj = CallStoredProcedure(cnc);
+ Console.WriteLine("Result: " + obj);
+
+ Console.WriteLine("Database Server Version: " +
+ ((PgSqlConnection)cnc).ServerVersion);
- // get the DataTable that holds\r
- // the schema\r
- Console.WriteLine("\t\tGet Schema.");\r
- DataTable dt = reader.GetSchemaTable();\r
- \r
- // number of columns in the table\r
- Console.WriteLine("dt.Columns.Count: " +\r
- dt.Columns.Count);\r
-\r
- int c;\r
- // display the schema\r
- for(c = 0; c < dt.Columns.Count; c++) {\r
- Console.WriteLine("* Column Name: " + \r
- dt.Columns[c].ColumnName);\r
- Console.WriteLine(" MaxLength: " +\r
- dt.Columns[c].MaxLength);\r
- Console.WriteLine(" Type: " +\r
- dt.Columns[c].DataType);\r
- }\r
-\r
- int nRows = 0;\r
- // Read and display the rows\r
- while(reader.Read()) {\r
- Console.WriteLine ("Row " + nRows + ":");\r
- for(c = 0; c < reader.FieldCount; c++) {\r
- Console.WriteLine ( \r
- " Col " + \r
- c + ": " + \r
- dt.Columns[c].ColumnName + \r
- " - " +\r
- reader.GetValue(c));\r
- }\r
- \r
- nRows++;\r
- }\r
- reader.Close();\r
- Console.WriteLine ("Rows: " + nRows); \r
-\r
/* Clean up */
Console.WriteLine ("Clean up...");
Console.WriteLine ("\t\tDrop table...");
}
}
- [STAThread]\r
- static void Main(string[] args)\r
- {\r
- SqlConnection cnc = new SqlConnection ();\r
-\r
- /*\r
- string connectionString =
- "host=localhost;" +
- "dbname=test;" +
- "user=userid;" +
- "password=password";
- */\r
-\r
+ [STAThread]
+ static void Main(string[] args) {
+ Console.WriteLine("Tests Start.");
+ Console.WriteLine("Creating PgSqlConnectioin...");
+ PgSqlConnection cnc = new PgSqlConnection ();
+
+ // possible PostgreSQL Provider ConnectionStrings
+ //string connectionString =
+ // "Server=hostname;" +
+ // "Database=database;" +
+ // "User ID=userid;" +
+ // "Password=password";
+ // or
+ //string connectionString =
+ // "host=hostname;" +
+ // "dbname=database;" +
+ // "user=userid;" +
+ // "password=password";
+
string connectionString =
"host=localhost;" +
"dbname=test;" +
- "user=postgres;";
-\r
- cnc.ConnectionString = connectionString;\r
-\r
- cnc.Open();\r
+ "user=postgres";
+
+ Console.WriteLine("Setting ConnectionString: " +
+ connectionString);
+ cnc.ConnectionString = connectionString;
+
+ Console.WriteLine("Opening database connection...");
+ cnc.Open();
+
+ Console.WriteLine("Do Tests....");
DoPostgresTest(cnc);
+
+ Console.WriteLine("Close database connection...");
cnc.Close();
+
+ Console.WriteLine("Tests Done.");
}
}
}
projects / mono.git / blobdiff