3 The coordinator for the ADO.NET implementation is <a
4 href="mailto:rodrigo@ximian.com">Rodrigo Moya</a>, with
5 the collaboration of <a href="mailto:danmorg@sc.rr.com">Daniel
10 The current plan to implement ADO.NET is as follows:
12 <b>Step 1:</b> SqlClient:
15 * Implementation of System.Data.SqlClient based on
16 the <a href="http://www.postgresql.org/idocs/">PostgreSQL C API</a>
18 * Once the <a href="http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatasqlclient.asp?frame=true">System.Data.SqlClient</a>
19 code is functional and is usable by other people, we willl move it to
20 System.Data.PostgreSQL, and will convert the existing
21 System.Data.SqlClient to be just a wrapper around
22 System.Data.PostgreSQL.
26 <b>Step 2:</b> <a href="http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataOleDb.asp?frame=true">System.Data.OleDb</a> Provider:
29 * On Unix systems: System.Data.OleDb will use the
30 <a href="http://www.gnome-db.org/">LibGDA</a>
33 LibGDA is a data access engine like ADO/OLE-DB, but for Unix. It is
34 used by Gnome-Db and libgnomedb. There is work under way to get libgda
35 working under Windows by the Gnome-Db developers.
37 * On Windows systems: System.Data.OleDb will use OLE-DB as
38 its engine. It may have the option of using libgda too.
41 <b>Step 3:</b> <a href="http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatasqlclient.asp?frame=true">System.Data.SqlClient</a> Provider:
44 * <p>System.Data.SqlClient will then become a managed
45 provider for Microsoft SQL Server, both on Windows and
46 Linux, to be compatible with applications written
47 for the Microsoft .NET Development Framework.
49 <p>Once Step 1 has been completed and the PostgreSQL
50 provider support has been moved to its own place at
51 System.Data.PostgreSQL, we will use
52 <a href="http://www.freetds.org/">FreeTDS</a> as the basis
53 for providing access to Microsoft SQL Server databases.
55 FreeTDS is a C API for Unix and Windows that implements
56 the TDS (Tabular Data Stream) protocol used in accessing
57 Microsoft SQL Server and Sybase databases.
60 <b>Step 4:</b> <a href="http://msdn.microsoft.com/downloads/sample.asp?url=/MSDN-FILES/027/001/668/msdncompositedoc.xml&frame=true">System.Data.Odbc</a> Provider:
63 * We will create a .NET Managaed Provider for ODBC
64 in System.Data.Odbc for those using ODBC.
65 On Unix and Windows, <a href="http://www.unixodbc.org/">unixODBC</a> will be used.
67 unixODBC works on Unix and Windows. Providers exist for
68 Oracle, Microsoft SQL Server and Sybase via FreeTDS,
69 MySQL, PostgreSQL, IBM DB2, Interbase, and others.
73 <b>Step 5:</b> Other System.Data providers:
76 * The idea in Microsoft .NET System.Data seems to be to have
77 a managed provider for each supported DBMS. So, apart
78 from System.Data.OleDb (generic) and System.Data.SqlClient,
79 we'll need to have System.Data.MySQL,
80 System.Data.Oracle, and System.Data.PostgreSQL. Others,
81 of course, are welcomed.
86 <p>We are still working on Step 1, but we are planning the other steps.
87 If you have any ideas, let us know.
89 <p>We are able to do simple CREATE TABLE, DROP TABLE, UPDATE, INSERT, and
90 DELETE SQL commands using the ExecuteNonQuery method in SqlCommand.
92 <p>We are also able to do simple aggregate functions,
93 ie, count(), sum(), min(), and max()
94 in a simple SELECT SQL query using the ExecuteScalar() now.
96 <p>We are also able to retrieve data with a simple SELECT SQL query
97 using ExecuteReader() which returns a SqlDataReader. We are able to
98 use GetSchemaTable() to get the meta data about the table columns.
99 We are able to Read() to get each row from the result set. We are able to get
100 String data (char, bpchar (character), text, varchar), Int16 (int2 or smallint),
101 Int32 (int4 or integer), Int64 (int8 or bigint), DateTime (time, date, timestamp),
102 Boolean (boolean), Single (float), and Double (double).
103 Other More data types will follow later on.
105 <p>Parameters and stored procedures have not been tested and most likely
108 <p>A lot of functionality in System.Data is missing, but the
109 infrastructure is starting to come together.
111 <p>Need help on the DataSet/DataAdaptor/DataTable/DataRelation/XML
112 functionality so we can integrate with
113 the ASP.NET controls and Windows.Forms.
115 <P>Need to add XML support in System.Data.
117 <p>The System.Data.dll gets built with the rest of the class library.
118 To compile the System.Data.dll assembly separately, you need:
123 * update your mono sources. Be sure you have latest mcs.exe
124 and .dll's, since there have been many fixes needed for
125 compilation on Linux.
127 * compile System.Data.dll:
129 cd mcs/class/System.Data
130 mcs --target library -o System.Data.dll @list
136 * update your mono sources. Be sure you have latest mcs.exe
137 and .dll's. You can use the same method as Linux, or you can use NAnt.
141 cd mcs/class/System.Data
144 This will automatically copy the System.Data.dll to Test.
145 If you need to do a clean for the System.Data.dll assembly,
146 cd mcs/class/System.Data and run ../../nant/NAnt.exe clean
151 <p>In order to test System.Data.SqlClient, you will need to have
152 access to a remote PostgreSQL DBMS, or you will have to install
153 one locally. PostgreSQL is the DBMS used for the initial
154 implementation of System.Data.SqlClient.
156 <p>Why? Because it is open source, has a client
157 library that is easy to use, PostgreSQL on Windows is easy to install on
158 Unix and Windows (using the Cygwin install program), not difficult to setup after
159 installation, and it runs under: Linux,
160 Windows (via cygwin and ipc-daemon), Unix, and
161 others. This allowed us to create the
162 System.Data functionality in Mono much quicker.
164 <p>If you plan on using a remote PostgreSQL DBMS Server,
165 than you will need to have the PostgreSQL client software on your
166 local computer that includes libpq.so (pq.dll on Windows).
168 <p>The System.Data tests use this connection string to connect
169 to the PostgreSQL database named "test" at host "localhost" as
173 "host=localhost;dbname=test;user=postgres"
176 <p>Installation instructions for PostgreSQL DBMS:
181 * Read the PostgreSQL Installation Instructions
182 at \usr\doc\postgresql-x.x.x\html\installation.html
184 * Depending on your Unix system,
185 PostgreSQL maybe already installed, a database user 'postgres' created,
186 a linux user 'postgres' created and initdb ran. Or maybe not.
191 mkdir /usr/local/pgsql/data
\r
192 chown postgres /usr/local/pgsql/data
\r
194 initdb -D /usr/local/pgsql/data
\r
195 postmaster -i -D /usr/local/pgsql/data >logfile 2>&1 &
\r
200 * Make sure you have a database user named postgres. It is best to install
201 the PostgreSQL DBMS under linux user postgres. When you run the postmaster,
202 run it under the user postgres as well. If this was not done, then you
203 will need to create a user named postgres for the System.Data tests.
205 * If you already installed PostgeSQL and you do not have a database
206 user named postgres, then you can create user postgres using psql:
210 create user postgres with password 'fun2db';
213 * The postmaster must be run with -i option.
215 * In the /usr/local/pgsql/data/pg_hba.conf file, you need
216 to have the AUTH_TYPE set to md5. You can read more on this at
217 /usr/doc/postgresql-7.2.1/html/client-authentication.html or wherever your
218 PostgreSQL html docs are located. See the 2nd line below,
219 host 127.0.0.1 has an AUTH_TYPE md5 in pg_hba.conf.
222 # TYPE DATABASE IP_ADDRESS MASK AUTH_TYPE AUTH_ARGUMENT
225 host all 127.0.0.1 255.255.255.255 md5
228 * If you can not find your PostgreSQL documentation locally or you
229 did not install it, then you
230 can get it <a href="http://www.postgresql.org/idocs/">here</a>.
237 * Use the <a href="http://www.cygwin.com/">Cygwin</a> installer to install the PostgreSQL DBMS. It is
238 found in the database category.
240 * <p>Read the file postgres-x.x.README at /usr/doc/Cygwin and read
241 the requirements to install PostgreSQL. Those requirements
242 are included with cygwin except cygipc. A default installtion
243 of cygwin does not install everything you will need, so on the
244 safe side, just include everything when installing cygwin.
246 * <p>The -x.x in postgres-x.x is the version of your PostgreSQL DBMS.
248 * <p>Once Cygwin has installed the PostgreSQL DBMS on your computer,
249 read the file FAQ_MSWIN which is available
250 in /usr/doc/postgres-x.x
252 * <p>Important notes from this file are:
255 <p><b>2.</b> - Install the latest <a href="http://www.neuro.gatech.edu/users/cwilson/cygutils/OBSOLETE/V1.1/cygipc/index.html">CygIPC</a> package.
257 <p>The cygipc package contains the ipc-daemon you will need
258 to run before you can
259 run the PostgreSQL DBMS Server daemon (postmaster) or run
260 initdb which initializes the PostgreSQL database.
262 <p><b>3.</b> The Cygwin bin directory has to be placed in
263 the path before the Windows program directories,
264 for example, C:\cygwin\bin
266 <p><b>My own note.</b> In the Windows control panel, I set
267 the environment variables PATH to my cygwin /usr/local/bin,
268 /usr/bin, and /bin. I also set my LD_LIBRARY_PATH to
269 /usr/local/lib and /usr/lib. For example:
273 PATH=c:\cygwin\usr\local\bin;c:\cygwin\usr\bin;c:\cygwin\bin;
274 LD_LIBRARY_PATH=c:\cygwin\usr\local\lib;c:\cygwin\usr\lib;
277 <p><b>4.</b> Start the ipc-daemon that came with the cygipc package. There
278 are two ways to do this: run it from the command line as:
284 <p>or you can set it up as a Windows service. See the
285 file cygrunsrv.README at /usr/doc/Cygwin on how to do this
286 for ipc-daemon and postmaster. Note the
287 troubleshooting section at the end of
288 the cygrunsrv.README file.
290 <p>To install ipc-daemon as a service,
295 ipc-daemon --install-as-service' (--remove-as-service)
305 <p>Read the installation.html file
306 at /usr/doc/postgresql-x.x/html/installation.html
308 <p>You will see in this file that you will need to
309 run the following commands:
313 mkdir /usr/local/pgsql/data
\r
314 initdb -D /usr/local/pgsql/data
\r
315 postmaster -D /usr/local/pgsql/data >logfile 2>&1 &
\r
320 <p>When you need to connect to the database,
321 you will need ipc-daemon and postmaster running. Start ipc-daemon
322 before any of the command above.
324 <p>psql is a command-line PostgreSQL client tool to
325 enter and run SQL commands and queries.
327 <p>If there is no database user named postgres, create a user named
328 postgres with the following SQL command in the client tool psql:
333 create user postgres with password 'fun2db';
335 <p>The only reason I say this is so you can easily use the System.Data tests
336 without having to change the database, userid, etc.
339 <p>In the path mcs/class/System.Data/Test
340 there is a PostgreSQL test program named
341 PostgreTest.cs. Thanks goes to Gonzalo for creating the original
344 <p>To use it to test System.Data, you
345 modify the file to your PostgreSQL database
346 connection requirements:
350 <li><b>dbname</b> database, ie., test</li>
351 <li><b>host</b> hostname of the PostgreSQL DBMS Server to connect to, ie., localhost</li>
352 <li><b>user</b> username, ie., someuser</li>
353 <li><b>password</b> password, ie., mypass1234</li>
356 <p>The connection string is in OLE-DB connection string format. Internally,
357 SqlConnection converts this to the PostgreSQL connection string format.
361 OLE-DB: "host=localhost;dbname=test;user=joe;password=smoe"
362 PostgreSQL: "host=localhost dbname=test user=joe password=smoe"
366 Note that OLE-DB includes the semicolons while PostgreSQL's connection
370 To compile the PostgresTest.cs program, do:
374 mcs PostgresTest.cs -r System.Data.dll
378 To run using mint, do:
382 mint PostgresTest.exe
386 To run using mono, do:
388 mono PostgresTest.exe
391 <p>You should get something like:
395 danmorg@DANPC ~/mono/mcs/class/System.Data/Test
\r
396 $ mcs PostgresTest.cs -r System.Data.dll
\r
398 danmorg@DANPC ~/mono/mcs/class/System.Data/Test
\r
399 $ mono PostgresTest.exe
\r
400 Postgres provider specific tests...
\r
403 Error (don't worry about this one)SqlError:PGRES_FATAL_ERROR ERROR: table "mono
\r
404 _postgres_test" does not exist
\r
407 Create table with all supported types:
\r
409 Insert values for all known types:
\r
413 Insert values for all known types:
\r
415 Aggregate: count(*)
\r
417 Aggregate: min(text_value)
\r
418 Agg Result: This is a text
\r
419 Aggregate: max(int4_value)
\r
420 Agg Result: 1048000
\r
421 Aggregate: sum(int4_value)
\r
422 Agg Result: 1048003
\r
423 Select values from the database:
\r
425 dt.Columns.Count: 12
\r
426 * Column Name: boolean_value
\r
428 Type: System.Boolean
\r
429 * Column Name: int2_value
\r
432 * Column Name: int4_value
\r
435 * Column Name: bigint_value
\r
438 * Column Name: float_value
\r
440 Type: System.Single
\r
441 * Column Name: double_value
\r
443 Type: System.Double
\r
444 * Column Name: char_value
\r
446 Type: System.String
\r
447 * Column Name: varchar_value
\r
449 Type: System.String
\r
450 * Column Name: text_value
\r
452 Type: System.String
\r
453 * Column Name: time_value
\r
455 Type: System.DateTime
\r
456 * Column Name: date_value
\r
458 Type: System.DateTime
\r
459 * Column Name: timestamp_value
\r
461 Type: System.DateTime
\r
463 Col 0: boolean_value: False
\r
464 Col 1: int2_value: 5
\r
465 Col 2: int4_value: 3
\r
466 Col 3: bigint_value: 9
\r
467 Col 4: float_value: 3.141590
\r
468 Col 5: double_value: 3.141593
\r
469 Col 6: char_value: Mono.Data!
\r
470 Col 7: varchar_value: It was not me!
\r
471 Col 8: text_value: We got data!
\r
472 Col 9: time_value: Monday, 01 January 1 21:13:14
\r
473 Col 10: date_value: Tuesday, 29 February 2000 00:00:00
\r
474 Col 11: timestamp_value: Sunday, 29 February 2004 14:00:11
\r
476 Col 0: boolean_value: True
\r
477 Col 1: int2_value: -22
\r
478 Col 2: int4_value: 1048000
\r
479 Col 3: bigint_value: 123456789012345
\r
480 Col 4: float_value: 3.141590
\r
481 Col 5: double_value: 3.141593
\r
482 Col 6: char_value: This is a char
\r
483 Col 7: varchar_value: This is a varchar
\r
484 Col 8: text_value: This is a text
\r
485 Col 9: time_value: Monday, 01 January 1 21:13:14
\r
486 Col 10: date_value: Tuesday, 29 February 2000 00:00:00
\r
487 Col 11: timestamp_value: Sunday, 29 February 2004 14:00:11
\r