Provider independant Data Access

[Philipp Brune]

Hello Newsgroup,

what I want to achieve is to build my own set of
DbCommand/DbConnection/DbDataAdapter/etc. classes
that wrap a given .NET Data Provider like
MySql, Firebird, SqlClient, OleDbClient, etc
and provide the following functionalities on top :

- @ as parameter identifier for parameters in every CommandText
- Ordering of parameters by their occurence in the
CommandText ( I noticed, OleDbClient does not take
the parameter names but uses the order they were appended)
- mapping of BCL types to DbTypes in the parameter (Set BCL type sets
corresponding DbType)
- Refetching identity values from the Db after insert through
DbDataAdapter.
- Fixing schema after reading through DbDataAdapter. For example in
our scenario every column in a Firebird db, which has a generator
assigned must be an AutoIncrement Column in the DataTable. The
Firebird.NET Provider does not see this.
- No SQL Processing, correct SQL for Provider will be generated by a
higher layer.
- Own DbProviderFactory implementation for this.

The first three goals can be easily achieved by deriving from the Db***
classes and redirecting calls to an instance of the underlying provider
with doing the given steps first. Probably someone knows a more elegant
way of doing so, huh ?!

The problem is the DbDataAdapter. I cannot see how to derive my own
DbDataAdapter and easily redirect calls to an underlying SqlDataAdapter
or DbDataAdapter of any other kind since some important methods are
inaccessible due to protection level. Externally registering to the
events of the DbDataAdapter is not suitable, because there is no way
to get notified when the adapter fetched a schema, so i cannot apply
my own schema modifications automagically. Directly implementing the
IDbDataAdapter interface is not a bet too, because my DbProviderFactory
must return a DbDataAdapter not an IDbDataAdapter ( why is this so ? ).

So, any expert, how can i

A ) Modify a DataTable´s schema after it has been fetched from the Db
B ) Postprocess inserting of rows.

with a DbDataAdapter and without any additional Method calls for the
client ? Any clues ?

Thanks a lot in advance

Philipp

 

[Cor Ligthert [MVP]]

Philipp,

Do you have a larger development team than Microsoft in Redmond, because I
am sure that if it was easy what you want that an answer on that could be
given in a newsgroup, than they had include it in their on new
DBfactoryprovider class.

Just my thought,

Cor

 

[Philipp Brune]

Cor,

Thank you for your immediate reply.

no i don´t, i am a student and students have a lot of spare-time per
definition ;-)

No, it is not that much work at all because i basically use the
functionality of the underlying .NET data providers. The problem
is to integrate the mentioned additional functionality but hiding
it from the client of my Db* classes, so the can use the straightforward
ado.NET interface without any additional method calls. Basically it is
around some additional lines of code in the FillSchema method and the
RowUpdating/RowUpdated events.

Philipp

 

