Storing .Net ArrayList in database

Question

I have a vb .net ArrayList that I would like to store in one field on my Ms SQL 2008 database. Is it possible to put it in and get it back out as an ArrayList?

I'm guessing i need to break down the Array some how and store it as a string, then rebuild it when reading back as i dont see any datatypes for sql that are for arrays. Which is a bummer for me!

Can anyone suggest the best way to do this?

Many thanks.

edit - The ArrayList stores a set of quote ID's. There is no limit to how big it is so it could hold 1 or 1001 different ID's

• user1090077: @Aliostad I edited the question. It holds a set of quote ID's

Answer 1

This is probably part of a one-to-many relationship. Say for example your ArrayList is storing strings, then create a separate table, where one of its fields in this string value. You can send the ArrayList as a param to your DALC class and loop through it there, adding one record at a time to the DB by calling a stored proceedure, etc.

Answer 2

You can use a serializer such as

var serializer = new XmlSerializer(typeof(ArrayList));
var buffer = new StringBuilder();
using(var tw = new XmlTextWriter(new StringWriter(buffer)){
 serializer.serialize(tw, myArrayList);
}
var xml = buffer.ToString();

BUT There are a few warnings:

• If you have a collection, this is often a sign you should make a many-to-one table but I will assume you know what you are doing and dont need that table
• When instantiating XmlSerializer, watch out for memory leaks. It may not apply in your case because you are using a built in type but this is something to be aware of: http://blogs.msdn.com/b/tess/archive/2010/05/05/net-memory-leak-xslcompiledtransform-and-leaked-dynamic-assemblies.aspx

Answer 3

Dim no As Integer() = {11, 22, 33, 44}
 Dim MS As New System.IO.MemoryStream
 Dim bin As New System.Runtime.Serialization.Formatters.Binary.BinaryFormatter
 bin.Serialize(MS, no)
 Dim bytes(MS.Length - 1) As Byte
 MS.Position = 0
 MS.Read(bytes, 0, bytes.Length)
 ....
 ...
 cmd.ComandText="insert into TableName values (@data)"
 cmd.Parameters.Add("@data", SqlDbType.Image, bytes.Length).Value = bytes

Source: http://www.daniweb.com/software-development/vbnet/threads/253410/save-array-to-database