CODEWITHSUNDEEP
c#databaselistsql-server-ce
Fractalife
Fractalife

Reputation: 87

How do I store a List<> as a field in a database? - C# (SQL Server Compact Edition)

I have an object whose fields I would like to store in a database. I will be using SQL Server Compact Edition (with Visual C# Express 2010). For the record, I'm fairly new to programming with databases (and databases in general). This program will be used every day to read emails, process the orders inside them, store them, and access them when necessary to help with completing the orders. The list of orders is going to become much to large to store in a List, write to a file, create the List from a file, etc. The problem is that each order contains a list of the items purchased. I am aware that I can serialize the list in binary or XML, and use that data as the field. However, this prevents me from searching/selecting based on that list. For instance, if I wanted to find an order based on what items are in it, or see how many times a particular item has been purchased. Since the list will be of arbitrary size, I can't just create a bunch of fields and fill only the ones I need (which in my opinion, is a bad idea anyway).

While writing this I realized a mistake. If I serialize the list again, I could compare the serialized data to find the same list again (though, this assumes that the same data is serialized the same way each time). However, I'm still prevented from finding any particular item.

So, is there any way to store the list of items, in a fixed number of fields (preferably 1) and still be able to search its contents with a query (I will most likely be using LINQ)?

Edit: To address what I've gotten so far: first, thanks! I'm starting to piece together what I have to do, but I'm not quite there. The consensus seems to be to have a table for each set of items. Does that mean I'd be creating thousands of tables each month?

After re-reading my question I realize I have to be more clear. As the order comes in, I parse the data and store it in an Order object, which consists of the customer's information, and the list of items. Here is a simplified version of what I'm trying to store:

class Order{
    private DateTime date;
    private String orderNumber;
    private String buyerName;
    private List<Item> items;
    private String address;
}

class Item{
    private String itemCode;
    private String description;
    private int quantity;
}

So would I have to create a new table for each List I create, or am I missing something?

Upvotes: 1

Views: 11199

Answers (3)

wilsjd
wilsjd

Reputation: 2258

Update: This may be a helpful reference for One-to-many relationships if you are new to the subject (especially check out the second and most upvoted answer): Create a one to many relationship using SQL Server

You'll want to create a new table in the database for your line items then create a foreign key relationship to the order table. It will be a one to many relationship. Some thing like the following - obviously you'll need to create a FK relationship, but you get the gist.

Existing

CREATE TABLE Order (
  OrderID INT,
  PONumber VARCHAR,
  ItemsList VARCHAR
)

New

CREATE TABLE Order (
  OrderID INT,
  PONumber VARCHAR
)

CREATE TABLE LineItem(
  LineItemID INT,
  Description VARCHAR,
  Quantity INT,
  SequenceNumber INT,
  OrderID INT -- <--- Important one here
)

Note that if you want to create just a simple lookup, the relationship would go the other way.

Upvotes: 5

jugg1es
jugg1es

Reputation: 1600

This can be solved in several ways, but if you want to be able to query the data directly, you should redesign your database with a seperate table where you store your list data with a reference ID. After all, databases are all about data lists.

Upvotes: 0

alex
alex

Reputation: 12654

Instead of storing the list as a field, you can create a separate table that hold it's items. And records in this ListTable will have a field pointing to record ID in your original table. That way you can write various queries afterwards.

Upvotes: 0

Related Questions