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.