+ // Enable logging. + NpgsqlEventLog.Level = LogLevel.Debug; // LogLevel. + NpgsqlEventLog.LogName = "NpgsqlTests.LogFile"; // LogFile. ++ +
In order to test Mono.Data.PostgreSqlClient, you will need to have access to a remote PostgreSQL DBMS, or you will have to install - one locally. PostgreSQL was first ADO.NET provider created in Mono. + one locally. PostgreSQL was the first ADO.NET provider created in Mono.
Why use PostgreSQL? Because it is free software, has a client library that is easy to use, PostgreSQL is easy to install on @@ -94,7 +226,9 @@ user "postgres".
-"host=localhost;dbname=test;user=postgres" +"Server=localhost;Database=test;User ID=postgres;Password=fun2db" + (or) +"host=localhost;dbname=test;user=postgres;password=fun2db"@@ -179,9 +313,15 @@ create user postgres with password 'fun2db'; *
Important notes from this file are:
2. - Install the latest CygIPC package. +
2. - Install the latest CygIPC package. + Cygwin includes a utility bunzip2 which can be used to unzip it. Now, change to + the root directory by + typing "cd /" then + you can use "tar xvf cygipc.xxx.tar" to untar it + in the root directory in cygwin. -
The cygipc package contains the ipc-daemon you will need +
The cygipc package contains the support to run ipc-daemon + that you will need to run before you can run the PostgreSQL DBMS Server daemon (postmaster) or run initdb which initializes the PostgreSQL database. @@ -267,34 +407,9 @@ create user postgres with password 'fun2db';
In the path mcs/class/System.Data/Test - there is a PostgreSQL test program named + there is a test for Mono.Data.PostgreSqlClient named PostgreTest.cs. Thanks goes to Gonzalo for creating the original PostgreSQL test. - -
To use it to test System.Data, you - modify the file to your PostgreSQL database - connection requirements: - -
-
The connection string is in OLE-DB connection string format. Internally, - SqlConnection converts this to the PostgreSQL connection string format. - -
-
- OLE-DB: "host=localhost;dbname=test;user=joe;password=smoe" -PostgreSQL: "host=localhost dbname=test user=joe password=smoe" -- -
- Note that OLE-DB includes the semicolons while PostgreSQL's connection - string does not.
To compile the PostgresTest.cs program, do: @@ -310,7 +425,7 @@ PostgreSQL: "host=localhost dbname=test user=joe password=smoe" to PgSqlConnection, then you need to run mcs like:
- mono f:/cygwin/home/DanielMorgan/mono/install/bin/mcs.exe \
+ mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \
PostgresTest.cs \
-r System.Data.dll \
-r Mono.Data.PostgreSqlClient.dll
@@ -328,185 +443,162 @@ mint PostgresTest.exe
To run using mono, do:
mono PostgresTest.exe
-
-
- Below, I show how the output from PostgresTest. I have omitted a lot
- of the meta data for the columns except two columns. The classes
- used were from Mono.Data.PostgreSqlClient and were used to connect to a
- PostgreSQL database and retrieve data.
-
-
-
-
- danmorg@DANPC ~/mono/mcs/class/System.Data/Test
- $ mcs PostgresTest.cs -r System.Data.dll
-
- danmorg@DANPC ~/mono/mcs/class/System.Data/Test
- $ mono PostgresTest.exe
- Postgres provider specific tests...
-
- Drop table:
- Error (don't worry about this one)SqlError:PGRES_FATAL_ERROR ERROR:
- table "mono_postgres_test" does not exist
-
-
- Create table with all supported types:
- OK
- Insert values for all known types:
- OK
- Update values:
- OK
- Insert values for all known types:
- OK
- Aggregate: count(*)
- Agg Result: 2
- Aggregate: min(text_value)
- Agg Result: This is a text
- Aggregate: max(int4_value)
- Agg Result: 1048000
- Aggregate: sum(int4_value)
- Agg Result: 1048003
- Select values from the database:
- Result is from a SELECT SQL Query. Records Affected: -1
- Result Set 1...
- Total Columns: 28
- ColumnName = boolean_value
- ColumnOrdinal = 1
- ColumnSize = 1
- NumericPrecision = 0
- NumericScale = 0
- IsUnique = False
- IsKey =
- BaseCatalogName =
- BaseColumnName = boolean_value
- BaseSchemaName =
- BaseTableName =
- DataType = System.Boolean
- AllowDBNull = False
- ProviderType = 16
- IsAliased = False
- IsExpression = False
- IsIdentity = False
- IsAutoIncrement = False
- IsRowVersion = False
- IsHidden = False
- IsLong = False
- IsReadOnly = False
-
- ...
-
- ColumnName = null_timestamp_value
- ColumnOrdinal = 28
- ColumnSize = 8
- NumericPrecision = 0
- NumericScale = 0
- IsUnique = False
- IsKey =
- BaseCatalogName =
- BaseColumnName = null_timestamp_value
- BaseSchemaName =
- BaseTableName =
- DataType = System.DateTime
- AllowDBNull = False
- ProviderType = 1184
- IsAliased = False
- IsExpression = False
- IsIdentity = False
- IsAutoIncrement = False
- IsRowVersion = False
- IsHidden = False
- IsLong = False
- IsReadOnly = False
-
- Gonna do a Read() now...
- Row 0:
- Col 0: boolean_value: False
- Col 1: int2_value: 5
- Col 2: int4_value: 3
- Col 3: bigint_value: 9
- Col 4: float_value: 3.141590
- Col 5: double_value: 3.14159
- Col 6: numeric_value: 123456789012.345
- Col 7: char_value: Mono.Data!
- Col 8: varchar_value: It was not me!
- Col 9: text_value: We got data!
- Col 10: point_value: (1,0)
- Col 11: time_value: 01/01/1 21:13:14
- Col 12: date_value: 02/29/2000 00:00:00
- Col 13: timestamp_value: 02/29/2004 14:00:11
- Col 14: null_boolean_value is NULL
- Col 15: null_int2_value is NULL
- Col 16: null_int4_value is NULL
- Col 17: null_bigint_value is NULL
- Col 18: null_float_value is NULL
- Col 19: null_double_value is NULL
- Col 20: null_numeric_value is NULL
- Col 21: null_char_value is NULL
- Col 22: null_varchar_value is NULL
- Col 23: null_text_value is NULL
- Col 24: null_point_value is NULL
- Col 25: null_time_value is NULL
- Col 26: null_date_value is NULL
- Col 27: null_timestamp_value is NULL
- Row 1:
- Col 0: boolean_value: True
- Col 1: int2_value: -22
- Col 2: int4_value: 1048000
- Col 3: bigint_value: 123456789012345
- Col 4: float_value: 3.141590
- Col 5: double_value: 3.14159
- Col 6: numeric_value: 123456789012.345
- Col 7: char_value: This is a char
- Col 8: varchar_value: This is a varchar
- Col 9: text_value: This is a text
- Col 10: point_value: (1,0)
- Col 11: time_value: 01/01/1 21:13:14
- Col 12: date_value: 02/29/2000 00:00:00
- Col 13: timestamp_value: 02/29/2004 14:00:11
- Col 14: null_boolean_value is NULL
- Col 15: null_int2_value is NULL
- Col 16: null_int4_value is NULL
- Col 17: null_bigint_value is NULL
- Col 18: null_float_value is NULL
- Col 19: null_double_value is NULL
- Col 20: null_numeric_value is NULL
- Col 21: null_char_value is NULL
- Col 22: null_varchar_value is NULL
- Col 23: null_text_value is NULL
- Col 24: null_point_value is NULL
- Col 25: null_time_value is NULL
- Col 26: null_date_value is NULL
- Col 27: null_timestamp_value is NULL
- Total Rows Retrieved: 2
- Total Result sets: 1
- Call ExecuteReader with a SQL Command.
- (Not INSERT,UPDATE,DELETE
- ).
- Result is from a SQL Command not (INSERT,UPDATE,DELETE).
- Records Affected: -1
- Total Result sets: 0
- Call ExecuteReader with a SQL Command.
- (Is INSERT,UPDATE,DELETE)
- .
- Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: 1
- Total Result sets: 0
- Calling stored procedure version()
- Result: PostgreSQL 7.2.1 on i686-pc-cygwin, compiled by GCC 2.95.3-5
- Database Server Version: PostgreSQL 7.2.1 on i686-pc-cygwin,
- compiled by GCC 2.9
- 5.3-5
- Clean up...
- Drop table...
- OK
- RESULT: 0
+
+ C# Example for Mono.Data.PostgreSqlClient:
+
+ using System;
+ using System.Data;
+ using Mono.Data.PostgreSqlClient;
+
+ public class Test
+ {
+ public static void Main(string[] args)
+ {
+ string connectionString =
+ "Server=localhost;" +
+ "Database=test;" +
+ "User ID=postgres;" +
+ "Password=fun2db;";
+ IDbConnection dbcon;
+ dbcon = new PgConnection(connectionString);
+ dbcon.Open();
+ IDbCommand dbcmd = dbcon.CreateCommand();
+ // requires a table to be created named employee
+ // with columns firstname and lastname
+ // such as,
+ // CREATE TABLE employee (
+ // firstname varchar(32),
+ // lastname varchar(32));
+ string sql =
+ "SELECT firstname, lastname" +
+ "FROM employee";
+ dbcmd.CommandText = sql;
+ IDataReader reader = dbcmd.ExecuteReader();
+ while(reader.Read()) {
+ string FirstName = (string) reader["firstname"];
+ string LastName = (string) reader["lastname"];
+ Console.WriteLine("Name: " +
+ FirstName + " " + LastName);
+ }
+ // clean up
+ reader.Close();
+ reader = null;
+ dbcmd.Dispose();
+ dbcmd = null;
+ dbcon.Close();
+ dbcon = null;
+ }
+ }
+
+
+ + mcs TestExample.cs -r System.Data.dll \ + -r Mono.Data.PostgreSqlClient.dll ++
+ mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \ + TestExample.cs \ + -lib:C:/cygwin/home/MyHome/mono/install/lib \ + -r System.Data.dll -r Mono.Data.PostgreSqlClient.dll+
+mono TestExample.exe ++
Eventually replace the PostgreSQL data provider in Mono - with Npgsql. - Npgsql is a .Net Data Provider for PostgreSQL which implements - the PostgreSQL Frontend/Backend Protocol. - Npgsql is implemented in 100% C#. This provider was created by Francisco Figueiredo jr. - and has many programmers developing the provider. +
+ using System;
+ using System.Data;
+ using Npgsql;
+
+ public class Test
+ {
+ public static void Main(string[] args)
+ {
+ string connectionString =
+ "Server=localhost;" +
+ "Database=test;" +
+ "User ID=postgres;" +
+ "Password=fun2db;";
+ IDbConnection dbcon;
+ dbcon.Open();
+ dbcon = new NpgsqlConnection(connectionString);
+ IDbCommand dbcmd = dbcon.CreateCommand();
+ // requires a table to be created named employee
+ // with columns firstname and lastname
+ // such as,
+ // CREATE TABLE employee (
+ // firstname varchar(32),
+ // lastname varchar(32));
+ string sql =
+ "SELECT firstname, lastname " +
+ "FROM employee";
+ dbcmd.CommandText = sql;
+ IDataReader reader = dbcmd.ExecuteReader();
+ while(reader.Read()) {
+ string FirstName = (string) reader["firstname"];
+ string LastName = (string) reader["lastname"];
+ Console.WriteLine("Name: " +
+ FirstName + " " + LastName);
+ }
+ // clean up
+ reader.Close();
+ reader = null;
+ dbcmd.Dispose();
+ dbcmd = null;
+ dbcon.Close();
+ dbcon = null;
+ }
+ }
+
+ + mcs TestExample.cs -r System.Data.dll \ + -r Npgsql.dll ++
+ mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \ + TestExample.cs \ + -lib:C:/cygwin/home/MyHome/mono/install/lib \ + -r System.Data.dll -r Npgsql.dll ++
+mono TestExample.exe ++