The generic problem...

[Frank Rizzo]

Ok, ado.net is great, yada, yada, yada.
However, I normally write code generators that are then used by other
people in the company. These generators are used, among other things,
to gen DB related code (stored proc wrappers, etc...). The database
servers are quite diverse (ms sql, sybase, etc...).

With VB6 it was easy to write generic code that could handle all the
databases, because Connection, Recordset, Command objects worked for all
databases. The only thing needed was to change the Provider value in
the ConnectionString.

In ADO.NET I can use the OleDbClient namespace but I won't because the
native ones are faster and I won't make my users suffer because it is
more convinient for me to use OleDbClient.

So has anyone been able to write generic code with the native providers
(SqlClient, SybaseClient, OracleClient, etc...) that works with all of them?

I've mostly resolved the problem with the connections. I wrote a class
that figures out which connection I want (sqlclient or sybaseclient or
....), and them simply return a connection object based on IDBConnection
interface.

Connections are easy, because you only need to connect and that's pretty
much it. Has anyone dealt with any other objects (command, dataset,
datareader, etc...) in a generic manner?

 

[Miha Markic]

Hi Frank,

Ok, ado.net is great, yada, yada, yada.

Yes, it is great

However, I normally write code generators that are then used by other
people in the company. These generators are used, among other things,
to gen DB related code (stored proc wrappers, etc...). The database
servers are quite diverse (ms sql, sybase, etc...).

With VB6 it was easy to write generic code that could handle all the
databases, because Connection, Recordset, Command objects worked for all
databases. The only thing needed was to change the Provider value in
the ConnectionString.

Khmm khmmm.

In ADO.NET I can use the OleDbClient namespace but I won't because the
native ones are faster and I won't make my users suffer because it is
more convinient for me to use OleDbClient.
Right.

So has anyone been able to write generic code with the native providers
(SqlClient, SybaseClient, OracleClient, etc...) that works with all of

them?

Yes.

I've mostly resolved the problem with the connections. I wrote a class
that figures out which connection I want (sqlclient or sybaseclient or
...), and them simply return a connection object based on IDBConnection
interface.

It is called factory pattern. And it is a good choice.

Connections are easy, because you only need to connect and that's pretty
much it. Has anyone dealt with any other objects (command, dataset,
datareader, etc...) in a generic manner?

Datasets are dtb independent.
You should use the same factory pattern for commands while there is no need
for DataReaders - just use interface IDataReader returned by
IDbCommand.ExecuteReader.
As for building commands, you might just create database specific command
instance and then use interfaces to build (IDbCommand.Parameters, etc) them
or you might build them in database specific way, which is more powerfull
(i.e. SqlParameters, ..) and then use IDbCommand.

 

[Frank Rizzo]

Hi Frank,




Yes, it is great




Khmm khmmm.



them?

Yes.




It is called factory pattern. And it is a good choice.




Datasets are dtb independent.
You should use the same factory pattern for commands while there is no need
for DataReaders - just use interface IDataReader returned by
IDbCommand.ExecuteReader.
As for building commands, you might just create database specific command
instance and then use interfaces to build (IDbCommand.Parameters, etc) them
or you might build them in database specific way, which is more powerfull
(i.e. SqlParameters, ..) and then use IDbCommand.

Thanks, I'll try it out.