[Miha Markic [MVP C#]]

Philipp,

As Cor suggested use factory approach supported in .net 2.
Or go with an ORM tool (I always suggest LLBLGenPro) that does all that job
for you.

 

[Philipp Brune]

Miha,

thank you for the immediate reply.

Well, i´m not sure i really understand this. It is a quite simple task,
just some text replacement before command execution ( @ => ? p.E.) and
some lines of code after Schema fetch

if (hasGenerator(table, column))
{
dataTable.Columns[column].AutoIncrement = true;
}

and after Insertion

....
row[column] = dbLayer.FetchInsertedIdentity(table, column);
row.AcceptChanges();
....

It is just about using existing .NET data providers, extending their
functionality as mentioned and again providing an ado.NET interface
to the clients. This cannot be so complex, can it ? It is really just
about some lines of code to add, no complex sql dialect manipulation
or ORM nescessary.

Philipp

 

[Mark Rae]

It is just about using existing .NET data providers, extending their
functionality as mentioned and again providing an ado.NET interface
to the clients. This cannot be so complex, can it ? It is really just
about some lines of code to add, no complex sql dialect manipulation
or ORM nescessary.

If it's so easy, I'm sure you'll have it completed by the end of the day...
;-)

 

[Miha Markic [MVP C#]]

If it's so easy, I'm sure you'll have it completed by the end of the
day... ;-)

Agreed, let us know tomorrow

 

[Massimo]

what I want to achieve is to build my own set of
DbCommand/DbConnection/DbDataAdapter/etc. classes
that wrap a given .NET Data Provider like
MySql, Firebird, SqlClient, OleDbClient, etc
and provide the following functionalities on top :

- @ as parameter identifier for parameters in every CommandText

I'm also trying to overcome this awful limit of the standard ADO.NET 2.0
classes; this alone, I think, is the biggest problem of the ADO.NET 2.0
library... and it's even worse because it could have been easily fixed by
having any data provider implement a GetParameterDelimiter() method and
using a generic delimiter in the DbCommand class!
I really don't know why Microsoft didn't handle this better :-/

I also have another couple suggestions for MS guys, maybe the next versio of
ADO.NET will have something like that:

1) Provide a higher-level interface for creating data parameters: I can do
SqlCommand.Parameters.Add(string name,SqlDbType type), but I can only do
DbCommand.Parameters.Add(Object o); so I have to do
DbCommand.CreateParameter(), manually set parameter properties and then
DbCommand.Parameters.Add(). Why is this?!?
2) Please, please, *PLEASE* provide a way to map System.Type to
System.Data.DbType!!! I'm trying to build a generic data access layer for
variable data formats, and there is simply *NO WAY* to do this, other than
building my own type mapping method!


Massimo

 

