SQLite3 -- storing variable size arraylists in C#

Question

I've never attempted to store any objects in my SQLite database and doing some searching on the subject didn't help. I'm using VS 2012, the NuGet SQLite DLLs, and C# with .NET 4.5.

Here are the available data types I can see and presumably, use: integer, int, smallint, tinyint, bigint, bit, varchar(50), nvarchar(50), text, ntext, image, money, float, real, decimal, numeric(18,0), char(10), nchar(10), datetime, guid.

I have graph points (x, y) that I must store for each graph. There are a variable amount. I store the values into an ArrayList currently and am having a hard time trying to figure out how to store them...

If I create a dedicated table just for each graph that stores all it's (x, y) points, how do I specify a variable amount of (x, y) coordinates to save within the graph table? Is there an easier way using some third party library? Can anyone else think of another simpler solution perhaps using the technology I currently have available?

Thanks!

• Trevor Elliott: Personally I would store them as a JSON array in a string (varchar(MAX)) field if it doesn't make sense to store each one as a record in its own table.
• Tarik: If you have lots of points, you might want to store your graph in a BLOB.
• CODe: Is BLOB available to me? It's not in my designer and won't show up as a recognized data type. @TrevorElliott Can you provide a link or two to get me started? There is so much information on the web that doesn't pertain to my project it's hard to find what does.
• CODe: @TrevorElliot Additionally, I had assumed I don't have varchar(max) available to me, and only varchar(50) which would limit the size of my JSON array. I have some pretty big graphs with a LOT of (x, y) points.

Answer 1

You could serialize the object as suggested - though that kind of kills the point of a relational database.

Or you can use a DB relation.

Graph
-id integer
-varchar(50) name
GraphPoints
-GraphID int FK to Graph
-x int
-y int

Then just join the two, and you can get all points for the table:

SELECT * FROM Graph g INNER JOIN GraphPoints gp on g.id = gp.GraphID
WHERE g.id = somegraphid

• CODe: I've debated doing this, but I've always ran into the same problem. How do I represent a variable number of (x, y) coordinates in a single table? By the way, I'm a rookie when it comes to SQL syntax, so forgive me if I'm missing something in your answer.
• Mathieson: There's one coordinate per row here. So the number of results returned will vary, and when reading it you will iterate over it and add it to a in-memory collection with variable length (like an ArrayList, or better a generic collection).
• CODe: Ah, I see now. Granted, serializing as you say would be against the grain for SQLite as it's relational, but would it make more sense to do it that way for this case? I'm going back and forth between the two, this seems to be more work and more convoluted in the end. I wish someone would post some more information about the JSON array to a varchar so I could compare! What would you recommend?
• Mathieson: If you're going to need many different kinds of objects stored, and don't want to do much reporting on them, but just want storage, JSON is fine. But if it's just one type of object, why pay the price of serializing and deserializing every time? I'd go with the graph table myself.

Answer 2

I ended up using JSON since it was much easier to manage and far less code than creating an entire table just for my graph coordinates, this seemed to be the best route for my situation. Hopefully this helps someone in the future!

[Serializable]
public class Coordinate
{
 public string x { get; set; }
 public string y { get; set; }
 public Coordinate() {}
}
public class CoordinateList
{
 public List<Coordinate> Coordinate { get; set; }
}

And here's a simple working example on how to create and serialize the above class into a JSON string you can store as a TEXT or VARCHAR type in SQLite 3. Also included is how to deserialize.

CoordinateList list = new CoordinateList();
list.Coordinate = new List<Coordinate>();
Coordinate cord = new Coordinate();
cord.x = "1";
cord.y = "10";
list.Coordinate.Add(cord);
list.Coordinate.Add(cord);
list.Coordinate.Add(cord);
JavaScriptSerializer serializer = new JavaScriptSerializer();
String jsonStr = serializer.Serialize(list);
MessageBox.Show("json:" + jsonStr);
list = serializer.Deserialize<CoordinateList>(jsonStr);
foreach (Coordinate c in list.Coordinate)
{
 MessageBox.Show("x: " + c.x + ", y: " + c.y);
}

P.S. The JavaScriptSerializer is included in the System.Web.Extentions reference. I was looking for the System.Web.Script.Serialization namespace forever and couldn't find it.

Special thanks to these ridiculously hard to find links:

Cannot find JavaScriptSerializer in .Net 4.0

Json string deserialized to array list of objects