Provider-independent data access, again

[Massimo]

I'm still trying to provide my application (ADO.NET 2.0) with a generic data
access layer, independent of the specific provider used.

Now, my main concern is about connection strings, which can be quite
different amongst various providers.

Imagine I have read some parameters from a configuration file, like the
database type and some connection informations, which every data provider in
the world will definitely supports: hostname, database name, username and
password. I'm purposely avoiding Windows authentication here, since only SQL
Server (and maybe Oracle) supports it.

Ok, I'm creating my provider factory with some code:

-----
Enum DBType
{
SQLServer,
Oracle,
MySQL
}; // So you know what "type" is


DbProviderFactory factory = null;

switch(type)
{
case DBType.SQLServer:
factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
break;
case DBType.Oracle:
factory =
DbProviderFactories.GetFactory("Oracle.DataAccess.Client");
break;
case DBType.MySQL:
factory = DbProviderFactories.GetFactory("MySql.Data.MySqlClient");
break;
default:
throw new NotImplementedException;
}

DbConnectionStringBuilder csb = factory.CreateConnectionStringBuilder();
-----


Ok, now what?
The generic DbConnectionStringbuilder is *so* abstract it only lets you add
generic key-value pairs; you can't even set the data source name or the
username, even if any provider has these parameters!

So, here we go again: the whole provider-independent data model of ADO.NET
2.0 isn't so provider-independent at all.

How to solve this without manually specifying the connection string, which I
don't know and actually don't want to care about? What I want is my
application to read the hostname, the database name, the username and the
password from a configuration file, not some f***ing connection string.

I'm also forced to use the factory model, otherwise I'll have to manually
configure the specific data provider in my db-abstraction class, which I can
surely do, but then the program won't start (or even compile!) if it
references Mysql.Data.MySqlClient and that software isn't installed on the
destination system.

Can someone please help?

Thanks


Massimo

 

[David Browne]

I'm still trying to provide my application (ADO.NET 2.0) with a generic
data access layer, independent of the specific provider used.

Now, my main concern is about connection strings, which can be quite
different amongst various providers.

Imagine I have read some parameters from a configuration file, like the
database type and some connection informations, which every data provider
in the world will definitely supports: hostname, database name, username
and password. I'm purposely avoiding Windows authentication here, since
only SQL Server (and maybe Oracle) supports it.

Ok, I'm creating my provider factory with some code:

-----
Enum DBType
{
SQLServer,
Oracle,
MySQL
}; // So you know what "type" is


DbProviderFactory factory = null;

switch(type)
{
case DBType.SQLServer:
factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
break;
case DBType.Oracle:
factory =
DbProviderFactories.GetFactory("Oracle.DataAccess.Client");
break;
case DBType.MySQL:
factory = DbProviderFactories.GetFactory("MySql.Data.MySqlClient");
break;
default:
throw new NotImplementedException;
}

DbConnectionStringBuilder csb = factory.CreateConnectionStringBuilder();
-----


Ok, now what?
The generic DbConnectionStringbuilder is *so* abstract it only lets you
add generic key-value pairs; you can't even set the data source name or
the username, even if any provider has these parameters!

So, here we go again: the whole provider-independent data model of ADO.NET
2.0 isn't so provider-independent at all.

How to solve this without manually specifying the connection string, which
I don't know and actually don't want to care about? What I want is my
application to read the hostname, the database name, the username and the
password from a configuration file, not some f***ing connection string.

Why do you care about the individual parts of the connection string. They
are inherently provider-specific. Just treat the connection string a single
opaque piece of data.

DbConnection con = factory.CreateConnection(string connectionString);

I'm also forced to use the factory model, otherwise I'll have to manually
configure the specific data provider in my db-abstraction class, which I
can surely do, but then the program won't start (or even compile!) if it
references Mysql.Data.MySqlClient and that software isn't installed on the
destination system.

It's a plugin model, not a factory model that removes the need for a
design-time reference to all the providers. Of course, you'll have to use a
bit of reflection, but it shoudn't be too bad.


David

 

[Massimo]

Why do you care about the individual parts of the connection string. They
are inherently provider-specific. Just treat the connection string a
single opaque piece of data.

DbConnection con = factory.CreateConnection(string connectionString);

But I don't have a connection string, that's the reason why I need to build
one!
I only know the hostname of the server, the name of the database and the
login information to access it.

It's a plugin model, not a factory model that removes the need for a
design-time reference to all the providers.

It should, when using the factories. But you can't if you don't know lots of
details about the underlying providers.
What a mess... :-/


Massimo