Is it bad practice to store SQL queries in resource file?

Question

I have a web application that communicates with SQL server. Rather than hard-coding all of the query strings, I have opted to store them in a global resource file. Is that considered bad practice?

On a side note, when I do this, Visual Studio yells at me about the possibility of SQL injection, despite those queries being parameterized (not to mention the "spelling" warnings inside the resource file).

Answer 1

Practices fall into a range (e.g. Avoid, Prefer, Use, etc.) and depend on context.

If you have a mandate from on high that stored-procs shalt not be used and neither shall ye use an ORM, then storing your complex SQL as a resource is not that bad of a practice because you at least don't have to escape characters in a System.String and you at least keep it somewhat safe from eyes. If your SQL is dynamic in nature, combining resource files with a text templating mechanism is fairly clean.

That said, generally (i.e. it seems in most contexts) using resource files should be avoided unless there's a clear benefit in maintenance costs, readability, and capability. There are quite a few clean ways to bind stored procedures to code; there are a number of competent ORM tools and mini-data access layers (aka micro-ORMs in today's parlance) that might do a better job.

Answer 2

Having the SQL queries separated from the application code is a good thing. Stored procedures is the normal way to do this, but if that's not feasible and you have to use SQL directly I think your approach is good. With recent versions of SQL server parameterized queries are precompiled the first time they are run and give similar performance to an SP.

I would however advise you to look into other data access methods such as linq-to-sql which automates the SQL query generation and gives you a cleaner interface in the code.