I have an ADO.NET data access layer that has been written to work with multiple data providers, and am finding that I can't make some operations completely data provider independent.
As part of my application's installation functionality, it tells the DAL to create a database with a table and some data. The DAL uses the Sql command CREATE DATABASE. Now this is fine for SQL Server, but when using SQLite, the database creation step is not required, as the data source specified in the connection string is sufficient to define the database to use.
I'm currently planning on handling this by writing a 'DatabaseCreator' class with a virtual createDatabase method, which I can subclass for specific data providers, and have a factory that serves up a specific DatabaseCreator based upon the data provider being used, or the default one.
The problem with this approach is that my app won't "just work" with new data providers that aren't compatible with the default way of creating a database, but will require a new release with a new DatabaseCreator for the new data provider.
The only way around this problem I can see would be to let my app be configured with a DatabaseCreator to use in a similar way that it is configured with a data provider, i.e. the DatabaseCreator could be provided in a separate assembly.
I'm new to ADO.NET, so I'm not sure how common this type of issue is when trying to write data provider independent applications.
Is there existing support for this kind of approach in ADO.NET?
E.g. Vendor supplied or third-party existing libraries defining interfaces and/or implementations of helper classes for common operations whose implementations may be data provider dependent?
Alternatively, is there a better way to do this?