Saving multidimensional byte array to SQL Server database

Question

I want to save a multidimensional byte array to a SQL Server database.

I know how to save a byte array which is a image conversion to a database. For that the data type I used is image. But now I want to store another byte array which is multidimensional byte array byte [,] temp, which has two dimensions with x,y values.

I searched on internet and here, it is said that using VARBINARY format. All I want to know is if I save my multidimensional array in a VARBINARY datatype data column, will the values will be altered? Is it possible to receive the data back as multidimensional array again?

Answer 1

Yes, you will be able to get back your multi-dimensional array unaltered.

How can you do it? Using a Varbinary(max) field in Sql Server and saving into it the serialized multidimensional byte array. In order to get your array back, obviusly, you need to deserialize what you stored in the database.

Here is an example of how to do it:

public void TestSO()
{
    using (SqlConnection conexion = new SqlConnection())
    {
        using (SqlCommand command = new SqlCommand())
        {
            //This is the original multidimensional byte array
            byte[,] byteArray = new byte[2, 2] {{1, 0}, {0,1}};
            ConnectionStringSettings conString = ConfigurationManager.ConnectionStrings["ConnectionString"];
            conexion.ConnectionString = conString.ConnectionString;
            conexion.Open();
            command.Connection = conexion;
            command.CommandType = CommandType.Text;
            command.CommandText = "UPDATE Table SET VarBinaryField = @Content WHERE Id = 73 ";
            command.Parameters.Add(new SqlParameter("@Content", SqlDbType.VarBinary, -1));
            //Serialize the multidimensional byte array to a byte[]
            BinaryFormatter bf = new BinaryFormatter();
            MemoryStream ms = new MemoryStream();
            bf.Serialize(ms, byteArray);
            //Set the serialized original array as the parameter value for the query
            command.Parameters["@Content"].Value = ms.ToArray();
            if (command.ExecuteNonQuery() > 0)
            {
                //This method returns the VarBinaryField from the database (what we just saved)
                byte[] content = GetAttachmentContentsById(73);
                //Deserialize Content to a multidimensional array
                MemoryStream ms2 = new MemoryStream(content);
                byte[,] fetchedByteArray = (byte[,])bf.Deserialize(ms2);
                //At this point, fetchedByteArray is exactly the same as the original byte array
            }
        }
    }
}

Answer 2

As I know there is no appropriate data type in Microsoft SQL Server to store multidimensional arrays. However there are many ways to save information about array structure. Some of them:

1. create several columns of BINARY (fixed-length) data type and each row of your multidimensional array to appropriate column; in this case it is expected that count of rows in your array is constant;

2. store the whole array as one-dimensional array into single column of VARBINARY (variable-length) data type and store in separate column of INT data type the count of elements in each row of multidimensional array; in this case it is expected that count of elements in each row is the same (not jagged C# array); when reading array then you will be able to split elements by this length into separate rows of multidimensional array.