salarcode
Bois for serializing objects to store in SQLite blob?
Is serializing objects for storing in a SQLite blob column a good use case for Bois? Would that be better/faster than using some kind of ORM?
It could be a good use case for it, more space saving than storing the object serialized as json .. Using an ORM or using plain sql has nothing to do with the datatype of the column or the way the data is serialized...
In my case, the blob would be a list of lists or dictionary of lists. The list items or dictionary values may be small objects. The first few columns will be indexed and used to identify the blob. I'll then submit a query, identify the blobs I need (a thousand or so) and deserialize them. This last step could be done in parallel. Does it sound like a reasonable approach? I created a simple experiment but cannot get it to compile. I'd welcome suggestions. I use mem.ToString() before inserting the blob and can't convert from SQLite.Blob to byte[] to deserialize afterwards. I commented the problematic lines. https://paste.mod.gg/vupyxpspcvfn/0
Hello delvewell, looks like you are trying to solve some general programming issues; specifically related to SQLite data retrieval. Some quick comments:
- What library references (and extensions) are you using? I can't find the 'GetBlob' method anywhere.
- Your last line of code needs to define the Type of what you're expecting to deserialize to:
e.g.
MemoryStream ms = new MemoryStream(result);
List<int> blob = boisSerializer.Deserialize<List<int>>(ms);
@mousetrap-systems . I was certain that GetBlob came from System.Data.SqlClient , but I don't see it there. So, that is definitely suspect! I'll define the Type in the last line of code. I missed that. Now I have some direction. Thank you.
Hi @delvewell , your question is more clear with the example of code. One other remark : for the SQLIte Parameter @pData, you should probably specify the datatype of the parameter on insert , something like :
new SqliteParameter()
{
ParameterName = "@pData",
Value = pdata_bois,
DbType = System.Data.DbType.Binary
}
As for the retrieval, not sure from where the extension "getBlob" come from but even with just accessing the field and cast it back as byte array should be enought and by mixing with @mousetrap-systems answer should give you something like :
byte[] result = (byte[])rdr["data"] ;
MemoryStream ms = new MemoryStream(result);
List<int> blob = boisSerializer.Deserialize<List<int>>(ms);