I have seen lots of articles but none really help me. That is because I want to use dapper as a DAL. Should I create repositories with special functions? Like getStaffActive()?
If I use repositories
- I can implement with dapper-extension a generic crud
- I have no idea how to handle database connection. Where to open the connection? If I do this at every function then how am I supposed to use transaction scope?
Somehow the repositories I work with should share a connection in order transaction to work. But how to do this? Openning connection in BLL?
If I use queries and execute them directly then still the same thing.
It looks like you are missing the concept of Unit of Work pattern in your DAL design.
There are nice posts that explaining this concepts here:
- Unit of Work pattern – managing parent child relationships
- The Unit Of Work Pattern And Persistence Ignorance
- Unit of Work Pattern
1
(Clarification: this answer was written when the question was on Stack Overflow)
All of these questions are architectural. How you architect your application is complex, and depends on many aspects. There are lots of ways, all valid, of doing this. Some depend each-other, so if one architectural decision is “use transaction scope”, then your connection management strategy needs to take that into account.
Dapper, however, is very deliberately not architectural. It is pure implementation detail. It tries very hard not to invade any of your code or force any design decisions upon you. Your architectural decision might be “use dapper”, or it might be “get that data from the DB; how we get it is an implementation detail that we don’t care about, because it is not tied to any other design components”.
Basically: no-one can answer this except you. It might be a reasonable question for programmers.SE, which has a more architectural (rather than implementation) bias. We could migrate the question there if you like.
1