|
The sprocs vs ad-hoc queries war is still raging and I imagine it will for a while. I think that one of the main reasons that people do stick with sprocs is not due to the fact that they in some way represent the ultimate über performance, but rather the convenience of having the queries in a storage where it is easy to change and maintain it. Since most of the Microsoft community still isn't using some kind of generator to create the query for you (like an ORM for example) it is really a pain to try to maintain something like this:
string specialProductsQuery = "select * from products " +
"inner join categories on products.categoryid = categories.categoryid " +
"inner join categorygroup on categorygroup.id = categories.groupid " +
"where "
etc, etc, etc.
It is just so awful to maintain and create queries that are represented in quoted strings.
In the rare cases when I need to create theese kind of queries I don't resort to sprocs though(It is just a principle I follow, If I really don't need them I will not use them). Instead I add them to my project as .sql files containing the query. No quotation, no string concatenation and really easy to maintain.
Theese could then either be sent with the application if you want the flexibility to change the query without recompile. (Now I DON'T recommend that, but people do so anyway, to them I just want to say don't change anything in production for god sake!! But that is another battle to be fought another day.) This might be convenient in the development environment though to quickly change and try stuff. Another more sane approach is to compile the .sql file together with the application as resource files.
Using this practices, it would be possible to maintain the sql files without all the headache and still avoid stored procedures.
I've added a distillation of the practice I use in some projects to the "My Code" section over here https://lowendahl.net/shoutCode.aspx as an example project with unit tests and a test client.
It is capable of reading and caching (and flushing the cache) sql queries with two different strategies which are configured from the outside for easy configuration in different environments.
The idea for this manager is to make it easy to load and maintain queries. No more quoted concatenated strings in our code.Just something like this:
public static class Queries {
public static string GetAllProductsQuery {
get {
QueryManager manager = QueryManager.CreateFromConfigFile();
string query = manager.GetQueryFor("GetAllProducts");
return query;
}
}
}
.
.
.
SqlDataAdapter adapter = new SqlDataAdapter(Queries.GetAllProductsQuery, ConnectionStrings.AdventureWorks);
The current version has the infrastructure to configure different cache strategies but it's not activated.
|