In these cases, I’ve elected to write queries using a templating system and describe the tables using the ORM. I get the convenience of an application level description of the table with direct use of SQL. It’s a lot less trouble than anything else I’ve used so far.
I was with the spirit of the article save for this.
Recently I've been developing some work for one of my client's in the .NET world. There had been ongoing discussions about developers wanting to use Entity Framework as an ORM vs. using stored procedures.
The client already uses SQL projects and DACPACs (effectively a system for specifying the DB in SQL then diffing it between versions to alter the database).
The arguments for and against ORM were largely naive from each side of the fence - the developers wanted strong typing (doesn't require an ORM) while the DBAs wanted to be able to review query execution and suggest changes if there's an issue (you can see the generated SQL for the ORM).
The solution I came up with was to use reflection on the build server as part of the CD process to map the inputs and outputs of the stored procedures into strongly-typed C# methods and objects, generate code for it, and build/pack/push a nuget package back to our feed. It's similar to what EF provides without maintaining the EDMX (which we can do as we don't need to cover every possible data access scenario like EF does). It means that once the database project is checked and the build green-lights, an updated Nuget package that constitutes the DAL is automatically waiting on the internal Nuget feed.
I've found it gives us the best of both worlds. We can do anything we need to in t-SQL and the C# wrapper only cares about the ultimate input/output. I can force the use of parameter sanitization in the wrapper (by not providing any other way to call the procs), and the DBAs can review/amend whatever they want without the developers needing to change their code as the interfaces don't break.
We also don't have to write DAL boilerplate or worry about inexperienced developers getting it wrong and opening injection attack surfaces at the DAL layer.
There's usually a solution to your use case if you look for it is my point.
One or two blog articles featured here, clearly explaining the difference in philosophy/architecture, and the advantages it has over ORMs, and you will have an audience.
I was with the spirit of the article save for this.
Recently I've been developing some work for one of my client's in the .NET world. There had been ongoing discussions about developers wanting to use Entity Framework as an ORM vs. using stored procedures.
The client already uses SQL projects and DACPACs (effectively a system for specifying the DB in SQL then diffing it between versions to alter the database).
The arguments for and against ORM were largely naive from each side of the fence - the developers wanted strong typing (doesn't require an ORM) while the DBAs wanted to be able to review query execution and suggest changes if there's an issue (you can see the generated SQL for the ORM).
The solution I came up with was to use reflection on the build server as part of the CD process to map the inputs and outputs of the stored procedures into strongly-typed C# methods and objects, generate code for it, and build/pack/push a nuget package back to our feed. It's similar to what EF provides without maintaining the EDMX (which we can do as we don't need to cover every possible data access scenario like EF does). It means that once the database project is checked and the build green-lights, an updated Nuget package that constitutes the DAL is automatically waiting on the internal Nuget feed.
I've found it gives us the best of both worlds. We can do anything we need to in t-SQL and the C# wrapper only cares about the ultimate input/output. I can force the use of parameter sanitization in the wrapper (by not providing any other way to call the procs), and the DBAs can review/amend whatever they want without the developers needing to change their code as the interfaces don't break.
We also don't have to write DAL boilerplate or worry about inexperienced developers getting it wrong and opening injection attack surfaces at the DAL layer.
There's usually a solution to your use case if you look for it is my point.