How should my application's data layer be if it is meant to be usable with any database?
I am writing an application which can connect to any SQL Server Database, thus can be considered as stand-alone. In other words, the first thing the application does is request a connection string. This application will be performing queries and other tasks on the database, thus SQL statements are required.
Hence, since I am not going to specifically attribute the application with a pre-defined database, how am I to execute SQL statements from my C# application? I would imagine that stored procedures are out of the question since the application can connect to any database.
I can easily just hard-code the SQL statements in the application itself, but I was wondering what the best practice approach could be in a case like this. Is it ok to embed SQL statements in the application, thus essentially having the business logic and data layer in the same application? I have no logical or technical problem with this, merely just wondering if it is a valid approach, or whether it would be frowned upon by seasoned developers?
I have heard of Entity Framework, however to be perfectly honest I am not sure what it does or if it would be valid. Up till now, all my data-driven applications have utilised stored procedures and this is the first time that I am writing an application which can be used with a database which is specified at run-time.
Since you are building a query builder (no pun intended), you need to query the database catalog, which is the same for all databases under the same kind of DBMS. That will allow you to get the list of tables and other objects in any given database, so you can present them to the user.
Whether you'll use the pure ADO.NET or an ORM is an orthogonal question.
Yes embedding the SQL queries into the application is acceptable. Instead of hard coding them in code you can use resource files. The connection strings provider name should enable you to then select the correct SQL dialect for the db you are connecting to.
See Event stores SQL persistence factory ResolveDialect method which uses a similar approach. It uses it too determine the syntax required to create and call stored procedures, while your application will (I assume) need to call SQL which returns database metadata/structure. While the SQL they will be calling is different this pattern should be a nice fit for your application, allowing you to support many different databases.
Depending on what you are attempting to achieve you may also need to create an internal model which represents the tables, columns, etc metadata in the database.
Sorry just seen the comments on being SQL server only. The SQL dialect could therefore be used more by the client to determine the structure of the SQL query (e.g. For code complete ).