Where to put sql when using dapper?

asp.net-mvc-3 c# dapper n-tier

Question

I'm using dapper for a mvc3 project at work, and I like it. However, how are you supposed to layer the application when using dapper? Currently I just have all my sql stuffed directly in the controller (slap) but I was thinking of making a class with static strings.. So I could do

var reports = Dapper.Query<Report>(conn, MySql.ReportsRunningQuery)

How do you store your sql when using dapper?

Accepted Answer

I would say put the sql where you would have put the equivalent LINQ query, or the sql for DataContext.ExecuteQuery. As for where that is... well, that is up to you and depends on how much separation you want.

However, personally I see no benefit hiding the SQL in a separate class away from the Query<T> call - you want to see them in context so you can easily verify the data (and indeed, the parameters). You might also be constructing the query (still parameterised) in situ. But for a regular static query I would keep the TSQL as a literal near the code, unless I have good reason to need it abstracted, i.e.

var reports = conn.Query<Report>(@"
select x.blah, y.blah
from x (snip)
where x.ParentId = @parentId and y.Region = @region", new {parentId, region});

(note also the alternative extension method usage in the above)

IMO, the key in the above is that it is extremely unlikely that you would ever re-use that query from any other place - the logic would instead be put into a method, and that method called from multiple places. So the only other reason you might use to hide the query behind a central wrapper is if you need to support different database providers (with different SQL dialects). And that is rarer than people make out.


Popular Answer

Using a resource file is really useful for us. We create .sql files in a folder call /Sql and drag them into the 'Files' section of our SqlResource object. The 'Strings' section of the resource file is really clean and easy for smaller snippets of sql (e.g. functions we may be querying).

So, our sql looks like:

var reports = conn.Query<Report>(SqlResource.Blahs_get, new {parentId, region});

This keeps the repositories real clean. And there are additional benefits to having all of your sql in a resource file in that you can iterate over the entries and potentially query the db with PARSEONLY to make sure that if db objects change your queries would break (note that this is mostly but not 100% reliable).

So, to conclude, for us Resource files keep things real clean, but to Marc Gravell's point they are not for reusability within the production code...each sql statement should only be used by one point in your application.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why