* PostgreSQL and Mono When it comes to Mono and PostgreSQL, there are many ways you can handle your data. You have many Mono Data Providers which can be used to access data from a application written for Mono. Then there is the future goal of having the ability to host Mono within PostgreSQL to have the applications run on the server which makes things much faster. * Hosting Mono in PostgreSQL There is a project to host Mono within PostgreSQL. plMono is a PostgreSQL language using the embedded Mono runtime. It provides support for writing functions in C#, or any other language that supports .NET. * Data Providers There are many ADO.NET data providers for PostgreSQL: There are two providers created specifically for PostgreSQL included with Mono:
// 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 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 Unix and Windows (using the Cygwin install program), not difficult to setup after installation, and it runs under: Linux, Windows (via cygwin and ipc-daemon), Unix, and others. This allowed us to create the System.Data functionality in Mono much quicker.
If you plan on using a remote PostgreSQL DBMS Server, than you will need to have the PostgreSQL client software on your local computer that includes libpq.so (pq.dll on Windows).
The System.Data tests use this connection string to connect to the PostgreSQL database named "test" at host "localhost" as user "postgres".
"Server=localhost;Database=test;User ID=postgres;Password=fun2db" (or) "host=localhost;dbname=test;user=postgres;password=fun2db"
Installation instructions for PostgreSQL DBMS: On Unix
su adduser postgres mkdir /usr/local/pgsql/data chown postgres /usr/local/pgsql/data su - postgres initdb -D /usr/local/pgsql/data postmaster -i -D /usr/local/pgsql/data createdb test psql test* Make sure you have a database user named postgres. It is best to install the PostgreSQL DBMS under linux user postgres. When you run the postmaster, run it under the user postgres as well. If this was not done, then you will need to create a user named postgres for the System.Data tests. * If you already installed PostgeSQL and you do not have a database user named postgres, then you can create user postgres using psql:
psql test create user postgres with password 'fun2db';* The postmaster must be run with -i option. * In the /usr/local/pgsql/data/pg_hba.conf file, you need to have the AUTH_TYPE set to md5. You can read more on this at /usr/doc/postgresql-7.2.1/html/client-authentication.html or wherever your PostgreSQL html docs are located. See the 2nd line below, host 127.0.0.1 has an AUTH_TYPE md5 in pg_hba.conf.
# TYPE DATABASE IP_ADDRESS MASK AUTH_TYPE local all trust host all 127.0.0.1 255.255.255.255 md5* If you can not find your PostgreSQL documentation locally or you did not install it, then you can get it here. On Windows
Read the file postgres-x.x.README at /usr/doc/Cygwin and read the requirements to install PostgreSQL. Those requirements are included with cygwin except cygipc. A default installtion of cygwin does not install everything you will need, so on the safe side, just include everything when installing cygwin. *
The -x.x in postgres-x.x is the version of your PostgreSQL DBMS. *
Once Cygwin has installed the PostgreSQL DBMS on your computer, read the file FAQ_MSWIN which is available in /usr/doc/postgres-x.x *
Important notes from this file are:
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 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.
3. The Cygwin bin directory has to be placed in the path before the Windows program directories, for example, C:\cygwin\bin
My own note. In the Windows control panel, I set the environment variables PATH to my cygwin /usr/local/bin, /usr/bin, and /bin. I also set my LD_LIBRARY_PATH to /usr/local/lib and /usr/lib. For example:
PATH=c:\cygwin\usr\local\bin;c:\cygwin\usr\bin;c:\cygwin\bin; LD_LIBRARY_PATH=c:\cygwin\usr\local\lib;c:\cygwin\usr\lib;
4. Start the ipc-daemon that came with the cygipc package. There are two ways to do this: run it from the command line as:
ipc-daemon &
or you can set it up as a Windows service. See the file cygrunsrv.README at /usr/doc/Cygwin on how to do this for ipc-daemon and postmaster. Note the troubleshooting section at the end of the cygrunsrv.README file.
To install ipc-daemon as a service, you just have to run
ipc-daemon --install-as-service' (--remove-as-service)
and then run
net start ipc-daemon
Read the installation.html file at /usr/doc/postgresql-x.x/html/installation.html
You will see in this file that you will need to run the following commands:
mkdir /usr/local/pgsql/data initdb -D /usr/local/pgsql/data postmaster -D /usr/local/pgsql/data createdb test psql test
When you need to connect to the database, you will need ipc-daemon and postmaster running. Start ipc-daemon before any of the command above. If you restart your computer, you need to start ipc-daemon and postmaster either manually or as a service.
psql is a command-line PostgreSQL client tool to enter and run SQL commands and queries.
If there is no database user named postgres, create a user named postgres with the following SQL command in the client tool psql:
psql test create user postgres with password 'fun2db';
The only reason I say this is so you can easily use the System.Data tests without having to change the database, userid, etc.
In the path mcs/class/System.Data/Test there is a test for Mono.Data.PostgreSqlClient named PostgreTest.cs. Thanks goes to Gonzalo for creating the original PostgreSQL test.
To compile the PostgresTest.cs program, do:
mcs PostgresTest.cs \ -r System.Data.dll \ -r Mono.Data.PostgreSqlClient.dll
If there are compile errors, such as, can not convert IDbConnection to PgSqlConnection, then you need to run mcs like:
mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \ PostgresTest.cs \ -r System.Data.dll \ -r Mono.Data.PostgreSqlClient.dll
To run using mint, do:
mint PostgresTest.exe
To run using mono, do:
mono PostgresTest.exe
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
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