Is there an easy way to store an array into a single column in a SQL Server CE database?

Question

I have reviewed possible answers here (for PHP, I think): http://www.lateralcode.com/store-array-database/ but I am unable to find a C#.net version of serialize/deserialize.

Would this be done the same as the way shown in my link, above, or is there a completely different approach I should be using, given the environment?

I just don't want to have a bunch of different columns for each of the 12 values in each of my 9 different arrays, so if there is another approach to achieve this (converting to byte[], etc.) I am more than willing to hear it.

If it helps any, the arrays will be simple string[] arrays.

Solution 1

Convert your string array into single String like given below:

 var a = String.Join(",",arrays); 

 //or aim is to provide a unique separator, 
 //i.e which won't be the part of string values itself.
 var a= String.Join("~~",arrays); 

and fetch it back like this:

var arr = a.Split(',');
//or split via multiple character 
var arr = a.Split(new string[] { "~~" }, StringSplitOptions.None);

Solution 2

Try this to seralize the array and create a column in the database of type Blob to store the byte array.

Serialization:

 if(array == null)
        return null;
 BinaryFormatter bf = new BinaryFormatter();
 MemoryStream ms = new MemoryStream();
 bf.Serialize(ms, array);

Deserialization:

String[] array = new String[10];        
BinaryFormatter bf = new BinaryFormatter();     
ms.Position = 0;        
array = (String[])bf.Deserialize(ms);

Comments

[Vladimirs]

With that approach is better to use separator that unlikely can be in one of array value like "|||" or something like that.

[VoidKing]

Can you show an example of deserialization using this method. Also, just to be sure, is this answer okay to use for sql-server-ce (using WebMatrix)?

[VoidKing]

Can you edit using "," as the separator, as that value could easily be natural text in the strings themselves, as they are stored from user input?

[Manish Mishra]

basically idea is to join your strings in a manner, that you can later, split it easily to get your original string array

[VoidKing]

Right, of course, I get that, I was just thinking of future viewers, but I suppose it's true that if they don't get that they should use a separator unique from their string values, there probably is no helping them, anyway, lol. Thanks for the answer, it seems obvious now, but I knew I had to make sure that my answer didn't violate any best practices or anything.

[VoidKing]

One thing, though, you may want to change your example code when using "Split()" that throws an error ("best overloaded method for Split has some invalid arguments").

[VoidKing]

Well, I say that, but after looking up the syntax I'm seeing pretty much what you're showing me, so I guess it's time to look at "my" values :)

[VoidKing]

Oh, I get what's going on, "Split()" only takes a single "char" as a value. I wonder how to "Split" on more than one char...

[VoidKing]

Finally got a chance to look at this. +1 for doing this through serialization/deserialization (concatenated strings may not work for everybody). I'd give another +1 if I could for showing me new methods I didn't know about! Thanks!

[VoidKing]

I was wondering if I was overlooking that. I do know what a 1 to many relationship is, but I haven't practiced that approach in so long. It would probably be beneficial to show an example of that here (for future viewers). This is a really good answer, considering the scenario.

[alvarorahul]

this Wikipedia article explains how to create a foreign key en.wikipedia.org/wiki/Foreign_key

[27k1]

I know this is old, but please don't use binary formatter, it is now classified as unsafe code.