-* PostgreSQL Data Provider
+* PostgreSQL and Mono
+
+ When it comes to Mono and PostgreSQL, there are many ways
+ you can access your data.
+
+* Data Providers
+
+ There are many ADO.NET data providers for <a href="http://www.postgresql.org/">PostgreSQL</a>:
+
+ There are two providers created specifically for PostgreSQL included with Mono:
<ul>
- <li>Exists in namespace Mono.Data.PostgreSql and assembly Mono.Data.PostgreSql</li>
-
- <li>Is a Mono Data Provider for the <a href="http://www.postgresql.org/">PostgreSQL</a>
- client/server database management system.</li>
-
- <li>Written in C# and has C# bindings to the PostgreSQL C Client library pq.dll on Windows
- and libpq.so on Linux.</li>
-
- <li>Alternative to the PostgreSQL provider in Mono:
+
+ <li><a href="http://gborg.postgresql.org/project/npgsql/projdisplay.php">Npgsql</a>
<ul>
- <li><a href="http://gborg.postgresql.org/project/npgsql/projdisplay.php">Npgsql</a>
- is a Managed PostgreSQL provider written in 100% C#, does not require a client library,
- and works on Microsoft .NET and Mono.
- </li>
+ <li>included with Mono</li>
+
+ <li>a .NET Managed Data Provider for PostgreSQL</li>
+
+ <li>Written in 100% C#</li>
+
+ <li>does not require a client library</li>
+
+ <li>works on Mono and Microsoft .NET</li>
+
+ <li>created by Francisco Figueiredo jr. and has many developers working on it
+
+ <li>works in the SQL# (command-line and GTK# GUI versions)</li>
+
+ <li>in namespace Npgsql and assembly Npgsql and is found in mcs
+ at mcs/class/Npgsql</li>
</ul>
</li>
- <li>See Testing for both Mono's Mono.Data.PostgreSqlClient and Npgsql.</li>
-</ul>
-
-** Current Status
-
-<ul>
- <li>We are able to do simple CREATE TABLE, DROP TABLE, UPDATE, INSERT, and
- DELETE SQL commands using the ExecuteNonQuery method in PgSqlCommand.
-
- <li>We can execute multiple queries and do a NextResult() in PgSqlDataReader()
- to get the next result set.
-
- <li>We are also able to do simple aggregate functions,
- ie, count(), sum(), min(), and max()
- in a simple SELECT SQL query using the ExecuteScalar() now.
-
- <li>We are also able to retrieve data with a simple SELECT SQL query
- using ExecuteReader() which returns a PgSqlDataReader. We are able to
- use GetSchemaTable() to get the meta data about the table columns.
- We are able to Read() to get each row from the result set.
-
- <li>We are able to get
- String data (char, character, text, varchar), Int16 (smallint),
- Int32 (integer), Int64 (bigint), DateTime (time, date, timestamp),
- Boolean (boolean), Single (float), and Double (double).
- More data types will come later. Note, the types that do work still
- need thorough testing.
-
- <li>Rows that are returned which contain columns that are NULL are handled now.
- The PgSqlDataReader method IsDBNull() needs to be called to determine
- if a field IS NULL before trying to read data from that field.
-
- <li>Calling PostgreSQL stored procedures works. It does not work perfectly.
- It may not
- even work to specification - yet. If you want to test it yourself, look at
- TestSqlDataReader.cs or PostgresTest.cs in
- mcs/class/System.Data/Test.
-
- <li>Below, I have some sample code you can
- use to call a PostgreSQL stored procedure named "version". This stored
- procedure returns a string containing the PostgreSQL server version. Notice
- the CommandType is StoredProcedure and the method ExecuteScalar() is called.
+ <li>Mono.Data.PostgreSqlClient (DEPRECATED)
+ <ul>
- <li>ExecuteScalar() is a lightweight method in class PgSqlCommand that only returns
- one row and one column as one object - even if there is more than row or column.
+ <li>Deprecated in favor of Npgsql. No longer included in a release of Mono.</li>
- <li>We have the beginnings of Parameters support PostgreSQL. Only
- Input Parameters are currently supported. Output, Input/Output,
- and Return parameters still need to be done.
+ </ul>
+ </li>
- <li>A lot of functionality in System.Data is missing, but the
- infrastructure is starting to come together.
+ <li><a href="http://sourceforge.net/projects/mysqlnet/">ByteFX.Data</a> has a provider for PostgreSQL too, but I do not know how well it works with Mono.</li>
- <li>A lot of Exceptions need to be thrown for various exceptions. However,
- PgSqlException, PgSqlErrorCollection, and PgSqlError have been partially
- implemented.
+ <li>There is another .NET data provider for PostgreSQL named <a href="http://sourceforge.net/projects/pgsqlclient/">PgSqlClient</a>, but I do not know if it works with Mono.</li>
- <li>Tim Coleman and Rodrigo Moya got the beginnings of the
- PgSqlDataAdapter/DataSet/DataTable/DataRow to work. Currently,
- the PgSqlDataAdapter can Fill() relational data into a DataTable in a DataSet.
- See the test mcs/class/System.Data/Test/TestSqlDataAdapter.cs to see it in action.
-</ul>
+ <li>If none of the above providers meet your needs. There is the ODBC and OLEDB providers included with Mono.</li>
+
+ <li>Bugs with Mono or the data provider should be reported
+ in Mono's Bugzilla <a href="http://bugzilla.ximian.com/">here</a>. If you
+ do not have Bugzilla user account, it is free
+ and easy to
+ create one <a href="http://bugzilla.ximian.com/createaccount.cgi">here</a>.</li>
-** Action Plan
- <p>More testing and fixing bugs...
+</ul>
+
+** Current Status
- <p>Eventually replace the PostgreSQL data provider in Mono
- with <a href="http://gborg.postgresql.org/project/npgsql/projdisplay.php">Npgsql</a>.
- Npgsql is a .Net Data Provider for PostgreSQL which implements
- the <a href="http://developer.postgresql.org/docs/postgres/protocol.html">PostgreSQL Frontend/Backend Protocol</a>.
- Npgsql is implemented in 100% C#. This provider was created by Francisco Figueiredo jr.
- and has many programmers developing the provider.
+<ul>
+ <li>Npgsql
+ <ul>
+ <li>Builds and Runs on both Microsoft .NET and Mono.</li>
+ <li>Works using SQL# (command-line and GTK# versions)</li>
+ <li>You can send insert, update, delete queries \r
+ through NpgsqlCommand.ExecuteNonQuery() method.</li>\r
+ <li>You can send queries like, select count(*) from table, select version()\r
+ with NpgsqlCommand.ExecuteScalar() method.</li>\r
+ <li>There is logging support. (Thanks Dave Page)\r
+ To use it, place code like that in your program:</li>\r
+\r
+<pre> \r
+ // Enable logging.\r
+ NpgsqlEventLog.Level = LogLevel.Debug; // LogLevel.\r
+ NpgsqlEventLog.LogName = "NpgsqlTests.LogFile"; // LogFile.\r
+</pre>\r
+ \r
+ <li>You can use Npgsql with Mono (Thanks Kristis Makris). It is not working perfectly.</li>\r
+ <li>There is a winforms test suite (Thanks Dave Page).</li>\r
+ <li>Clearer code in NpgsqlConnection removing *magic* numbers and constants. (Thanks Kristis Makris)</li>\r
+ <li>Better support of ODBC-like ConnectionString in NpgsqlConnection (Thanks Dave Page)</li>\r
+ <li>Thanks Ulrich Sprick for all discussion and ideas.</li>\r
-** Testing Mono's Mono.Data.PostgreSqlClient
+ </ul>
+ </li>
+</ul>
+** Action Plan
<ul>
- * <p>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.
+ <li>More testing and fixing bugs</li>
- <p>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.
+ <li>Better error handling</li>
- <p>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).
+ <li>More Data Types to use</li>
+
+ <li>Any features for Npgsql should be implemented in Npgsql's main cvs repository at
+ gborg.postgresql.org. Most bugs should be fixed in gborg.postgresql.org's cvs.
+ Only bugs neccessary for building and running of Npgsql on Mono can be done in Mono cvs,
+ but once applied they should be sent to Npgsql's mailing list
+ at gborg.postgresql.org for inclusion into cvs there. Whenever there is
+ a release of Npgsql (determined by Francisco Figueiredo jr. or a release
+ of Mono (determined by Miguel de Icaza), then the Npgsql source
+ in gborg.postgresql.org's cvs will be used to update the Npgsql source in
+ Mono's cvs.
+ </li>
- <p>The System.Data tests use this connection string to connect
- to the PostgreSQL database named "test" at host "localhost" as
- user "postgres".
+ <li>Add any missing functionality to Npgsql. If this funtionality works on
+ .NET but not on Mono, implement the missing features or fix the bugs in Mono</li>
+
+ <li>Npgsql has been replaced Mono.Data.PostgreSqlClient as the provider of
+ choice to use. Mono.Data.PostgreSqlClient is deprecated and is no longer included in
+ Mono releases. Please use Npgsql for PostgreSQL data access.</li>
+
+ <li>Implement new features of PostgreSQL.</li>
+ </ul>
+
+</ul>
+
+** Testing Npgsql
+<ul>
+ <li>Have a working mono and mcs</li>
+
+ <li>Get <a href="http://gborg.postgresql.org/project/npgsql/projdisplay.php">Npgsql</a>
+ and make sure the binary assembly Npgsql.dll is installed in the same place that the
+ mono class libraries are located.
+
+ <li>C# Example for Npgsql:
+<pre>
+ 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 = new NpgsqlConnection(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;
+ }
+ }
+</pre>
+ </li>
+ <li>Building C# Example:
+ <ul>
+ <li>Save the example to a file, such as, TestExample.cs</li>
<pre>
-"Server=localhost;Database=test;User ID=postgres;Password=fun2db"
- (or)
-"host=localhost;dbname=test;user=postgres;password=fun2db"
+ mcs TestExample.cs -r System.Data.dll \
+ -r Npgsql.dll
+</pre>
+ </ul>
+ </li>
+ <li>Running the Example:
+<pre>
+mono TestExample.exe
</pre>
+</li>
</ul>
-
- <p>Installation instructions for PostgreSQL DBMS:
- <b>On Unix</b>
+
+** Installation instructions for PostgreSQL DBMS:
+ <p><b>On Unix</b>
<ul>
* Read the PostgreSQL Installation Instructions
without having to change the database, userid, etc.
</ul>
- <p>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.
-
- <p>
- To compile the PostgresTest.cs program, do:
-
- <p>
-<pre>
- mcs PostgresTest.cs \
- -r System.Data.dll \
- -r Mono.Data.PostgreSqlClient.dll
-</pre>
-
- <p>If there are compile errors, such as, can not convert IDbConnection
- to PgSqlConnection, then you need to run mcs like:
-
-<pre>
- mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \
- PostgresTest.cs \
- -r System.Data.dll \
- -r Mono.Data.PostgreSqlClient.dll
-</pre>
-
- <p>
- To run using mint, do:
-
- <p>
-<pre>
-mint PostgresTest.exe
-</pre>
-
- <p>
- To run using mono, do:
-<pre>
-mono PostgresTest.exe
-</pre>
- <p>C# Example for Mono.Data.PostgreSqlClient:
-<pre>
- 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);
- 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.ConnectionString = sql;
- IDataReader reader = dbcmd.ExecuteReader();
- while(reader.Read()) {
- string FirstName = reader["firstname"];
- string LastName = reader["lastname"];
- Console.WriteLine("Name: " +
- FirstName + " " + LastName);
- }
- // clean up
- reader.Close();
- reader = null;
- dbcmd.Dispose();
- dbcmd = null;
- dbcon.Close();
- dbcon = null;
- }
- }
-</pre>
- </li>
- <li>Building C# Example:
- <ul>
- <li>Save the example to a file, such as, TestExample.cs</li>
- <li>Build on Linux:
-<pre>
- mcs TestExample.cs -r System.Data.dll \
- -r Mono.Data.PostgreSqlClient.dll
-</pre>
- </li>
- <li>Build on Windows via Cygwin:
-<pre>
- 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
-</pre>
- </li>
- </ul>
- </li>
- <li>Running the Example:
-<pre>
-mono TestExample.exe
-</pre>
-</li>
-</ul>
-
-** Testing Npgsql
-
-<ul>
- <li>Have a working mono and mcs</li>
-
- <li>Get <a href="http://gborg.postgresql.org/project/npgsql/projdisplay.php">Npgsql</a>
- and make sure the binary assembly Npgsql.dll is installed in the same place that the
- mono class libraries are located.
-
- <li>Read the Testing notes for Mono.Data.PostgreSqlClient too
-
- <li>C# Example for Npgsql:
-<pre>
- 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 = 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.ConnectionString = sql;
- IDataReader reader = dbcmd.ExecuteReader();
- while(reader.Read()) {
- string FirstName = reader["firstname"];
- string LastName = reader["lastname"];
- Console.WriteLine("Name: " +
- FirstName + " " + LastName);
- }
- // clean up
- reader.Close();
- reader = null;
- dbcmd.Dispose();
- dbcmd = null;
- dbcon.Close();
- dbcon = null;
- }
- }
-</pre>
- </li>
- <li>Building C# Example:
- <ul>
- <li>Save the example to a file, such as, TestExample.cs</li>
- <li>Build on Linux:
-<pre>
- mcs TestExample.cs -r System.Data.dll \
- -r Npgsql.dll
-</pre>
- </li>
- <li>Build on Windows via Cygwin:
-<pre>
- 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
-</pre>
- </li>
- </ul>
- </li>
- <li>Running the Example:
-<pre>
-mono TestExample.exe
-</pre>
-</li>
-</ul>
-
projects / mono.git / blobdiff