I have a C# application that needs to connect to either an Oracle database or a SQLite database. The databases can be considered "identical" - same schema - but users have the ability to "load" their own SQLite file for faster local access. I'm trying to design a data access layer for this need.
My problem is that the abstract
DbParameter classes don't have a very useful user interface. For example,
DbCommand.Parameters.Add(object) is not well typed (and has unclear semantics in general). I am aware that
DbCommand.CreateDbParameter() exists and creates a
DbParameter that can be populated with data. In contrast,
SQLiteCommand.Parameters.Add is overloaded with several parameters that specify what the parameter name and value are.
I see two unsatisfactory options -
Create an interface and make two classes which implement it. There is potential for some shared data - perhaps query strings, but even then differing parameter syntax may disallow this. This duplicates code but best utilizes the database provider APIs.
Create a single class that relies on the generic implementation. Have the constructor/methods accept a connection
DbConnection, and execute the same code on different connections. It's unclear whether this is even possible due to syntax issues from point 1. If possible this would create a single, flexible class, which may be clunky internally.
Are there any "native" C# ways to handle this case gracefully? I am surprised that the abstract base
DbXxx classes are so useless. The ADO.NET docs are housed under a .NET Framework category, which seems to imply it's legacy technology. I wouldn't think a language as popular as C# would not come with a well typed way to add parameters to queries; it's not a vendor specific feature. Go's
database/sql built in package, for example, handles all these cases.
I do not want to use Entity Framework - this is application replaces legacy software which has some custom queries which are sloppy to translate into LINQ/EF.