Change VS created dataset's source database at runtime

Question

Hi,

This question and answers discuss the theory as well as methods to achieve the desired result so I don't wish to re-hash those suggestions. I'm trying to make sure there isn't an easy solution in my instance before I rewrite all my tableadapters.

What I do have is a relatively mature and complex project utilising many datasets all designed with VS and accessing a VistaDB database (it was SQLCE originally). For various reasons I now wish to make the actual database that the dataset's are bound to user selectable. The end-user's database selection will be limited to databases that all use the same SQL commands.

I've done a fair bit of research and several supposed solutions claim to do this but on delving deeper they do not change the database type - only it's connection string or path.

Has anyone actually achieved this and if so could they please at least tell me the theory and at best provide the code they have used.

Thanks ...

Answer 1

You can target multiple database engines if you implement your own database layer using the interfaces IDbConnection, IDataReader, IDbCommand and IDbDataAdapter and not the database engine classes directly.

This approach is described in this article here: http://www.dotnetjohn.com/articles.aspx?articleid=244

Also note that when targeting multiple database engines you should modify your sql statements. For example VistaDB and SQL Server accept dates inside single quotes ('2010-06-26') while Access database accepts the # symbol (#2010-06-26#) for dates.

This can also be done in your database layer using functions to return these characters:

'Access DB Provider
Public Function GetDateSQLChar() As String Implements MyDataLayer.GetDateSQLChar
   Return "#"
End Function

and

'VistaDB Provider
Public Function GetDateSQLChar() As String Implements MyDataLayer.GetDateSQLChar
   Return "'"
End Function