3

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

Questions:

  1. What design pattern should I use?
  2. I should be able to write multiple queries with a bunch of mix and match column's retrieved on every query. Maybe, not a good idea to map this type of resultset to POCO classes?
  3. I may have other queries with more or less the same type of columns needed to return from SQL resultset.
  4. Should I maintain seperate Entities just to support queries?

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.

Coder Absolute
  • 3,659
  • 3
  • 21
  • 33
  • 1
    What actual problem are you having? What's wrong with using dapper to map your result into a customer/user/userrole/employee ? – Rob Dec 14 '15 at 11:00
  • Well, what you are asking is doable! But, many of the field values for those entities are going to empty most of the time. Not sure, whether should I be really passing around those POCO objects? – Coder Absolute Dec 14 '15 at 11:03
  • The query gets executed in the Data Repository Layer. Then, customer/user/userrole/employee gets sent down to Business Manager, then further down that will be thrown to Web API. Later, it will be consumed by the consumer of that API. So, am not sure whether I should be really throwing around the almost empty object? I really don't know, am just asking the expert opinions. :) – Coder Absolute Dec 14 '15 at 11:05
  • 1
    have you consider? Transaction Script - Organizes business logic by procedures where each procedure handles a single request from the presentation. for reference http://stackoverflow.com/questions/575136/transactions-in-the-repository-pattern – Anil Dec 14 '15 at 11:09

2 Answers2

2

The pattern you are looking for is Data Mapper

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.

Implementation

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.

DB POCO Management

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.

theDmi
  • 15,621
  • 6
  • 62
  • 121
  • If I take the approach of creating one DB Poco per query then I may end up with many queries and Poco's as the application grows? How about mapping to the existing DB Poco's? – Coder Absolute Dec 16 '15 at 01:51
  • Well, if I chose an option of maintaining a separate POCO per Sql Query then Dapper would do the perfect job and there is no need of any mapper. – Coder Absolute Dec 16 '15 at 01:53
  • Of course reuse the DB POCOs for queries with the same result structure, yes. But only in that case. Don't build up inheritance hierarchies with POCOs just because POCO A has a property X, and POCO B has also a property X. So yes, there will be a lot of them, but they are dead simple, so I don't see a problem here. If Dapper already does the mapping for you, even better. – theDmi Dec 16 '15 at 07:38
  • Just to let you know, I have taken the CQRS approach and it is working out quite well for me so far. – Coder Absolute Jan 12 '16 at 13:53
0

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

TGN12
  • 93
  • 1
  • 8
  • I think, my question is misunderstood. I have edited the question, please give some thoughts. :) – Coder Absolute Dec 14 '15 at 12:35
  • you shouldn't alter your question this way as now my answer is useless to other users. you should use UPDATE header and then expand your question. Regarding your question, I agree with @theDmi about the data mapper pattern. but always prefer to do it myself rather using the automapper because of performance issues I've had with it. you'll still need to create POCO for each table and DTO for each business model. – TGN12 Dec 15 '15 at 19:29
  • Am sorry for altering it. Initially, I was having difficulty about describing my problem. But, appreciate your answer and there may be performance issues with AutoMapper. That's why am using SimpleMapper from this link: http://stackoverflow.com/questions/930433/apply-properties-values-from-one-object-to-another-of-the-same-type-automaticall – Coder Absolute Dec 16 '15 at 01:30