Serializing a object with an image to be saved to SQL database

Question

I have the following object:

[Serializable]
public class ExampleImage
{
    public int ID { get; set; }
    public string Filename { get; set; }
    public byte[] Content { get; set; }
}

I store this in a List<ExampleImage> which I then pass to the following function to serialize it to a string:

static string SerializeObjectToXmlString(object o)
{
    System.Xml.Serialization.XmlSerializer serializer = new System.Xml.Serialization.XmlSerializer(o.GetType());
    System.IO.StringWriter writer = new System.IO.StringWriter();
    serializer.Serialize(writer, o);
    return writer.ToString();
}

I then pass this serialized string to a stored procedure in SQL2000 as an NTEXT which then handled for inserting it into the database:

SELECT * INTO #TempImages
FROM OpenXML(@iDoc, '/ArrayOfExampleImage/ExampleImage')
WITH ([Filename] VARCHAR(255) './Filename', [Content] IMAGE './Content')

The problem I am having is the image is getting trashed. The btye[] is not getting saved properly to the DB. The other fields are just fine. This is the first time I have attempt to send a binary data via XML to SQL so I am most likely doing something wrong at this point. Is my SerializeObjectToXmlString function the problem and it is not handling the serialization of a byte[] properly, maybe the OpenXML SQL function or even the fact that I am sending the XML in as an NTEXT param. I would expect the serialize function to encode the binary properly but I could be wrong.

Any idea what is the issue or maybe a better approach to saving a bunch of images at once?

Edit: I think what is happening, is the serializer is making the byte[] into a base64 string, which is then getting passed along to the stored proc as base64. I am then saving this base64 string into an Image field in SQL and reading it out as a btye[]. So I think I need to somehow get it from base64 to a byte[] before inserting it in my table?

Edit: I am starting to think my only option is to change the stored proc to just do 1 image at a time and not use XML and just pass in the byte[] as an Image type and wrap all the calls in a transaction.

Answer 1

Instead of serializing it to XML, I would serialize it to a byte[] and store that in varbinary(MAX) type field in your DB.

Answer 2

You could try converting it to base64, then saving it to TEXT field or something.

Answer 3

As Gaidin suggested, base64 is the best option. It's the usual way of writing binary data to XML. You can use the following code :

public class ExampleImage
{
    public int ID { get; set; }
    public string Filename { get; set; }

    [XmlIgnore]
    public byte[] Content { get; set; }

    [XmlElement("Content")]
    public string ContentBase64
    {
        get { return Convert.ToBase64String(Content); }
        set { Content = Convert.FromBase64String(value); }
    }
}

(by the way, the Serializable attribute has no meaning for XML serialization)