[mono.git] / web / ado-net

* 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>, 
        <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

        The current plan to implement ADO.NET is as follows:

        <b>Step 1:</b><a href="http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataOleDb.asp?frame=true">System.Data.OleDb</a> Provider:
                
        <ul>
                * <p>On Unix systems: System.Data.OleDb uses the
                <a href="http://www.gnome-db.org/">LibGDA</a>
                 engine.   This provider was started by Rodrigo Moya and Tim Coleman.

                  <p>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.  glib2 and libxml2 both work on Linux
                  and Windows.
                  
                  <p>There is work under way to get libgda working under 
                  Windows using Cygwin by the GNOME-DB developers, but we have not
                  been successful yet.
                  
                  <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 <a href="http://www.microsoft.com/office/access/default.asp">MS Access</a> 
                  (via <a href="http://mdbtools.sourceforge.net/">MDB Tools</a>).
                  
        </ul>

        <b>Step 2:</b> <a href="http://msdn.microsoft.com/library/en-us/cpref/html/frlrfsystemdatasqlclient.asp?frame=true">System.Data.SqlClient</a> Provider:

        <ul>
                * <p><b>System.Data.SqlClient</b> will become a
                  provider for Microsoft SQL Server, both on Windows and
                  Linux, to be compatible with applications written
                  for the Microsoft .NET Development Framework.

                * <p>The <a href="http://wwww.go-mono.com/tds-providers.html">SqlClient and SybaseClient Design</a>
                  states that System.Data.SqlClient will be written completely in
                  C#.  FreeTDS and jTDS projects will be used as references on how to
                  implement the TDS protocol.  SqlClient will use common internal code with SybaseClient
                  and TdsClient.
          
                * <p><a href="http://www.freetds.org/">FreeTDS</a> 
                  is a collection of free native C libraries: tds, dblib, 
                  and ctlib for accessing TDS protocol databases like
                  Microsoft SQL Server and Sybase.
                  
                * <p><a href="http://jtds.sf.net/">jTDS</a> 
                  is a 100% Java JDBC Provider implementing the TDS protocol
                  which allows data access to Microsoft SQL Server and
                  Sybase databases.       
                  
        </ul>

        <b>Step 3:</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>
                * An ADO.NET Provider for ODBC has been created in
                  System.Data.Odbc by Brian Ritchie for those using ODBC.
                  
                * <p>On Unix, the provider uses <a href="http://www.unixodbc.org/">unixODBC</a> 
                  for its ODBC connectivity.  The ODBC provider uses the libodbc.dll that
                  comes with unixODBC.  
                  
                  <p>There is an alternative to unixODBC, <a href="http://www.iodbc.org/">iODBC</a>, 
                  but it has not been tested with the ODBC provider.
                
                * On Windows, the provider 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 Windows, get the latest MDAC 
                  from Microsoft.  
                  
                * <p>The mapping between these native shared libraries is handled 
                  in etc/mono/config
                  
                  <p>In mono cvs, the file is mono/data/config.in                         
                  
                  <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, Ingres, Sap DB, and others.
                  
        </ul>

        <b>Step 4:</b> Other ADO.NET providers:

        <ul>
                * <p>The idea in Microsoft .NET System.Data is to have
                  a managed provider for each supported DBMS.
                  
                  <p>System.Data.SqlClient for Microsoft SQL Server.  See the
                  Mono.Data.TdsClient provider.
                  
                  <p>Mono.Data.TdsClient for older Microsoft SQL Server
                  and Sybase databases.  This has been started by Tim Coleman
                  and will provide the functionality for System.Data.SqlClient
                  and Mono.Data.SybaseClient.
                  
                  <p>Mono.Data.SybaseClient for Sybase SQL Server.  See the
                  Mono.Data.TdsClient provider.
                          
                  <p>Mono.Data.PostgreSqlClient for PostgreSQL.  Started by
                  Rodrigo Moya, Daniel Morgan, and Tim Coleman.  This provider 
                  uses the PostgreSQL C API (libpq.so on Linux and pq.dll on
                  Windows).
          
          <p>Mono.Data.MySql for MySQL client/server database.  Started by
          Daniel Morgan using C# Bindings to MySQL from Brad Meril.  
          This provider uses the MySQL C API (libmysqlclient.so on
          Linux and libmySQL.dll on Windows).
          
          <p>Mono.Data.SqliteClient for the SQL Lite database.  Started by
          Vladimir Vukicevic.  This provider uses 
          <a href"http://www.hwaci.com/sw/sqlite/download.html">SQL Lite</a> database engine.
          It is used in mPhoto.
          
          <p>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.
                                  
        </ul>

        <b>Step 5:</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/tools/SqlSharp/SqlSharpCli.cs
                  It is written in C# and runs on Mono and .NET

                * <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><b>ADO.NET Configuration command-line and GUI tools</b>.  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>The<b>PostgreSQL</p> provider as Mono.Data.PostgreSqlClient 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, and SQL errors and exceptions for PostgreSQL
        are working. 
        
        <p>The <b>PostgreSQL</b> provider
        has been moved from System.Data.SqlClient to Mono.Data.PostgreSqlClient
        so System.Data.SqlClient can be used for the Microsoft SQL Server provider.
                
        <p><b>System.Data.OleDb</b> is working with libgda (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) and schema support.  We need help building libgda on Windows though.  libgda
        builds find on linux though.  An inital implementation of GetSchemaTable() for
        the OleDbDataReader has been checked into cvs.  GetSchemaTable() isn't correct for OleDb,
        but the foundation is there.
        
        <p>Tim Coleman has just started on the <b>TDS</b> protocol support neccessary for the
        <b>SqlClient, SybaseClient, and TdsClient</b> providers. The provider will be 
        implemented in pure managed C#.  We are using FreeTDS and jTDS as references.  
        The TDS provider will exist     in Mono.Data.TdsClient.  
        The System.Data.SqlClient and Mono.Data.SybaseClient will
        be based off of Mono.Data.TdsClient provider.  Currently, we are providing
        the foundation for the TdsClient provider.  Next step is to implement the TDS
        protocol in C#.
        
        <p><b>ODBC</b> support has been started by Brian Ritchie.\r
        It uses unixODBC on Linux and uses native ODBC support on Windows.\r
        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 tested on Windows XP against a Microsoft SQL Server 2000 database.\r
        
        <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.   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 <a href"http://www.hwaci.com/sw/sqlite/download.html">SQL Lite</a>
        database.  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><b>DataSet, DataAdaptor, DataTable, DataRelation, DataRow, DataColumn,
        DataColumnCollection, DataRowCollection, and others</b> need more work.  There are
        many classes that are just stubs and need to be implemented.  
                
        <p><b>Integration with ASP.NET</b> has not been 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>
        
        <p><b>Integration with Windows.Forms</b> has not been 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> 
        has not been 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> has not been 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#</b> has not been started.  It is 
        included in the GTK# project, GDA# 
        does not require GTK+ nor GNOME.  GDA# is C# bindings 
        to <a href="http://www.gnome-db.org/">GDA</a> which
        is an ADO/OLE-DB like layer for Linux provided as a C library.  Classes
        that would bind data between the ADO.NET data model and the GDA data model will
        need to be created.  Rodrigo Moya is the best person to contact about this.
        
        <p><b>Integration with GnomeDb#</b> has not been started.  It is
        included in the GTK# project and uses GDA# for its data access model.
        Creating classes to bind data between the data model in ADO.NET 
        and the data model that exists in GDA# and GnomeDb# will
        need to be started.  GnomeDb# is C# bindings to
        <a href="http://www.gnome-db.org/">GnomeDb</a> which uses 
        <a href="http://www.gnome-db.org/">GDA</a> as its data access layer.  GnomeDb
        is a C API in a library that contains
        gtk+ widgets (GUI controls) for data access.  There are some nifty widgets
        (GUI controls) that can be used, such as, GnomeDbGrid, GnomeDbBrowswer, 
        GnomeDbSqlEditor, GnomeDbList, GnomeDbLogin, GnomeDbReportEditor, GnomeDbTableEditor, 
        GnomeDbCombo, and GnomeDbForm.  Rodrigo Moya is the best person to contact.
                
        <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
        <p>After some work done in System.Configuration, you can now do something\r
    like:   \r
\r
        <p>\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
    \r
 and in the machine.config file:\r
\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
\r
 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
    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
\r
 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
</pre>
        
        <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:

        <b>On Unix</b>

        <ul>
                * update your mono sources. Be sure you have latest mcs.exe
                  and .dll's, since there have been many fixes needed for
                  compilation on Linux.

                * compile System.Data.dll:
<pre>
 cd mcs/class/System.Data<br>
 mcs --target library -o System.Data.dll @list
</pre>
        </ul>

        <b>On Windows</b>

        <ul>
                * update your mono sources. Be sure you have latest mcs.exe
                  and .dll's.  You can use the same method as Linux, 
                  or you can use NAnt.

                * To use NAnt:

<pre>
 cd mcs/class/System.Data
 ../../nant/NAnt.exe
</pref>
                        
                * This will automatically copy the System.Data.dll to Test.
                  If you need to do a clean for the System.Data.dll assembly,<br><br>

<pre>
 cd mcs/class/System.Data
 ../../nant/NAnt.exe clean
 
</pre>
        </ul>