C# – Deep SQL Relationships to a C# Object Model

cdesign-patternssql

I have a database with deep table to table relationships

for example

Clients (one to many) -> ClientData (one to many) -> ClientJob (one to many) -> ClientProcess (one to many) -> ClientStep... so on.

And these aren't one branch trees either. So Clients also has a one to many relationship with another tree of tables. The database has a very large number of tables and a bunch of relationships between them.

The C# models I have are similar in design to this

public class Client {

   //base client data
   public string ClientId { get; set; }

   public string ClientName { get; set; }
   //etc...

   //Objects with the one to many relationship
   public List<ClientData> {get; set;}

   public List<ClientLocation> {get; set;}

   //etc..
}

And the ClientData object has it's base data and lists of objects representative of it's relational database relationships and so on.

I have been directed to not use Entity Framework, and instead I must create hand written views or stored procedures for data access.

My question is, is there a design pattern I can employ here that makes this SQL/C# structure efficient and maintainable?

I would like the ability to also query substructures, so instead of always getting a complete Client and all of it's related objects, I can get a specific ClientData and all of it's nested objects but not the Client which owns it.

Best Answer

Sounds like somebody wants you to write a lot of SQL.

I'm assuming here only reading data since that is all the question specifically mentions.

In the past, I have created an AutoMapper-like component which would map a DataRow to a class by examining the class's property names with reflection, then assign matching DataTable columns of same name and similar type to the class's properties.

Then building on that only a little, you can convert an entire DataTable to a list of objects. I believe AutoMapper will do this already.

The query to populate an entire object tree usually returned multiple DataTables in the DataSet.

-- data table 0
SELECT ... FROM Client WHERE ClientId = @clientId;
-- data table 1
SELECT ... FROM ClientData WHERE ClientId = @clientId;
-- data table 2
SELECT b.*
FROM ClientData a
JOIN ClientJob b ON b.ClientDataId = a.ClientDataId
WHERE a.ClientId = @clientId;
-- etc.

Then building on that and with some up-front configuration, you can convert an entire DataSet into an object tree.

var sqlToObjectConfig = new []
{
    typeof(Client),
    typeof(ClientData),
    typeof(ClientJob)
};

// call your converter
ConvertToObject<Client>(sqlToObjectConfig, dataSet);
// it will use the config to know the type of each DataTable (by index)

So your normal steps would be:

  1. Develop a query to get the data you want
  2. Define a configuration array for the query's conversion to objects
  3. Define a method to execute the query and convert

You could relatively easily query a specific branch like ClientJobs on down.

Related Topic