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

I believe an implementation of Walden's idea is out there and ready to
be downloaded.  Some time ago, we came across a Microsoft developed data
access layer for SQL Server. Using the notation outlined below it was a
simple matter to adapt it for DB2.  What you wind up with is standard
method calls for data access regardless of the database.  You pass it
SQL, you get a data set, data reader or whatever object you want back.
All of the database specific code is in the underlying object. Since we
have both SQL Server and iSeries in our environment I have 2 different
classes, and I can switch between them by changing the using clauses. 

Using this, we have been able to switch from OLEDB to ODBC and now to
the native DB2 provider to get at our iSeries with nearly no application
code changes and relatively simple find and replaces in the object
itself.

The link to get to the code is here:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/h
tml/daab-rm.asp  

Regards,

Jim Reinardy
Director - IS
Badger Meter, Inc.

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Walden H. Leverich
Sent: Wednesday, November 17, 2004 9:51 AM
To: Midrange Systems Technical Discussion
Subject: RE: Where can I get the New DB2 .NET provider?

>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.)

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.