Skip to main content
Software Engineering

Return to Question

Editing for cocmment feedback: clarified what I'm asking for
Source Link
Adam
Adam
  • 113
  • 4

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 DbCommand and 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 options,parameters that specify what the parameter name and canvalue are.

I see two unsatisfactory options -

  1. 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 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.

  2. 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.

    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.

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 DbCommand and 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 options, and can

I see two options -

  1. 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.
  2. 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.

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.

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 DbCommand and 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 -

  1. 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.

  2. 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.

Source Link
Adam
Adam
  • 113
  • 4

How can I design a data access layer that connects to two different databases?

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 DbCommand and 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 options, and can

I see two options -

  1. 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.
  2. 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.

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.