凡用短小精悍的时候把SQL？ [英] Where to put sql when using dapper?

问题描述

我使用的是短小精悍在工作MVC3项目，我喜欢它。然而，如何是你应该用短小精悍的时候层应用？目前，我只是有我所有的SQL控制器直接塞到（巴掌的），但我想使一类的静态字符串。所以我可以做

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)

当使用

你如何保存你的SQL短小精悍？

How do you store your sql when using dapper?

推荐答案

我想说的放在哪里，你将投入相当于LINQ查询的SQL，或DataContext.ExecuteQuery的sql 的。至于其中，这是......嗯，这是给你的，取决于你想要多少分。

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.

不过，我个人认为没有受益于查询＆LT隐藏SQL中的一个单独的类了; T＆GT; 电话 - 你的希望的看他们在上下文中，因此您可以轻松地验证数据（而事实上，该参数）。您可能还可以构建就地查询（参数化仍然）。但是对于一个普通静态查询我会保持TSQL为近code文字，除非我有很好的理由以需要抽象，即

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});

（注意也替代的扩展方法的用法在上面）

IMO，在上面的关键在于它是的极不可能的，你将永远重复使用该查询 的任何其他地方的 - 所述逻辑将代替被放入一个方法，并从多个地方调用的方法。所以，你可能使用躲在中央包装查询的唯一另外一个原因是，如果你需要支持不同的数据库供应商（具有不同的SQL方言）。这是罕见的比人做出来。

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.

这篇关于凡用短小精悍的时候把SQL？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！