* ADO.NET The coordinator for the ADO.NET implementation is Rodrigo Moya, with the collaboration of Daniel Morgan. * Action plan The current plan to implement ADO.NET is as follows: Step 1: SqlClient:
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.
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 yet.
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.
To compile that test program, you need: On Linux
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).
Installation instructions for PostgreSQL DBMS: On Linux
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:
dbname is the database, ie, test host is the hostname of the PostgreSQL DBMS Server to connect to user is the username, ie, someuser password is the password, ie, mypass1234
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
To run using mint, do:
mint PostgresTest.exe
To run using mono, do:
mono PostgresTest.exe
You should get something like:
Administrator@DANPC ~/mono/mcs/class/System.Data/Test $ mcs PostgresTest.cs -r System.Data Administrator@DANPC ~/mono/mcs/class/System.Data/Test $ mint PostgresTest.exe Postgres provider specific tests... Drop table: Error (don't worry about this one) Create table with all supported types: OK Insert values for all known types: OK Select values from the database: Get Schema. dt.Columns.Count: 6 * 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: 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 Row 0: Col 0: int2_value - -22 Col 1: int4_value - 1048000 Col 2: bigint_value - 123456789012345 Col 3: char_value - This is a char Col 4: varchar_value - This is a varchar Col 5: text_value - This is a text Rows: 1 Clean up... Drop table... OK