[Miha Markic [MVP C#]]

I'm also trying to overcome this awful limit of the standard ADO.NET 2.0
classes; this alone, I think, is the biggest problem of the ADO.NET 2.0
library... and it's even worse because it could have been easily fixed by
having any data provider implement a GetParameterDelimiter() method and
using a generic delimiter in the DbCommand class!
I really don't know why Microsoft didn't handle this better :-/

Wow, guys, it is not that easy at all. It is not just parameter naming
convention. It goes way beyond parameters.
There are a ton of databases out there each with its own perks and features
and slightly different sql language, etc.

 

[Massimo]

Wow, guys, it is not that easy at all. It is not just parameter naming
convention. It goes way beyond parameters.
There are a ton of databases out there each with its own perks and
features and slightly different sql language, etc.

That's ok; but I'm only talking about command parameters here. Every ADO.NET
2.0 data provider has to implement them someway, so, if a DB maker
implements a data provider, this means parameter support has to be there.
And it has to support the same feature set of other providers, since the
methods it needs to implement are defined at the interface level. The only
true difference here are data types (which can be taken care of by using the
abstract System.Data.DbType instead of DB-specific ones) and the infamous
parameter delimiter, which is different for any data provider. It really
doesn't seem so hard to me to implement some text substitution here to
handle this automatically and finally make these parameters usable.


Massimo

 

[Cor Ligthert [MVP]]

Massimo,

AFAIK is there not any problem to use the same code for parameters forever
as long as you use them in the sequence as they are used in the SQL transact
code that you use. Therefore you need no wrapper.

Cor

 

[Philipp Brune]

Hey Friends,

i got an idea now, how to solve the basic issues of refetching
identities / fixing DataTable schemas and CommandText replacement :

Since DbDataAdapter and DbCommand inherit from Component which inherits
from MarshalByRefObject I will use the
System.Runtime.Remoting.Proxies.RealProxy to intercept client calls to
certain Methods.

Now, i could possibly solve my problem like this :

Program my own DbProviderFactory, lets call it DBF that wraps the
DbProviderFactory of a given .NET data provider. DBF returns the
return values of the wrapped factory for all method calls except
CreateCommand / CreateDataAdapter. For those a transparent proxy is
returned. The transparent proxy does the following :

DbCommand : Intercept calls to the setter Method of CommandText property
to fix the given CommandText.

DbDataAdapter : Intercept calls to FillSchema / Fill methods for
postprocessing the retrieved schema.

Additionally the factory will register for the RowUpdating/RowUpdated
events for retrieval of the db generated identities.

Does this make any sense to you ?

Philipp

 

[Massimo]

AFAIK is there not any problem to use the same code for parameters forever
as long as you use them in the sequence as they are used in the SQL
transact code that you use. Therefore you need no wrapper.

What do you mean exactly?
I'm talking about parameter placeholders in the SQL query; even if I use
unnamed parameters (which I don't want to), I'd still have to place them in
my SQL string... and they need their delimiter there. Which is different for
each provider...


Massimo

 

[Robert Simpson]

Lots of people have had problems with the various providers and how they
formulate parameter names and place parameter markers inside their queries.
I answered this problem using a helper class, the original post is here:

http://groups.google.com/group/micr...935e2e4eb78?tvc=1&hl=en&#doc_519e9efb82fc7f58

Robert

 

[Massimo]

Lots of people have had problems with the various providers and how they
formulate parameter names and place parameter markers inside their
queries. I answered this problem using a helper class, the original post
is here:

Of course this problem can be solved
But I think this issue should definitely be taken care of by the framework,
since ADO.NET 2.0 tries to build a generic and rovider-independant data
access layer; after creating System.Data.Common, DbProviderFactory,
DbConnectionStringBuilder and so on, failing to establish a common interfare
to SQL parameters is a little... ridicouous.


Massimo

 

[Frans Bouma [C# MVP]]

I'm also trying to overcome this awful limit of the standard ADO.NET
2.0 classes; this alone, I think, is the biggest problem of the
ADO.NET 2.0 library... and it's even worse because it could have been
easily fixed by having any data provider implement a
GetParameterDelimiter() method and using a generic delimiter in the
DbCommand class! I really don't know why Microsoft didn't handle
this better :-/

You're confusing the problems caused by a chosen 'solution' to the
real problem WITH the real problem.

It's not that ADO.NET 2.0 lacks some classes and thus has an 'awful'
limit, it's just that you try to use it for something that it's not
designed for.

What you want is a layer on top of ADO.NET.

I also have another couple suggestions for MS guys, maybe the next
versio of ADO.NET will have something like that:

1) Provide a higher-level interface for creating data parameters: I
can do SqlCommand.Parameters.Add(string name,SqlDbType type), but I
can only do DbCommand.Parameters.Add(Object o); so I have to do
DbCommand.CreateParameter(), manually set parameter properties and
then DbCommand.Parameters.Add(). Why is this?!?

because not all databases support SqlDbType?

2) Please, please,
PLEASE provide a way to map System.Type to System.Data.DbType!!! I'm
trying to build a generic data access layer for variable data
formats, and there is simply *NO WAY* to do this, other than building
my own type mapping method!

Well, let me tell you as a guy who wrote this code some time ago, it's
simply not possible to create that mapping. DbType is too vague. Not
all providers map a given type T to the same DbType.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Guest]

I have done something like this, a database abstraction database and provider
agnostic.
Subclasses “the normal†DataReader, DataTable and so on. The most difficult
part is ironing out the database behavior differences (now supports SQL
Server, Oracle, DB2 (also the AS400) and Postgre SQL).
The development time was in the years…

 

[Massimo]

You're confusing the problems caused by a chosen 'solution' to the
real problem WITH the real problem.

It's not that ADO.NET 2.0 lacks some classes and thus has an 'awful'
limit, it's just that you try to use it for something that it's not
designed for.

It looks to me like it's designed to offer provider-independant data access,
but then you have to write parameterized queries using a provider-specific
syntax.
I don't know how you call this, but I call it "bad design"...

because not all databases support SqlDbType?

I meant, why can't I do DbCommand.Parameters.Add(string name,DbType type)? I
need some more lines of code to do this, and it's completely useless...


Massimo

 

[Frans Bouma [C# MVP]]

It looks to me like it's designed to offer provider-independant data
access, but then you have to write parameterized queries using a
provider-specific syntax. I don't know how you call this, but I call
it "bad design"...

MS had this in the past with ADO. As the designer of ADO once said: it
was a maintenance nightmare and they wanted to get rid of it. The main
problem is that you'll get something like a big phat featureset which
contains ALL features of ALL databases and from that big set you
implement the ones supported by the DB the provider has to support and
ignore the rest or convert them to features supported.

So they defined a common set of base classes you can program against
if you really want to and let the provider writer in charge of
implementing db specific features.

Since the beginning of .NET, people have critisized this approach and
they do have a point, however it's not as bad as it looks. The thing is
that the assumption that all databases are equal and work the same is
not true. So you can create to some extend an API which works on all
databases or a subset of the databases, but that API will always either:
- have to have DB-specific code paths (most o/r mappers, like ours) use
this)
- use a common subset of the features of all databases and ignore the
rest.

the second approach is tempting, though will likely give you problems.
The first requires a lot of code and then also for a subset of
databases.

I meant, why can't I do DbCommand.Parameters.Add(string name,DbType
type)? I need some more lines of code to do this, and it's completely
useless...

That's not entirely true. You can first ask a DbProviderFactory from
the DbProviderFactories class in System.Data.Common. Then you can ask
that factory to create a DbParameter instance. That parameter is then
uninitialized and you can set its DbType to a value if you want and
other properties.

FB

--
------------------------------------------------------------------------
Lead developer of LLBLGen Pro, the productive O/R mapper for .NET
LLBLGen Pro website: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Massimo]

"Frans Bouma [C# MVP]" <[email protected]> ha scritto nel
messaggio

MS had this in the past with ADO. As the designer of ADO once said: it
was a maintenance nightmare and they wanted to get rid of it. The main
problem is that you'll get something like a big phat featureset which
contains ALL features of ALL databases and from that big set you
implement the ones supported by the DB the provider has to support and
ignore the rest or convert them to features supported.

So they defined a common set of base classes you can program against
if you really want to and let the provider writer in charge of
implementing db specific features.

Since the beginning of .NET, people have critisized this approach and
they do have a point, however it's not as bad as it looks. The thing is
that the assumption that all databases are equal and work the same is
not true. So you can create to some extend an API which works on all
databases or a subset of the databases, but that API will always either:
- have to have DB-specific code paths (most o/r mappers, like ours) use
this)
- use a common subset of the features of all databases and ignore the
rest.

