RE: Where can I get the New DB2 .NET provider?

["Walden H. Leverich" <WaldenL@xxxxxxxxxxxxxxx>]

>I have a questions for whomever said...

That was me. 

>The questions is what are the "ins and outs" of that statement? 

Well, if you already have an application using .NET and the .NET OLE/DB
driver talking to IBMDA400 then this should look familiar:

using System;
using System.Data;
using System.Data.OleDb;
using System.Configuration;     

string sCS = ConfigurationSettings.AppSettings["ConnectionString"];
using (OleDbConnection cn = new OleDbConnection(sCS))
{
        OleDbCommand cm = cn.CreateCommand();
        cm.CommandText = "select this, that from there";
        OleDbDataReader dr = cm.ExecuteReader();
        while (dr.Read())
        {
                string sThis = dr["this"].ToString();
                string sThat = dr["That"].ToString();
        }
}

The nice thing about this code is that if the ConnectionString setting
in my Web.Config contains "Provider=IBMDA400;Data Source=myAS400;User
ID..." I connect to the iSeries and run. However, if I simply change the
connection string to "Provider=sqloledb;Data Source=mySQLServer;User
ID..." I connect to my SQLServer and the same code runs. 

In the new .NET world (and this isn't just IBM, it's MS that's pushing
this) the code would look like:

using System;
using System.Data;
using System.Configuration;     
using IBM.Data.DB2.iSeries;

string sCS = ConfigurationSettings.AppSettings["ConnectionString"];
using(iDB2Connection cn = new iDB2Connection(sCS))
{
        iDB2Command cm = cn.CreateCommand();
        cm.CommandText = "select this, that from there";
        iDB2DataReader dr = cm.ExecuteReader();
        while (dr.Read())
        {
                string sThis = dr["this"].ToString();
                string sThat = dr["That"].ToString();
        }
}

So you can imaging that with all those iSeries specific objects running
around, it's not a simple change to move from DB2 to SQLServer, for
example.

However, sitting here typing this e-mail I'm thinking of something. All
these database specific objects implement common interfaces. That is,
iDB2Connection, SqlConnection, OleDbConnection, etc. all implement the
IDbConnection interface. So, the following is syntatically valid code:

string sCS = ConfigurationSettings.AppSettings["ConnectionString"];
using(IDbConnection cn = new iDB2Connection(sCS))
{
        IDbCommand cm = cn.CreateCommand();
        cm.CommandText = "select this, that from there";
        IDataReader dr = cm.ExecuteReader();
        while (dr.Read())
        {
                string sThis = dr["this"].ToString();
                string sThat = dr["That"].ToString();
        }
}

And if I replace the "new iDB2Connection(sCS)" part with a call to a
class factory that returned whatever type of connection I needed, I may
be able to work with provider-specific objects in a generic way.
However, while I complied the above code to see if it was syntatically
correct, I've _NOT_ tried to use it at all. I may have all sorts of
problems actually trying to assign different connection objects to the
generic interface.

So to summarize: What are the in's and out's? Well, you may (should)
gain performance, and you will be in only managed code if you use the
native providers (The OLE/DB provider needs to hop out of managed code
to call OLE/DB) so that's the "in". The "out" is that you're _really_
hooking yourself to a provider. Now, this new idea of using the
interfaces definitely is worth investigating, since it would eliminate
the patalogical coupling between my data access code and my back end
database.

-Walden

------------
Walden H Leverich III
President & CEO
Tech Software
(516) 627-3800 x11
WaldenL@xxxxxxxxxxxxxxx
http://www.TechSoftInc.com 

Quiquid latine dictum sit altum viditur.
(Whatever is said in Latin seems profound.)