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. -
We are still working on Step 1, but we are planning the other steps. - If you have any ideas, let us know. - -
We are able to do simple CREATE TABLE, DROP TABLE, UPDATE, INSERT, and - DELETE SQL commands using the ExecuteNonQuery method in SqlCommand. - -
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. We are able to get - String data (char, bpchar (character), text, varchar), Int16 (int2 or smallint), - Int32 (int4 or integer), Int64 (int8 or bigint), DateTime (time, date, timestamp), - Boolean (boolean), Single (float), and Double (double). - Other More data types will follow later on. - -
Parameters and stored procedures have not been tested and most likely - do not work. - -
A lot of functionality in System.Data is missing, but the - infrastructure is starting to come together. +
Need help on the DataSet/DataAdaptor/DataTable/DataRelation/XML - functionality so we can integrate with - the ASP.NET controls and Windows.Forms. +
Need to add XML support in System.Data. +** ADO.NET Data Providers -
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 - -
Mono has many ADO.NET Data Providers to get you connected: - This will automatically copy the System.Data.dll to Test. - If you need to do a clean for the System.Data.dll assembly, - cd mcs/class/System.Data and run ../../nant/NAnt.exe clean -
In order to test System.Data.SqlClient, you will need to have - access to a remote PostgreSQL DBMS, or you will have to install - one locally. PostgreSQL is the DBMS used for the initial - implementation of System.Data.SqlClient. - -
Why? Because it is open source, has a client - library that is easy to use, PostgreSQL on Windows 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. - -
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). +
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. -
The System.Data tests use this connection string to connect - to the PostgreSQL database named "test" at host "localhost" as - user "postgres". - -
-"host=localhost;dbname=test;user=postgres" -- -
Installation instructions for PostgreSQL DBMS: - - On Unix - -
- su - adduser postgres - mkdir /usr/local/pgsql/data - chown postgres /usr/local/pgsql/data - su - postgres - initdb -D /usr/local/pgsql/data - postmaster -i -D /usr/local/pgsql/data >logfile 2>&1 & - createdb test - psql test -- - * Make sure you have a database user named postgres. It is best to install - the PostgreSQL DBMS under linux user postgres. When you run the postmaster, - run it under the user postgres as well. If this was not done, then you - will need to create a user named postgres for the System.Data tests. - - * If you already installed PostgeSQL and you do not have a database - user named postgres, then you can create user postgres using psql: - -
-psql test -create user postgres with password 'fun2db'; -- - * The postmaster must be run with -i option. - - * In the /usr/local/pgsql/data/pg_hba.conf file, you need - to have the AUTH_TYPE set to md5. You can read more on this at - /usr/doc/postgresql-7.2.1/html/client-authentication.html or wherever your - PostgreSQL html docs are located. See the 2nd line below, - host 127.0.0.1 has an AUTH_TYPE md5 in pg_hba.conf. - -
- # TYPE DATABASE IP_ADDRESS MASK AUTH_TYPE AUTH_ARGUMENT - - local all trust - host all 127.0.0.1 255.255.255.255 md5 -- - * If you can not find your PostgreSQL documentation locally or you - did not install it, then you - can get it here. - - - - On Windows - +** Database Access from ASP.NET
Read the file postgres-x.x.README at /usr/doc/Cygwin and read - the requirements to install PostgreSQL. Those requirements - are included with cygwin except cygipc. A default installtion - of cygwin does not install everything you will need, so on the - safe side, just include everything when installing cygwin. - - *
The -x.x in postgres-x.x is the version of your PostgreSQL DBMS. - - *
Once Cygwin has installed the PostgreSQL DBMS on your computer, - read the file FAQ_MSWIN which is available - in /usr/doc/postgres-x.x - - *
Important notes from this file are: - -
2. - Install the latest CygIPC package. - -
The cygipc package contains the ipc-daemon you will need - to run before you can - run the PostgreSQL DBMS Server daemon (postmaster) or run - initdb which initializes the PostgreSQL database. - -
3. The Cygwin bin directory has to be placed in - the path before the Windows program directories, - for example, C:\cygwin\bin - -
My own note. In the Windows control panel, I set - the environment variables PATH to my cygwin /usr/local/bin, - /usr/bin, and /bin. I also set my LD_LIBRARY_PATH to - /usr/local/lib and /usr/lib. For example: - -
-
-PATH=c:\cygwin\usr\local\bin;c:\cygwin\usr\bin;c:\cygwin\bin; -LD_LIBRARY_PATH=c:\cygwin\usr\local\lib;c:\cygwin\usr\lib; -- -
4. Start the ipc-daemon that came with the cygipc package. There - are two ways to do this: run it from the command line as: - -
-
-ipc-daemon & --
or you can set it up as a Windows service. See the - file cygrunsrv.README at /usr/doc/Cygwin on how to do this - for ipc-daemon and postmaster. Note the - troubleshooting section at the end of - the cygrunsrv.README file. - -
To install ipc-daemon as a service, - you just have to run - -
-
-ipc-daemon --install-as-service' (--remove-as-service) -- -
and then run - -
-net start ipc-daemon -+
Read the installation.html file - at /usr/doc/postgresql-x.x/html/installation.html - -
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 >logfile 2>&1 & -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. - -
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. +** 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 | +
-
- 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 -- -
You should get something like: +
-
- 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: - Get Schema. - dt.Columns.Count: 12 - * Column Name: boolean_value - MaxLength: 1 - Type: System.Boolean - * Column Name: int2_value - MaxLength: 2 - Type: System.Int16 - * Column Name: int4_value - MaxLength: 4 - Type: System.Int32 - * Column Name: bigint_value - MaxLength: 8 - Type: System.Int64 - * Column Name: float_value - MaxLength: 4 - Type: System.Single - * Column Name: double_value - MaxLength: 8 - Type: System.Double - * Column Name: char_value - MaxLength: -1 - Type: System.String - * Column Name: varchar_value - MaxLength: -1 - Type: System.String - * Column Name: text_value - MaxLength: -1 - Type: System.String - * Column Name: time_value - MaxLength: 8 - Type: System.DateTime - * Column Name: date_value - MaxLength: 4 - Type: System.DateTime - * Column Name: timestamp_value - MaxLength: 8 - Type: System.DateTime - 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.141593 - Col 6: char_value: Mono.Data! - Col 7: varchar_value: It was not me! - Col 8: text_value: We got data! - Col 9: time_value: Monday, 01 January 1 21:13:14 - Col 10: date_value: Tuesday, 29 February 2000 00:00:00 - Col 11: timestamp_value: Sunday, 29 February 2004 14:00:11 - 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.141593 - Col 6: char_value: This is a char - Col 7: varchar_value: This is a varchar - Col 8: text_value: This is a text - Col 9: time_value: Monday, 01 January 1 21:13:14 - Col 10: date_value: Tuesday, 29 February 2000 00:00:00 - Col 11: timestamp_value: Sunday, 29 February 2004 14:00:11 - Rows: 2 - Clean up... - Drop table... - OK - RESULT: 0 - -