Where to put sql when using dapper?

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?

Answer 1

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.

Answer 2

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.

Answer 3

Though this question is now considerably aged, I'd like to further suggest the external storage of SQL. Visual Studio (at least 2015+) has syntax highlighting, as well as a small debugger and connection manager for *.sql files. The files can further be marked as Embedded Resources and completely contained within the assembly, but separate from your code. You'll grow to loathe seeing colorless SQL embedded in non-syntax-verified strings.

I've adopted this pattern on all of my recent projects, and combined with an ORM like Dapper, the interfacing between C# and SQL becomes very minimal. I have an open-source project extending Dapper available on GitHub which can provide examples, as well as a NuGet Package. It also includes a moustache inspired string replacement engine, which is useful for templating your scripts to make them reusable, or inserting dynamic filtering conditions.