Page rating	88% for 9 votes	Useless Brilliant

Introduction

Microsoft Access does not provide a method of storing image or BLOB data. There are those who say the database should not contain this type of information but we will not get into that here. This can be done with SQL Server and is useful when allow users to upload images or data to a web server. Here we will cover how to convert a BLOB from a byte array into a Unicode string that can be stored as a memo in the database

Limitation

Microsoft Access help says this about memo field size. Up to 65,535 characters. (If the Memo field is manipulated through DAO and only text and numbers [not binary data] will be stored in it, then the size of the Memo field is limited by the size of the database.) Does this mean more than 65,535 if you use ADO.NET and is this 65,535 bytes or Unicode characters. Someone should test this and add a comment below with the result.

How it works

The image or BLOB is presented to a method that converts a array of bytes to a string of Unicode characters replacing null with 0x100. With no embedded nulls the string can be stored as a string in the database. To recover the array the string in placed back into bytes and because the byte can not hold 0x100 it is converted back to null

/// <summary>
/// Build the byte array into a Unicode string replacing nulls
/// with byte 256 (0x100). This allows us to store byte arrays
/// in MS ACCESS database memo fields as long as they are less 
/// than 65,535 byte long.
/// </summary>
/// <param name="byData">Array of byte to convert</param>
/// <returns>Unicode string</returns>
public static string ConvertBlobToString( byte [] byData )
{
    if( byData == null )
        return "";
    StringBuilder sb = new StringBuilder( byData.Length );
    // Replace all the nulls with 0x100 Unicode character
    foreach( byte byVal in byData )
    {
        if( byVal == 0 ) 
            sb.Append( (char)0x100 );
        else
            sb.Append( (char)byVal );
    }
    return sb.ToString();
}

/// <summary>
/// Convert a Unicode string to an array of bytes inserting
/// nulls where 0x100 if found.
/// </summary>
/// <param name="sData">Unicode string to convert</param>
/// <returns>array with embedded nulls</returns>
public static byte [] ConvertStringToBlob( string sData )
{
    int nLength = sData.Length;
    byte [] byData = new byte[nLength];
    for( int n = 0; n < nLength; n++ )
    {
        byData[n] = (byte)sData[n];
    }
    return byData;
}

Conclusion

This code above could probably be optimised.