Create a connection object (sql/oledb/odbc) based on connection method/string.

[Hetal]

Hi...

I am a newbie VB.NET developer and i am looking at working with
ADO.NET rather than ADO. In one of our native VB application with ADO,
we used to create 1 connection object and that would help us to
connect to MS-SQL and MySQL alternatively based on the database type
and connection string i provide. So, one connection object was good
enough to connect to any of these 2 type of databases.

The sample connection string we use to connect to the databases are as
follows:

MS-SQL: "Provider=sqloledb;Data Source=<server_name>; Initial
Catalog=<database_name>;User Id=xxx;Password=xxx"

MySQL: "DRIVER={MySQL ODBC 3.51
Driver};SERVER=<server_name>;DATABASE=<database_bame>;UID=xxx;PWD=xxx;OPTION=16427;"

With ADO.NET, it makes us create different connection objects based on
database type and connection string we use. To connect to MS-SQL
database, we will have to create an oleDBConenction object and for
MySQL database, we will have to create an odbcConnection object.

The challenge we face here though is that we had all our database as
MS-SQL and we are moving them to MySQL one by one (some are already
migrated to MySQL). And we would like to make our connect feature such
that it dynamically creates a connection object (any of sql/oledb/
odbc) based on what the back end is and what connection string/method
we are using. Is there a possible way to make it dynamics?

Any help on this one will be very much appreciated. Thanks.

 

[Guest]

The challenge we face here though is that we had all our database as
MS-SQL and we are moving them to MySQL one by one (some are already
migrated to MySQL). And we would like to make our connect feature such
that it dynamically creates a connection object (any of sql/oledb/
odbc) based on what the back end is and what connection string/method
we are using. Is there a possible way to make it dynamics?

You'll need to build a data abstraction layer. The problem with generic
data layers is that different databases support different features so I
believe that's why Microsoft created custom implementations for each
database.

However, there are a couple tools out there to make your ADO.NET "generic":

CoreLab's UNIDirect ADO.NET driver provides access to several database
platforms

Microsoft has the Data Access Application Blocks to may help to connect to
multiple databases.

You can use a DAL framework like LLBLGen Pro/CodeSmith (which contains
layers for several database).

You could build your own custom DAL.

I personally use the LLBLGen Pro DAL framework and it's great Supports
SQL Server, MySQL, DB2, Firebird, Oracle, and more.

 

[Hetal]

Thank you Spam Catcher.