0

I have a problem where I data for an object, for example billing file, where the data elements are different from client to client. More specifically the number of fields within the data and of different names. I am looking for a solution when working with the objects in C#.

Currently I have created tables for each client. The fields are specific to the client and I use a mapping process when uploading data. I also have dynamic queries in SQL Server to handle all crud processes. It all works pretty well but I believe there is a better solution and I believe saving Json data would be one of them. Pulling the Data I first query the headers of the table and then map the data to those headers for data grids and such. Again, I already have a working solution, but I believe there is a better solution and I am looking for suggestions with examples. By the way, I have thought about dynamic object, in C#, but it would appear you have to know what fields of the object are upfront.

Troy Crowe
  • 123
  • 2
  • 12
  • ExpandoObject might be the droid you are looking for. .NET is strongly typed and that includes wich Properties/Fields any class has. ExpandoObject is there so we can work with weakly typed stuff like JSON. – Christopher Jan 12 '19 at 17:53
  • Christopher, thanks for your answer. I have thought about using the ExpandoObject for the reason you have stated. However, I am not sure that is the right solution. As I stated, each billing object has different fields and a different range of them. The ExpandoObject provides flexibility but you still have to identify some specifics to use them. I am thinking of using strick json throughout along with preset fields, that are industry specific to the type of app, and requiring client fields be mapped to them at some point where those fields are needed for business logic. Good suggestion. – Troy Crowe Jan 12 '19 at 21:40

1 Answers1

0

I suggest that you should create mapping table, but there is no need to use something like dynamic sql, here are tables:

create table d_billing_object -- one row here means one field from your question
(
     id int not null identity (1, 1) primary key
    ,name nvarchar(255) not null
)
create table d_billing_client
(
     id int not null identity (1, 1) primary key
    ,name nvarchar(255) not null
)
create table d_billing_mapping 
(
     billing_client_id int not null
    ,client_billing_object_id int not null
    ,billing_object_id int not null
    ,constraint PK_d_billing_mapping primary key (billing_client_id, client_billing_object_id, billing_object_id)
    ,constraint FK_d_billing_mapping_d_billing_object foreign key (client_billing_object_id) references d_billing_object (id)
    ,constraint FK_d_billing_mapping_d_billing_object_2 foreign key (billing_object_id) references d_billing_object (id)
    ,constraint FK_d_billing_mapping_d_billing_client foreign key (billing_client_id) references d_billing_client (id)
)

After that you just need to create all billing objects and use them in mapping table for all clients you have.

Alex Sham
  • 489
  • 7
  • 14
  • Alex, thanks for your answer. Your solution is interesting and would be useful moving to saving the data as Json objects. I am not concerned with how to implement the database piece. I was asking for suggestions on the service or application layer. I can use strict Json for the UI as I am doing now. Getting around the dynamic queries to add and update is pretty easy changing to Json as well. Good suggestion however. – Troy Crowe Jan 12 '19 at 21:56