Each table in my database has it's own
POCO class. Now, I have started to write complex SQL joins and the query resultset should be mapped to some Entity which can be sent to the Business Manager (another layer) for further processing. For an example, imagine my query returns the columns something like this (table name is prefixed with column name for simplicity's sake):
Customer.CustomerId, Customer.CustomerName, Customer.CustomerAddress, [User].UserId, [User].UserName, [User].FirstName, [User].LastName, UserRole.RoleId, UserRole.RoleName, Employee.EmployeeId, Employee.EmployeeName, Employee.JoinDate, MAX(AuditTrail.LastLoginDate) etc
Note: Am using Dapper ORM to talk to SQL Server 2012 with .NET 4.5 Framework (C#). Please let know, if the question is unclear.
A layer of Mappers (473) that moves data between objects and a database while keeping them independent of each other and the mapper itself.
What this means is that the things outside of your persistence layer should not know anything about the database - neither ORM frameworks nor POCOs that mimic a table structure.
So in the context of DDD this means that you use a domain model and map the DB POCOs to that domain model. The data mapper is responsible for that mapping.
You can of course do the mapping yourself: Create a method in the data mapper that takes a DB POCO and returns the corresponding domain object. If you have a lot of mappings, this requires a lot of boilerplate code, however.
To mitigate this, use an object-to-object mapping library like AutoMapper. It reduces the amount of code you write for a mapping, and it also makes the mappings more maintainable. The drawback is that you have to learn a new library and take a dependency on it.
Your DB POCOs (called DTOs by many) should serve one purpose only: Describe the structure of a table or a result set in the C# world - nothing more, nothing less. So yes, create one DB POCO per query that returns a different result structure.
1) The repository and Unit of work patterns are suitable for dealing with database CRUD operations. 2 + 4) DTO and POCO is basically the same thing. where DTO is usually a POCO that is used to store data. It is a good practice to use them because that way you are always know what you have in the object to use. If you build you're DTO according to business requirements and not DB requirements then in most of the time you won't need to change it at all 3) what is the question?
P.S. Regarding using SQL in a .Net environment I would recommend using the not so new SSDT database project. It will help you to keep track of your schema