Having an embedded list as data

Question

I have a data table made of columns and rows (rows being records and columns being data items).  I want to be able to have a list as a data item (for example, a list of part numbers in an "items ordered" column for a single "order" row).  I realize that one can link tables within a table but I don't really need a table.  I just need to have a list of numbers as separate data pieces (rather than using a workaround involving a string with commas separating each item).  How would I go about doing this?  PS.  I am using .NET version 2.0 with Visual Studio 2005. Thanks for any help you can provide.

Answer 1

Hello Primem.

This is the DataSet forum. A dataset is a collection of relations and data tables. In fact is a datatable a database table reflecting collection which holds rows, but likewise a database a seperated columncollection which describes the items.

However, the row collection in the datatable is just an list.

If you need something else not related to this, then ask it in the forum of your progam language.

http://social.msdn.microsoft.com/Forums/en-US/vbgeneral

http://social.msdn.microsoft.com/Forums/en-US/csharpgeneral/threads

---

Success
Cor

Answer 2

I am aware this is the dataset forum.  I have created a strongly typed dataset with several datatables in it.  I am new to ADO but have determined it would be a more versatile and universal system than the one I was using before.  I also realize that a row collection in a strongly typed dataset is a list of what could be thought of as "records" with columns as "properties" that can be accessed directly.  This is what I have set up.  And up until now, all the data I have implemented in the table can be managed using a single value that can be created as a string (the value of each property for each record).  However, now I am faced with the need to create a single dimensional series of values of an undetermined size that need to be placed into a single "cell". 

In terms of xml, this concept is easy.  I could just place the series as a set of identical tags (with the appropriate data as values) in a container element but I am unfamiliar with how to set this up using the VS 2005 dataset designer.   Keep in mind that I don't need a sub table (with multiple columns per row).  I simply have one "column" of values that need to be assigned to a single cell.  (I probably could link a sub table of a single column but part of me thinks there has to be a simpler way). 

Answer 3

Hi primem0ver,

Welcome to MSDN Forum.

I'm not clear about the question, do you mean you want to convert a datatable to a List<T>? If I understand incorrectly, please feel free to correct me.

Best Regards

---

Allen Li [MSFT]
MSDN Community Support | Feedback to us

Answer 4

Be aware that XML supports complex datatypes, databases don't. So you cannot get everything done in a database (and therefore not in a dataset which reflect that) like in XML

However, know also that in .Net serializing a dataset to XML is plain easy by using WriteXML and you can put that in one cell as XML column.

---

Success
Cor

Answer 5

I appreciate all the attempts to help me so far.  However, we seem to be reading too much into my request and making it more complicated that it really is.

Remember I am new to database design and so I am learning all the terminology as I go.  Let me put this in terms of database terminology I just learned.

I am designing a database that involves clients, products, and "packages" among other entities.  Packages are a way to purchase products at a discounted rate instead of purchasing each product individually.  They aren't quite the same as an order because an order can consist of a "package" and an additional product.

Packages and products have a many to many relationship like an order but orders in the database examples I have seen have an "orderproduct" entity (defining the many to many relationship using an intermediate entity) that serves as an intermediate table with two columns: order id and product id.  But products don't really need to keep track of their orders so to me that seems like extraneous data.  That is particularly true here.  All I need is to have a list of primary keys that refer to the products that are part of the package deal.  Each package is different and so the number of products within it varies.  Essentially it is a single columned table that is referred to by reference within the datatable that represents the existing package, i.e. a list of product id's.  

This seems to be beyond the scope of traditional database design however, in spite of the fact that the whole purpose of database design is to avoid redundancy.  Instead of embedding a list of id's (of type int) which theoretically could be four bytes each, the cell must refer to another table, which in turn lists not only the id's of the parts, but the order as well. 

Am I wrong?  Is there a way to do this using traditional database design without having to invent a new entity and create its corresponding table?  The idea is simple, but in terms of database design it seems unnecessarily complicated.

Answer 6

It is not in the Relational DataBase design where everything is based on relations between tables. 

To do what you want you need to create extra tables (with ID's), but in your original question you told you want to avoid that.

Be aware there have been databases in which we were able to add list types to items (complex data types), but it is already for decades not anymore in the ones currently generally used.  I've always found that a pity, but if I tell that then there is mostly told to me that I don't understood the relational concept.

AFAIK has MS Office Access it also outside the OleDB concept available. 

---

Success
Cor

Answer 7

Cor had a good idea when he mentioned storing XML. Your packages table could store all the product IDs for a package as XML in one varchar column. Storing it as XML allows you to easily use the collection of IDs in your application as a DataSet/DataTable:

// To initially create a package, create and fill a DataSet
DataSet dsProducts = new DataSet("PackageProducts");
dsProducts.Tables.Add("ProductIDs");
dsProducts.Tables[0].Columns.Add("ProductID", typeof(int));

// Fill that DataTable by adding a row for each applicable ProductID
DataTable dt = dsProducts.Tables[0];
DataRow row = dt.NewRow();
row["ProductID"] = 666;
dt.Rows.Add(row);
row = dt.NewRow();
row["ProductID"] = 777;
dt.Rows.Add(row);
// etc.etc. adding all the necessary product ids

// now, add the product ids to the DataTable that contains your packages
// for example, I'm assuming a schema something like PackageID, PackageName, ProductIDs
// This dtPackage DataTable should be part of a different DataSet, not the dsProducts DataSet
row = dtPackage.NewRow();
row["PackageName"] = "My Cool Package";
row["ProductIDs"] = dsProducts.GetXml();
dtPackage.Rows.Add(row);
// Obviously, update your database at some point with the new Package

// Now, to work with this list of Product IDs, you'd deserialize the XML like this
DataSet dsProcess;
foreach (DataRow rowID in dtPackage.Rows)
{
    StringReader sr = new StringReader(rowID["ProductIDs"].ToString());
    dsProcess.ReadXml(sr, XmlReadMode.InferSchema);
    // Now, dsProcess will contain a DataTable that contains a row for each ProductID
    // And you can do whatever you need to do for each ID
}

Does this look like it might work for you?

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 8

I think Cor is right in that using the structures available within relational databases, I really don't have a choice but to add a table as you suggest Bonnie.  In addition (and this is annoying... but I suppose necessary to keep the database scalable), I will need to include a two columned table since defining a table within a table is impossible with relational databases.  It is similar to what you have suggested Bonnie but also incorporate a reference to the original package as well for each entry.

Answer 9

I wasn't talking about adding a new database table though ... I don't know if that's what you thought I meant.

The "list" of ProductIDs for a package is simply stored as XML in the already existing Packages database table (in the column "ProductIDs"). That XML is created using a DataSet/DataTable (dsProducts in my example above), but that DataTable is not persisted to the database as another table ... only it's XML is stored. I simply suggested using a DataTable for ease of creating the XML and an easy way to manipulate the data once you retrieve the XML from the Packages database table (by  deserializing that XML back into an in-memory DataSet).

Database schema for your Packages database table:

PackageID     int
PackageName   varchar(100)
ProductIDs    varchar(max)

I don't know if you're using SQL Server or not ... if not, that varchar(max) data type above is the same as "text". You could also change it to simply be a larger varchar.

Does it make sense?

---

~~Bonnie Berent DeWitt [C# MVP]

geek-goddess-bonnie.blogspot.com