C# asp.net how to store Object containing array in SQL

[January Memes]

Hello, 
I have a Class CLASS1 containing multiple Strings, a String array and an Array of CLASS2
CLASS2 contains a String and an Integer, I currently store CLASS1 objects by Serializing them into XML and Deserializing them when I need to read them, but I want to be able to filter them more easily using SQL how can I store them in a way that I can read and filter them using one SELECT command, like I would when deserializing the xml document. The size of the Arrays isn't fixed   

 

I tried storing the two arrays in their own tables conecting them using foreign keys to the table were the rest of Class1 values are stored, but when I read them I get an extra output for every Element in each of the arrays, but I want just one output containing every element of both arrays and the other values from Class1

 

[Franck]

Serializing / deserializing have nothing to do with querying the object. If it's on asp.net just load the data once static so all session can use it and just use LINQ to query. You can either use the lambda or the SQL version to filter tru your object

[MisterWhite]

First of all don't store XML or JSON in a column like that unless there is a good reason for that. Querying/filtering/ordering is not possible in SQL if you do it like that.

 

Also showing class code (that it contains ints. strings etc.) is way easier to read and understand rather than reading text. Furthermore I don't quite understand the output you except, examples would help here too.

 

Say you have ClassA (coded as I understood your text)

class ClassA
{
  public string Exampple1 {get; set;}
  public string Exampple2 {get; set;}
  public string[] ExampleArray {get; set;}
  public ClassB[] OtherClassArray {get; set;}
}

And

class ClassB
{
  public string ExamppleB {get; set;}
  public int Example2B {get; set;}
}

 

Now if you want to store this in an SQL database you need at least three tables:

- One for ClassA containing and Id, string1, string2. We will later use the unique Id to get other arrays

- One for ExampleArray values, it would contain something like and Id, foreign key to the parent object (ClassA) and the actual string value.

- Last one for OtherClassArray values, again with Id, foreign key to ClassA, and the two values

 

Since we have a valid SQL schema we can proceed with storing and retrieving the data and of course filtering. Writing the SQL to retrieve all we want is a bit cumbersome, here is where I'd suggest using EntityFramework for such a setup. By using EF (EntityFramework) you wouldn't need to write SQL to create your own tables - it would do it for you based on the class objects we have created (google code first entity framework migrations). Retrieving the data would also be taken care of by EF. If using a framework is out of the question you'd need to write SQL to retrieve all the data yourself and that would require to query the first ClassA table, once you have that you can query ExampleArray and OtherClassArray for the array values by joining on the foreign key.

[Franck]

On 2/7/2021 at 6:49 AM, MisterWhite said:

First of all don't store XML or JSON in a column like that unless there is a good reason for that. Querying/filtering/ordering is not possible in SQL if you do it like that.

Actually it is possible. I do not recommend it but it's doable

[MisterWhite]

On 2/8/2021 at 3:38 PM, Franck said:

Actually it is possible. I do not recommend it but it's doable

Yes, sorry about that. In case you use a specific field it's doable. I assumed OP meant varchar type.

PostgreSQL is in fact pretty performant with json types.