Reduce number of places driver-specific class names used?

[Randall Parker]

I'm just learning ADO.Net having previously used DAO, ADO, JDBC, ODBC, and other
database drivers.

One thing odd strikes me from the source code examples of driver usage that I'm
looking at: A lot of class names that are specific to each driver get used in the
examples.

For example for MySQL using either MySQL's own driver or the CoreLabs driver one does
something like this:

using System;
using System.Data;
using System.Diagnostics;
using MySql.Data.MySqlClient;

// I'm leaving out some class declaration stuff here...

string DataSource = "localhost";
string Database = "test";
string UserID = "root";
string Password = "root";

string MyConnString = Data Source=" + DataSource +
";Database=" + Database +
";User ID=" + UserID +
";Password=" + Password;

MySqlConnection dbConn = new MySqlConnection(MyConString);
dbConn.Open();
MySqlCommand dbCmd = new MySqlCommand();
MySqlDataAdapter dbAdapter = new MySqlDataAdapter();


Note that not only is one driver-specific for creating the connection but also for
creating the SQL Command object and the adapter object.

To me it would make more sense to have syntax where one did something like:
MySqlConnection dbConn = new MySqlConnection(MyConString);
dbConn.Open();
BaseCommand dbCmd = dbConn.GetCommand();
BaseAdapter dbAdapter = dbConn.GetAdapter();

In this fictitious example I use "BaseCommand" and "BaseAdapter" as base classes and
the connection object dbConn knows how to get driver-specific derived classes in a
way that avoids mentioning those class names in the source code.

So is there some way to do that sort of thing? Or does one have to mention
driver-specific class names a lot more?

 

[W.G. Ryan - MVP]

Randall, the short answer to is there a base class is technically "no" but
there are common interfaces. For instance, Data Adapters all implement
IDBDataAdatper, Connections all implement IDBConnection Commands all
implement IDBCommand. As such, you can use a factory pattern ie
IDbConnection cn = new Sqlconnection();. There was a guy here last year who
was really into the 'generic' approach, I believe his name was Michael Lang
that had a Sourceforge project creating provider agnostic library. The
problem is that there are features in Sql Server, Oracle etc that aren't
available in other providers. So yes, the generic approach does make
implementation simpler to code, but it does so at the expense of performance
and functionality. Many consider using a less specific provider a no-no.
Typically you are working with x number of back ends so the variation here
is pretty easy to deal with, and like I said, you can use a factory pattern
to implement what you need fairly easily.

Also, in the 2.0 Framework there's a ProviderFactory that does much of this
for you. If you are interested, pick up Sahil Malik's new book on ADO.NET
2.0 for a discussion on it.

So essentially, you don't have one base class that you can deal with, but
you can use the interfaces to get you where you need to go and in the 2.0
framework, there are some classes that will do what you want.

HTH,

Bill

 

[Cor Ligthert [MVP]]

Randall,

OleDb covers them all, however gives less performance. An approach for you
can be to create a class (most probably less work than you think), that
covers the main databases and include than as well OleDb. For OleDb is only
the connection string from every type of database different.

I hope this helps,

Cor

 

[W.G. Ryan - MVP]

Cor - the problem with OleDb is that you can't really write aganostic code
across the board with it. Admittedly most RDBMS systems that are available
today are OleDb compliant, but even then, the syntax is markedly different.
take for example querying an Excel sheet vs a Sql Server database. The
specific snytax of the command text is markedly different so you can't just
write code in one way that will handle every scenario. The ProviderFactory
in the 2.0 Framework gets you a lot closer to being there, but for now,
there are more than a few problems. For instance, if you use a reserved
word in Access for a column name (which is not a good practice in any case),
you'll probably have a problem. Access has many of its own functions that
are different from other providers (i.e. IsNull in access returns true or
false, not the second value in case the first one is null). In addition,
more than the performance, there are syntax differences in each RDBMS flavor
that aren't supported, Sql Server doesn't supprot Decode, Oracle doesn't
have a Coalesce function etc. Join syntax as well can be different as well
(i.e. += vs join) although this isn't as big of an issue b/c join is still
supported.

 

[W.G. Ryan - MVP]

Randall - I found the project by Lang that I mentioned. Here's the book name
that he used as his source of inspiration ADO.NET Programming, Arlen
Feldman, http://shrinkster.com/8qu ISBN 1-930110-29-4

Here are the links though to the project that you may find useful
http://sourceforge.net/projects/colcodegen (simple code generator)
http://sourceforge.net/projects/dbobjecter (database app code generator)
http://sourceforge.net/projects/genadonet ("generic" ADO.NET)

Also you may want to check out http://developer.com/db/article.php/3494396
or Sahil Malik' blog or the stuff at DataWorks, this has been discussed in
respect to the new ADo.NET 2.0 framework.

 

[Cor Ligthert [MVP]]

Bill,

The most purpose of my message was. Don't try to get everything with special
providers as you don't use them. For those which you still not know or not
yet use you can include OleDb.

The second part of it, "was don't be afraid to build a class witch can
handle more providers, it is probably easier than you think".

For the rest thanks, it gave me some extra to think about.

In my opinion have you than yourself the choice in this kind of classes the
procedures or whatever that are common and not the end of the possibilites.
(I assume that this was as well as context included in your message).

Cor

 

[W.G. Ryan - MVP]

I definitely agree that creating a class, particularly if you use a Factory
pattern, is quite simple and one should not be afraid of it. However in the
first case, there's a trade off if you shun specific providers and in my
experience, the cost of using them is quite high b/c you don't know in
advance necessarily if your code will work or not, and if it does, if it
will work as expected. The case of IsNull is the most glaring one that I
can think of but it's not the only one. i think the provider factory in
2.0 fixes some of this but for other reasons. Since we don't often flip
back end db systems I have a strong bias toward using the specific
providers - but that's just a personal preference.