Design issues with DTO's etc

Question

Hello, I am designing a web application in c# that mostly talks with the database. I have created a DTO for each table in the database. Now for each Table there is a custom table repository class that does all the saving and fetching pertaining to that particular table, and I'm using stored procedures for that.

The issue is now some stored procedures will actually do a inner join operation and then send a custom output which is a combination of 2 tables.

Therefore I'm not able to store the data in a single DTO object.

If there are any basic operations which are mostly done on a single table then things are fine.. I can call the table dto object for both inserts and fetching from the database. But when joins are there in stored procedures and during certain situations we update multiple tables.

How should we design the app?

Please Help

Thanks

Answer 1

Sounds like you may want to re-think having a DTO that directly corresponds to each table in the database.

Rather, a DTO should correspond to something logical in the real-world model that's used behind the Web site. So, for instance, a Customer object might have a collection of Order IDs within it, even though these would be logically stored in two tables in the database.

There is nothing wrong (in fact it's often essential) to have DTO referencing other DTOs, so for instance you could have a Customer class that points to a list of Address classes.

If things get more complex you will most likely want to consider introducing another layer at this point with true "domain objects" that map to your DTOs, which from the sound of it are "persistence objects".