-1

I make & use a datatable with a column through

test_dt.Columns.Add("list_paragraphs", typeof(List<string>));

In this way, I just need 1 row to save List<string> object which has many string items.

Is there a way to do this for Microsoft SQL Server 2005?

If available, the size of column is limited like 4000 (varchar)? Or enough like datatable?

I've searched for hours but there are only some threads talking about inserting a list with a loop row by row.

James Z
  • 12,209
  • 10
  • 24
  • 44
Kay Lee
  • 922
  • 1
  • 12
  • 40
  • do you want to have in one SQL column to have the whole list content? – styx Mar 18 '18 at 10:28
  • @styx, yes, in only 1 column, row. It's available in DataTable conveniently. – Kay Lee Mar 18 '18 at 10:29
  • You can encode it as CSV and then decode it back after reading it from the database. – Zoran Horvat Mar 18 '18 at 10:30
  • @ZoranHorvat, Thanks for your advice. I know some way like saving as xml but just doubt if there's direct, convenient way like datatable.. – Kay Lee Mar 18 '18 at 10:33
  • You could also use a blob but are you sure you want to do that ? It's against the first normal form. – Merlin Mar 18 '18 at 10:33
  • you can also create a reference table and save the list name or indicator in the main table and on the reference table save the actual values – styx Mar 18 '18 at 10:38
  • @Merlin, Thanks for your advice. If there's no convenient way, I can but I have to do with a complicated way. – Kay Lee Mar 18 '18 at 10:42
  • @styx, Many thanks for your idea but seems more complicated.... – Kay Lee Mar 18 '18 at 10:43
  • @styx idea is actually more complicated but its the recommended way to go. You are using a relational database, in which you do no store aggregate of data in a simple row, that's bad practice. I recommend you read the first three normal forms and go for an association table. – Merlin Mar 18 '18 at 10:45
  • @KayLee not that complicated :) – styx Mar 18 '18 at 10:50
  • @styx, I deeply appreciate your kindness. Though I use relational DB, the aggregated paragraphs is only for 1 product as I'm working on insert paper of medicines. A obvious fact I've learned from SQL experts is there's no convenient way. Thank you ! :) – Kay Lee Mar 18 '18 at 11:06
  • @Merlin, I deeply appreciate your kindness. Though I use relational DB, the aggregated paragraphs is only for 1 product as I'm working on insert paper of medicines. A obvious fact I've learned from SQL experts is there's no convenient way. Thank you ! :) – Kay Lee Mar 18 '18 at 11:07
  • @Crowcoder, I'm using it in a DataTable. And .NET Framework version is 4.7 – Kay Lee Mar 18 '18 at 11:27
  • @KayLee yes but that is not a fully supported type for a DataColumn, which is part of makes up a DataTable. You lose functionality, though I guess if it works for you then you don't need that functionality. – Crowcoder Mar 18 '18 at 11:36
  • @Crowcoder, thanks for your information. I've made a decision to go with xml in regard of my situation. Thanks :) – Kay Lee Mar 18 '18 at 12:05

1 Answers1

2

Yuo have several choices to store your data, but more important is the question: What are you going to do with this?

You might read this related question to find some general hints, in which cases storing many values as one might be a good idea - and when you should avoid this.

You can

  • Use some delimiter and store all values as CSV
    • tight in storage
    • awful to read from
  • Use some common format like XML or JSON (not supported in v2005)
    • rather tight in storage
    • better to read, but still clumsy
  • Use a related side table, where you store your values together with a foreign-key-constraint one-by-one
    • A small overhead while storing
    • But - almost for certain! - the best way to go in your case.

A obvious fact I've learned from SQL experts is there's no convenient way.

No, the convenient way is the related table. Any other approach is something you should have a really good reason to do this.

Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Many thanks for your support ! I've made a decision to go with xml. I also showed a thank in the useful link there. :) – Kay Lee Mar 18 '18 at 12:02