VB.NET abstracting Database connection

amirsky
amirsky used Ask the Experts™
on
I am trying to figure out the architecture to have a VB.NET program that interacts with a database. I issue which most people face is to be able to work with multiple Databases (a quick shopping list SQLite, MySQL, DB2, Oracle, and MS SQLServer).

I did some playing around with SQLite and it seems I needed to use some SQLite-specific code which has me all confused and concerned.

So basically my question is how can I setup my database connections and interaction to be abstracted from all Databases? Is there any good resources for this?

I've done it in Java where i just define the Driver at the beginning and the code is the same from then on, but cannot figure out how to do the same/similar thing in VB.NET
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If you want to handle the gritty details yourself, I've accomplished this in the past by writing all my data access code using the interfaces provided by the System.Data namespace. Here's the general idea:

    IDbConnection
    IDbCommand
    IDataReader
    etc...

I included a configuration section so I can specify my database type at runtime. This required a configuration element for each interface, i.e.:

    <add key="IDbConnectionImpl" value="System.Data.SqlClient.SqlConnection" />
    <add key="IDbCommandImpl" value="System.Data.SqlClient.SqlCommand" />

And so on. At runtime, you use Reflection to instantiate concrete instances of the classes, and perhaps a static class with methods like "IDbConnection CreateDbConnection(string connectionString)" or something along those lines.

It might just be easier to define a factory class for each database type that use a common interface, and use the configuration to just specify the factory class type instead of all of the individual data access class types:

public interface IDbClassFactory {
    IDbConnection GetDbConnection(string connString);
    IDbCommand GetDbCommand();
    //...
}

public class SqlDbClassFactory : IDbClassFactory {
    IDbConnection GetDbConnection(string connString) { return new SqlConnection(connString); }
    IDbCommand GetDbCommand()  { return new SqlCommand(); }
    //...
}


If specific database types require specialized code, you need to account for all those scenarios ahead of time and branch your application based on the DB type.

Another option is to use an ORM like nHibernate. nHibernate can be configured against multiple database types, and under the hood uses a similar mechanism to determine the database type at runtime. These days, it's the method I prefer when available: http://nhforge.org/Default.aspx
Adam MenkesC# ASP.NET Developer
Top Expert 2010
Commented:
nHibernate (as suggested above) is one option, Telerik OpenAccess is another (which I have been using). It is free for certain free database versions (MS SQL Server Express, Oracle Express, MySQL and Firebird).

http://www.telerik.com/community/free-products.aspx

Lots of other good free stuff here too.

Author

Commented:
Sorry for my slowness but I am confused. Is there no way in VB.NET that I can just load the a Database driver like in Java and then everything is the same code from then on?

Also keep in mind because we are a small team under a tight schedule we are currently really restricted to what we know - that is VB.NET (+ADO.NET which we have been studying).

Let me ask this in a different way. Using wikipedia (which I know is not always reliable) is says ADO.NET supports connections to multiple databases that have data providers available. The list is as follows:

A wide range of data providers can give access to the database engines like Oracle, Microsoft SQL Server, MySQL, PostgreSQL, SQLite, DB2, and others. Such data-providers include:

    * Connector/Net: native data provider for MySQL database server (free)
    * DataDirect Connect for ADO.NET: data providers for Oracle, DB2, SQL Server, and Sybase database servers from DataDirect (commercial)
    * DB2 .NET: data provider for DB2 database server from IBM (free)
    * dotConnect: data providers for Oracle, MySQL, PostgreSQL, SQL Server, and SQLite database servers from Devart (free and commercial)
    * Npgsql: open source data povider for PostgreSQL database server (free)
    * Oracle Data Provider for .NET (ODP.NET): data provider for Oracle database server from Oracle (free)
    * VistaDB: 100% managed ADO.NET provider with SQL Server like syntax
    * EffiProz: open source ADO.NET provider for EffiProz pure c# database
    * RDM Server: data provider for the RDM Server database system from Birdstep Technology, Inc (free)
    * System.Data.SQLite: open source ADO.NET provider for SQLite databases (free)

Our goal is to have an abstraction layer between our business logic and the actual database interaction. That way all the business logic had to say is "Add this" and the database layer deals with the query work and so on to actually "Add this" to what ever database is being used.

