Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Where to put sql when using dapper?

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?

like image 836
Christian Wattengård Avatar asked May 13 '11 06:05

Christian Wattengård


People also ask

What is Dapper SQL?

Dapper is an object–relational mapping (ORM) product for the Microsoft . NET platform: it provides a framework for mapping an object-oriented domain model to a traditional relational database. Its purpose is to relieve the developer from a significant portion of relational data persistence-related programming tasks.

How do I set up Dapper?

In Visual Studio, create a new console project, and in Solution Explorer right-click References and select Manage NuGet Package Manager and search for Dapper and using the NuGet Package Manager Console command for the Nugget Package Manager “install-package dapper”, and this will install Dapper into your project.

What databases does Dapper support?

Dapper has no DB specific implementation details, it works across all . NET ADO providers including SQLite, SQL CE, Firebird, Oracle, MySQL, PostgreSQL and SQL Server. Dapper was created by team at Stack Overflow. To utilize Dapper, we add the package reference to the project with the dotnet tool.


3 Answers

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.

like image 88
Marc Gravell Avatar answered Sep 28 '22 08:09

Marc Gravell


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.

like image 21
BlackjacketMack Avatar answered Sep 28 '22 08:09

BlackjacketMack


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.

like image 28
Null511 Avatar answered Sep 28 '22 10:09

Null511