26

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?

Christian Wattengård
  • 5,543
  • 5
  • 30
  • 43

3 Answers3

33

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.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • About that extension method usage... How do I make that work? 'Cause mine doesn't :/ – Christian Wattengård May 13 '11 at 06:55
  • 1
    @Christian - just make sure you have a `using Dapper;` directive – Marc Gravell May 13 '11 at 07:00
  • 1
    Thanks. But putting all the sql in the controller, wouldn't you basically be making a "fat controller"? And depending on who you ask, isn't that a "Bad Thing"? – Christian Wattengård May 13 '11 at 07:27
  • 7
    @Christian I deliberately didn't say anything about the controller. If you *choose* to do your data access in your controller, then arguing over where the TSQL lives is a moot point - the data access is still there. Fatness is about ***what it does***, not the lines-of-code count. – Marc Gravell May 13 '11 at 07:35
  • Yes. Thanks. I think this probably is the wrong app to learn MVC and n-tier architecture with. (It's a web-frontend for a legacy db) – Christian Wattengård May 13 '11 at 07:44
  • Christian, check out the Repository pattern. You data access could live within a repository class abstracted from your controllers. Ideally, your model will be on a separate project. Try to keep your controllers clean and talk to the model with a repository so you can abstract the database as much as possible. That way you can easily mock and fake data if you need to run unit tests or even change the DB engine in the future. – Diego May 18 '11 at 22:26
  • *(Don't get me wrong. I respect Mark's work on Dapper lots but...)* TSQL executes elsewhere than LINQ/C# does. Just like Javascript executes elsewhere than C#. Do you like mixing Javascript with C#? Probably not. The same should apply to TSQL. That's why I rather use stored procedures where all SQL logic resides and keep calls to them as automated as possible. Using T4 namely so I get intellisense, compile-time errors and lower the human factors errors. C# should be C#, TSQL should be TSQL, Javascript should be Javascript and HTML should be HTML. Things should be separated for a reason. – Robert Koritnik May 21 '11 at 09:03
  • @Robert - that is up to you, but that is orthogonal. Dapper can call stored procedures (including "out" parameters etc). The point is simply to provide efficient materialisation of your records. It isn't to tell you how or where to put your data logic, which is exactly why I avoided the "controller" discussion above. I've worked on SP environments, and SP-free environments. Both have pros and cons. But again: that is orthogonal to the point of dapper. Dapper will be perfectly happy calling SPROCs – Marc Gravell May 21 '11 at 13:20
  • @Robert - one very compelling advantae of SP-free is ease of deployment; and flexible SQL without having to do SQL generation inside SQL (for use with sp_executesql) - but either choice can be valid – Marc Gravell May 21 '11 at 13:23
  • @Mark: I totally agree with you. One side question: How likely is it that Dapper is going to be extended with useful stuff to PetaPoco (like auto split detection and auto references)? I'm asking sine this project was mainly written to speed up SO and succeeded in doing that. How likely is it that it will get extended for community reasons (not as a fork but as main product)? – Robert Koritnik May 21 '11 at 14:41
  • 1
    @Robert no problem; happy to consider anything that is logged as a feature suggestion. We have added a number of features due to *community* suggestion - some of which we now use internally, some we don't. – Marc Gravell May 21 '11 at 14:47
12

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.

BlackjacketMack
  • 5,472
  • 28
  • 32
  • Very nice solution for those one off queries (for reports etc). I've been looking for this and never knew about resource files. No more trying to wrangle queries inside a huge string. – CJ Edgerton Jan 14 '22 at 21:08
6

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.

Null511
  • 418
  • 6
  • 7
  • I don't remember if it was in that specific project. But I ended up using .SQL-files and a T4-generator that generated a static class of string constants. So I could get the query by `SQL.Filename` from wherever in my code. – Christian Wattengård Dec 01 '17 at 13:08
  • Ah, that's a clever approach; and provides an integration with Dapper (or anything really) without any modifications. The only part I'm impartial towards is relying on an external tool during my development process, but I suppose putting it in a build-event would mostly automate this process. Thank you for the follow-up and additional suggestion. – Null511 Dec 01 '17 at 14:23
  • As I am a Visual Studio user the T4-processing is integrated in the IDE. I would probably just check in the resulting .cs-files. So no extra build-step would - in that case - be needed. – Christian Wattengård Dec 08 '17 at 07:13