I'm trying create a .NET Core project and followed some guides to create a basic architecture

I'm not using an ORM like Entity Framework, I want to use raw SQL for my Maria database so I'm using the official MySQL Connector. I set up the database connection and dependency injection parts.

As you can see in the sample repositories linked above (sample here) the database access happens in the Application layer and is coupled to the business logic. The persistence layer only acts like a database configuration container.

I want to know about best practises on how to organize the database access logic.

  • Before executing queries I have to open a connection and close it afterwards. Should I do this in my Application command / query because then I can fire multiple queries with one connection?
  • How should I structure the database logic? Should I create one file per SQL statement which handles the sql query and the mapping from database result to domain object?

It would be awesome if you could also provide a sample folder structure for visual purposes.


2 Answers 2


ORMs like Entity Framework do not preclude you from using Raw SQL queries.

If Entity Framework is a bit too much for your liking, consider using Dapper or any of a number of different micro-frameworks. These tools remove a lot of the hassle of making a connection to the database and managing query parameters without encountering Little Bobby Tables, allowing you to focus your time and effort on writing the SQL queries.

ORMs also serve as your Data Access Layer, abstracting CRUD operations away from the more interesting Service Layer of your application. If you still feel like writing your own Data Access Layer, you can still do so. You will need one class and four methods for each table in your database (Create, Read, Update & Delete).

The way you manage your connection can vary. Depending on how quickly MariaDB can open connections and whether or not it caches connections, you might want to just open a connection for each query. You could also do it at the Aggregate level if you're practicing DDD. Or, you could simply open a connection and leave it open. It really all depends on your application and what you want to do with it.

One file per SQL statement sounds like a bit much. Try putting your SQL statements inside your entity classes; it's a very convenient place to stash them.

Folder structures are largely a matter of taste. There's no "standard," and everyone does them differently.

Further Reading
P of EAA : Data Mapper
P of EAA : Repository
P of EAA : Service Layer

  • 1
    Upvote for mentioning Martin Fowlers EAA Patterns ;-) May 25, 2020 at 19:43

If you are looking for best practices, well, the linked resources are pretty much a summary of the worst practices of our industry at the moment.

Unmaintainable anemic-centered "domain" with no behavior whatsoever, technical organization and package naming, devoid of any business-related knowledge. Complete regression into full procedural design. The sample app could have been written in COBOL (no offense), and would have made absolutely no difference to the design.

That being said, ditching the ORM is generally a good idea, helps make better design decisions. But to arrive at a sensible architecture, you'll have to move back the logic into the objects they belong to. Then, there are multiple options you can do:

  • Pass a transaction-type parameter around, which executes outside the objects, but objects can add database operations to it. (So open-close is not an issue of the objects themselves)
  • Return a transaction-type object that can be combined with others and executed later. In fp this would be a "monad", but that is just a fancy word for "it can be combined with others".
  • There should be no separate "persistence" structure, there is only the business structure, of which the persistence is just a feature of.
  • 1
    I'm all for ideas that challenge the status quo, but some justifications are still worthwhile. Ideas without some rationale to back them up are not very useful. May 25, 2020 at 21:07
  • The rationale is that the object-oriented style is much more maintainable. There are so many arguments to be made, for OO and against the procedural style above, that I've been writing about it for years, haven't even covered all the basics yet. May 26, 2020 at 10:42
  • OK, so I read your article, and the example you provide is that of a Point class, in fact the very same Point class that I use in my introductory programming classes. Deeply-nested inheritance hierarchies are common in graphical user interfaces, and while they are great at modeling UI, they're bad at almost everything else. May 26, 2020 at 13:46
  • When I read data from a database, I write a SQL statement. That data has to go somewhere. Where I now work, it used to go into a loosely-typed collection. Now it goes into DTOs using an ORM, your so-called Anemic Model. The ORM saves a ton of work, and the classes are code-generated anyway. May 26, 2020 at 14:00
  • Classes are Code + Data. I'm all for that fusion, where it is useful. Think of it this way: if you had a class that represented a bolt on an assembly line, you rightly wouldn't expect that bolt to know how to assemble itself onto a car, would you? If you had a dollar in a wallet, you wouldn't expect that dollar to know how to transfer itself into your checking account using double-ledger accounting, would you? May 26, 2020 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.