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
6 Morgan</a> and <a href="mailto:tim@timcoleman.com">Tim Coleman</a>.
11 The current plan to implement ADO.NET is as follows:
13 <b>Step 1:</b> Initial <a href="http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatasqlclient.asp?frame=true">System.Data.SqlClient</a> Provider:
16 * Initial implementation of System.Data.SqlClient is based on
17 the <a href="http://www.postgresql.org/idocs/">PostgreSQL C API</a> which is a
18 client API to the PostgreSQL DBMS. PostgreSQL was chosen so we could quickly
19 create and test the System.Data classes.
21 * Once the <a href="http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatasqlclient.asp?frame=true">System.Data.SqlClient</a>
22 code is functional and is usable by other people, we willl move it to
23 Mono.Data.PostgreSQL, and will convert the existing
24 System.Data.SqlClient to be just a wrapper around
29 <b>Step 2:</b> <a href="http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataOleDb.asp?frame=true">System.Data.OleDb</a> Provider:
32 * <p>On Unix systems: System.Data.OleDb will use the
33 <a href="http://www.gnome-db.org/">LibGDA</a>
36 <p>LibGDA is a data access engine like ADO/OLE-DB, but for Unix. The
37 GDA in libGDA stands for GNU/GNOME Data Access, but it does not require GNOME.
38 It only requires glib2 and libxml2. LibGDA is used by
39 libgnomedb, GNOME-DB, and gaSQL.
41 <p>There is work under way to get libgda working under
42 Windows using Cygwin by the GNOME-DB developers.
44 <p>LibGDA has providers for MySQL, PostgreSQL, XML, ODBC,
45 and MDB Tools (MS Access support).
47 * On Windows systems: System.Data.OleDb will use OLE-DB as
48 its engine. It may have the option of using libgda too.
51 <b>Step 3:</b> <a href="http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatasqlclient.asp?frame=true">System.Data.SqlClient</a> Provider:
54 * <p>System.Data.SqlClient will then become a managed
55 provider for Microsoft SQL Server, both on Windows and
56 Linux, to be compatible with applications written
57 for the Microsoft .NET Development Framework.
59 <p>Once Step 1 has been completed and the PostgreSQL
60 provider support has been moved to its own place at
61 System.Data.PostgreSQL, we will use
62 <a href="http://www.freetds.org/">FreeTDS</a> as the basis
63 for providing access to Microsoft SQL Server databases.
65 <P>FreeTDS is a C API for Unix and Windows that implements
66 the TDS (Tabular Data Stream) protocol used in accessing
67 Microsoft SQL Server and Sybase databases. A .NET Data Provider could be
68 created for Sybase databases as well, but this would be put in Mono.Data.Sybase.
71 <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:
74 * We will create a .NET Managaed Provider for ODBC
75 in System.Data.Odbc for those using ODBC.
76 On Unix and Windows, <a href="http://www.unixodbc.org/">unixODBC</a> mabye used.
77 iODBC is an alternative to using unixODBC.
79 <p>unixODBC works on Unix and Windows. Well, I have not actually
80 used it on Cygwin, but it does build and install without problems.
82 <p>unixODBC has providers for:
83 Oracle, Microsoft SQL Server and Sybase via FreeTDS,
84 MySQL, PostgreSQL, Informix, IBM DB2 (Universal Database),
85 Interbase, miniSQL (mSQL), AdabasD, Empress, YARD SQL, and others.
89 <b>Step 5:</b> Other System.Data providers:
92 * <p>The idea in Microsoft .NET System.Data is to have
93 a managed provider for each supported DBMS. System.Data.SqlClient
94 for Microsoft SQL Server.
95 System.Data.OracleClient for Oracle 8i and 9i.
97 <p>We will need to have Mono.Data.MySQL, Mono.Data.PostgreSQL,
98 Mono.Data.DB2, and Mono.Data.miniSQL. Others,
99 of course, are welcomed.
101 <p>System.Data has been designed so
102 non-database providers can be created too.
107 <p>We are still working on Step 1, but we are planning the other steps.
108 If you have any ideas, let us know.
110 <p>We are able to do simple CREATE TABLE, DROP TABLE, UPDATE, INSERT, and
111 DELETE SQL commands using the ExecuteNonQuery method in SqlCommand.
113 <p>We can execute multiple queries and do a NextResult() in SqlDataReader()
114 to get the next result set.
116 <p>We are also able to do simple aggregate functions,
117 ie, count(), sum(), min(), and max()
118 in a simple SELECT SQL query using the ExecuteScalar() now.
120 <p>We are also able to retrieve data with a simple SELECT SQL query
121 using ExecuteReader() which returns a SqlDataReader. We are able to
122 use GetSchemaTable() to get the meta data about the table columns.
123 We are able to Read() to get each row from the result set.
125 <p>Here is a sample of code that is based on PostgresTest.cs and
126 TestSqlDataReader.cs tests:
129 static void SelectData (IDbConnection cnc) {
131 IDbCommand selectCommand = cnc.CreateCommand();
134 selectCommand.CommandType = CommandType.Text;
135 selectCommand.CommandText =
136 "select * from pg_user;" +
\r
137 "select * from pg_tables;" +
\r
138 "select * from pg_database";
\r
140 reader = selectCommand.ExecuteReader ();
144 Console.WriteLine("Result Set " + results + "...");
\r
146 // get the DataTable that holds
\r
148 DataTable dt = reader.GetSchemaTable();
\r
150 // number of columns in the table
\r
151 Console.WriteLine(" Total Columns: " +
\r
154 // display the schema
\r
155 for(c = 0; c < dt.Columns.Count; c++) {
\r
156 Console.WriteLine(" Column Name: " +
\r
157 dt.Columns[c].ColumnName);
\r
158 Console.WriteLine(" MaxLength: " +
\r
159 dt.Columns[c].MaxLength);
\r
160 Console.WriteLine(" Type: " +
\r
161 dt.Columns[c].DataType);
\r
165 // Read and display the rows
\r
166 while(rdr.Read()) {
\r
167 Console.WriteLine(" Row " + nRows + ": ");
\r
169 for(c = 0; c < rdr.FieldCount; c++) {
\r
170 if(reader.IsDBNull(c) == true)
\r
171 Console.WriteLine(" " +
\r
172 reader.GetName(c) + " is DBNull");
\r
174 Console.WriteLine(" " +
\r
175 reader.GetName(c) + ": " +
\r
176 reader[c].ToString());
\r
180 Console.WriteLine(" Total Rows: " +
\r
182 } while(reader.NextResult());
\r
183 Console.WriteLine("Total Result sets: " + results);
\r
190 <p>We are able to get
191 String data (char, character, text, varchar), Int16 (smallint),
192 Int32 (integer), Int64 (bigint), DateTime (time, date, timestamp),
193 Boolean (boolean), Single (float), and Double (double).
194 More data types will come later. Note, the types that do work still
195 need thorough testing.
197 <p>Rows that are returned which contain columns that are NULL are handled now.
198 The SqlDataReader method IsDBNull() needs to be called to determine
199 if a field IS NULL before trying to read data from that field.
201 <p>Calling PostgreSQL stored procedures works. It does not work perfectly. It may not
202 even work to specification - yet. If you want to test it yourself, look at
203 TestSqlDataReader.cs or PostgresTest.cs in
204 mcs/class/System.Data/Test.
206 <p>Below, I have some sample code you can
207 use to call a PostgreSQL stored procedure named "version". This stored
208 procedure returns a string containing the PostgreSQL server version. Notice
209 the CommandType is StoredProcedure and the method ExecuteScalar() is called.
211 <p>ExecuteScalar() is a lightweight method in class SqlCommand that only returns
212 one row and one column as one object - even if there is more than row or column.
215 static string GetDatabaseServerVersion (SqlConnection cnc)
217 SqlCommand cmd = cnc.CreateCommand ();
220 cmd.CommandType = CommandType.StoredProcedure;
221 cmd.CommandText = "version";
223 data = (string) cmd.ExecuteScalar ();
229 <p>Parameters have not been tested and most likely do not work.
231 <p>A lot of functionality in System.Data is missing, but the
232 infrastructure is starting to come together.
234 <p>A lot of Exceptions need to be thrown for various exceptions.
236 <p>Tim Coleman and Rodrigo Moya got the beginnings of the
237 SqlDataAdapter/DataSet/DataTable/DataRow to work. Currently,
238 the SqlDataAdapter can Fill() relational data into a DataTable in a DataSet.
239 See the test mcs/class/System.Data/Test/TestSqlDataAdapter.cs to see it in action.
240 Below, I show a snippets from the test:
243 string connectionString;
245 SqlDataAdapter adapter;
246 DataSet dataSet = null;
253 sqlQuery = "select * from pg_tables";
255 adapter = new SqlDataAdapter (sqlQuery,
258 adapter.SelectCommand.Connection.Open ();
260 dataSet = new DataSet ();
262 adapter.Fill (dataSet);
264 if (dataSet != null) {
265 foreach (DataRow row in dataSet.Tables["Table"].Rows)
266 Console.WriteLine("tablename: " + row["tablename"]);
270 <p>We do need help on the DataSet/DataAdaptor/DataTable/DataRelation/XML
271 functionality so we can integrate with
272 the ASP.NET controls and Windows.Forms controls by allowing the controls to bind
275 <P>Need to add XML support in System.Data. This involves working on
276 the classes: DataSet and XmlDataDocument and the ExecuteXmlReader() in SqlCommand.
278 <p>The System.Data.dll gets built with the rest of the class library.
279 To compile the System.Data.dll assembly separately, you need:
284 * update your mono sources. Be sure you have latest mcs.exe
285 and .dll's, since there have been many fixes needed for
286 compilation on Linux.
288 * compile System.Data.dll:
290 cd mcs/class/System.Data<br>
291 mcs --target library -o System.Data.dll @list
298 * update your mono sources. Be sure you have latest mcs.exe
299 and .dll's. You can use the same method as Linux,
305 cd mcs/class/System.Data
309 This will automatically copy the System.Data.dll to Test.
310 If you need to do a clean for the System.Data.dll assembly,<br><br>
313 cd mcs/class/System.Data
314 ../../nant/NAnt.exe clean
320 <p>In order to test System.Data.SqlClient, you will need to have
321 access to a remote PostgreSQL DBMS, or you will have to install
322 one locally. PostgreSQL is the DBMS used for the initial
323 implementation of System.Data.SqlClient.
325 <p>Why? Because it is open source, has a client
326 library that is easy to use, PostgreSQL is easy to install on
327 Unix and Windows (using the Cygwin install program), not difficult to setup after
328 installation, and it runs under: Linux,
329 Windows (via cygwin and ipc-daemon), Unix, and
330 others. This allowed us to create the
331 System.Data functionality in Mono much quicker.
333 <p>If you plan on using a remote PostgreSQL DBMS Server,
334 than you will need to have the PostgreSQL client software on your
335 local computer that includes libpq.so (pq.dll on Windows).
337 <p>The System.Data tests use this connection string to connect
338 to the PostgreSQL database named "test" at host "localhost" as
342 "host=localhost;dbname=test;user=postgres"
345 <p>Installation instructions for PostgreSQL DBMS:
350 * Read the PostgreSQL Installation Instructions
351 at \usr\doc\postgresql-x.x.x\html\installation.html
353 * Depending on your Unix system,
354 PostgreSQL maybe already installed, a database user 'postgres' created,
355 a linux user 'postgres' created and initdb ran. Or maybe not.
360 mkdir /usr/local/pgsql/data
\r
361 chown postgres /usr/local/pgsql/data
\r
363 initdb -D /usr/local/pgsql/data
\r
364 postmaster -i -D /usr/local/pgsql/data
\r
369 * Make sure you have a database user named postgres. It is best to install
370 the PostgreSQL DBMS under linux user postgres. When you run the postmaster,
371 run it under the user postgres as well. If this was not done, then you
372 will need to create a user named postgres for the System.Data tests.
374 * If you already installed PostgeSQL and you do not have a database
375 user named postgres, then you can create user postgres using psql:
379 create user postgres with password 'fun2db';
382 * The postmaster must be run with -i option.
384 * In the /usr/local/pgsql/data/pg_hba.conf file, you need
385 to have the AUTH_TYPE set to md5. You can read more on this at
386 /usr/doc/postgresql-7.2.1/html/client-authentication.html
388 PostgreSQL html docs are located. See the 2nd line below,
389 host 127.0.0.1 has an AUTH_TYPE md5 in pg_hba.conf.
392 # TYPE DATABASE IP_ADDRESS MASK AUTH_TYPE
395 host all 127.0.0.1 255.255.255.255 md5
398 * If you can not find your PostgreSQL documentation locally or you
399 did not install it, then you
400 can get it <a href="http://www.postgresql.org/idocs/">here</a>.
407 * Use the <a href="http://www.cygwin.com/">Cygwin</a> installer to
408 install the PostgreSQL DBMS. It is
409 found in the database category.
411 * <p>Read the file postgres-x.x.README at /usr/doc/Cygwin and read
412 the requirements to install PostgreSQL. Those requirements
413 are included with cygwin except cygipc. A default installtion
414 of cygwin does not install everything you will need, so on the
415 safe side, just include everything when installing cygwin.
417 * <p>The -x.x in postgres-x.x is the version of your PostgreSQL DBMS.
419 * <p>Once Cygwin has installed the PostgreSQL DBMS on your computer,
420 read the file FAQ_MSWIN which is available
421 in /usr/doc/postgres-x.x
423 * <p>Important notes from this file are:
426 <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.
428 <p>The cygipc package contains the ipc-daemon you will need
429 to run before you can
430 run the PostgreSQL DBMS Server daemon (postmaster) or run
431 initdb which initializes the PostgreSQL database.
433 <p><b>3.</b> The Cygwin bin directory has to be placed in
434 the path before the Windows program directories,
435 for example, C:\cygwin\bin
437 <p><b>My own note.</b> In the Windows control panel, I set
438 the environment variables PATH to my cygwin /usr/local/bin,
439 /usr/bin, and /bin. I also set my LD_LIBRARY_PATH to
440 /usr/local/lib and /usr/lib. For example:
444 PATH=c:\cygwin\usr\local\bin;c:\cygwin\usr\bin;c:\cygwin\bin;
445 LD_LIBRARY_PATH=c:\cygwin\usr\local\lib;c:\cygwin\usr\lib;
448 <p><b>4.</b> Start the ipc-daemon that came with the cygipc
450 are two ways to do this: run it from the command line as:
456 <p>or you can set it up as a Windows service. See the
457 file cygrunsrv.README at /usr/doc/Cygwin on how to do this
458 for ipc-daemon and postmaster. Note the
459 troubleshooting section at the end of
460 the cygrunsrv.README file.
462 <p>To install ipc-daemon as a service,
467 ipc-daemon --install-as-service' (--remove-as-service)
477 <p>Read the installation.html file
478 at /usr/doc/postgresql-x.x/html/installation.html
480 <p>You will see in this file that you will need to
481 run the following commands:
485 mkdir /usr/local/pgsql/data
\r
486 initdb -D /usr/local/pgsql/data
\r
487 postmaster -D /usr/local/pgsql/data
\r
492 <p>When you need to connect to the database,
493 you will need ipc-daemon and postmaster running. Start ipc-daemon
494 before any of the command above. If you restart your computer, you
495 need to start ipc-daemon and postmaster either manually or as a
498 <p>psql is a command-line PostgreSQL client tool to
499 enter and run SQL commands and queries.
501 <p>If there is no database user named postgres, create a user named
502 postgres with the following SQL command in the client tool psql:
507 create user postgres with password 'fun2db';
509 <p>The only reason I say this is so you can easily use the System.Data tests
510 without having to change the database, userid, etc.
513 <p>In the path mcs/class/System.Data/Test
514 there is a PostgreSQL test program named
515 PostgreTest.cs. Thanks goes to Gonzalo for creating the original
518 <p>To use it to test System.Data, you
519 modify the file to your PostgreSQL database
520 connection requirements:
524 <li><b>dbname</b> database, ie., test</li>
525 <li><b>host</b> hostname of the PostgreSQL DBMS Server to connect to, ie., localhost</li>
526 <li><b>user</b> username, ie., someuser</li>
527 <li><b>password</b> password, ie., mypass1234</li>
530 <p>The connection string is in OLE-DB connection string format. Internally,
531 SqlConnection converts this to the PostgreSQL connection string format.
535 OLE-DB: "host=localhost;dbname=test;user=joe;password=smoe"
536 PostgreSQL: "host=localhost dbname=test user=joe password=smoe"
540 Note that OLE-DB includes the semicolons while PostgreSQL's connection
544 To compile the PostgresTest.cs program, do:
548 mcs PostgresTest.cs -r System.Data.dll
552 To run using mint, do:
556 mint PostgresTest.exe
560 To run using mono, do:
562 mono PostgresTest.exe
565 <p>You should get something like:
569 danmorg@DANPC ~/mono/mcs/class/System.Data/Test
\r
570 $ mcs PostgresTest.cs -r System.Data.dll
\r
572 danmorg@DANPC ~/mono/mcs/class/System.Data/Test
\r
573 $ mono PostgresTest.exe
\r
574 Postgres provider specific tests...
\r
577 Error (don't worry about this one)SqlError:PGRES_FATAL_ERROR ERROR:
\r
578 table "mono_postgres_test" does not exist
\r
581 Create table with all supported types:
\r
583 Insert values for all known types:
\r
587 Insert values for all known types:
\r
589 Aggregate: count(*)
\r
591 Aggregate: min(text_value)
\r
592 Agg Result: This is a text
\r
593 Aggregate: max(int4_value)
\r
594 Agg Result: 1048000
\r
595 Aggregate: sum(int4_value)
\r
596 Agg Result: 1048003
\r
597 Select values from the database:
\r
599 dt.Columns.Count: 28
\r
600 * Column Name: boolean_value
\r
602 Type: System.Boolean
\r
603 * Column Name: int2_value
\r
606 * Column Name: int4_value
\r
609 * Column Name: bigint_value
\r
612 * Column Name: float_value
\r
614 Type: System.Single
\r
615 * Column Name: double_value
\r
617 Type: System.Double
\r
618 * Column Name: numeric_value
\r
620 Type: System.Decimal
\r
621 * Column Name: char_value
\r
623 Type: System.String
\r
624 * Column Name: varchar_value
\r
626 Type: System.String
\r
627 * Column Name: text_value
\r
629 Type: System.String
\r
630 * Column Name: point_value
\r
632 Type: System.String
\r
633 * Column Name: time_value
\r
635 Type: System.DateTime
\r
636 * Column Name: date_value
\r
638 Type: System.DateTime
\r
639 * Column Name: timestamp_value
\r
641 Type: System.DateTime
\r
642 * Column Name: null_boolean_value
\r
644 Type: System.Boolean
\r
645 * Column Name: null_int2_value
\r
648 * Column Name: null_int4_value
\r
651 * Column Name: null_bigint_value
\r
654 * Column Name: null_float_value
\r
656 Type: System.Single
\r
657 * Column Name: null_double_value
\r
659 Type: System.Double
\r
660 * Column Name: null_numeric_value
\r
662 Type: System.Decimal
\r
663 * Column Name: null_char_value
\r
665 Type: System.String
\r
666 * Column Name: null_varchar_value
\r
668 Type: System.String
\r
669 * Column Name: null_text_value
\r
671 Type: System.String
\r
672 * Column Name: null_point_value
\r
674 Type: System.String
\r
675 * Column Name: null_time_value
\r
677 Type: System.DateTime
\r
678 * Column Name: null_date_value
\r
680 Type: System.DateTime
\r
681 * Column Name: null_timestamp_value
\r
683 Type: System.DateTime
\r
685 Col 0: boolean_value: False
\r
686 Col 1: int2_value: 5
\r
687 Col 2: int4_value: 3
\r
688 Col 3: bigint_value: 9
\r
689 Col 4: float_value: 3.141590
\r
690 Col 5: double_value: 3.141593
\r
691 Col 6: numeric_value: 123456789012.345
\r
692 Col 7: char_value: Mono.Data!
\r
693 Col 8: varchar_value: It was not me!
\r
694 Col 9: text_value: We got data!
\r
695 Col 10: point_value: (1,0)
\r
696 Col 11: time_value: Monday, 01 January 1 21:13:14
\r
697 Col 12: date_value: Tuesday, 29 February 2000 00:00:00
\r
698 Col 13: timestamp_value: Sunday, 29 February 2004 14:00:11
\r
699 Col 14: null_boolean_value is NULL
\r
700 Col 15: null_int2_value is NULL
\r
701 Col 16: null_int4_value is NULL
\r
702 Col 17: null_bigint_value is NULL
\r
703 Col 18: null_float_value is NULL
\r
704 Col 19: null_double_value is NULL
\r
705 Col 20: null_numeric_value is NULL
\r
706 Col 21: null_char_value is NULL
\r
707 Col 22: null_varchar_value is NULL
\r
708 Col 23: null_text_value is NULL
\r
709 Col 24: null_point_value is NULL
\r
710 Col 25: null_time_value is NULL
\r
711 Col 26: null_date_value is NULL
\r
712 Col 27: null_timestamp_value is NULL
\r
714 Col 0: boolean_value: True
\r
715 Col 1: int2_value: -22
\r
716 Col 2: int4_value: 1048000
\r
717 Col 3: bigint_value: 123456789012345
\r
718 Col 4: float_value: 3.141590
\r
719 Col 5: double_value: 3.141593
\r
720 Col 6: numeric_value: 123456789012.345
\r
721 Col 7: char_value: This is a char
\r
722 Col 8: varchar_value: This is a varchar
\r
723 Col 9: text_value: This is a text
\r
724 Col 10: point_value: (1,0)
\r
725 Col 11: time_value: Monday, 01 January 1 21:13:14
\r
726 Col 12: date_value: Tuesday, 29 February 2000 00:00:00
\r
727 Col 13: timestamp_value: Sunday, 29 February 2004 14:00:11
\r
728 Col 14: null_boolean_value is NULL
\r
729 Col 15: null_int2_value is NULL
\r
730 Col 16: null_int4_value is NULL
\r
731 Col 17: null_bigint_value is NULL
\r
732 Col 18: null_float_value is NULL
\r
733 Col 19: null_double_value is NULL
\r
734 Col 20: null_numeric_value is NULL
\r
735 Col 21: null_char_value is NULL
\r
736 Col 22: null_varchar_value is NULL
\r
737 Col 23: null_text_value is NULL
\r
738 Col 24: null_point_value is NULL
\r
739 Col 25: null_time_value is NULL
\r
740 Col 26: null_date_value is NULL
\r
741 Col 27: null_timestamp_value is NULL
\r
743 Calling stored procedure version()
\r
744 Result: PostgreSQL 7.2.1 on i686-pc-cygwin, compiled by GCC 2.95.3-5
\r
745 Database Server Version: PostgreSQL 7.2.1 on i686-pc-cygwin, compiled by GCC 2.9
\r