* ADO.NET
- The coordinator for the ADO.NET implementation is <a
- href="mailto:rodrigo@ximian.com">Rodrigo Moya</a>, with
- the collaboration of <a href="mailto:danmorg@sc.rr.com">Daniel
- Morgan</a> and <a href="mailto:tim@timcoleman.com">Tim Coleman</a>.
+ The coordinator for the ADO.NET implementation is
+ <a href="mailto:rodrigo@ximian.com">Rodrigo Moya</a>,
+ with the collaboration of:
+ <a href="mailto:danmorg@sc.rr.com">Daniel Morgan</a>,
+ <a href="mailto:tim@timcoleman.com">Tim Coleman</a>,
+ <a href="mailto:brianlritchie@hotmail.com">Brian Ritchie</a>, and
+ <a href="mailto:vladimir@pobox.com">Vladimir Vukicevic</a>.
* Action plan
* Once the <a href="http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatasqlclient.asp?frame=true">System.Data.SqlClient</a>
code is functional and is usable by other people, we willl move it to
- Mono.Data.PostgreSQL, and will convert the existing
- System.Data.SqlClient to be just a wrapper around
Mono.Data.PostgreSQL.
+
+ * <a href="http://www.go-mono.com/postgresql.html">Current PostgreSQL ADO.NET Provider Status and Test Notes</a>
</ul>
<p>There is work under way to get libgda working under
Windows using Cygwin by the GNOME-DB developers.
- <p>LibGDA has providers for MySQL, PostgreSQL, XML, ODBC,
- Oracle, Interbase, Sybase/SQL server (via FreeTDS), IBM DB2,
- SQLite and MDB Tools
- (MS Access support).
+ <p>LibGDA has providers for
+ <a href="http://www.mysql.com/">MySQL</a>,
+ <a href="http://www.postgresql.org/">PostgreSQL</a>,
+ XML,
+ ODBC (via <a href="http://www.unixodbc.org/">unixODBC</a>),
+ <a href="http://www.oracle.com/">Oracle</a>,
+ <a href="http://www.borland.com/products/downloads/download_interbase.html">Interbase</a>,
+ <a href="http://www.sybase.com/downloads">Sybase</a> and
+ <a href="http://www.microsoft.com/sql/default.asp">Microsoft SQL Server</a> (
+ via <a href="http://www.freetds.org/">FreeTDS</a>),
+ <a href="http://www-3.ibm.com/software/data/db2/">IBM DB2 Universal Database</a>,
+ <a href="http://www.hwaci.com/sw/sqlite/download.html">SQL Lite</a>,
+ and http://www.microsoft.com/office/access/default.asp">MS Access</a>
+ (via <a href="http://mdbtools.sourceforge.net/">MDB Tools</a>).
* On Windows systems: System.Data.OleDb will use libgda as well,
if we can get it working, or OLE-DB as
its engine.
+
</ul>
<b>Step 3:</b> <a href="http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatasqlclient.asp?frame=true">System.Data.SqlClient</a> Provider:
<ul>
- * <p>System.Data.SqlClient will then become a managed
+ * <p>System.Data.SqlClient will then become a
provider for Microsoft SQL Server, both on Windows and
Linux, to be compatible with applications written
for the Microsoft .NET Development Framework.
provider support has been moved to its own place at
Mono.Data.PostgreSQL, we will use
<a href="http://www.freetds.org/">FreeTDS</a> as the basis
- for providing access to Microsoft SQL Server databases.
+ for providing access to Microsoft SQL Server databases. Or we will
+ use FreeTDS and jTDS (a 100% Java JDBC Provider for TDS) as a basis for
+ creating a pure managed SQL Server provider.
<P>FreeTDS is a C API for Unix and Windows that implements
the TDS (Tabular Data Stream) protocol used in accessing
<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:
<ul>
- * We will create a .NET Managaed Provider for ODBC
- in System.Data.Odbc for those using ODBC.
- On Unix and Windows, <a href="http://www.unixodbc.org/">unixODBC</a> mabye used.
- iODBC is an alternative to using unixODBC.
-
+ * An ADO.NET Provider for ODBC has been created in
+ System.Data.Odbc for those using ODBC.
+ On Unix, <a href="http://www.unixodbc.org/">unixODBC</a> mabye used
+ which has libodbc.so
+ On Windows, uses the native ODBC libraries (odbc32.dll) that comes
+ with Windows since unixODBC is supposed to be compatible with that.
+ If you do not have odbc32.dll on Wwindows, get the latest MDAC
+ from Microsoft. The mapping between these native shared libraries is handled
+ in etc/mono/config
+ In mono cvs, the file is mono/data/config.in
+
<p>unixODBC works on Unix and Windows. Well, I have not actually
used it on Cygwin, but it does build and install without problems.
<p>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.
+ Oracle, Microsoft SQL Server and Sybase (via FreeTDS),
+ MySQL, PostgreSQL, Informix, IBM DB2 Universal Database,
+ Interbase, miniSQL (mSQL), AdabasD, Empress, Ingres, Sap DB, and others.
</ul>
- <b>Step 5:</b> Other System.Data providers:
+ <b>Step 5:</b> Other ADO.NET providers:
<ul>
* <p>The idea in Microsoft .NET System.Data is to have
for Microsoft SQL Server.
System.Data.OracleClient for Oracle 8i and 9i.
- <p>We will need to have Mono.Data.MySQL, Mono.Data.PostgreSQL,
- Mono.Data.DB2, and Mono.Data.miniSQL. Others,
- of course, are welcomed.
+ <p>Two Mono.Data providers have been started:
+ Mono.Data.MySql for the MySQL client/server database
+ and Mono.Data.SqliteClient for the SQL Lite database.
+ See Current Status for more information on these providers.
+
+ <p>Some providers we would like to have are
+ Mono.Data.DB2, Mono.Data.miniSQL,
+ System.Data.OracleClient, Mono.Data.Interbase,
+ Mono.Data.Berkeley, and SapDB.
+ Others, of course, are welcomed.
<p>System.Data has been designed so
- non-database providers can be created too.
+ non-database providers can be created too.
+ </ul>
+
+ <b>Step 6:</b> Create tools for ADO.NET
+
+ <ul>
+ * <p><b>SQL# CLI - a command-line tool</b> to execute SQL commands,
+ test connection strings, and connect to various ADO.NET
+ providers in Mono. The SQL# CLI has been started on and can be found in
+ mcs/class/System.Data/Test/SqlSharpCli.cs
+ It is written in C# and runs on Mono.
+
+ * <p><b>SQL# GUI - a GUI tool</b> to execute SQL commands. This
+ has not been started. The idea is to use the GnomeDbSqlEditor,
+ GnomeDbBrowser, and GnomeDbGrid gtk+ widgets that exists
+ in libgnomedb# that is part of gtk#. However, we could base it
+ on System.Windows.Forms, but System.Windows.Forms in Mono
+ is not as complete as gtk#.
+
+ <p>libgnomedb uses libgda for its data model and data access capabilities and
+ can be found <a href="http://www.gnome-db.org/">here</a>. libgnomedb#
+ and gda# are part of <a href="http://gtk-sharp.sourceforge.net/">gtk#</a>
+ libgnomedb# is the C# bindings to libgnomedb which is a gtk+ GUI
+ widget library for data access.
+ gda# is the C# bindings to libgda which is a generic data access
+ library that has providers for many databases.
+
+ * <p>ADO.NET Configuration command-line and GUI tools. These tools have
+ not been started. They could become a part of SQL# though. The
+ configuration tool can be used to set up DSNs for the ODBC and OLE-DB
+ providers. The tools would be written in C# and run on Mono.
+
</ul>
* Current Status
- <p>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.
-
- <p>For Step 1, the PostgreSQL is starting to come together - it
- still needs a lot of work.
+ <p>We are working on Steps 1, 2, 4, and 5. We still have tons and tons of
+ stuff still to do. If you have any ideas, let us know on the mono-list@ximian.com
- <p>For Step 2, Rodrigo Moya has been working on System.Data.OleDb which uses libgda
+ <p><b>For Step 1</b>, the <p>PostgreSQL</p> provider can connect,
+ execute commands, retrieve data via a Data Reader, read data in a DataTable
+ in a DataSet via a Data Adapter, get a DataTable holding schema data,
+ and use input parameters in its SQL. Currently, the PostgreSQL provider
+ exists in System.Data.SqlClient. It needs to be moved
+ to Mono.Data.PostgreSQL so the Microsoft SQL Server provider can
+ be placed in System.Data.SqlClient.
+
+ <p><b>For Step 2</b>, Rodrigo Moya has been working on <b>System.Data.OleDb</b>
+ which uses 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. Basic
functionality (execution of commands, data retrieval, transactions, etc) are
now working. Current focus is on filling up the missing pieces (Data adapters
- mainly).
-
- <p>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.
-
- <p>For Step 4, we need someone to start the unixODBC .NET Data Provider, or add
- the support in libgda.
-
- <p>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).
+ mainly) and schema support. We need help building libgda on Windows though. libgda
+ builds find on linux though.
+
+ <p><b>For Step 3</b>, we need someone to start on the <b>Microsoft SQL Server</b>
+ and <b>Sybase</b> support. This can be done by creating
+ a <b>FreeTDS</b> .NET Data Provider
+ or C# bindings to FreeTDS native libraries. A better solution, but a solution
+ that would take longer, would be to create a pure
+ managed provider in C# that implements the TDS (Tabular DataStream) protocol. TDS
+ is used to access Microsoft SQL Server and Sybase databases. Another possibility
+ is to wrap System.Data.SqlClient around System.Data.OleDb since System.Data.OleDb on
+ Mono uses <a href="http://www.gnome-db.org/">libgda</a> and libgda has
+ a FreeTDS provider. Yet another possibility, port
+ the <a href="http://jtds.sourceforge.net/">jTDS</a> provider to a pure C# ADO.NET \r
+ provider. jTDS is a 100% pure Java (Type 4) open source JDBC 2.0 driver for \r
+ the Microsoft SQL Server series (6.5, 7.x and 2000). \r
+
+ <p><b>For Step 4</b>, Brian Ritchie has contributed an initial implementation of
+ an <b>ODBC</b> ADO.NET Provider that uses unixODBC on Linux and the native odbc\r
+ that comes on Windows. There is basic implementations of \r
+ OdbcConnection, OdbcCommand, OdbcDataReader, OdbcParameter, and \r
+ OdbcParameterCollection. The provider can execute queries and display result sets \r
+ (and ExecuteNonQuery too). Currently, developers are working on the Parameter & \r
+ Transaction support. It was tested on Debian against a DB2 7.2 UDB. It was\r
+ also test on Windows XP against a Microsoft SQL Server 2000.\r
+
+ <p><b>For Step 5</b>, we have create two Mono.Data providers so far:
+ Mono.Data.MySql and Mono.Data.SqliteClient.
+
+ <p><b>Mono.Data.MySql</b> is an ADO.NET provider for
+ the <b>MySQL</b> client/server database management system
+ that uses the MySQL C Client Library. This provider is
+ found in Mono.Data.MySql. Currently, we can connect,
+ execute simple commands, and
+ retrieve results using a data reader. A schema DataTable has been partially
+ implemented which is returned from GetSchemaTable() in MySqlDataReader.
+ The shared client libraries
+ between windows version and linux are different: windows has libmySQL.dll
+ while linux has libmysqlclient.so. This is handled by the
+ file etc/mono/config which is mapped by the mono runtime in knowing
+ which native shared library to load. In cvs, this file is mono/config.in and
+ can be modified with a text editor.
The C# Bindings to MySQL are thanks to Brad Meril.
+
+ <p><b>Mono.Data.SqliteClient</b> is an ADO.NET provider for
+ the <b>SQL Lite</b> database. SQL Lite can be
+ found <a href"http://www.hwaci.com/sw/sqlite/download.html">here</a>.
+ There is SQL Lite binaries for Linux and Windows. sqlite.dll on Windows
+ and sqlite.so on Linux. The SQL Lite proivder
+ was contributed by Vladimir Vukicevic.
- <p>We are able to do simple CREATE TABLE, DROP TABLE, UPDATE, INSERT, and
- DELETE SQL commands using the ExecuteNonQuery method in SqlCommand.
-
- <p>We can execute multiple queries and do a NextResult() in SqlDataReader()
- to get the next result set.
-
- <p>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.
-
- <p>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.
-
- <p>Here is a sample of code that is based on PostgresTest.cs and
- TestSqlDataReader.cs tests:
-<pre>
-
- static void SelectData (IDbConnection cnc) {
+ <p><b>DataSet, DataAdaptor, DataTable, DataRelation, DataRow, DataColumn,
+ DataColumnCollection, DataRowCollection, and others</b> need more work.
+
+ <p><b>Integration with ASP.NET</b> needs to be started, such as, data binding
+ to a System.Web.UI.WebControls.DataGrid. This may involve implementing many classes
+ in System.Web and System.Data. Gonzalo, Gaurav, Leen, Patrik, Duncan, and others are
+ working very hard on the ASP.NET support. If you want to help,
+ contact <a href="mailto:gonzalo@ximian.com">Gonzalo Paniagua Javier</a>
- IDbCommand selectCommand = cnc.CreateCommand();
- IDataReader reader;
-
- selectCommand.CommandType = CommandType.Text;
- selectCommand.CommandText =
- "select * from pg_user;" + \r
- "select * from pg_tables;" + \r
- "select * from pg_database";\r
-
- reader = selectCommand.ExecuteReader ();
-
- do {\r
- // get the DataTable that holds\r
- // the schema\r
- DataTable dt = rdr.GetSchemaTable();\r
+ <p><b>Integration with Windows.Forms</b> needs to be started, such as, data binding
+ to a System.Windows.Forms.DataGrid. This may involve implementing many classes
+ in System.Windows.Forms and System.Data.
+
+ <p><b>Integration with <a href="http://gtk-sharp.sourceforge.net/">GTK#</a></b>
+ needs to be started, such as, data binding
+ to a GtkTreeView. This may involve creating new classes
+ to go between the glist data model and the ADO.NET data model.
+ Mike Kestner would be the best person to ask for help on GTK#.
+
+ <p><b>Integration with QT#</b> needs to be started. Any information on
+ how this can be done is appreciated. Adam Treat would be the best
+ person to ask about QT#.
+
+ <p><b>Integration with GDA# and Libgnomedb#</b> which exists in GTK# needs
+ to be started. Creating classes
+ to bind data between the data model in ADO.NET and the data model that
+ exists in <a href="http://www.gnome-db.org/">GDA</a> and
+ <a href="http://www.gnome-db.org/">libgnomedb</a>. There are some nifty widgets
+ (GUI controls) that can be used, such as, GnomeDbGrid, GnomeDbBrowswer,
+ GnomeDbSqlEditor, GnomeDbList, GnomeDbLogin, GnomeDbReportEditor, GnomeDbTableEditor,
+ GnomeDbCombo, and GnomeDbForm.
+
+ <p><b>XML support in System.Data</b> needs to be started. This involves working on
+ the classes: DataSet, XmlDataDocument, and the method ExecuteXmlReader() that
+ exists in a provider's class that implements IDbCommand, and others.
+
+ <p><b>ADO.NET Multiplexor Provider</b> needs to be created. This is not
+ part of Microsoft .NET, but it needs to be able to run on Microsoft .NET
+ and Mono. This provider can be a generic provider, much like the ODBC.NET and
+ OLEDB.NET providers are, but be written in 100% C# and be configurable via
+ a configuration file. This provider will multiplex to other providers
+ that exist. If one does not exist for a given DBMS,
+ default to the ODBC or OLEDB .NET provider. Or it could be a class
+ that its sole duty is to dynamically create a provider's Connection class
+ based on a configuration file. It can provide some utility functions as
+ well that can be used in all or most of the providers, such as, what
+ parameter marker character should be used: a colon, at, question mark,
+ or square brackets.
+
+ <p>According to Gonzalo, this is how it could be implemented:
\r
- if(rdr.RecordsAffected != -1) {\r
- // Results for \r
- // SQL INSERT, UPDATE, DELETE Commands \r
- // have RecordsAffected >= 0\r
- Console.WriteLine("Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);\r
- }\r
- else if (dt == null)\r
- Console.WriteLine("Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: " + rdr.RecordsAffected);\r
- else {\r
- // Results for\r
- // SQL not INSERT, UPDATE, nor DELETE\r
- // have RecordsAffected = -1\r
- Console.WriteLine("Result is from a SQL SELECT Query. Records Affected: " + rdr.RecordsAffected);\r
- \r
- // Results for a SQL Command (CREATE TABLE, SET, etc)\r
- // will have a null reference returned from GetSchemaTable()\r
- // \r
- // Results for a SQL SELECT Query\r
- // will have a DataTable returned from GetSchemaTable()\r
+ <p>After some work done in System.Configuration, you can now do something\r
+ like: \r
\r
- results++;\r
- Console.WriteLine("Result Set " + results + "...");\r
- \r
- // number of columns in the table\r
- Console.WriteLine(" Total Columns: " +\r
- dt.Columns.Count);\r
+<pre>\r
+ // Get an instance of the multiplexor from machine.config file\r
+ // Can be overriden in the application config file \r
+ object o = ConnectionSettings.GetConfig ("mono.data/multiplexor");\r
+ if (o == null)\r
+ --- error\r
+ \r
+ Multiplexor mp = (Multiplexor) o;\r
+ \r
+ // may be a string [] argument can help passing arguments\r
+ IDbConnection cnc = mp.CreateConnection (providerName);\r
+</pre>\r
+ \r
+ <p>and in the machine.config file:\r
\r
- // display the schema\r
- foreach (DataRow schemaRow in dt.Rows) {\r
- foreach (DataColumn schemaCol in dt.Columns)\r
- Console.WriteLine(schemaCol.ColumnName + \r
- " = " + \r
- schemaRow[schemaCol]);\r
- Console.WriteLine();\r
- }\r
+<pre>\r
+ <configuration>\r
+ <configSections>\r
+ <section name="mono.data"\r
+ type="Mono.Data.MultiplexorSectionHandler,Mono.Data" />\r
+ ....\r
+ </configSections>\r
+ ...\r
+ <sectionGroup name="mono.data">\r
+ <multiplexor>\r
+ <add provider="PostgreSQL" type="Mono.Data.PostgreSQLClient,Mono.Data"\r
+ validate="false" parameters="USER=xxx;HOST=127.0.0.1;DBNAME=xxx" />\r
+ </multiplexor>\r
+ </sectionGroup>\r
+ </configuration>\r
+</pre>\r
\r
- int nRows = 0;\r
- string output, metadataValue, dataValue;\r
- // Read and display the rows\r
- Console.WriteLine("Gonna do a Read() now...");\r
- while(rdr.Read()) {\r
- Console.WriteLine(" Row " + nRows + ": ");\r
- \r
- for(c = 0; c < rdr.FieldCount; c++) {\r
- // column meta data \r
- DataRow dr = dt.Rows[c];\r
- metadataValue = \r
- " Col " + \r
- c + ": " + \r
- dr["ColumnName"];\r
- \r
- // column data\r
- if(rdr.IsDBNull(c) == true)\r
- dataValue = " is NULL";\r
- else\r
- dataValue = \r
- ": " + \r
- rdr.GetValue(c);\r
- \r
- // display column meta data and data\r
- output = metadataValue + dataValue; \r
- Console.WriteLine(output);\r
- }\r
- nRows++;\r
- }\r
- Console.WriteLine(" Total Rows: " + \r
- nRows);\r
- } \r
- } while(rdr.NextResult());\r
- Console.WriteLine("Total Result sets: " + results);\r
- \r
- rdr.Close();\r
- }
-
-</pre>
-
- <p>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.
-
- <p>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.
-
- <p>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.
-
- <p>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.
-
- <p>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.
-
-<pre>
- static string GetDatabaseServerVersion (SqlConnection cnc)
- {
- SqlCommand cmd = cnc.CreateCommand ();
- string data;
-
- cmd.CommandType = CommandType.StoredProcedure;
- cmd.CommandText = "version";
-
- data = (string) cmd.ExecuteScalar ();
+ <p>validate="false" tells MultiplexorSectionHandler not to load the Type\r
+ until an instance is required. You can add more attributes or whatever\r
+ inside <multiplexor> as long as MultiplexorSectionHandler parses it.\r
+\r
+<pre> \r
+ public class MultiplexorSectionHandler :\r
+ IConfigurationSectionHandler\r
+ {\r
+ public object Create (object parent, object configContext,\r
+ XmlNode section)\r
+ {\r
+ Multiplexor mp;\r
+ // Here you get the ChildNodes and set up a Multiplexor\r
+ // instance that will hold the information needed to create \r
+ // instances of each provider. Only one instance will be \r
+ // created by the config system.\r
+ return mp;\r
+ }\r
+ }\r
+</pre>\r
+\r
+ <p>This way, if our providers works with MS, the user can test them in both\r
+ MS and mono by just adding a few lines to machine.config (i still have\r
+ to upload a default machine.config file for mono). And this is the .NET\r
+ way of doing it (of course, if you prefer, you can use the other config\r
+ file. I just wanted to make people aware of this feature that now works\r
+ on mono).\r
- return data;
- }
-</pre>
-
- <p>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.
-
- <p>A lot of functionality in System.Data is missing, but the
- infrastructure is starting to come together.
-
- <p>A lot of Exceptions need to be thrown for various exceptions. However,
- SqlException, SqlErrorCollection, and SqlError have been partially
- implemented.
- <p>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:
-
-<pre>
- 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);
-
- if (dataSet != null) {
- foreach (DataRow row in dataSet.Tables["Table"].Rows)
- Console.WriteLine("tablename: " + row["tablename"]);
- }
-</pre>
-
- <p>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 <a href="mailto:gonzalo@ximian.com">Gonzalo Paniagua Javier</a>
-
- <P>Need to add XML support in System.Data. This involves working on
- the classes: DataSet and XmlDataDocument and the ExecuteXmlReader() in SqlCommand.
+ <p><b>Building System.Data</b>
<p>The System.Data.dll gets built with the rest of the class library.
To compile the System.Data.dll assembly separately, you need:
</pre>
</ul>
-* Testing
-
- <p>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.
-
- <p>Why? Because it is free software, has a client
- library that is easy to use, PostgreSQL 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.
-
- <p>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).
-
- <p>The System.Data tests use this connection string to connect
- to the PostgreSQL database named "test" at host "localhost" as
- user "postgres".
-
-<pre>
-"host=localhost;dbname=test;user=postgres"
-</pre>
-
- <p>Installation instructions for PostgreSQL DBMS:
-
- <b>On Unix</b>
-
- <ul>
- * Read the PostgreSQL Installation Instructions
- at \usr\doc\postgresql-x.x.x\html\installation.html
-
- * Depending on your Unix system,
- PostgreSQL maybe already installed, a database user 'postgres' created,
- a linux user 'postgres' created and initdb ran. Or maybe not.
-
-<pre>
- su\r
- adduser postgres\r
- mkdir /usr/local/pgsql/data\r
- chown postgres /usr/local/pgsql/data\r
- su - postgres\r
- initdb -D /usr/local/pgsql/data\r
- postmaster -i -D /usr/local/pgsql/data\r
- createdb test\r
- psql test
-</pre>
-
- * 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:
-
-<pre>
-psql test
-create user postgres with password 'fun2db';
-</pre>
-
- * 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.
-
-<pre>
- # TYPE DATABASE IP_ADDRESS MASK AUTH_TYPE
-
- local all trust
- host all 127.0.0.1 255.255.255.255 md5
-</pre>
-
- * If you can not find your PostgreSQL documentation locally or you
- did not install it, then you
- can get it <a href="http://www.postgresql.org/idocs/">here</a>.
-
- </ul>
-
- <b>On Windows</b>
-
- <ul>
- * Use the <a href="http://www.cygwin.com/">Cygwin</a> installer to
- install the PostgreSQL DBMS. It is
- found in the database category.
-
- * <p>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.
-
- * <p>The -x.x in postgres-x.x is the version of your PostgreSQL DBMS.
-
- * <p>Once Cygwin has installed the PostgreSQL DBMS on your computer,
- read the file FAQ_MSWIN which is available
- in /usr/doc/postgres-x.x
-
- * <p>Important notes from this file are:
-
- <ul>
- <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.
-
- <p>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.
-
- <p><b>3.</b> The Cygwin bin directory has to be placed in
- the path before the Windows program directories,
- for example, C:\cygwin\bin
-
- <p><b>My own note.</b> 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:
-
- <p>
-<pre>
-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;
-</pre>
-
- <p><b>4.</b> Start the ipc-daemon that came with the cygipc
- package. There
- are two ways to do this: run it from the command line as:
-
- <p>
-<pre>
-ipc-daemon &
-</pre>
- <p>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.
-
- <p>To install ipc-daemon as a service,
- you just have to run
-
- <p>
-<pre>
-ipc-daemon --install-as-service' (--remove-as-service)
-</pre>
-
- <p>and then run
-
-<pre>
-net start ipc-daemon
-</pre>
- </ul>
-
- <p>Read the installation.html file
- at /usr/doc/postgresql-x.x/html/installation.html
-
- <p>You will see in this file that you will need to
- run the following commands:
-
- <p>
-<pre>
-mkdir /usr/local/pgsql/data\r
-initdb -D /usr/local/pgsql/data\r
-postmaster -D /usr/local/pgsql/data\r
-createdb test\r
-psql test
-</pre>
-
- <p>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.
-
- <p>psql is a command-line PostgreSQL client tool to
- enter and run SQL commands and queries.
-
- <p>If there is no database user named postgres, create a user named
- postgres with the following SQL command in the client tool psql:
-
- <p>
-<pre>
-psql test
-create user postgres with password 'fun2db';
-</pre>
- <p>The only reason I say this is so you can easily use the System.Data tests
- without having to change the database, userid, etc.
- </ul>
-
- <p>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.
-
- <p>To use it to test System.Data, you
- modify the file to your PostgreSQL database
- connection requirements:
-
- <p>
- <ul>
- <li><b>dbname</b> database, ie., test</li>
- <li><b>host</b> hostname of the PostgreSQL DBMS Server to connect to, ie., localhost</li>
- <li><b>user</b> username, ie., someuser</li>
- <li><b>password</b> password, ie., mypass1234</li>
- </ul>
-
- <p>The connection string is in OLE-DB connection string format. Internally,
- SqlConnection converts this to the PostgreSQL connection string format.
-
- <p>
-<pre>
- OLE-DB: "host=localhost;dbname=test;user=joe;password=smoe"
-PostgreSQL: "host=localhost dbname=test user=joe password=smoe"
-</pre>
-
- <p>
- Note that OLE-DB includes the semicolons while PostgreSQL's connection
- string does not.
-
- <p>
- To compile the PostgresTest.cs program, do:
-
- <p>
-<pre>
-mcs PostgresTest.cs -r System.Data.dll
-</pre>
-
- <p>
- To run using mint, do:
-
- <p>
-<pre>
-mint PostgresTest.exe
-</pre>
-
- <p>
- To run using mono, do:
-<pre>
-mono PostgresTest.exe
-</pre>
-
- <p>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.
-
-<p>
-<pre>
-\r
-danmorg@DANPC ~/mono/mcs/class/System.Data/Test\r
-$ mcs PostgresTest.cs -r System.Data.dll\r
-\r
-danmorg@DANPC ~/mono/mcs/class/System.Data/Test\r
-$ mono PostgresTest.exe\r
- Postgres provider specific tests...\r
-\r
- Drop table:\r
-Error (don't worry about this one)SqlError:PGRES_FATAL_ERROR ERROR: table "mono\r
-_postgres_test" does not exist\r
- <Stack Trace>\r
-\r
- Create table with all supported types:\r
-OK\r
- Insert values for all known types:\r
-OK\r
- Update values:\r
-OK\r
- Insert values for all known types:\r
-OK\r
-Aggregate: count(*)\r
-Agg Result: 2\r
-Aggregate: min(text_value)\r
-Agg Result: This is a text\r
-Aggregate: max(int4_value)\r
-Agg Result: 1048000\r
-Aggregate: sum(int4_value)\r
-Agg Result: 1048003\r
- Select values from the database:\r
-Result is from a SELECT SQL Query. Records Affected: -1\r
-Result Set 1...\r
- Total Columns: 28\r
-ColumnName = boolean_value\r
-ColumnOrdinal = 1\r
-ColumnSize = 1\r
-NumericPrecision = 0\r
-NumericScale = 0\r
-IsUnique = False\r
-IsKey =\r
-BaseCatalogName =\r
-BaseColumnName = boolean_value\r
-BaseSchemaName =\r
-BaseTableName =\r
-DataType = System.Boolean\r
-AllowDBNull = False\r
-ProviderType = 16\r
-IsAliased = False\r
-IsExpression = False\r
-IsIdentity = False\r
-IsAutoIncrement = False\r
-IsRowVersion = False\r
-IsHidden = False\r
-IsLong = False\r
-IsReadOnly = False\r
-\r
- ...\r
-\r
- ColumnName = null_timestamp_value\r
- ColumnOrdinal = 28\r
- ColumnSize = 8\r
- NumericPrecision = 0\r
- NumericScale = 0\r
- IsUnique = False\r
- IsKey =\r
- BaseCatalogName =\r
- BaseColumnName = null_timestamp_value\r
- BaseSchemaName =\r
- BaseTableName =\r
- DataType = System.DateTime\r
- AllowDBNull = False\r
- ProviderType = 1184\r
- IsAliased = False\r
- IsExpression = False\r
- IsIdentity = False\r
- IsAutoIncrement = False\r
- IsRowVersion = False\r
- IsHidden = False\r
- IsLong = False\r
- IsReadOnly = False\r
-\r
- Gonna do a Read() now...\r
- Row 0:\r
- Col 0: boolean_value: False\r
- Col 1: int2_value: 5\r
- Col 2: int4_value: 3\r
- Col 3: bigint_value: 9\r
- Col 4: float_value: 3.141590\r
- Col 5: double_value: 3.14159\r
- Col 6: numeric_value: 123456789012.345\r
- Col 7: char_value: Mono.Data!\r
- Col 8: varchar_value: It was not me!\r
- Col 9: text_value: We got data!\r
- Col 10: point_value: (1,0)\r
- Col 11: time_value: 01/01/1 21:13:14\r
- Col 12: date_value: 02/29/2000 00:00:00\r
- Col 13: timestamp_value: 02/29/2004 14:00:11\r
- Col 14: null_boolean_value is NULL\r
- Col 15: null_int2_value is NULL\r
- Col 16: null_int4_value is NULL\r
- Col 17: null_bigint_value is NULL\r
- Col 18: null_float_value is NULL\r
- Col 19: null_double_value is NULL\r
- Col 20: null_numeric_value is NULL\r
- Col 21: null_char_value is NULL\r
- Col 22: null_varchar_value is NULL\r
- Col 23: null_text_value is NULL\r
- Col 24: null_point_value is NULL\r
- Col 25: null_time_value is NULL\r
- Col 26: null_date_value is NULL\r
- Col 27: null_timestamp_value is NULL\r
- Row 1:\r
- Col 0: boolean_value: True\r
- Col 1: int2_value: -22\r
- Col 2: int4_value: 1048000\r
- Col 3: bigint_value: 123456789012345\r
- Col 4: float_value: 3.141590\r
- Col 5: double_value: 3.14159\r
- Col 6: numeric_value: 123456789012.345\r
- Col 7: char_value: This is a char\r
- Col 8: varchar_value: This is a varchar\r
- Col 9: text_value: This is a text\r
- Col 10: point_value: (1,0)\r
- Col 11: time_value: 01/01/1 21:13:14\r
- Col 12: date_value: 02/29/2000 00:00:00\r
- Col 13: timestamp_value: 02/29/2004 14:00:11\r
- Col 14: null_boolean_value is NULL\r
- Col 15: null_int2_value is NULL\r
- Col 16: null_int4_value is NULL\r
- Col 17: null_bigint_value is NULL\r
- Col 18: null_float_value is NULL\r
- Col 19: null_double_value is NULL\r
- Col 20: null_numeric_value is NULL\r
- Col 21: null_char_value is NULL\r
- Col 22: null_varchar_value is NULL\r
- Col 23: null_text_value is NULL\r
- Col 24: null_point_value is NULL\r
- Col 25: null_time_value is NULL\r
- Col 26: null_date_value is NULL\r
- Col 27: null_timestamp_value is NULL\r
- Total Rows Retrieved: 2\r
- Total Result sets: 1\r
- Call ExecuteReader with a SQL Command. (Not INSERT,UPDATE,DELETE\r
- ).\r
- Result is from a SQL Command not (INSERT,UPDATE,DELETE). Records Affected: -1\r
- Total Result sets: 0\r
- Call ExecuteReader with a SQL Command. (Is INSERT,UPDATE,DELETE)\r
- .\r
- Result is from a SQL Command (INSERT,UPDATE,DELETE). Records Affected: 1\r
- Total Result sets: 0\r
- Calling stored procedure version()\r
- Result: PostgreSQL 7.2.1 on i686-pc-cygwin, compiled by GCC 2.95.3-5\r
- Database Server Version: PostgreSQL 7.2.1 on i686-pc-cygwin, compiled by GCC 2.9\r
- 5.3-5\r
- Clean up...\r
- Drop table...\r
- OK\r
- RESULT: 0\r
-\r
-</pre>\r
-\r