the second approach is tempting, though will likely give you problems.
The first requires a lot of code and then also for a subset of
databases.

I absolutely agree on the wrongness of the assumption that "all databases
are equal"; and I know I have to use only a small subset of database
features if I want my application to work with every one of them. But the
problem here is, even if I'm writing a really simple application (in terms
of DB queries), I *still* have to worry about provider-specific issues!
My application simply does single-row INSERTs. It uses plain SQL language.
It doesn't even use stored procedures, since A) it needs to work on any
database, and B) it just doesn't need them. The SQL queries it creates *can*
actually work on every database known to man... *but* I have to worry about
parameter delimiters, because each ADO.NET provider has its own one. So my
only options are avoiding parameters (which I don't want to) or...
parameterizing parameter markers themselves, using my internal placeholder
character for them and then running them through a parser of my own and
putting in the actual provider-specific delimiter.
This really looks like a nonsense to me, the whole ADO.NET 2.0 database
abstraction is invalidated, even for really simple queries, due to...
*single characters*, which the framework could have handled by itself quite
easily. This isn't even a database-specific issue: it's only created by the
*providers*!

That's not entirely true. You can first ask a DbProviderFactory from
the DbProviderFactories class in System.Data.Common. Then you can ask
that factory to create a DbParameter instance. That parameter is then
uninitialized and you can set its DbType to a value if you want and
other properties.

I can directly ask a parameter from the DbCommand, for that matter
But I need to set its values and then pass it do DbCommand.Parameters.Add(),
when I could just have used DbCommand.Parameters.Add(name,type), like I do
for SqlCommand's. I know, it's really a little issue and it's easy to do it
in a method of my own, but then, why wasn't this included in the
framework?!?


Massimo