1 * PostgreSQL Data Provider
3 There are two ADO.NET data providers for <a href="http://www.postgresql.org/">PostgreSQL</a> in Mono:
7 <li><a href="http://gborg.postgresql.org/project/npgsql/projdisplay.php">Npgsql</a>
9 <li>a .NET Managed Data Provider for PostgreSQL
11 <li>Written in 100% C#</li>
13 <li>does not require a client library</li>
15 <li>works on Mono and Microsoft .NET</li>
17 <li>created by Francisco Figueiredo jr. and has many developers working on it
19 <li>works in the SQL# (command-line and GTK# GUI versions)</li>
21 <li>in namespace Npgsql and assembly Npgsql and is found in mcs
22 at mcs/class/Npgsql</li>
26 <li>Mono.Data.PostgreSQL (deprecated)
28 <li>Deprecated in favor of Npgsql</li>
30 <li>Exists in namespace Mono.Data.PostgreSql and assembly Mono.Data.PostgreSql</li>
32 <li>Is a Mono Data Provider for the <a href="http://www.postgresql.org/">PostgreSQL</a>
33 client/server database management system.</li>
35 <li>Written in C# and has C# bindings to the PostgreSQL C Client library pq.dll on Windows
36 and libpq.so on Linux.</li>
40 <li>Bugs with Mono or the data provider should be reported
41 in Mono's Bugzilla <a href="http://bugzilla.ximian.com/">here</a>. If you
42 do not have Bugzilla user account, it is free
44 create one <a href="http://bugzilla.ximian.com/createaccount.cgi">here</a>.</li>
49 Below, see separate Testing sections for Npgsql and Mono.Data.PostgreSqlClient.
56 <li>Builds and Runs on both Microsoft .NET and Mono.</li>
57 <li>Works using SQL# (command-line and GTK# versions)</li>
58 <li>You can send insert, update, delete queries
\r
59 through NpgsqlCommand.ExecuteNonQuery() method.</li>
\r
60 <li>You can send queries like, select count(*) from table, select version()
\r
61 with NpgsqlCommand.ExecuteScalar() method.</li>
\r
62 <li>There is logging support. (Thanks Dave Page)
\r
63 To use it, place code like that in your program:</li>
\r
67 NpgsqlEventLog.Level = LogLevel.Debug; // LogLevel.
\r
68 NpgsqlEventLog.LogName = "NpgsqlTests.LogFile"; // LogFile.
\r
71 <li>You can use Npgsql with Mono (Thanks Kristis Makris). It is not working perfectly.</li>
\r
72 <li>There is a winforms test suite (Thanks Dave Page).</li>
\r
73 <li>Clearer code in NpgsqlConnection removing *magic* numbers and constants. (Thanks Kristis Makris)</li>
\r
74 <li>Better support of ODBC-like ConnectionString in NpgsqlConnection (Thanks Dave Page)</li>
\r
75 <li>Thanks Ulrich Sprick for all discussion and ideas.</li>
\r
79 <li>Mono.Data.PostgreSQL status
82 <li>Deprecated in favor of Npgsql</li>
84 <li>We are able to do simple CREATE TABLE, DROP TABLE, UPDATE, INSERT, and
85 DELETE SQL commands using the ExecuteNonQuery method in PgSqlCommand.</li>
87 <li>We can execute multiple queries and do a NextResult() in PgSqlDataReader()
88 to get the next result set.</li>
90 <li>We are also able to do simple aggregate functions,
91 ie, count(), sum(), min(), and max()
92 in a simple SELECT SQL query using the ExecuteScalar() now.</li>
94 <li>We are also able to retrieve data with a simple SELECT SQL query
95 using ExecuteReader() which returns a PgSqlDataReader. We are able to
96 use GetSchemaTable() to get the meta data about the table columns.
97 We are able to Read() to get each row from the result set.</li>
99 <li>We are able to get
100 String data (char, character, text, varchar), Int16 (smallint),
101 Int32 (integer), Int64 (bigint), DateTime (time, date, timestamp),
102 Boolean (boolean), Single (float), and Double (double).
103 More data types will come later. Note, the types that do work still
104 need thorough testing.</li>
106 <li>Rows that are returned which contain columns that are NULL are handled now.
107 The PgSqlDataReader method IsDBNull() needs to be called to determine
108 if a field IS NULL before trying to read data from that field.</li>
110 <li>Calling PostgreSQL stored procedures works. It does not work perfectly.
112 even work to specification - yet. If you want to test it yourself, look at
113 TestSqlDataReader.cs or PostgresTest.cs in
114 mcs/class/System.Data/Test.</li>
116 <li>Below, I have some sample code you can
117 use to call a PostgreSQL stored procedure named "version". This stored
118 procedure returns a string containing the PostgreSQL server version. Notice
119 the CommandType is StoredProcedure and the method ExecuteScalar() is called.</li>
121 <li>ExecuteScalar() is a lightweight method in class PgSqlCommand that only returns
122 one row and one column as one object - even if there is more than row or column.</li>
124 <li>We have the beginnings of Parameters support PostgreSQL. Only
125 Input Parameters are currently supported. Output, Input/Output,
126 and Return parameters still need to be done.</li>
128 <li>A lot of Exceptions need to be thrown for various exceptions. However,
129 PgSqlException, PgSqlErrorCollection, and PgSqlError have been partially
132 <li>Tim Coleman and Rodrigo Moya got the beginnings of the
133 PgSqlDataAdapter/DataSet/DataTable/DataRow to work. Currently,
134 the PgSqlDataAdapter can Fill() relational data into a DataTable in a DataSet.
135 See the test mcs/class/System.Data/Test/TestSqlDataAdapter.cs to see it in action.</li>
137 <li>Works in the SQL# command-line version
138 and the GTK# version on Linux. It only works in SQL# command-line version
148 <li>More testing and fixing bugs</li>
150 <li>Better error handling</li>
152 <li>More Data Types to use</li>
154 <li>Any features for Npgsql should be implemented in Npgsql's main cvs repository at
155 gborg.postgresql.org. Most bugs should be fixed in gborg.postgresql.org's cvs.
156 Only bugs neccessary for building and running of Npgsql on Mono can be done in Mono cvs,
157 but once applied they should be sent to Npgsql's mailing list
158 at gborg.postgresql.org for inclusion into cvs there. Whenever there is
159 a release of Npgsql (determined by Francisco Figueiredo jr. or a release
160 of Mono (determined by Miguel de Icaza), then the Npgsql source
161 in gborg.postgresql.org's cvs will be used to update the Npgsql source in
165 <li>Mono.Data.PostgreSqlClient even though deprecated can still
166 accept bug fixes. This is because other areas, such as, ASP.NET examples
167 may still use this provider.</li>
169 <li>Add any missing functionality to Npgsql. If this funtionality works on
170 .NET but not on Mono, implement the missing features or fix the bugs in Mono</li>
172 <li>Npgsql has replaced Mono.Data.PostgreSqlClient as the provider of
173 choice to use. However, Mono.Data.PostgreSqlClient will remain in a
174 deprecated state until nobody uses it anymore - then it can be removed</li>
176 <li>Implement more of PostgreSQL 7.3 features in Npgsql</li>
179 ** Testing Mono.Data.PostgreSqlClient
182 * <p>In order to test Mono.Data.PostgreSqlClient, you will need to have
183 access to a remote PostgreSQL DBMS, or you will have to install
184 one locally. PostgreSQL was the first ADO.NET provider created in Mono.
186 <p>Why use PostgreSQL? Because it is free software, has a client
187 library that is easy to use, PostgreSQL is easy to install on
188 Unix and Windows (using the Cygwin install program), not difficult to setup after
189 installation, and it runs under: Linux,
190 Windows (via cygwin and ipc-daemon), Unix, and
191 others. This allowed us to create the
192 System.Data functionality in Mono much quicker.
194 <p>If you plan on using a remote PostgreSQL DBMS Server,
195 than you will need to have the PostgreSQL client software on your
196 local computer that includes libpq.so (pq.dll on Windows).
198 <p>The System.Data tests use this connection string to connect
199 to the PostgreSQL database named "test" at host "localhost" as
203 "Server=localhost;Database=test;User ID=postgres;Password=fun2db"
205 "host=localhost;dbname=test;user=postgres;password=fun2db"
209 <p>Installation instructions for PostgreSQL DBMS:
214 * Read the PostgreSQL Installation Instructions
215 at \usr\doc\postgresql-x.x.x\html\installation.html
217 * Depending on your Unix system,
218 PostgreSQL maybe already installed, a database user 'postgres' created,
219 a linux user 'postgres' created and initdb ran. Or maybe not.
224 mkdir /usr/local/pgsql/data
225 chown postgres /usr/local/pgsql/data
227 initdb -D /usr/local/pgsql/data
228 postmaster -i -D /usr/local/pgsql/data
233 * Make sure you have a database user named postgres. It is best to install
234 the PostgreSQL DBMS under linux user postgres. When you run the postmaster,
235 run it under the user postgres as well. If this was not done, then you
236 will need to create a user named postgres for the System.Data tests.
238 * If you already installed PostgeSQL and you do not have a database
239 user named postgres, then you can create user postgres using psql:
243 create user postgres with password 'fun2db';
246 * The postmaster must be run with -i option.
248 * In the /usr/local/pgsql/data/pg_hba.conf file, you need
249 to have the AUTH_TYPE set to md5. You can read more on this at
250 /usr/doc/postgresql-7.2.1/html/client-authentication.html
252 PostgreSQL html docs are located. See the 2nd line below,
253 host 127.0.0.1 has an AUTH_TYPE md5 in pg_hba.conf.
256 # TYPE DATABASE IP_ADDRESS MASK AUTH_TYPE
259 host all 127.0.0.1 255.255.255.255 md5
262 * If you can not find your PostgreSQL documentation locally or you
263 did not install it, then you
264 can get it <a href="http://www.postgresql.org/idocs/">here</a>.
271 * Use the <a href="http://www.cygwin.com/">Cygwin</a> installer to
272 install the PostgreSQL DBMS. It is
273 found in the database category.
275 * <p>Read the file postgres-x.x.README at /usr/doc/Cygwin and read
276 the requirements to install PostgreSQL. Those requirements
277 are included with cygwin except cygipc. A default installtion
278 of cygwin does not install everything you will need, so on the
279 safe side, just include everything when installing cygwin.
281 * <p>The -x.x in postgres-x.x is the version of your PostgreSQL DBMS.
283 * <p>Once Cygwin has installed the PostgreSQL DBMS on your computer,
284 read the file FAQ_MSWIN which is available
285 in /usr/doc/postgres-x.x
287 * <p>Important notes from this file are:
290 <p><b>2.</b> - Install the latest <a href="http://www.neuro.gatech.edu/users/cwilson/cygutils/cygipc/index.html">CygIPC</a> package.
291 Cygwin includes a utility bunzip2 which can be used to unzip it. Now, change to
292 the root directory by
294 you can use "tar xvf cygipc.xxx.tar" to untar it
295 in the root directory in cygwin.
297 <p>The cygipc package contains the support to run ipc-daemon
299 to run before you can
300 run the PostgreSQL DBMS Server daemon (postmaster) or run
301 initdb which initializes the PostgreSQL database.
303 <p><b>3.</b> The Cygwin bin directory has to be placed in
304 the path before the Windows program directories,
305 for example, C:\cygwin\bin
307 <p><b>My own note.</b> In the Windows control panel, I set
308 the environment variables PATH to my cygwin /usr/local/bin,
309 /usr/bin, and /bin. I also set my LD_LIBRARY_PATH to
310 /usr/local/lib and /usr/lib. For example:
314 PATH=c:\cygwin\usr\local\bin;c:\cygwin\usr\bin;c:\cygwin\bin;
315 LD_LIBRARY_PATH=c:\cygwin\usr\local\lib;c:\cygwin\usr\lib;
318 <p><b>4.</b> Start the ipc-daemon that came with the cygipc
320 are two ways to do this: run it from the command line as:
326 <p>or you can set it up as a Windows service. See the
327 file cygrunsrv.README at /usr/doc/Cygwin on how to do this
328 for ipc-daemon and postmaster. Note the
329 troubleshooting section at the end of
330 the cygrunsrv.README file.
332 <p>To install ipc-daemon as a service,
337 ipc-daemon --install-as-service' (--remove-as-service)
347 <p>Read the installation.html file
348 at /usr/doc/postgresql-x.x/html/installation.html
350 <p>You will see in this file that you will need to
351 run the following commands:
355 mkdir /usr/local/pgsql/data
356 initdb -D /usr/local/pgsql/data
357 postmaster -D /usr/local/pgsql/data
362 <p>When you need to connect to the database,
363 you will need ipc-daemon and postmaster running. Start ipc-daemon
364 before any of the command above. If you restart your computer, you
365 need to start ipc-daemon and postmaster either manually or as a
368 <p>psql is a command-line PostgreSQL client tool to
369 enter and run SQL commands and queries.
371 <p>If there is no database user named postgres, create a user named
372 postgres with the following SQL command in the client tool psql:
377 create user postgres with password 'fun2db';
379 <p>The only reason I say this is so you can easily use the System.Data tests
380 without having to change the database, userid, etc.
383 <p>In the path mcs/class/System.Data/Test
384 there is a test for Mono.Data.PostgreSqlClient named
385 PostgreTest.cs. Thanks goes to Gonzalo for creating the original
389 To compile the PostgresTest.cs program, do:
393 mcs PostgresTest.cs \
395 -r Mono.Data.PostgreSqlClient.dll
398 <p>If there are compile errors, such as, can not convert IDbConnection
399 to PgSqlConnection, then you need to run mcs like:
402 mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \
405 -r Mono.Data.PostgreSqlClient.dll
409 To run using mint, do:
413 mint PostgresTest.exe
417 To run using mono, do:
419 mono PostgresTest.exe
422 <p>C# Example for Mono.Data.PostgreSqlClient:
426 using Mono.Data.PostgreSqlClient;
430 public static void Main(string[] args)
432 string connectionString =
433 "Server=localhost;" +
435 "User ID=postgres;" +
438 dbcon = new PgConnection(connectionString);
440 IDbCommand dbcmd = dbcon.CreateCommand();
441 // requires a table to be created named employee
442 // with columns firstname and lastname
444 // CREATE TABLE employee (
445 // firstname varchar(32),
446 // lastname varchar(32));
448 "SELECT firstname, lastname" +
450 dbcmd.CommandText = sql;
451 IDataReader reader = dbcmd.ExecuteReader();
452 while(reader.Read()) {
453 string FirstName = reader["firstname"];
454 string LastName = reader["lastname"];
455 Console.WriteLine("Name: " +
456 FirstName + " " + LastName);
469 <li>Building C# Example:
471 <li>Save the example to a file, such as, TestExample.cs</li>
474 mcs TestExample.cs -r System.Data.dll \
475 -r Mono.Data.PostgreSqlClient.dll
478 <li>Build on Windows via Cygwin:
480 mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \
482 -lib:C:/cygwin/home/MyHome/mono/install/lib \
483 -r System.Data.dll -r Mono.Data.PostgreSqlClient.dll
488 <li>Running the Example:
498 <li>Have a working mono and mcs</li>
500 <li>Get <a href="http://gborg.postgresql.org/project/npgsql/projdisplay.php">Npgsql</a>
501 and make sure the binary assembly Npgsql.dll is installed in the same place that the
502 mono class libraries are located.
504 <li>Read the Testing notes for Mono.Data.PostgreSqlClient too
506 <li>C# Example for Npgsql:
514 public static void Main(string[] args)
516 string connectionString =
517 "Server=localhost;" +
519 "User ID=postgres;" +
523 dbcon = new NpgsqlConnection(connectionString);
524 IDbCommand dbcmd = dbcon.CreateCommand();
525 // requires a table to be created named employee
526 // with columns firstname and lastname
528 // CREATE TABLE employee (
529 // firstname varchar(32),
530 // lastname varchar(32));
532 "SELECT firstname, lastname " +
534 dbcmd.CommandText = sql;
535 IDataReader reader = dbcmd.ExecuteReader();
536 while(reader.Read()) {
537 string FirstName = reader["firstname"];
538 string LastName = reader["lastname"];
539 Console.WriteLine("Name: " +
540 FirstName + " " + LastName);
553 <li>Building C# Example:
555 <li>Save the example to a file, such as, TestExample.cs</li>
558 mcs TestExample.cs -r System.Data.dll \
562 <li>Build on Windows via Cygwin:
564 mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \
566 -lib:C:/cygwin/home/MyHome/mono/install/lib \
567 -r System.Data.dll -r Npgsql.dll
572 <li>Running the Example: