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.

Tephra

why don't you simply var a = String.Join(",",arrays); and store a in db? and fetch it back like var arr= a.Split(","); – Pearlene

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

Try to adapt this sample: sqlcebulkcopy.codeplex.com – Altigraph

@ManishMishra Is there anyway you could post as an answer? It seems like a really good approach and deserves to be an accepted answer, if it works (I don't see why it wouldn't, as long as something more like "|||" was used as a separator, instead of "," as Vladimirs suggested). – Tephra

Answer 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);

Pearlene

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? – Tephra

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

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. – Tephra

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"). – Tephra

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 :) – Tephra

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... – Tephra

Answer 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);

Singsong

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)? – Tephra

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! – Tephra

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

Answer 3

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.

From the above description, it looks like you are using an RDBMS.

The fact that you want to store multiple values in a single column screams of an issue with the design.

I concur that having separate columns may not be the way to go, especially if the number of items in each array could potentially change in the future.

Consider separating this data into a separate table and having a 1 to many mapping with your original table with a foreign key relationship

Commandment

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. – Tephra

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