On Unix systems: System.Data.OleDb will use the - LibGDA - engine. - -
LibGDA is a data access engine like ADO/OLE-DB, but for Unix. The - GDA in libGDA stands for GNU/GNOME Data Access, but it does not require GNOME. - It only requires glib2 and libxml2. LibGDA is used by - libgnomedb, GNOME-DB, and gaSQL. - -
There is work under way to get libgda working under - Windows using Cygwin by the GNOME-DB developers. - -
LibGDA has providers for MySQL, PostgreSQL, XML, ODBC, - and MDB Tools (MS Access support). - - * On Windows systems: System.Data.OleDb will use OLE-DB as - its engine. It may have the option of using libgda too. -
System.Data.SqlClient will then become a managed - provider for Microsoft SQL Server, both on Windows and - Linux, to be compatible with applications written - for the Microsoft .NET Development Framework. - -
Once Step 1 has been completed and the PostgreSQL - provider support has been moved to its own place at - System.Data.PostgreSQL, we will use - FreeTDS as the basis - for providing access to Microsoft SQL Server databases. - -
FreeTDS is a C API for Unix and Windows that implements - the TDS (Tabular Data Stream) protocol used in accessing - Microsoft SQL Server and Sybase databases. A .NET Data Provider could be - created for Sybase databases as well, but this would be put in Mono.Data.Sybase. -
unixODBC works on Unix and Windows. Well, I have not actually - used it on Cygwin, but it does build and install without problems. - -
unixODBC has providers for: - Oracle, Microsoft SQL Server and Sybase via FreeTDS, - MySQL, PostgreSQL, Informix, IBM DB2 (Universal Database), - Interbase, miniSQL (mSQL), AdabasD, Empress, YARD SQL, and others. - -
The idea in Microsoft .NET System.Data is to have - a managed provider for each supported DBMS. System.Data.SqlClient - for Microsoft SQL Server. - System.Data.OracleClient for Oracle 8i and 9i. +
We will need to have Mono.Data.MySQL, Mono.Data.PostgreSQL, - Mono.Data.DB2, and Mono.Data.miniSQL. Others, - of course, are welcomed. - -
System.Data has been designed so - non-database providers can be created too. -
We are working on Steps 1, 2, and 5. We have only just begun on - steps 2 and 5 though. We still have tons and tons of stuff to do. - If you have any ideas, let us know. - -
For Step 1, the PostgreSQL is starting to come together - it - still needs a lot of work. +
For Step 2, Rodrigo Moya has begun System.Data.OleDb which will use libgda - which is an OLE-DB/ADO data access for Unix. The C-Sharp bindings to libgda - currently work - meaning they can compile, run, and you can connect to a - PostgreSQL database via libgda via the C-Sharp bindings to libgda. He has also - added class stubs for System.Data.OleDb to cvs. +** ADO.NET Data Providers -
For Step 3, we need someone to start the FreeTDS .NET Data Provider so - we can have data access to Microsoft SQL Server and Sybase databases, or either - add the support in libgda. - -
For Step 4, we need someone to start the unixODBC .NET Data Provider, or add - the support in libgda. - -
For Step 5, we have just begun creating a Mono.Data .NET Provider - a MySQL - .NET Provider that uses the MySQL C Client Library. This provider is - found in Mono.Data.MySql. We can currently connect and do a SQL INSERT and insert - a row into a MySQL database. However, it currently only works on Cygwin because - the MySQL client library libmySQL.dll is different - from the library on Linux libmysqlclient.dll. Another problem, mysql thread functions - do not load for some reason. Also, the provider only runs if you use "mint" (the Mono - runtime interpreter). It does not work on "mono" (the Mono Just-In-Time compiler). - The C# Bindings to MySQL are thanks to Brad Meril. - -
We are able to do simple CREATE TABLE, DROP TABLE, UPDATE, INSERT, and - DELETE SQL commands using the ExecuteNonQuery method in SqlCommand. - -
We can execute multiple queries and do a NextResult() in SqlDataReader() - to get the next result set. - -
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. - -
We are also able to retrieve data with a simple SELECT SQL query - using ExecuteReader() which returns a SqlDataReader. 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. - -
Here is a sample of code that is based on PostgresTest.cs and - TestSqlDataReader.cs tests: -
-
- static void SelectData (IDbConnection cnc) {
-
- IDbCommand selectCommand = cnc.CreateCommand();
- IDataReader reader;
-
- selectCommand.CommandType = CommandType.Text;
- selectCommand.CommandText =
- "select * from pg_user;" +
- "select * from pg_tables;" +
- "select * from pg_database";
-
- reader = selectCommand.ExecuteReader ();
-
- do {
- // get the DataTable that holds
- // the schema
- DataTable dt = rdr.GetSchemaTable();
-
- if(rdr.RecordsAffected != -1) {
- // Results for
- // SQL INSERT, UPDATE, DELETE Commands
- // have RecordsAffected >= 0
- Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
- }
- else if (dt == null)
- Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);
- else {
- // Results for
- // SQL not INSERT, UPDATE, nor DELETE
- // have RecordsAffected = -1
- Console.WriteLine("Result is from a SQL SELECT Query. Records Affected: " + rdr.RecordsAffected);
-
- // Results for a SQL Command (CREATE TABLE, SET, etc)
- // will have a null reference returned from GetSchemaTable()
- //
- // Results for a SQL SELECT Query
- // will have a DataTable returned from GetSchemaTable()
-
- results++;
- Console.WriteLine("Result Set " + results + "...");
-
- // number of columns in the table
- Console.WriteLine(" Total Columns: " +
- dt.Columns.Count);
-
- // display the schema
- foreach (DataRow schemaRow in dt.Rows) {
- foreach (DataColumn schemaCol in dt.Columns)
- Console.WriteLine(schemaCol.ColumnName +
- " = " +
- schemaRow[schemaCol]);
- Console.WriteLine();
- }
-
- int nRows = 0;
- string output, metadataValue, dataValue;
- // Read and display the rows
- Console.WriteLine("Gonna do a Read() now...");
- while(rdr.Read()) {
- Console.WriteLine(" Row " + nRows + ": ");
-
- for(c = 0; c < rdr.FieldCount; c++) {
- // column meta data
- DataRow dr = dt.Rows[c];
- metadataValue =
- " Col " +
- c + ": " +
- dr["ColumnName"];
-
- // column data
- if(rdr.IsDBNull(c) == true)
- dataValue = " is NULL";
- else
- dataValue =
- ": " +
- rdr.GetValue(c);
-
- // display column meta data and data
- output = metadataValue + dataValue;
- Console.WriteLine(output);
- }
- nRows++;
- }
- Console.WriteLine(" Total Rows: " +
- nRows);
- }
- } while(rdr.NextResult());
- Console.WriteLine("Total Result sets: " + results);
-
- rdr.Close();
- }
-
-
-
- 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. - -
Rows that are returned which contain columns that are NULL are handled now. - The SqlDataReader method IsDBNull() needs to be called to determine - if a field IS NULL before trying to read data from that field. - -
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. - -
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. - -
ExecuteScalar() is a lightweight method in class SqlCommand that only returns - one row and one column as one object - even if there is more than row or column. - -
- static string GetDatabaseServerVersion (SqlConnection cnc)
- {
- SqlCommand cmd = cnc.CreateCommand ();
- string data;
-
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.CommandText = "version";
-
- data = (string) cmd.ExecuteScalar ();
-
- return data;
- }
-
-
- 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. - -
A lot of functionality in System.Data is missing, but the - infrastructure is starting to come together. - -
A lot of Exceptions need to be thrown for various exceptions. However, - SqlException, SqlErrorCollection, and SqlError have been partially - implemented. - -
Tim Coleman and Rodrigo Moya got the beginnings of the - SqlDataAdapter/DataSet/DataTable/DataRow to work. Currently, - the SqlDataAdapter 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. - Below, I show a snippets from the test: - -
- string connectionString; - string sqlQuery; - SqlDataAdapter adapter; - DataSet dataSet = null; - - connectionString = - "host=localhost;" + - "dbname=test;" + - "user=postgres"; - - sqlQuery = "select * from pg_tables"; - - adapter = new SqlDataAdapter (sqlQuery, - connectionString); - - dataSet = new DataSet (); - - adapter.Fill (dataSet); +- -Mono has many ADO.NET Data Providers to get you connected: - if (dataSet != null) { - foreach (DataRow row in dataSet.Tables["Table"].Rows) - Console.WriteLine("tablename: " + row["tablename"]); - } -
We do need help on the DataSet/DataAdaptor/DataTable/DataRelation/XML - functionality so we can integrate with - the ASP.NET controls and Windows.Forms controls by allowing the controls to bind - to a data source. Gonzalo, Gaurav, Leen, Patrik, Duncan, and others are - working very hard on the ASP.NET support. If you want to help, - contact Gonzalo Paniagua Javier - -
Need to add XML support in System.Data. This involves working on - the classes: DataSet and XmlDataDocument and the ExecuteXmlReader() in SqlCommand. - -
The System.Data.dll gets built with the rest of the class library. - To compile the System.Data.dll assembly separately, you need: - - On Unix - +
External Projects that have created ADO.NET Providers that work on Mono: + +
Some tools that can be used for ADO.NET and other technologies (ASP.NET, XML, etc). + +
An ADO.NET Provider Factory + was created by Brian Ritchie. The + Provider Factory is a way to dynamically create + connections, commands, parameters, and data adapters based on configuration + information. + +** Database Access from ASP.NET
- cd mcs/class/System.Data+
- mcs --target library -o System.Data.dll @list -
- cd mcs/class/System.Data - ../../nant/NAnt.exe -+** Testing + +
| Name | +Assembly / Namespace | +Test | +
| Microsoft SQL Server | +System.Data / System.Data.SqlClient | +SqlTest.cs at System.Data/Test | +
| PostgreSQL (Npgsql) | +Npgsql / Npgsql | +*.cs at Npgsql/Test | +
| Oracle (Mono) | +System.Data.OracleClient / System.Data.OracleClient | +TestOracleClient.cs at System.Data.OracleClient/Test | +
| ODBC (Mono) | +System.Data.Odbc / System.Data | +OdbcTest.cs (to connect to MS SQL Server)at System.Data/Test | +
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 PostgreSQL test program named - PostgreTest.cs. Thanks goes to Gonzalo for creating the original - PostgreSQL test. - -
To use it to test System.Data, you - modify the file to your PostgreSQL database - connection requirements: - -
-
The connection string is in OLE-DB connection string format. Internally, - SqlConnection converts this to the PostgreSQL connection string format. - -
-
- OLE-DB: "host=localhost;dbname=test;user=joe;password=smoe" -PostgreSQL: "host=localhost dbname=test user=joe password=smoe" -- -
- Note that OLE-DB includes the semicolons while PostgreSQL's connection - string does not. - -
- To compile the PostgresTest.cs program, do: - -
-
-mcs PostgresTest.cs -r System.Data.dll -- -
- To run using mint, do: - -
-
-mint PostgresTest.exe -- -
- To run using mono, do: -
-mono PostgresTest.exe -- -
Below, I show how the output from PostgresTest. I have omitted a lot - of the meta data for the columns except two columns. The classes - used were from System.Data.SqlClient and were used to connect to a - PostgreSQL database and retrieve data. + -
-
- -danmorg@DANPC ~/mono/mcs/class/System.Data/Test -$ mcs PostgresTest.cs -r System.Data.dll - -danmorg@DANPC ~/mono/mcs/class/System.Data/Test -$ mono PostgresTest.exe - Postgres provider specific tests... - - Drop table: -Error (don't worry about this one)SqlError:PGRES_FATAL_ERROR ERROR: table "mono -_postgres_test" does not exist --- - Create table with all supported types: -OK - Insert values for all known types: -OK - Update values: -OK - Insert values for all known types: -OK -Aggregate: count(*) -Agg Result: 2 -Aggregate: min(text_value) -Agg Result: This is a text -Aggregate: max(int4_value) -Agg Result: 1048000 -Aggregate: sum(int4_value) -Agg Result: 1048003 - Select values from the database: -Result is from a SELECT SQL Query. Records Affected: -1 -Result Set 1... - Total Columns: 28 -ColumnName = boolean_value -ColumnOrdinal = 1 -ColumnSize = 1 -NumericPrecision = 0 -NumericScale = 0 -IsUnique = False -IsKey = -BaseCatalogName = -BaseColumnName = boolean_value -BaseSchemaName = -BaseTableName = -DataType = System.Boolean -AllowDBNull = False -ProviderType = 16 -IsAliased = False -IsExpression = False -IsIdentity = False -IsAutoIncrement = False -IsRowVersion = False -IsHidden = False -IsLong = False -IsReadOnly = False - - ... - - ColumnName = null_timestamp_value - ColumnOrdinal = 28 - ColumnSize = 8 - NumericPrecision = 0 - NumericScale = 0 - IsUnique = False - IsKey = - BaseCatalogName = - BaseColumnName = null_timestamp_value - BaseSchemaName = - BaseTableName = - DataType = System.DateTime - AllowDBNull = False - ProviderType = 1184 - IsAliased = False - IsExpression = False - IsIdentity = False - IsAutoIncrement = False - IsRowVersion = False - IsHidden = False - IsLong = False - IsReadOnly = False - - Gonna do a Read() now... - Row 0: - Col 0: boolean_value: False - Col 1: int2_value: 5 - Col 2: int4_value: 3 - Col 3: bigint_value: 9 - Col 4: float_value: 3.141590 - Col 5: double_value: 3.14159 - Col 6: numeric_value: 123456789012.345 - Col 7: char_value: Mono.Data! - Col 8: varchar_value: It was not me! - Col 9: text_value: We got data! - Col 10: point_value: (1,0) - Col 11: time_value: 01/01/1 21:13:14 - Col 12: date_value: 02/29/2000 00:00:00 - Col 13: timestamp_value: 02/29/2004 14:00:11 - Col 14: null_boolean_value is NULL - Col 15: null_int2_value is NULL - Col 16: null_int4_value is NULL - Col 17: null_bigint_value is NULL - Col 18: null_float_value is NULL - Col 19: null_double_value is NULL - Col 20: null_numeric_value is NULL - Col 21: null_char_value is NULL - Col 22: null_varchar_value is NULL - Col 23: null_text_value is NULL - Col 24: null_point_value is NULL - Col 25: null_time_value is NULL - Col 26: null_date_value is NULL - Col 27: null_timestamp_value is NULL - Row 1: - Col 0: boolean_value: True - Col 1: int2_value: -22 - Col 2: int4_value: 1048000 - Col 3: bigint_value: 123456789012345 - Col 4: float_value: 3.141590 - Col 5: double_value: 3.14159 - Col 6: numeric_value: 123456789012.345 - Col 7: char_value: This is a char - Col 8: varchar_value: This is a varchar - Col 9: text_value: This is a text - Col 10: point_value: (1,0) - Col 11: time_value: 01/01/1 21:13:14 - Col 12: date_value: 02/29/2000 00:00:00 - Col 13: timestamp_value: 02/29/2004 14:00:11 - Col 14: null_boolean_value is NULL - Col 15: null_int2_value is NULL - Col 16: null_int4_value is NULL - Col 17: null_bigint_value is NULL - Col 18: null_float_value is NULL - Col 19: null_double_value is NULL - Col 20: null_numeric_value is NULL - Col 21: null_char_value is NULL - Col 22: null_varchar_value is NULL - Col 23: null_text_value is NULL - Col 24: null_point_value is NULL - Col 25: null_time_value is NULL - Col 26: null_date_value is NULL - Col 27: null_timestamp_value is NULL - Total Rows Retrieved: 2 - Total Result sets: 1 - Call ExecuteReader with a SQL Command. (Not INSERT,UPDATE,DELETE - ). - Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: -1 - Total Result sets: 0 - Call ExecuteReader with a SQL Command. (Is INSERT,UPDATE,DELETE) - . - Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: 1 - Total Result sets: 0 - Calling stored procedure version() - Result: PostgreSQL 7.2.1 on i686-pc-cygwin, compiled by GCC 2.95.3-5 - Database Server Version: PostgreSQL 7.2.1 on i686-pc-cygwin, compiled by GCC 2.9 - 5.3-5 - Clean up... - Drop table... - OK - RESULT: 0 - -