I need to deliver a small app allowing the client to create queries (or "rules") against their local database and trigger certain actions (send mails, text messages and stuff).
Since they will be allowed to state the actual SQL for the job, I am wondering how my data layer is supposed to look like. It seems I don't have any need for entities and repositories, because all data interaction will be weakly typed.
So what should my data layer do? Open the connection, accept the input SQL, and return a list of property bags? Do I even need a data layer for this?
[Update]
This is what client wants to have in his app, to be able to write or build queries against their database. It will run on their local computer, meaning that SQL injection attacks are unnecessary for a malicious employee.
But even if I have a visual control for building the query, validating and sanitizing it, the end result of this layer is SQL code, isn't it? How do I abstract that if I want to test it?
Actually, we had the almost same problem more than 10 years ago (though it was not on a local database). IMHO for ad-hoc SQL queries (however they are created, the user could just have a textual shell to type them in), a DAL does not make much sense - for the generic reporting / query part of the application. That does not mean you cannot have a DAL for the parts of the application where data is modified or entered into your database.
You should, however, take care for some things. Obviously, syntactically incorrect queries should give a meaningful error message, and there should be a possibility to interrupt long-running queries.
It may be also a good idea to restrict the ad-hoc query access to certain tables, or provide a layer of certain views, to hide technical parts of your data model away from your users. And I would avoid to allow your users to modify any data in a similar ad-hoc manner - make sure they cannot inject any UPDATE statements into a SELECT statement created by them.