none
Create an object or alternative approach? RRS feed

  • Question

  • I have to process cars, where car is at the top-level. At sub-level it has 27 different propertysets like small_tire, medium_tire, large_tire and so on. Each propertyset contains between 2 and 30 properties.

    Sub-level properties can be none or X. X can be more than 27 as one car can have more than one small_tire. In the below picture X=3.
    The result will be serialized over JSON to clients/browsers.

    Above is an example of how a car could look. I know, my drawing skills rock :-)

    The data is imported from external systems via JSON, stored and processed in SQL. All the info ultimately resides in SQL so that's where the information for this question is fetched from.

    What would be a good way of achieving this? If you have a code example of something similar, that would be great since that usually gets the point across. I'm only asking about the object/alternative approach, not the serialization. Yes, I'm a c# newbie.

    Cheers!

    Tuesday, September 10, 2019 10:06 AM

Answers

  • If you do not need to manipulate the data, but only pick it up from the SQL server and send it to the browser, and you have a recent version of SQL Server, then there is a very easy solution: Simply send to the server a "select" statement that joins the tables to extract the required fields, and add a "FOR JSON" clause at the end of the query. This will return all the chosen properties as a single block of json that you can directly send to the browser. This requires essentially no C# code (except for the small bit to send the query and retrieve the results).

    https://docs.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-2017

    • Marked as answer by IgotStuckAgain Tuesday, September 10, 2019 1:19 PM
    Tuesday, September 10, 2019 1:06 PM
    Moderator
  • Yes, you can create a class in C#. Fill it with data, and then serialize it as JSON and send the result to the browser. If you are creating a WebApi, then the serialization will be done automatically for you if you simply return the class from the action method.

    Don't worry about creating a List of objects, the overhead will be insignificant. In fact, this is the way to go when you need the class to contain a variable number of members: you use a list to contain these members.

    Ideally you want to avoid to use "object" as the member type for the list. If it has to contain different types of members, create a base class to use as the data type for the list and then make child classes for the various types that you need to store in the list.

    • Marked as answer by IgotStuckAgain Friday, September 13, 2019 2:46 PM
    Tuesday, September 10, 2019 2:10 PM
    Moderator

All replies

  • If you do not need to manipulate the data, but only pick it up from the SQL server and send it to the browser, and you have a recent version of SQL Server, then there is a very easy solution: Simply send to the server a "select" statement that joins the tables to extract the required fields, and add a "FOR JSON" clause at the end of the query. This will return all the chosen properties as a single block of json that you can directly send to the browser. This requires essentially no C# code (except for the small bit to send the query and retrieve the results).

    https://docs.microsoft.com/en-us/sql/relational-databases/json/format-query-results-as-json-with-for-json-sql-server?view=sql-server-2017

    • Marked as answer by IgotStuckAgain Tuesday, September 10, 2019 1:19 PM
    Tuesday, September 10, 2019 1:06 PM
    Moderator
  • That  would certainly be one way of handling it since I'm running SQL 2017, it's an alternative approach. I discarded that approach when searching for a solution since the SQL syntax will be a long story. But maybe a structured monster sproc is the right way...I only need to send in the id to SQL for everything to work.

    Is it hard to create an object like the one described, or is it ineffective?
    Currently looking at lists of objects, but that will probably mean a little bit of overhead.

    I'm looking for the easiest long term approach. For me, that might just be mocking about in SQL, but now I'm curious as to how to solve it in C# as well.

    I should learn something new today as well!


    Tuesday, September 10, 2019 1:19 PM
  • Yes, you can create a class in C#. Fill it with data, and then serialize it as JSON and send the result to the browser. If you are creating a WebApi, then the serialization will be done automatically for you if you simply return the class from the action method.

    Don't worry about creating a List of objects, the overhead will be insignificant. In fact, this is the way to go when you need the class to contain a variable number of members: you use a list to contain these members.

    Ideally you want to avoid to use "object" as the member type for the list. If it has to contain different types of members, create a base class to use as the data type for the list and then make child classes for the various types that you need to store in the list.

    • Marked as answer by IgotStuckAgain Friday, September 13, 2019 2:46 PM
    Tuesday, September 10, 2019 2:10 PM
    Moderator
  • Hello again!

    I went with option number two, a class in C#. As a concept it's now up and running.

    Thanks again, have a great weekend and cheers!

    Friday, September 13, 2019 2:48 PM