The question again is lets say we use ADO.NET and have all the data providers above. Aside from defining the connection (which is different for each database type as I see on MS's site http://msdn.microsoft.com/en-us/library/32c5dh3b%28VS.71%29.aspx) . Will the actual code for executing a insert, select, creating tables, reading result sets, etc, be the same? Or will all have their own different code (function names, return types, etc). If the former then all we (generally) have to do is be specific for the database connection code but for the actual database interaction (queries) we can use the same code.  If the latter then, we will have to write and maintain a class for each and every type of database - which obviously can become a nightmare.

I hope my ramblings haven't confused my question -  but anyone have any insights?
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
What you can do is setup ODBC System DSNs.

You could define, in a configuration file or in your code, the connection strings, and depending on which database you want to connect to, use that connection string.

In your code, you can call a function to get the connection string.

Something like

Const Conn_Oracle = '....'
Const Conn_MSSQL ="..."
...

Function GetConnectionString()

' Check some global variable or setting here
select case
...
case 1
GetConnectionString = Conn_MSSSQL
...


and later in your code when you need to create an open a connection, call the function to return the string.

Author

Commented:
Okay so i got that I have to maintain different connections strings in order to connect to different databases. But once connected would the code for queries (INSERT,DELETE,SELECT,CREATE,etc) be the exact same?

Isn't ODBC old and slow? Another EE seems to argue for ADO.NET which is what we have been looking at.

So again the primary concern is if we will have to maintain different code for and every database type (DB2, Oracle, SQLite, etc), in order to do the exact same things (SELECT, INSERT, etc). Or, once the connection is made (in ADO.NET?) is the code onwards is the exact same?

This whole question is based on the goal of a program that can access multiple database types with minimal maintenance.

Author

Commented:
Let me augment me question by an example. I did a prototype using SQLite ADO.NET data prodiver recommended on source forge.

Notice that after connecting to the SQLite DB, I still had to reference "SQLite.SQLiteCommand" and "SQLite.SQLiteDataReader" in order to actually interact with the DB.

So will this be the case with every database type or something unique to SQLite? Will DB2 require some sort of "DB2.DB2Command" and MySQL require some sort of "MySQL.MySQLCommand" ?

If not and the code will be the exact same can you show me a sample or it with two different DBs similar to the one i provide?

SQLconnect = New SQLite.SQLiteConnection()
SQLconnect.ConnectionString = "Data Source=" & etc & ";"
SQLconnect.Open()

Dim SQLcommand As SQLite.SQLiteCommand
SQLcommand = SQLconnect.CreateCommand
SQLcommand.CommandText = "SELECT * FROM foo"

Dim SQLreader As SQLite.SQLiteDataReader = SQLcommand.ExecuteReader()

While SQLreader.Read()
    //do some work
End While

SQLreader.Close()
SQLcommand.Dispose()

Open in new window

Adam MenkesC# ASP.NET Developer
Top Expert 2010
Commented:
You are trying to have 2 different things going on.

1) Easy to maintain code that is not database specific
2) Speed

If you use ADO, you use its features, and do not do pass-through SQL code since each of the databases has its own nuances for SQL syntax. But, going ADO -> ODBC -> SQL adds a layer over going direct to a database with a native driver. So, you sacrifice speed for maintainability.

Using nHibernate, Entity Frameworks, OpenAccess (which I use), you configure in the web.config (for web apps) or in your settings for desktop apps the connection string, and the code does not change. But again, you do not pass through SQL code, you use the built-in functionality.

For example, here is a sample method using OpenAccess and LINQ. This is the equivalent to

SELECT TOP 1 * FROM Address WHERE ID = @addressID  AND Active = 1

public static Address GetAddressByID(IObjectScope scope, int addressID)
{
  return (from a in scope.Extent<Address>()
          where a.Active && a.Id == addressID
          select a).FirstOrDefault();
}

Open in new window

Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:
Another thing you can do is limit the SQL to the common code that should work for almost everything
(SELECT * FROM mytable WHERE condition)

http://www.vbexplorer.com/VBExplorer/vb_feature/june2000/Database_Beginner_ADO_DAO.asp

Dim rs as recordset

instead of

  rs.execute "INSERT INTO tablename(ID,Name) VALUES (10,Anne)"

use
set rs=db.openrecordset({tablename or SQL})
rs.addnew
rs!ID=intID
rs!Name=strName
rs.update
Lets step back a second... you CAN select your database driver at runtime. You do not have to use the ODBC layer in order to operate with different DB connections. The limitation you do have to follow is to limit your choice of databases and queries to T-SQL compatibility (that covers a majority of database usage scenarios).

There are a few different methods for selecting the database connection type at runtime. To provide a basis for where to start using the code you have already provided:

Dim SQLconnect as IDbConnection
SQLconnect = New SQLite.SQLiteConnection()
SQLconnect.ConnectionString = "Data Source=" & etc & ";"
SQLconnect.Open()

Dim SQLcommand As IDbCommand
SQLcommand = SQLconnect.CreateCommand
SQLcommand.CommandText = "SELECT * FROM foo"

Dim SQLreader As IDataReader = SQLcommand.ExecuteReader()

While SQLreader.Read()
    //do some work
End While

SQLreader.Close()
SQLcommand.Dispose()


You'll notice in this example the only SQLList specific line is the instantiation of the SQLConnection. Everything else uses generic ADO.NET interfaces. So, if you want to use SQL Server, all you would have to change is to replace "New SQLite.SQLiteConnection()" with "New SqlClient.SqlConnection()". For ODBC, you would use "New ODBCConnection()". You can use an Oracle client, OLE, MySql, all with the rest of the code remaining the same because every database that is ADO.NET compatible uses the same interfaces for data access.

If this sounds like what you're after, let me know and I can elaborate on how to make the database type configurable so you don't have to recompile your application to change data sources (as I was talking about in my first post).
You can also go the ORM route. By configuring an ORM layer such as nHibernate (I mention hHibernate because 1) it is based on the java hibernate ORM and skill set is transferable, 2) it is very mature, widely used, and well-documented, and 3) I use it all the time), you abstract your database with entities. In the most basic scenario, you might have one data object per table in your data base. nHibernate can use either an object-oriented query building method or the nHibernate "HQL" syntax (which has some SQL-like features but is based on your object model instead of your database model), either of which allows you to write your query once and use it with any supported database.

There is a learning curve, I believe, of at least a couple of days. In the long run, the time spent learning is well invested, and you get what you're after - complete abstraction of the database from the application.

Author

Commented:
I looked at nhibernate when you first suggested it. Isn't it limited to a small number of databases (MySQL, MS SQL, Firebird)?
Adam MenkesC# ASP.NET Developer
Top Expert 2010

Commented:

Author

Commented:
Oh, never mind.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial